0 like 0 dislike
102 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (4k points)  

Assign the values to Greenplum environmental variables and export value in bash shell. You can provide the below statement in .bashrc file which is present in user $HOME 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 Greenplum database using a PostgreSQL-compatible psql client. Users can always connect to the Greenplum database via masters; the segments cannot accept any client connection. Segments can only store user data and process the query distributed by the masters.

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

Option 1. Export Greenplum Database Environmental Variables

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

Environmental Variable Description
PGHOST The host name of the Greenplum Database master. This is required if you are connecting Greenplum 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 Greenplum Database master instance is running on. Default 5432 .
PGUSER Greenplum Database user name to connect. Typically, Developer, Tester and PROD users will be different.
PGPASSWORD Greenplum Database user password.

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

For Example, follow below steps.

[gpadmin@gpdb-training ~]$ export PGDATABASE=training 
[gpadmin@gpdb-training ~]$ export PGHOST=GPTRAINING01
[gpadmin@gpdb-training ~]$ export PGPORT=5432 
[gpadmin@gpdb-training ~]$ export PGUSER=user1 
[gpadmin@gpdb-training ~]$ export PGPASSWORD=XXXXX 
[gpadmin@gpdb-training ~]$ 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 Greenplum 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.

[gpadmin@gpdb-training ~]$ pwd 
/home/gpadmin 
[gpadmin@gpdb-training ~]$ chmod 600 .pgpass
[gpadmin@gpdb- training ~]$ ls -la .pgpass 
-rw------- 1 gpadmin gpadmin 31 Apr 21 10:55 .pgpass 
[gpadmin@gpdb- training ~]$ more .pgpass 
*:5432:training:user:password

Whenever connection requires the password, Greenplum 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 Greenplum database master with no password prompt.

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

Related questions

0 like 0 dislike
1 answer
...