Connecting from psql in different ways

One can connect to the database in various ways from psql. One can pass the whole connection string or connection details on the psql command line. Let’s go through such methods of connection.

Method 1: Connecting using individual connection options.

In this method, the password can not be given as part of the connection options.

Create a new database and user for connection purposes. We will go through the database and user creation in detail later.

$ psql

postgres=#

create database appdb;
\pset pager off
\l+
create user hr_user with encrypted password 'hr#123';
\du
\q

Connect to the database with required inputs.

psql -p 5433 -d appdb -U hr_user -h 192.168.0.111

$ psql -p 5433 -d appdb -U hr_user -h 192.168.0.111
 Password for user hr_user:
 psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
 SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
 Type "help" for help.
 appdb=>

Running psql with -W option will always ask for the password. But, even if this option is not used and the authentication doesn’t succeed with any other methods internally (for example operating system authentication etc), then the password will be asked automatically, as seen above.

psql -p 5433 -d appdb -U hr_user -h 192.168.0.111 -W

$ psql -p 5433 -d appdb -U hr_user -h 192.168.0.111 -W
Password:
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

appdb=>

Running psql with -w option doesn’t ask for a password. But, if the authentication doesn’t succeed as per the given inputs then the connection will fail without asking for the password.

psql -p 5433 -d appdb -U hr_user -h 192.168.0.111 -w

$ psql -p 5433 -d appdb -U hr_user -h 192.168.0.111 -w
psql: error: fe_sendauth: no password supplied

Method 2: Connecting using key-value pair connection options.

In this method, optionally, the password can be given as part of the connection options.

Option -d specifies all the connection options in a set of key-value pairs.

psql -d "host=191.168.0.111 port=5433 dbname=appdb user=hr_user connect_timeout=10"

psql -d "host=191.168.0.111 port=5433 dbname=appdb user=hr_user connect_timeout=10"
Password for user hr_user:
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

appdb=>

In the below example, the password is given in connection options itself.

psql -d "host=127.0.0.1 port=5433 dbname=appdb user=hr_user password=##### connect_timeout=10"

psql -d "host=127.0.0.1 port=5433 dbname=appdb user=hr_user password=##### connect_timeout=10"
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

appdb=>

Method 3: Connecting using connection string or URI.

The connection can also be made using ‘connection string or URI‘ as shown below. Here, postgresql: or postgres:, both will work.

psql postgresql://[userspec@][hostspec][/dbname][?paramspec]
where userspec is:

user[:password]

and hostspec is:

[host][:port][,...]

and paramspec is:

name=value[&...]

A few connection string examples are given below. The password can also be given as part of the connection string itself.

psql postgresql://hr_user@127.0.0.1:5433/appdb?'connect_timeout=10&ssl=true'

psql postgresql://hr_user:password@127.0.0.1:5433/appdb?'connect_timeout=10&ssl=true'

psql postgres://hr_user@127.0.0.1:5433/appdb?'connect_timeout=10&ssl=true'

psql postgresql://hr_user@127.0.0.1:5433/appdb?'connect_timeout=10&keepalives=2&application_name=myapp'

Another example is here. In this connection string, ‘connect_timeout’, ‘keepalives’ and ‘application_name’ are connection parameters while ‘synchronous_commit’ is a PostgreSQL cluster parameter that can also be set at the transaction, session, user, database, or cluster level. Here, ‘synchronous_commit’ will get set at the session-level.

psql postgresql://hr_user@127.0.0.1:5433/appdb?'connect_timeout=10&keepalives=2&application_name=myapp&options=-c%20synchronous_commit%3Doff'

All possible connection parameters can be found here.

The connection URI needs to be encoded with percent-encoding if it includes symbols with special meaning in any of its parts. Here is an example where the equal sign (=) is replaced with %3D and the space character with %20:

psql postgresql://hr_user@127.0.0.1:5433/appdb?options=-c%20synchronous_commit%3Doff

The host part may be either a hostname or an IP address. To specify an IPv6 address, enclose it in square brackets:

psql postgresql://hr_user@[fe80::9d98:6f02:5287:db7e]:5433/appdb?options=-c%20synchronous_commit%3Doff