≡ Menu

remote access

By default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:
$ ssh user@remote.pgsql.server.com

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:
$ su - postgres
Edit the file:
$ vi /var/lib/pgsql/data/pg_hba.conf
OR
$ vi /etc/postgresql/8.2/main/pg_hba.conf
Append the following configuration lines to give access to 10.10.29.0/24 network:
host all all 10.10.29.0/24 trust
Save and close the file. Make sure you replace 10.10.29.0/24 with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.
# vi /etc/postgresql/8.2/main/postgresql.conf
OR
# vi /var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
listen_addresses='localhost'
Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to 'localhost', and '*' is all ip address:
listen_addresses='*'
Or just bind to 202.54.1.2 and 202.54.1.3 IP address
listen_addresses='202.54.1.2 202.54.1.3'
Save and close the file. Skip to step # 4.


Step #3a - Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:
# vi /var/lib/pgsql/data/postgresql.conf
Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
tcpip_socket = true
Save and close the file.


Step # 4: Restart PostgreSQL Server

Type the following command:
# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 10.10.29.50  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 10.10.29.50 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:
# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address 10.10.29.50 and login using vivek username and sales database, enter:
$ psql -h 10.10.29.50 -U vivek -d sales

Further readings:

=> man page: pgsql and configuration file

When you work in tech support department and deal with inexperienced clients debugging problems turns into a nightmare. As a sysadmin, you won't become too paranoid if less experienced people have root-access. As a consultant, you won’t feel isolated if you don’t have remote access to your systems. As a support engineer, you won’t become frustrated if a customer has fiddled around with some important config file and you have to find which. As a performance tuner, you can capture the state of the system configuration in between performance tests/benchmarks.

Luckily, some nifty tools can create a system's hardware and software configuration snapshot. This kind of information is valuable asset while troubleshooting problems.

dconf (System config collector) is one of such tool. It allows to take your system configuration with you on the road, compare identical systems (like nodes in a cluster) to troubleshoot HW or SW problems, indeed a lifesaver.

Dconf is also useful in projects where you have to manage changes as a team. Dconf can run periodically and send out system changes to a list of email addresses so that they can be revised and discussed in group.

You can customize your dconf configuration for specific needs, like making a profile of your web server’s hardware or copy specific software configuration files to send out or compare with other systems.

As a sysadmin, you will not become too paranoid if less experienced people have root-access. As a consultant, you will not feel isolated if you do not have remote access to your systems. As a support engineer, you will not become frustrated if a customer has fiddled around with some important config file and you have to find which. As a performance tuner, you can capture the state of the system configuration in between performance tests/benchmarks.

Install dconf

If you are using Debian / Ubuntu Linux then type the command:
# apt-get install dconf
You can download Dconf for RedHat or Suse Linux here

Create a system's hardware and software configuration snapshot

Once installed you can simply create a snapshot using dconf command:
# dconf
It will write snapshot in /var/log/dconf/ directory. To view current snapshot info, enter:
# zcat /var/log/dconf/dconf-$HOSTNAME-latest.log.gz
To check the latest changes against the previous snapshot:
# zdiff -u /var/log/dconf/dconf-$HOSTNAME-previous.log.gz /var/log/dconf/dconf-$HOSTNAME-latest.log.gz

See also:

By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. This post will explain how to setup a user account and access a mysql server remotely on a Linux or Unix-like systems.
[click to continue…]