How to execute PostgreSQL script-file from command line without userinput / password

Question

During the installation of my app, I want to create a PostgreSQL-Database and some tables and functions.

For that purpose I use PSQL.EXE that ships with PostgreSQL. I have 2 scripts. The first one creates the database and a corresponding user that has rights to execute scripts on that database. I want to execute the second script as this just created user. Unfortunately I can't find a way to pass the password for that user as a command line argument. Omitting the password leads to a stop of execution and a prompt for the user to enter the password, which I would like to avoid - since this is executed during installtion of my app.

Is there any way to pass the password as argument or is there any other command line tool I could use?

To explain the environment a bit further. I use WiX 3.5 setup as a "MSI-Builder".

Karajan

Answer 1

You can either use a pgpass file as dbenhur answerd, or you can set the environment variable PGPASSWORD before calling psql:

SET PGPASSWORD=my_very_secret_password
psql somedb someuser

All supported environment variables are documented in the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html

Nava

it will not be so secret every one can see the password from environment variables. – Torp

Answer 2

You can't supply password via cmdline arg (and don't want to as that's poor security practice).

You can provide a .pgpass file to support automatic script authentication. Here's the docs.

Proconsul

How would I be able to supply that .pgpass file during an installation of my application with WiX on Windows? As far as I understood the linked doc, the file resides under %APPDATA%\postgresql\pgpass.conf And there will only be one file, which I would have to change in order to add my user that has been created through the first script. I hope there is a slicker way in doing that. – Karajan

@Karajan As the linked doc says, you can put a pgpass file anywhere you want and use the environment variable PGPASSFILE to tell psql (really libpq) where to find it. On unix the pgpass file must not be group or world accessible. On windows, postgres lib will assume you put it in a safe place but wont check or complain (so be smart and control permissions to the file). – Proconsul

Answer 3

For completion, you can also use URI (doc link)

List dbs

psql "postgresql://username:password@localhost/postgres" -l

I also crafted this command to have only names (please tell me if you know a better way):

psql "postgresql://username:password@localhost/postgres" -l | awk -F '|' '{print $1}'| sed -e '/^\s*$/ d' -e '1,3d'|sed '$d'|awk '{print $1}'

You can also use unix socket to connect:

# ss -x -a |grep postgres|awk '{print $5}'
/var/run/postgresql/.s.PGSQL.5432

Note that the parent directory of the socket is used:

# sudo -u postgres psql -d "postgresql:///postgres?host=/var/run/postgresql/" -l

You can only do this if you have this line in your pg_hba.conf:

local   all     postgres                ident

"ident" uses unix user for authent

dump a db

Here I added a different port number

pg_dump -Fc "postgresql://username:password@localhost:9001/${db}" > "backup_${db}.pgdump"

With dumpall you need a super user or role (with CREATE ROLE ... SUPERUSER). And it must have access to all DB. By default postgres can.

but in my case I couldn't use pg_dumpall with postgres because his password was removed by devs.

So I used:

 sudo -u postgres pg_dumpall -d "postgresql:///?host=/var/run/postgresql/" > all.dump

tested version

# cat /opt/postgresql/PG_VERSION
9.6

hth

Containment

Answer 4

Better still, if you have access to create the db role then you already have all the access you need without having to carefully log in with a password. Have the second script operate under the same user as the first but include the following line to switch user:

set role my_new_user;

Where my_new_user is the name of the role you want to run it as.

If you only divided the scripts because of the different logins then with this they can go in the same file and just switch role mid way through.

---

Note: On the off chance that you are not creating the DB and new role as a super user this may be a little more complex. If this is the case you will need to create the new role with:

create role my_new_role ... ADMIN my_role;

Where my_new_role is the role you're creating and my_role is your current user. Then when you're finished simply:

revoke my_new_role from my_role;

Vickey