Remote Connections in PostgreSQL:
Remote connections will allow you to connect to a PostgreSQL server from a different machine or network. In this article, we will explore the steps to establish remote connections in PostgreSQL and provide best practices for securing these connections.
This article discusses two types of remote connections in PostgreSQL.
Accessing a database from one Linux machine to another Linux machine:
Here we have two Linux machines server1 and server2, we are going to access the database in server1 through server2 remotely.
Follow the steps below for establishing a connection:
1. Start the server1:
|
1 2 3 4 5 6 7 8 9 10 |
[root@postgres1 ~]# su - postgres [postgres@postgres1 ~]$ source pg_env1.sh [postgres@postgres1 ~]$ pg_ctl start waiting for server to start....2023-06-13 11:16:41.858 IST [3669] LOG: listeg on IPv6 address "::1", port 5432 2023-06-13 11:16:41.859 IST [3669] LOG: listening on IPv4 address "127.0.0.1port 5432 2023-06-13 11:16:41.868 IST [3669] LOG: listening on Unix socket "/tmp/.s.PG.5432" 2023-06-13 11:16:41.904 IST [3670] LOG: database system was shut down at 2026-12 17:39:12 IST 2023-06-13 11:16:41.919 IST [3669] LOG: database system is ready to accept cections done server started |
2. Configure PostgreSQL server:
Edit the “postgresql.conf” file with the vi editor which will be present in the cluster data directory.
|
1 2 3 4 5 6 7 8 9 10 11 |
[postgres@postgres1 ~]$ cd /u01/data1 [postgres@postgres1 data1]$ ls base pg_dynshmem pg_logical pg_replslot pg_stat pg_tbls global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twop pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERS [postgres@postgres1 data1]$ vi postgresql.conf Modify the below configurations then save and exit the file. listen_address='*' port=5432 Now, restart the cluster to apply changes. [postgres@postgres1 data1]$ pg_ctl restart |
3. Modify pg_hba_conf:
Navigate through the data directory and open the ‘pg_hba.conf’ file.
|
1 |
[postgres@postgres1 data1]$ vi pg_hba.conf |
This file controls the authentication and access rules for Postgresql connections.
The entry should specify the IP address or subnet range of the client machine.
|
1 2 3 4 5 6 7 |
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 192.168.0.103/0 trust # IPv6 local connections: host all all ::1/128 trust |
Here we alter the IPv4 local connections.
|
1 2 3 4 5 6 7 |
TYPE – host Database – all #to access all databases. User – all # all users can access the database. ADDRESS – 192.168.0.103/0 # provide your client IP address. Method – trust # no password needed. Now reload the cluster to modify changes. [postgres@postgres1 data1]$ pg_ctl reload |
4. Configure Firewall:
To allow connection from outside we should alter the firewall configuration.
Firstly, switch to the root user
|
1 |
[postgres@postgres1 ~]$ su – root |
Now use the following to stop the firewall.
|
1 2 3 4 |
[root@postgres1 ~]# service iptables stop iptables: Setting chains to policy ACCEPT: nat mangle filte [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] |
Check the status by using following command.
|
1 2 |
[root@postgres1 ~]# service iptables status iptables: Firewall is not running. |
5. Add details of hosts in both servers.
Run the following command and add the details.
|
1 2 3 |
[root@postgres1 ~]# vi /etc/hosts 192.168.0.106 server1.oracle.com server1 192.168.0.109 server2.oracle.com server2 |
Note:
We should do the same in both servers.
Run the command by switch to the Postgres root user.
6. Establishing connection from client to server:
Make sure that cluster is running in server1 before establishing connection. Later run the following command to access database in server1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[postgres@postgres1 ~]$ psql -d postgres -U postgres -h server1.oracle.com -p 5433 psql (11.19, server 11.0) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+--------------------------------------------- dbname | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) |
In the above command,
Database – postgres
Username – postgres
Hostname – server1.oracle.com # which contains database.
Port – 5433 # psql cluster port number
Now you will successfully access the databases i.e create a database or modify the database.
Run the following command to verify any cluster is running in server 2.
|
1 2 3 4 5 |
[postgres@postgres1 ~]$ ps -efa|grep postgres root 3956 3681 0 12:30 pts/1 00:00:00 su - postgres postgres 3957 3956 0 12:30 pts/1 00:00:00 -bash postgres 4040 3957 0 12:34 pts/1 00:00:00 ps -efa postgres 4041 3957 0 12:34 pts/1 00:00:00 grep postgres |
Here no cluster is running in server2 though we can access the database in another cluster
| Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
