Loading ...How Do I Enable remote access to PostgreSQL database server?
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
Want to stay up to date with the latest Linux tips, news and announcements? Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
You may also be interested in other helpful articles:
- Howto improve ssh session performance by reusing an existing connection to a remote openssh server
- Setup SSH to run on a non-standard port
- Quickly Backup / dump MySql / Postgres database to another remote server securely
- Linux: Iptables Allow PostgreSQL server incoming request
- How Do I Enable remote access to MySQL database server?
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
Tags: /etc/init.d/postgresql restart, /etc/postgresql/8.2/main/pg_hba.conf, /var/lib/pgsql/data/postgresql.conf, access database, configure remote access, configuring remote access, database remote access, postgres remote access, postgresql connect, postgresql password, postgresql remote access, postgresql remote connection, postgresql sql server, postgresql_database_server, postgresql_remote-access, remote_access, security_reasons, sql server remote access, web_server
Hey,
Just tried out this and worked like wonder.
Keep up the great blogging!
Just wanted to point out that with PostgreSQL 8.x the option tcpip_socket is no longer valid.
The correct parameter is,
listen_address = ‘localhost’
which needs to be changed to,
listen_address = ‘*’
and PostgreSQL daemon restarted.
am working on postgres8.2.4
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
these additions gave me error on restarting, asking me to restore the iptables.
I removed the iptables from the above lines and it worked.
as am novice in this field, so am not sure if what i did was correct.
how to improve speed and performance of database server on linux
“How Do I Enable remote access to PostgreSQL database server? ”
Still does not answer the question…
the answer just points on to giving access to a small network, but what if you want to give access to ANY IP not just your network, basically REAL remote access.
whats the command for that
Xionicfire,
‘*’ = ALL, so use
host all all * md5Refer to man page.
but what if you want to give access to ANY IP not just your network, basically REAL remote access.
This is a bad idea, giving out access to whole world is really a bad idea. Do you wanna anyone come and try to connect your database server?