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 – 192.168.0.111
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.
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.
admin
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.
PGHOSTbehaves the same as the host connection parameter.PGHOSTADDRbehaves the same as the hostaddr connection parameter. This can be set instead of or in addition toPGHOSTto avoid DNS lookup overhead.PGPORTbehaves the same as the port connection parameter.PGDATABASEbehaves the same as the dbname connection parameter.PGUSERbehaves the same as the user connection parameter.PGPASSWORDbehaves 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).PGPASSFILEbehaves the same as the passfile connection parameter.PGCHANNELBINDINGbehaves the same as the channel_binding connection parameter.PGSERVICEbehaves 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:
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
psqlwill ask for the password in runtime.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.conffile.The connection can also be made without prompting for the password by setting the password in an OS variable ‘PGPASSWORD’.