Install postgreSQL in Ubuntu and Configure for External Access

PostgreSQL is a real good database. This website relies on it and we are working on another website. Typically only localhost connections are needed. But what if you want to configure it for external accesses? Well, let me show you.

First of all, let's install it in Ubuntu, as below.

sudo apt install postgresql

Now let's go to the postgresql.conf file and change one line. It's under /etc/postgresql/16/main, where 16 is the current version of postgres. In the future, that number can go higher.

So let's

cd /etc/postgresql/16/main

Now let's open the file and edit it.

sudo nano postgresql.conf

Somewhere in the middle of the file, you will see"

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

Delete the "#" at the front and change localhost to '*' and now that line looks like this:

listen_addresses = '*'         # what IP address(es) to listen on;

Just save the file and quit. Now let's get to another file, pg_hba.conf in the same directory.

Add the following to the top of the file:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 scram-sha-256

Obviously, this would create some security risks. You can specify the "listen_addresses" to just those ones that need access. However, There are lots of problem online and I can't get it to work. So I just decide to make it this way and then use ufw to control access. Please refer to our other post on ufw for details. Basically, let's say the IP that you want to allow access to postgreSQL is 8.8.8.8, add the following into your ufw rules:

sudo ufw allow from 8.8.8.8 to any port 5432

No other IPs can have access to your postgreSQL anymore, on the best day at least.

Now let's create a database. Let's get into it by:

sudo -u postgres psql

Please make sure that the prompt becomes : postgres=#. Now just do the following in order. Of course please make sure that you name your database and user better and have a strong password. A ";" is required at the end.

CREATE DATABASE db;
CREATE USER dbuser WITH PASSWORD 'password';
ALTER ROLE dbuser SET client_encoding TO 'utf8';
ALTER ROLE dbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE dbuser SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE db TO dbuser;
GRANT ALL ON SCHEMA public TO dbuser;
ALTER DATABASE db OWNER TO dbuser;
\q

It's our experience that it's better to keep the user and database names all in lower cases. You could run into issues if they are not.

Make sure that you restart postgres: