Configuring .pgpass (Password File) on Windows/Linux/Solaris

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:
hostname:port:database:username:password
Eg:
localhost:5432:demo:dbuser:demo@123

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

cd %appdata%
mkdir postgresql
cd postgresql
notepad pgpass.conf  //You can also check the file ownership from command line with "calcs" command.
Eg:
C:UserspostgresAppDataRoamingpostgresql>cacls pgpass.conf
C:UserspostgresAppDataRoamingpostgresqlpgpass.conf NT AUTHORITYSYSTEM:(ID)F
                                                         BUILTINAdministrators:(ID)F
                                                         WIN-G3BNU2NQAI1postgres:(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 
-bash-3.2$ psql
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.

Regards
Raghav