Homepage

Installing PostgreSQL 13 on Ubuntu EC2 Instance

Introduction

In this tutorial, we will show how to install PostgreSQL 13 on Ubuntu Server that is running on AWS EC2 Instance. After the installation, we will go through some basics on how to create some database for some application, and a few users on the database, with different permissions.

Even though we are running this example on an AWS EC2 instance, it should work on any Ubuntu instance you have.

Installing PostgreSQL 13

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list
sudo apt-get update
sudo apt-get -y install postgresql-13

To validate that the PostgreSQL Client is installed, run

psql --version

It will print something like this

psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

Enabling Firewall for higher security

Even though you can secure any networking ports using AWS Security Groups, but we will show here today how to use the Uncomplicated Firewall to secure some TCP Ports on your machine.

For the purposes of this tutorial, we will block all ports from being accessed from outside, but we will keep both SSH and PostgreSQL open.

I strongly recommend against keeping the PostgreSQL port to be accessed publicly on the Internet, on a Production or Pre-Production system. Hopefully in a future tutorial I can show you how to make this setup more secure.

sudo ufw allow postgresql
sudo ufw allow ssh
sudo ufw enable

To get the list of the services allowed, you can run this command

cat /etc/services

One of alternative is to enable the ports by number instead

sudo ufw allow 5432/tcp
sudo ufw allow 22/tcp
sudo ufw enable

Changing PostgreSQL Superuser User

First we will switch to the Ubuntu postgres user.

sudo -i -u postgres
psql

On the PostgreSQL Console run this command

postgres=# \password postgres
Enter new password: [ROOT PASSWORD]
Enter it again: [ROOT PASSWORD]
postgres=# \q

This will bring you back to the Terminal, but still with the postgres Ubuntu user, now run the exit command to get back to the ubuntu user.

postgres@ip-172-31-36-195:~$ exit
logout
ubuntu@ip-172-31-36-195:~$

To validate the new password, login into PostgreSQL using the new password, using the command

psql -h localhost -U postgres -d postgres

It will ask you for the superuser password, then you will be able to login into the PostgreSQL terminal successfully. To list all the PostgreSQL users available, you can run the command \du on PostgreSQL terminal

postgres=# \du 
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Creating a Database and Database Users with enough permissions on the database

Now let us go ahead and create a database app and two users app_main and app_readonly.

app_main will own this database, and have all the necessary permissions on it. app_readonly will have only readonly access on this database.

create database app;
create user app_main WITH ENCRYPTED PASSWORD 'test123_main';
create user app_readonly WITH ENCRYPTED PASSWORD 'test123_readonly';

Main User Permissions

We will first make the app_main the owner of the database app

alter database app OWNER TO app_main;

You can now validate by going back to the Terminal by entering \q, and logging in using the user app_main, and connect directly to the new database. Go ahead and run the command \d to also list the existing tables in this database.

psql -h localhost -U app_main -d app
app=> \d 
Did not find any relations.

Creating a table and inserting some data

Now let's continue by creating a small table notes, and insert two two records to this table. Make sure you are still running the CREATE TABLE command.

Make sure you are still connected via the app_main user, and connected to the app database.

app=> CREATE TABLE notes (
     id serial PRIMARY KEY,
     title character varying(60) NOT NULL,
     body text NOT NULL
);

INSERT INTO notes(title, body) values('A', 'A_TEXT'),('B', 'B_TEXT');

We can validate by running this select command:

app=> SELECT * from notes;
 id | title |  body  
----+-------+--------
  1 | A     | A_TEXT
  2 | B     | B_TEXT
(2 rows)

Granting enough readonly permissions for the readonly user on PostgreSQL Database

If we were connected to the database app with the app_readonly user, reading from the notes table would have resulted in an error

app=> select * from notes;
ERROR:  permission denied for table notes

That is why now we need to GRANT enough readonly permissions to this user, still using the app_main account

GRANT CONNECT ON DATABASE app to app_readonly;
GRANT USAGE ON SCHEMA public to app_readonly;
GRANT SELECT ON ALL TABLES in SCHEMA public to app_readonly;
GRANT SELECT ON ALL SEQUENCES in SCHEMA public to app_readonly;

You can now validate by trying to run these SELECT and INSERT commands using the app_readonly user.

psql -h localhost -U app_readonly -d app

app=> select * from notes;
 id | title |  body  
----+-------+--------
  1 | A     | A_TEXT
  2 | B     | B_TEXT
(2 rows)

app=> INSERT INTO NOTES(title) VALUES('HEY');
ERROR:  permission denied for table notes

That is great. But we have a tricky problem now. When we ran the commands

GRANT SELECT ON ALL TABLES ....
GRANT SELECT ON ALL SEQUENCES ....

This only grants permissions on existing tables, not future tables. To solve this problem, we need to change default permissions using the command ALTER DEFAULT PRIVILEGES

Granting enough readonly permissions on future tables

Make sure you are running these commands while logged to PostgreSQL Console using the user app_main again.

For tables:

ALTER DEFAULT PRIVILEGES FOR USER app_main IN SCHEMA PUBLIC
GRANT SELECT ON TABLES TO app_readonly;

For sequences:

ALTER DEFAULT PRIVILEGES FOR USER app_main IN SCHEMA PUBLIC
GRANT SELECT ON SEQUENCES TO app_readonly;

These commands basically mean, that for future tables in this database app, the default permissions for any objects (tables and sequences in this case) that are only owned by app_main, will be SELECT on both tables and sequences for the user app_readonly.

To put it in another words, if another user, with enough permissions, created tables on this database, then app_readonly will not have enough permissions to read from these tables, unless we change the default permissions, like we did here.

This command \ddp will show you the default permissions on the database.

app=> \ddp
               Default access privileges
  Owner   | Schema |   Type   |    Access privileges    
----------+--------+----------+-------------------------
 app_main | public | sequence | app_readonly=r/app_main
 app_main | public | table    | app_readonly=r/app_main

References

About Me

My name is Omar Qunsul. I write these articles mainly as a future reference for me. So I dedicate some time to make them look shiny, and share them with the public.

You can find me on twitter @OmarQunsul, and on Linkedin.

Homepage