An ALTER USER postgres PASSWORD 'postgres' fails when provisioning vagrant with Postgresql

Question

I'm provisioning a vagrant box with Postgresql.
It looks going well, but at the end, it cannot run an ALTER USER postgres PASSWORD 'postgres'; for a Permission denied.

Here's what I'm doing, and what I notice.

config.vm.provision "shell", privileged: true, path: "../common/150_Postgresql-Postgis.sh", args: ["15", "3", "postgres"] 

150_Postgresql-Postgis.sh:

sudo apt-get -y -qq install "postgresql-$1" "postgresql-client-$1" -o Dpkg::Progress-Fancy="0" sudo apt-get -y -qq install "postgis" "postgresql-$1-postgis-$2" "postgresql-$1-postgis-$2-scripts" -o Dpkg::Progress-Fancy="0" -o Dpkg::Use-Pty="0" POSTGRES_PORT="5432" POSTGRES_PASSWORD=$3 sudo -u postgres psql -f common/155_CreatePassword.sql -v password="'$POSTGRES_PASSWORD'" 

During it's installation, I see the following logs when the two apt-get are executed. It starts installing Postgresql 15 and its related Postgis,

default: Running provisioner: shell... default: Running: /tmp/vagrant-shell20240716-1139149-5w52kg.sh default: /tmp/vagrant-shell: line 1: w#!/bin/bash: No such file or directory default: 150 : Installation de Postgresql 15 et Postgis 3 [...] default: Preparing to unpack .../01-postgresql-client-common_261.pgdg120+1_all.deb ... default: Unpacking postgresql-client-common (261.pgdg120+1) ... [...] default: Selecting previously unselected package postgresql-client-15. default: Preparing to unpack .../09-postgresql-client-15_15.7-1.pgdg120+1_amd64.deb ... default: Unpacking postgresql-client-15 (15.7-1.pgdg120+1) ... default: Selecting previously unselected package postgresql-15. default: Preparing to unpack .../10-postgresql-15_15.7-1.pgdg120+1_amd64.deb ... [...] default: Setting up postgresql-15 (15.7-1.pgdg120+1) ... default: Creating new PostgreSQL cluster 15/main ... default: /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main --auth-local peer --auth-host scram-sha-256 --no-instructions default: The files belonging to this database system will be owned by user "postgres". default: This user must also own the server process. [...] default: fixing permissions on existing directory /var/lib/postgresql/15/main ... ok default: creating subdirectories ... ok [...] default: Selecting previously unselected package postgis. default: Preparing to unpack .../48-postgis_3.4.2+dfsg-1.pgdg120+1_amd64.deb ... default: Unpacking postgis (3.4.2+dfsg-1.pgdg120+1) ... default: Selecting previously unselected package postgis-doc. default: Preparing to unpack .../49-postgis-doc_3.4.2+dfsg-1.pgdg120+1_all.deb ... default: Unpacking postgis-doc (3.4.2+dfsg-1.pgdg120+1) ... default: Selecting previously unselected package postgresql-15-postgis-3-scripts. default: Preparing to unpack .../50-postgresql-15-postgis-3-scripts_3.4.2+dfsg-1.pgdg120+1_all.deb ... default: Unpacking postgresql-15-postgis-3-scripts (3.4.2+dfsg-1.pgdg120+1) ... default: Selecting previously unselected package postgresql-15-postgis-3. default: Preparing to unpack .../51-postgresql-15-postgis-3_3.4.2+dfsg-1.pgdg120+1_amd64.deb ... default: Unpacking postgresql-15-postgis-3 (3.4.2+dfsg-1.pgdg120+1) [...] 

Then it immediately adds an installation of Postgresql 16.

default: Selecting previously unselected package postgresql-client-16. default: Preparing to unpack .../52-postgresql-client-16_16.3-1.pgdg120+1_amd64.deb ... default: Unpacking postgresql-client-16 (16.3-1.pgdg120+1) ... default: Selecting previously unselected package postgresql-16. default: Preparing to unpack .../53-postgresql-16_16.3-1.pgdg120+1_amd64.deb ... default: Unpacking postgresql-16 (16.3-1.pgdg120+1) ... default: Selecting previously unselected package postgresql-16-postgis-3-scripts. default: Preparing to unpack .../54-postgresql-16-postgis-3-scripts_3.4.2+dfsg-``` [...] 

155_CreatePassword.sql:

ALTER USER postgres PASSWORD :password; 

The vagrant box creation fails with:

default: Building PostgreSQL dictionaries from installed myspell/hunspell packages... default: Removing obsolete dictionary files: default: Processing triggers for fontconfig (2.14.1-4) ... default: psql: error: common/155_CreatePassword.sql: Permission denied default: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres" default: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres" The SSH command responded with a non-zero exit status. Vagrant 

Is the sudo -u causing a problem, preventing the ALTER USER postgres PASSWORD :password; from being executed?

Or is it its attempt to install two different versions of Postgresql at the same time?

Answer 1

I've eventually found a solution. It's more a workaround that a clean solution. But it works.

POSTGRES_PASSWORD=$3 sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '"$POSTGRES_PASSWORD"';" 

• replacing su -u by sudo -u

• PASSWORD '"$POSTGRES_PASSWORD"' I had to write it that way →
  it expands to ALTER USER postgres WITH PASSWORD 'postgres'; that psql -c expects.
  (and I was missing a -c on my psql command...)

• entering sudo -u postgres I wasn't able to find my common/155_CreatePassword.sql file anymore. It was out of reach or out of rights. I had to copy the SQL statement that was within.