Postgresql – Set up a postgres database with a specific role without password prompt

passwordpostgresqlrole

Context

I have a PostgreSQL server (localhost:5432) which holds a database called my_database.

I have created long time ago, a simple role which owns this database; my_user, different from the postgres role;

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 my_user   | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This manually created role does not have an OS equivalent user, like postgres does.

I have a bash script to reinitialize my_database with a set of schemas and tables (from the create_db.sql file);

#!/bin/bash
set -e
dropdb -U my_user my_database || true
createdb -U my_user my_database --owner=my_user
psql -U my_user -d my_database -h localhost -v ON_ERROR_STOP=1 -a -f create_db.sql

Issue

Since a few days, this script is asking me 3 times the password for my_user. I guess one time per line.
Before that, I was able to run it without any prompt for any password. That was really cool.

The only thing I changed in between was this;
I usually connect to this server using pgadmin3 with the postgres user to checkout what's inside my tables.
In pgadmin3, I once set up this server credentials to my_user and its password to checkout some differences with the postgres login.
Then, always in pgadmin3, I switched back the credentials to postgres as they were before.

Question

I cannot understand why this would have broken something which had since then made my script asking for password…
It may be related to something other, but I really don't know what?

I also tried to set up a .pgpass file with this line; 

localhost:5432:my_database:my_user:my_user_password

and changing its access to 0600 as stated in the documentation.

But it doesn't seem to be recognized; the bash script is still asking 3 times for my_user password.
And, as I (the ubuntu normal OS user, which is not the same as the role my_user which owns my_database) owns this file , I wonder if I should set up permissions to this file to the postgres OS user instead?

Anyway, this .pgpass file was empty before, when I was able to reset my_database without any password prompt. So if I can recover the previous behavior it would be nice, except if this is not a good practice.

Any clue would be appreciated as I'm not much used to database management.

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

EDIT

I just noticed that if I set the .pgpass file with this content;

*:*:*:my_user:my_user_password

it works (it stops asking for a password).

But with the database name;

*:*:my_database:my_user:my_user_password

it doesn't (it keeps asking for a password).

Best Answer

You edited away the problem.

In your comments you say that the database is really projATX_db. At least that is what you think. But PostgreSQL translated all unquoted text to lower case, so when you run

CREATE DATABASE projATX_db;

the database is really called projatx_db

Now the entries in the password file are not subject to this lower case conversion (that is specific to SQL), so projATX_db in the password file does not match your database.

Related Question