In this tutorial, we will learn everything about PostgreSQL user authentication in the PostgreSQL server.
Table of Contents
How the PostgreSQL user authentication is done when you login to the database?
By default, when PostgreSQL server is installed, a user called Postgres and a database called Postgres is created.
There will be two more databases called template0 and template1 are created by default, but we limit the post with user authentication only.
If you want to login to the Postgres database with postgres user we simply query psql.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[postgres@postgreshelp data]$ psql psql (9.6.8) Type "help" for help. postgres=# postgres=# select current_user; current_user -------------- postgres (1 row) postgres=# |
But, what is the default password for Postgres user?
The answer is simple, there isn’t be any default password for Postgres user.
The first thing we need to do after the database is created is to set the password for the Postgres user.
How to Change a Password for PostgreSQL user?
use the following command to change/set the password for your current user
\password
|
1 2 3 4 |
postgres=# \password Enter new password: Enter it again: postgres=# |
But the question is how did it login without prompting for the password or even without setting a password?
The reason why it didn’t ask for a password is its authentication method.
The authentication method configuration will be there in pg_hba.conf file under the data directory.
The default authentication method for PostgreSQL server is either be ident or peer
There are two more authentication methods which are widely used are trust and md5
Let’s understand each of them in detail.
PostgreSQL User Authentication types:
Peer Authentication:
Obtain the client’s operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections.
|
1 2 3 4 5 |
[postgres@postgreshelp ~]$ psql -U postgres psql (9.6.8) Type "help" for help. postgres=# |
Here, as the database username and the OS username are same, the peer authentication method used OS credentials and logged in successfully.
If I use a database user other than Postgres it throws an error.
psql: FATAL: Peer authentication failed for user
|
1 2 3 4 5 6 |
postgres=# create user testing ; CREATE ROLE postgres=# \q [postgres@postgreshelp ~]$ psql -U testing <strong>psql: FATAL: Peer authentication failed for user "testing"</strong> [postgres@asristgdb ~]$ |
Here, as testing user is not there at OS level my authentication failed.
How to resolve the issue psql: FATAL: Peer authentication failed for user
To resolve the above issue, we have to map the operating system user name to a database user.
User Name Mapping :
username mapping can be done in two steps.
STEP 1: Add a mapping configuration in pg_ident.conf file.
STEP 2: specify map=map-name in the options field in pg_hba.conf.
|
1 2 3 4 5 6 7 8 9 10 11 |
STEP 1: [postgres@postgreshelp data]$ cat pg_ident.conf MAPNAME SYSTEM-USERNAME PG-USERNAME postgres-testing postgres testing STEP 2: [postgres@postgreshelp data]$ cat pg_hba.conf local all all peer map=postgres-testing |
Reload the Postgres server
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# \q [postgres@postgreshelp ~]$ psql -U testing -d postgres psql (9.6.8) Type "help" for help. postgres=> |
Trust Authentication
Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.
The following is the line mentioned in pg_hba.conf for local authentication
|
1 |
local all all trust |
Here, irrespective of the password given, the postgres server allows the user to login.
|
1 2 3 4 5 |
[postgres@postgreshelp data]$ psql -U testing -d postgres psql (9.6.8) Type "help" for help. postgres=> |
md5 Authentication
Require the client to supply a double-MD5-hashed password for authentication.
The following is the line mentioned in pg_hba.conf for md5 authentication
|
1 |
local all all md5 |
How does it work?
|
1 2 3 4 5 6 7 |
postgres=# create user md5user; CREATE ROLE postgres=# \q [postgres@postgreshelp data]$ psql -U md5user Password for user md5user: <strong>psql: FATAL: password authentication failed for user "md5user"</strong> [postgres@asristgdb data]$ |
Here, as I have not supplied any password while creating the user, my login failed.
After changing the password for the md5user I could log in to the database.
|
1 2 3 4 5 6 7 8 |
postgres=# alter user md5user password 'md5user'; ALTER ROLE [postgres@postgreshelp data]$ psql -U md5user -d postgres Password for user md5user: psql (9.6.8) Type "help" for help. postgres=> |
ident Authentication
Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead.
ident works similar to peer authentication.
|
1 2 3 4 5 6 7 8 9 10 |
[postgres@postgreshelp data]$ psql -d postgres -U md5user -h 10.10.12.143 <strong>psql: FATAL: Ident authentication failed for user "md5user"</strong> [postgres@postgreshelp data]$ psql -d postgres -U md5user <strong>psql: FATAL: Peer authentication failed for user "md5user"</strong> [postgres@asristgdb data]$ [postgres@postgreshelp data]$ cat pg_hba.conf | grep ident local all all ident host all all ident [postgres@asristgdb data]$ |
More about ident authentication method can be found here.
Conclusion:
Depending on the environment needs, we either set trust or md5 as our default authentication method.
Peer and ident methods are more dependent on OS usernames, so we don’t use these authentication methods.
Do you use ident or peer authentication in your environment?
or
What authentication method you follow the most?
Please share your thoughts by commenting below.
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
Very Informative. Thank you!