PostgreSQL users access to the database goes through libpq library. It
also has a feature of allowing automation for users without prompting
password by keeping their details in .pgpass file. Lets see…
Points to be considered when setting .pgpass file.
1. It should be created in postgres user home directory.
2. It should disallow any access-level to world or group.
3. Location can be controlled with PGPASSFILE environment variable.
Syntax to follow in .pgpass file:
1. Open the command prompt as Postgres user. So, any files/directories
created will be owned by “postgres” user, no need to give explicit
runas /user:postgres "cmd"
Note: It prompts for OS postgres user password. It can also be set
from command prompt opened as administrator. Right-click on
"Command Prompt" option and choose "Runas Administrator".
Command to set pasword: "net user user_name new_password".
Switch to user home directory and create “postgresql” directory and
“pgpass.conf” file as below
notepad pgpass.conf //You can also check the file ownership from command line with “calcs” command.
C:\Users\postgres\AppData\Roaming\postgresql\pgpass.conf NT AUTHORITY\SYSTEM:(ID)F
Now connection will be via pgpass.conf file.
Procedure is same on both of the platforms.
su - postgres //this will land in the home directory set for postgres user
vi .pgpass.conf //enter all users entries
chmod 0600 .pgpass.conf // change the ownership to 0600 to avoid error like
WARNING: password file "/opt/PostgreSQL/9.2//.pgpass" has group or world access; permissions should be u=rw (0600) or less
Note: If the environment variable PGPASSWORD is set, then the ~/.pgpass
file is not read.