Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Configuring PsotgreSQL 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:\Users\postgres\AppData\Roaming\postgresql>cacls pgpass.conf
C:\Users\postgres\AppData\Roaming\postgresql\pgpass.conf NT AUTHORITY\SYSTEM:(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.


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL