Tag Archives: PostGreSQL

PostgreSQL – .pgpass file

.pgpass file in a user’s home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user’s profile).

This file should contain lines of the following format:
hostname:port:database:username:password

You can follow below steps to connect to PostgreSQL or PostgreSQL compatible tool or database systems.

Step 1: Created the .pgpass file. Below command will create the hidden .pgpass file in the home directory.

vi ~/.pgpass

Step 2: Add the connection details with the instnace, port, database, user and password information in the below format. You can also use the wild card character like * as well.

PostgreSQLInstance1:5432:mydatabase:myuser:mypassword
*:*:mydatabase:myuser:mypassword

Step 3: On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. Changed file mode to 600 as below

chmod 600 ~/.pgpass

Step 4: Export the PGPASSFILE file
export PGPASSFILE=~/.pgpass

Step 5: Test the connection. PGSQL -w (lower case) option will not prompt for password and will connect using the password from the .pgpass file.

Example:
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”

id
—-
3
1
2

(3 rows)

PGSQL -W (upper case) will prompt for the password even specified in .pgpass file.
Example :
psql -U imuser -h MySQLPgsql.sqldbpool.com myDB -p 5432 -w -c “select * from tb1”
Password for user imuser:

id
—-
3
1
2

Advertisements

Steps to install the PostgreSQL on windows

Steps to install the PostgreSQL on windows

 

 

You can get the PostGreSQL installable from the http://www.postgresql.org/download/ site.

In the below step setup will create the super user PostGres for the database and service account.

Un-check the stack builder if you don’t want to download any additional tools.

What is PostgreSQL?

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. PostgreSQL is open source database system which supports all the below features.

 

  • complex queries
  • foreign keys
  • triggers
  • views
  • transactional integrity
  • multiversion concurrency control

 

Also, PostgreSQL can be extended by the user in many ways, for example by adding new

  • data types
  • functions
  • operators
  • aggregate functions
  • index methods
  • procedural languages

 

 

License

PostgreSQL can be used, modified, and distributed by anyone free of charge for any purpose, be it private, commercial, or academic