How to Login to PostgreSQL Without Password

By default, PostgreSQL requires you to enter password, every time you login. This can be tedious and can be avoided, if you are working with local databases. Often we may need to login to PostgreSQL without password, that is, employ a passwordless login. This is especially true if you are running a local PostgreSQL server. In this article, we will learn how to login to PostgreSQL without password.

How to Login to PostgreSQL Without Password

There are different ways to setup passwordless login in PostgreSQL.

One of the simplest ways to enable passwordless login is to create a file .pgpass in the user’s home directory with the following connection information.

hostname:port:database:username:password

Each of the first 4 values can be specific string, or * to match all values. If any of the above entries contains : or \ then you can escape it with \ character. If you are connecting to local database, you can keep hostname as localhost.

The .pgpass can be created in user’s home (~/.pgpass) or in location specified by PGPASSFILE in PostgreSQL config file. You can also create it with the following command.

$ vi ~/.pgpass

Also on Linux systems, you need to set the file permission for pgpass file as shown below, else it will be ignored.

$ chmod 0600 ~/.pgpass

On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf

Alternatively, you can also set PGPASSWORD environment variable to your PostgreSQL connection string.

PGPASSWORD=<password> psql -U <username> <database_name>

Please note, if you run the above command on terminal, then it will work only as long as the session exists. If you want to permanently set this environment variable, you need to add the above line to ~/.profile file (or /etc/profile, ~/.bash_profile, ~/.bash_login, ~/.bashrc) which is loaded automatically on every session.

In this article, we have learnt a couple of simple ways to enable passwordless login.

Also read:

How to Store PostgreSQL Output to File
How to Get Row Count for All Tables in MySQL
How to Get Row Count for All Tables in PostgreSQL
How to Select Every Nth Row in PostgreSQL
How to Insert Text With Single Quotes in PostgreSQL