Postgresql – How to set password for PostgreSQL database with a script

linuxpostgresqlscripting

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?

Best Answer

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.)

Related Question