Configuring PostgreSQL Database to be accessed remotely from a limited network segment
In this article, I try to give an alternative on how to configure your remote PostgreSQL Database Server to be accessed remotely.
RDBMS : PostgreSQL
Version : 9.4
By executing the following command we will get the version which is used or which is installed in the running operating system in Linux Distribution :
We can check the version while using root account or event postgres account as follows :
[root@hostname ]# psql --version psql (PostgreSQL) 9.2.15 [root@hostname]# [root@hostname]# su - postgres Last login: Mon Apr 11 20:01:09 2016 on pts/0 -bash-4.2$ psql --version psql (PostgreSQL) 9.2.15 -bash-4.2$
Operating System : CentOS 7.2.1511 (Core)
By executing the following command we can see the version of the operating system :
[username@hostname ]# cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) [username@hostname ]#
Objective :
To be able to provide access to certain IP Address segment, for an example 192.168.100.0/24, in order for all of the user whom have IP Addresses in the segment range can access PostgreSQL database service, we have to do several things.Those things including the steps to create an authorization to PostgreSQL database access is detailed as follows :
- Access to your own server which is assumed that the location is unknown or you don’t have any direct physical access to it, so it will be done remotely by executing the following command :
username@localhost:~$ ssh username@remotehost username@remotehost's password: Last login: xxx xxx xx xx:xx:xx xxxx from remotehost [username@remotehost ~]$ By accessing the server via remote SSH we can continue on configuring the PostgreSQL database access connection.
- First of all, we have to switch user to root account :
[username@remotehost ~]$ sudo su - [sudo] password for username: Last login: xxx xxx xx xx:xx:xx xxx xxxx on pts/0 [root@remotehost ~]# -bash-4.2$
- Succeeding on switching to root account, we can try to switch again to another user which is more appropriate to configure PostgreSQL database access’s authorization. Do the switch user’s process to get postgres account as follows :
[root@remotehost ~]# su - postgres Last login: xxx xxx xx xx:xx:xx xxx xxxx on pts/0
- After switching to postgres account, we can access PostgreSQL console by executing the following command but first of all we have to be aware of all of the environment variables which is defined as PostgreSQL’s connection parameter. In my context, I have to redefine PGPORT as the environment variable used to connect to PostgreSQL database since I do not use default PostgreSQL port to be accessed. So we have to execute the following command :
-bash-4.2$ export PGPORT=XXXX
The above command is defining environment variables in form of active port of the PostgreSQL database service with the following format :
export PGPORT=[port-number]
- So we can directly execute the following command to enter the PostgreSQL console :
-bash-4.2$ psql -Upostgres
Password for user postgres:
psql (9.2.15, server 9.4.7)
WARNING: psql version 9.2, server version 9.4.
Some psql features might not work.
Type "help" for help.
postgres=#
- After entering the PostgreSQL’s console, we can execute the following command :
postgres=#create user username with password 'password';
- In order for the above account to be abe to be use to access certain database, we have to grant access to the account so it can be used to manage or to execute certain operation to the database itself. We have to execute the following command to do it in PostgreSQL’s console :
postgres=# grant all privileges on database "database_name" to username;
- After executing the above command, our next step is to define access from segmen alamat IP Address segmen 192.168.100.0/24 which is done by editing PostgreSQL’s configuration file exists in PostgreSQL’s data directory. It is normally exists in yang terdapat pada /var/lib/pgsql/x.x/data/pg_hba.conf :
host all all 192.168.100.0/24 md5
Add one line above to the configuration script.
- Because we are editing PostgreSQL’s configuration in order to be implemented, we have to restart the service so the change in the PostgreSQL’s configuration can be reenacted throughout the PostgreSQL’s restart process. We have to exit first from the PostgreSQL’s console :
postgres=#\q
Execute the following command to restart PostgreSQL service :
[root@remotehost ~]#systemctl restart postgresql-x.x.service [root@remotehost ~]#
- Edit iptables’s configuration file which exists in /etc/sysconfig/iptables by adding the following line below :
-A INPUT -m state --state NEW -m tcp -p tcp --dport 7117 -j ACCEPT
The addition of one line above is used in order to open database port so that it can be accessed from outside of the server.
In conclusions, basically we can create authorization for remote database access as a user by using user account and password which has been created before based on the above steps. Furthermore, we can limit the access so that it can only be used from every device exists in network segment 192.168.100.0/24.