JIYIK CN >

This article shows various ways to connect to PostgreSQL using a password. It can be through the command line, pgpassa file, PGPASSWORDan environment variable or a connection string.

Connecting to PostgreSQL with a password using the command line interface (CLI)

If you have PostgreSQL installed on your machine, you can try different ways to connect to the database. A simple way is to type it in the command line psql, which will ask you for adminthe user's password.

If you just type without mentioning the username in the command psql, you will be asked for the PostgreSQL admin user password as shown below.

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

If you want to log in using a different account, you need to use the flag -Uand then provide the username as shown below.

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 user postgres's password. Logging in is a two-step process.

First, psqlyou define the username in the command, and then when the CLI asks, you enter the password. But we can connect to PostgreSQL directly using a single line of command.

To do this, we need to set environment variables PGPASSWORD.

In Windows:

C:\Users\Admin>set PGPASSWORD=root

In Linux:

export PGPASSWORD=root

If we want to connect to psql, it will PGPASSWORDget the password from , 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 does not require a password to connect psql. Learn more about PostgreSQL environment variables from here .

pgpass.confDefine the password to log in to PostgreSQL in the file

In the PostgreSQL APPDATAfolder, there is a pgpass.conffile called . You can define your password there.

passfileAdditionally, you can define your password file using the connection parameters . The file format is as follows.

hostname:port:database:username:password

This structure is mentioned on the PostgreSQL official page here .

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

Connecting to PostgreSQL using a connection string with a password

When you get localhostan online database or a remote database that is not in , you need to connect through a connection string. The connection string contains a username, password, database name, port, and host address.

The format of the connection string is as follows.

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

If you use PostgreSQL localhost, you don't need to use it for remote databases sslmode. This method is most useful when using a PostgreSQL database in a Python, C++, or Java project.

So, the command to connect to the database (local) using the connection string is as follows.

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 is a list of methods available for client authentication. It comes from the PostgreSQL official documentation.