20.13 PostgreSQL Remote Access

20200819

The default install of PostgreSQL on Ubuntu supports local access only. To allow remote access we need to edit a couple of configurations.

First edit /etc/postgresql/10/main/postgresql.conf to ask PostgreSQL to listen as any IP address (rather than just localhost) by changing the first line we see below to the second line shown.

#listen_addresses = 'localhost'
listen_addresses = '*'

Next, edit /etc/postgresql/12/main/pg_hba.conf to allow connections from any IP address by changing the first line to the second:

#host    all             all             127.0.0.1/32            md5
host     all             all             0.0.0.0/0               md5

To instead allow connections from a single specific IP address we can specify that address using a CIDR (see Section ??):

host    all             all             219.113.36.19/32         md5

Your firewall will need to be opened to allow access via port 5432:

$ sudo ufw allow 5432/tcp

Then restart the server:

$ wajig restart postgresql

The users will need a password to connect:

$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

kayon=# \password
Enter new password: 
Enter it again: 
kayon=# \q

Then on the remote host connect to the postgresql server:

$ psql -h 123.145.167.89 -d kayon -U kayon
Password for user kayon: 

psql (12.2 (Ubuntu 12.2-4))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

kayon=# SELECT * FROM iris LIMIT 5;
 sepal_length | sepal_width | petal_length | petal_width | species 
--------------+-------------+--------------+-------------+---------
          5.1 |         3.5 |          1.4 |         0.2 | setosa
          4.9 |         3.0 |          1.4 |         0.2 | setosa
          4.7 |         3.2 |          1.3 |         0.2 | setosa
          4.6 |         3.1 |          1.5 |         0.2 | setosa
          5.0 |         3.6 |          1.4 |         0.2 | setosa
(5 rows)
kayon=# 

---

Your donation will support ongoing availability and give you access to the PDF version of this book. Desktop Survival Guides include Data Science, GNU/Linux, and MLHub. Books available on Amazon include Data Mining with Rattle and Essentials of Data Science. Popular open source software includes rattle, wajig, and mlhub. Hosted by Togaware, a pioneer of free and open source software since 1984. Copyright © 1995-2022 Graham.Williams@togaware.com Creative Commons Attribution-ShareAlike 4.0