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:
On Windows Platform:
1. Open the command prompt as Postgres user. So, any files/directories created will be owned by “postgres” user, no need to give explicit permissions.
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".
2. 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:UserspostgresAppDataRoamingpostgresqlpgpass.conf NT AUTHORITYSYSTEM:(ID)F
Now connection will be via pgpass.conf file.
On Solaris/Linux platform:
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.