Logging into PostgreSQL without password prompt

Postgresql is a powerful opensource database software that is very useful as both
a production and development database management software.
In this tutorial we will explore three ways we can log into a postgresql database without password prompt.

By default, when logging into postgresql using psql, you will encounter the fatal error of authentication.

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"

How do you rectify this if you don’t know the password or you are doing an automation task. There are three main ways.

Use default postgres super user to login

sudo -u postgres psql

Per your configuration when the above command is used, you are switching to the postgres as the user with your current whoami password to access postgres.

The above option will prompt you for a password, and this password is your actual password when you login into your system. It is your sudo password.

If I check the connection info or who is connected, you will see that the current user is postgres and is connected to the default postgres database

Now let us check the other methods if you are not a sudo user.

Configure the Host-Based Authentication Via the pg_hba.conf file

The pg_hba.conf file stands for the postgres host-based authentication which is a configuration file that controls how we connect and authenticate to a postgresql service. The simplest method for finding the location of the pg_hba.conf file when you are a sudo user is

sudo -u postgres psql -c "SHOW hba_file;"

This is where you configure how postgres authenticates for both localhost and remote (host).

There are many options here which includes

• trust
• peer
• md5
• password
• ident
• scram-sha-256
• etc

The METHOD can be “trust”, “reject”, “md5”, “password”, “scram-sha-256”, “gss”, “sspi”, “ident”, “peer”, “pam”, “ldap”, “radius” or “cert”. Note that “password” sends passwords in clear text; “md5” or “scram-sha-256” are preferred since they send encrypted passwords.

When we check the pg_hba.conf our local admin login is postgres and the authentication method is set to peer hence the peer authentication error

In order to not get a prompt when logging in using the psql -U postgres command, you will need to set it to trust as below

After making the changes, you will have to restart your postgresql service using

sudo service postgresql restart
#or
sudo systemctl restart postgresql

We have now fixed the error for the authentication.

Since we only modified the local unix domain socket connection only the above will work. However when we use psql -U postgres -h localhost, it will fail as we did not change the host under IPv4 local connections to trust.

Lets modify that and restart.

Upon logging in using psql -U postgres -h localhost we will be able to have access without password prompt

Logging In Via Postgres URL

You can also login via a postgres url . This is useful if you are working with other web frameworks and you want to connect via a DATABASE_URL eg in Django or Flask. The general syntax is below

psql postgres://USERNAME:PASSWORD@IP_ADDRESS_OR_HOST:PORT/DATABASE_NAME

In our case without password prompt we will omit the password section and it will be connected directly

psql postgres://postgres@localhost:5432/postgres

We have seen how to login to Postgres without password prompt via the pg_hba.conf file. Let us check the other two options

Exporting the Password in Your Environment Variable

You can also export or set your Postgres password via the PGPASSWORD variable. This is useful when you are on a trusted device and you dont want to be entering the password every time. All the configurations in the pb_hba.conf file must be set to either peer or md5 or scram-sha-256.

export PGPASSWORD=yourpassword

Then you can run psql -U postgres and you will be logged in without password prompt. Postgresql service will automatically detect the password from the environment variable that you have exported. This method is not the most securest way.

Using a pgpass file

The third method involves the use of a .pgpass file. Posgresql allows us to use a .pgpass file that contains our credentials to login in to a database.

#hostname:port:database:username:password
localhost:5432:postgres:postgres:password123

You will have to set the permission on the file in order for it to work appropriately.

chmod 600 .pgpass

We have seen three methods on how to login to PostgreSQL without password prompt. I hope this was helpful.

Thanks For your attention

Jesus Save

By Jesse E.Agbe(JCharis)