0 like 0 dislike
151 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4k points)  

Assign the values to postgres environmental variables and export value in bash shell. You can provide the below statement in .bashrc file which is present in user  /opt/PostgreSQL/9.3/pg_env.sh  directory. Values will be set everytime you login to system.

export PGHOST=master_host_name/IP address
export PGPORT=port. 
export PGUSER=username
export PGPASSWORD=password
export PGDATABASE=database 

For Example :

Users can access postgres database using a PostgreSQL-compatible psql client. Users can always connect to the postgresdatabase 

Couple of options available to set up connection with no password prompt.

Option 1. Export postgres Database Environmental Variables

In order to access postgres database with no password prompt, you need to set up some environmental variables.

Environmental Variable Description
PGHOST The host name of the postgres Database master. This is required if you are connecting postgres database from edge node or other systems.
PGDATABASE The name of the database to which you want to connect
PGPORT The port number that the postgres Database master instance is running on. Default 5432 .
PGUSER postgres Database user name to connect. Typically, Developer, Tester and PROD users will be different.
PGPASSWORD postgres Database user password.

You have to export the above mentioned system variables in Unix/Linux bash shell.

For Example, follow below steps.

$ export PGDATABASE=training 
$ export PGHOST=GPTRAINING01
$ export PGPORT=5432 
$ export PGUSER=user1 
$ export PGPASSWORD=XXXXX 
$ psql 
psql (8.2.15) 
Type "help" for help. 
training=>

Option 2. Use a .pgpass file to store the password

The other option to access postgres database with no password prompt is use of .pgpass file. The file .pgpass in a user’s home directory or the file referenced by PGPASSFILE (set password file location to PGPASSFILE environmental variable) can contain passwords to be used if the connection requires a password.

File should contains information in following line format. You can even add comments to the file by preceding it with #.

hostname:port:database:username:password

For instance, below is the location of file to be used when connection requires password.

$ pwd 
/opt/PostgreSQL/9.3/data
$ chmod 600 .pgpass
$ ls -la .pgpass 
-rw------- 1 postgres postgres 31 Apr 21 10:55 .pgpass 
 more .pgpass 
*:5432:training:user:password

Whenever connection requires the password, postgres database get it from .pgpass file stored in particular user $HOME directory.

There are other couple of options such as trusted authentication and use of connection URI available to connect postgres database with no password prompt.

The above mentioned options are easy to set up and manage.

Related questions

0 like 0 dislike
1 answer
...