PostgreSQL - Create user and database from shell failing

Question

I have a shell script which runs on deployment and I have these lines:

# Database
createdb $DBNAME
createuser -D -A $DBNAME

However, in my logs I get this error:

• createdb: could not connect to database postgres: FATAL: Ident authentication failed for user "root"
• createuser: could not connect to database postgres: FATAL: Ident authentication failed for user "root"

Would anyone mind telling me what is going wrong here and how I can correct my lines. Surely root should have permission to do this?

Answer 1

the best way to do this, especially if you want your script to be portable, is:

su --login postgres --command "createdb $DBNAME"

this should be safer, more secure, and more portable than using -U. i do it this way in all my posgreSQL scripts. you might find it a useful technique. obviously it still needs to be run as a root user (e.g. with sudo).

Answer 2

If your DB is secured, you need to connect as a DB user, not as a user of the OS. For example:

createdb -U dbrootuser -W $DBNAME 

See this link for full syntax