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.
Read:
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.