This is an article which is explained about accessing PostgreSQL without any necessity to enter password. It is using command line in a Linux or UNIX based operating system distribution.
It has been successfully tried in a local environment which means it is on the host, workstation or server that we can physically accessed or it doesn’t need any remote connection to be able to access the database server.
By doing the steps below, PostgreSQL command console can be accessed without any requirement to input password.
1. Choose a user which is going to be used to execute the command for accessing PostgreSQL via command in the bash prompt. In this article context, ‘root’ is being chosen.
2. Create a file named .pgpass in the home directory of the user which is chosen to execute the command in order to access PostgreSQL. Since ‘root’ is chosen, the file will be created in /root/.pgpass as shown below :
[root@hostname ~]# pwd [root@hostname ~]# /root [root@hostname ~]# touch .pgpass
- After creating the file, fill the file with the following content :
hostname:port:database:username:password
The following are the content example :
127.0.0.1:5432:test:postgres:mypass Description : 127.0.0.1 : The IP Address which is used to connect to the PostgreSQL database server 5432 : The port of the PostgreSQL database server test : The name of the database exists in PostgreSQL database server which is going to be accessed postgres : The username which is going to be used to access PostgreSQL database server mypass : The password which is correspond the the user used to access PostgreSQL database server
The content of the file might be different depends on the condition and environment of PostgreSQL database server installed.
4. After the file has been created and edited, the permission of the file itself need to be modified so that it cannot be accessed by group owner or other. It can be accomplished by executing the following command :
[root@hostname ~]# chown root.root .pgpass
The above command is used to set the owner and group of .pgpass file to ‘root’.
[root@hostname ~]# chmod 0600 .pgpass
The next command is to set the permission of the file specified by the bit mask represented in the decimal number ‘6’. It is a bit value for read permission only on the owner of the file.
5. Check the file permission attribute :
[root@hostname ~]# ls -al | grep .pgpass -r-------- 1 root root 35 Aug 3 20:47 .pgpass [root@hostname ~]#
To prove that the authentication method for accessing PostgreSQL is still valid, below is the content of pg_hba.conf which is normally located in the PostgreSQL folder installation. It is stated that in order to be accessed locally, it used md5 authentication method as specified below :
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 host all all 0.0.0.0/0 md5
Although the PostgreSQL configuration file handling authentication mechanism in pg_hba.conf stated so, since there is an already created .pgpass file with the valid entry, it can be used to bypass the access without having to enter the password interactively. It will be proven through the following action :
[root@hostname ~]# psql -Upostgres psql (9.4.5) Type "help" for help. postgres=#
As it can be seen in the above output display of PostgreSQL command execution to access PostgreSQL command console, the interactively password request didn’t show up at all.
One thought on “Access PostgreSQL without Password”