Go to TogaWare.com Home Page. GNU/Linux Desktop Survival Guide
by Graham Williams
Duck Duck Go



CLICK HERE TO VISIT THE UPDATED SURVIVAL GUIDE

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=#


Support further development by purchasing the PDF version of the book.
Other online resources include the Data Science Desktop Survival Guide.
Books available on Amazon include Data Mining with Rattle and Essentials of Data Science.
Popular open source software includes rattle and wajig.
Hosted by Togaware, a pioneer of free and open source software since 1984.
Copyright © 1995-2020 Togaware Pty Ltd. Creative Commons ShareAlike V4.