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:

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.
C:UserspostgresAppDataRoamingpostgresql>cacls pgpass.conf
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
-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.


12 Replies to “Configuring .pgpass (Password File) on Windows/Linux/Solaris”

  1. Since 9.2 Postgres does not create (or require) a dedicated Windows user account any more.

Comments are closed.