PostgreSQL – .pgpass file


.pgpass file in a user’s home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user’s profile).

This file should contain lines of the following format:
hostname:port:database:username:password

You can follow below steps to connect to PostgreSQL or PostgreSQL compatible tool or database systems.

Step 1: Created the .pgpass file. Below command will create the hidden .pgpass file in the home directory.

vi ~/.pgpass

Step 2: Add the connection details with the instnace, port, database, user and password information in the below format. You can also use the wild card character like * as well.

PostgreSQLInstance1:5432:mydatabase:myuser:mypassword
*:*:mydatabase:myuser:mypassword

Step 3: On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. Changed file mode to 600 as below

chmod 600 ~/.pgpass

Step 4: Export the PGPASSFILE file
export PGPASSFILE=~/.pgpass

Step 5: Test the connection. PGSQL -w (lower case) option will not prompt for password and will connect using the password from the .pgpass file.

Example:
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”

id
—-
3
1
2

(3 rows)

PGSQL -W (upper case) will prompt for the password even specified in .pgpass file.
Example :
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”
Password for user imuser:

id
—-
3
1
2

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s