How to set password for PostgreSQL database with a script?

Question

In a bash script I'm writing, I need to create a database and set a password that is generated automatically by my script.

The problem is I can't find a way to set up the password without user interaction.

I need something like this:

$ createdb mydb -p $(cat password_file)

Any ideas?

Answer 1

From the documentation:

createdb is a wrapper around the SQL command CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server.

This means that you can write a script which issues a command like

psql -f createdb.sql -v passwd=$passwd -v user=$user

Where createdb.sql contains something like

CREATE ROLE :user WITH LOGIN PASSWORD :passwd;
CREATE DATABASE mydb OWNER :user;
GRANT {some privileges on the newly created database and/or its objects};

This means that you will have a user for every database created.

(At the same time I have a mild suspicion that your problem is what riouj answered.)

Answer 2

In order to connect to your PostgreSQL instance and run the createdb script, which is a client, you can use a password file. This file is generally located at ~/.pgpass and must contain theses information :

hostname:port:database:username:password

If you want to use a different path for your password file, you can set the PGPASSFILE environment variable.

For security concerns, you can change its rights :

chmod 600 ~/.pgpass

If you don't want createdb to prompt you for any password, you can use the -w switch (or --no-password) and use the password file.

You can take a look at the documentation for these features :

• http://www.postgresql.org/docs/9.2/static/app-createdb.html
• http://www.postgresql.org/docs/9.2/static/libpq-pgpass.html