How to Connect to PostgreSQL With Password

1. Use Command-Line Interface (CLI) to Connect to PostgreSQL With Password
2. Define Password in pgpass.conf File to Log-In to PostgreSQL
3. Use the Connection String With Password to Connect to PostgreSQL

This article shows various methods for connecting PostgreSQL with a password. It can be via command-line, pgpass file, PGPASSWORD environment variable or the connection string.

Use Command-Line Interface (CLI) to Connect to PostgreSQL With Password

If you have PostgreSQL installed in your machine, you can try different methods to connect to the database. One simple method is to type psql in the command line, and it will ask you the password for the admin user.

If you type only psql without mentioning the username in the command, the system will ask you the admin user password for the PostgreSQL, like the following.

C:\Users\Admin>psql
Password for user Admin:

If you want to log in with a different account, you need to use a flag -U, then after that, provide the username, like the following.

C:\Users\Admin>psql -U postgres
Password for user postgres:
psql (14.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Here, the CLI asks for the password for user postgres. It takes two steps to log in.

First, define the username in the psql command, then when the CLI asks, you put the password. But we can use one line command to directly connect to the PostgreSQL.

For this, we need to set the environment variable PGPASSWORD.

In Windows:

C:\Users\Admin>set PGPASSWORD=root

In Linux:

export PGPASSWORD=root

If we want to connect to the psql, it will take the password from PGPASSWORD, which is now available in the environment.

C:\Users\Admin>psql -U postgres
psql (14.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

You see, now it’s not asking for a password to connect to the psql. Learn more about the environment variables of PostgreSQL from here.

Define Password in pgpass.conf File to Log-In to PostgreSQL

In the APPDATA folder for PostgreSQL, there is a file named pgpass.conf. You can define your password there.

Also, you can define your password file using the connection parameter passfile. The format of the file is below.

hostname:port:database:username:password

This structure is mentioned on the PostgreSQL official page here.

It knows which database the user is valid and the password for that specific user.

Use the Connection String With Password to Connect to PostgreSQL

You need to connect via a connection string when you get a database online or remote that’s not in localhost. Connection string contains a segment of username, password, database name, port and host address.

The format of the connection string is below.

postgresql://<username>:<password>@<host><port>/<database_name>?sslmode=require

You don’t need to use the sslmode for a remote database if you use the localhost. This method is the most useful when using the PostgreSQL database in your Python, C++ or Java project.

So, the command to connect the database (local) with the connection string is below.

C:\Users\Admin>psql postgresql://postgres:root@localhost:5432/postgres
psql (14.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Here’s a list of methods you can use for client authentication. It’s from the PostgreSQL official documentation.