postgres uses a database password or a user password

Question

I imported a postgres database in my local postgres server. I had to connect to the database (to allows django to retrive data) using the file called setup.local.

There is required to specify: DB_HOST=localhost, DB_NAME, DB_USER, DB_PASSWORD.

DB_HOST is localhost without any doubt. The DB_name is the one I choose importing (psql imported_db < downloaded_DB)

DB_USER is my_name (or I can change the owner ALTER DATABASE imported_db OWNER TO other_name).

The wire thing, for me, is that I have to use the user (either the my_name or other_name) password and not the database password (even if the variable name is DB_PASSWORD).

So the question: does a psql database have a password or just the roles/users have ones and use them to access the database?

Andrea

Answer 1

Passwords are set for USER and ROLE only. A user may access multiple databases, according to the GRANTs for the ROLE.

See also:

• https://www.postgresql.org/docs/10/static/ddl-priv.html
• https://www.postgresql.org/docs/10/static/client-authentication.html
• https://www.postgresql.org/docs/10/static/user-manag.html

Answer 2

DB_HOST=localhost is a key here. Look into the pg_hba.conf you will find ident against localhost connections most probably.

https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-IDENT

When ident is specified for a local (non-TCP/IP) connection, peer authentication (see Section 20.3.6) will be used instead.

https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-PEER

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.