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

Question

During the installation of my application, I want to create a PostgreSQL database and some tables and functions.

For this purpose, I use PSQL.EXE, which comes with PostgreSQL. I have 2 scripts. The first creates a database and the corresponding user who has the rights to execute scripts in this database. I want to execute a second script like this just created user. Unfortunately, I cannot find a way to pass the password for this user as a command line argument. The omission of the password will stop execution and will prompt the user to enter a password that I would like to avoid, since this is done during the installation of my application.

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

To explain the environment a little further. I am using WiX 3.5 setup as "MSI-Builder".

Answer 1

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

SET PGPASSWORD=my_very_secret_password psql somedb someuser 

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

Answer 2

You cannot provide a password through cmdline arg (and do not want this bad security practice).

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

Answer 3

Even better, if you have access to create the db role, you already have all the access that does not require a thorough login with a password. Let the second script run under the same user as the first, but include the following line to switch the user:

 set role my_new_user; 

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

If you only split scripts because of different logins, then with this they can go to the same file and just switch the role in the middle.

---

Note: Otherwise, you are not creating a database and a new superuser role, it can be a little more complicated. If so, you need to create a new role using:

 create role my_new_role ... ADMIN my_role; 

Where my_new_role is the role you are creating and my_role is your current user. Then, when you are done, simply:

 revoke my_new_role from my_role;