Author Avatar



Share post:

All connection parameters can be defined as operating system environment variables and then just ‘psql’ can be run to connect to the database. A full list of such variables is given here.

Some variables are described below.

  • PGHOST behaves the same as the host connection parameter.
  • PGHOSTADDR behaves the same as the hostaddr connection parameter. This can be set instead of or in addition to PGHOST to avoid DNS lookup overhead.
  • PGPORT behaves the same as the port connection parameter.
  • PGDATABASE behaves the same as the dbname connection parameter.
  • PGUSER behaves the same as the user connection parameter.
  • PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using a password file (see Section 33.15).
  • PGPASSFILE behaves the same as the passfile connection parameter.
  • PGCHANNELBINDING behaves the same as the channel_binding connection parameter.
  • PGSERVICE behaves the same as the service connection parameter.

Let’s see this in action. First, make a new connection to the client terminal and remove any other configurations which would be providing connection details implicitly. Then, find the connection details of the PostgreSQL cluster to which you need to connect. In this case, below are the details:

  • The server where PostgreSQL cluster is running –
  • The port on which PostgreSQL cluster is listening for new connections – 5433
  • The database which we want to connect to – appdb
  • The username in the cluster which we want to connect as – hr_user

Now, rather than giving all these details each time in connection details string of psql, we will define these as operating system variables and just run psql We can set the password too via PGPASSWORD but it is not recommended for security reasons. Setting PGPASSFILE will be a better approach to automate the input of the password. For now, we will not set the password at all so that psql will ask for the password in runtime.

export PGPORT=5433
export PGDATABASE=appdb
export PGUSER=hr_user

Next, instead of defining environment variables for each input, service can be defined, and then a single environment variable PGSERVICE can be set at the operating system level. Note that if the value assigned to PGSERVICE can not be resolved, it will not fall back to any other connection methods, and the connection will fail.

Let’s see that in action. Add a service entry to pg_service.conf file.

echo `pg_config --sysconfdir`/pg_service.conf
sudo touch /etc/postgresql-common/pg_service.conf
sudo chmod 777 /etc/postgresql-common/pg_service.conf

sudo cat <<EOT>> /etc/postgresql-common/pg_service.conf

export PGSERVICE=pgdb_jupiter

The connection can also be made without prompting for the password by setting the password in an OS variable ‘PGPASSWORD’.

export PGPASSWORD=app#123; psql -p 5433 -d appdb -U hr_user -h
Connecting using Service File
In-depth with psql

Leave a Comment

Your email address will not be published. Required fields are marked *