Securing PostgreSQL

Install PostgreSQL

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Upon installation, Postgres creates a Linux user called “postgres” which can be used to access the system. We can change to this user by typing:

sudo su - postgres

From here, we can connect to the system by typing:


Notice how we can connect without a password. This is because Postgres has authenticated by username, which it assumes is secured.

Exit out of PostgreSQL and the postgres user by typing the following:


Disable Remote Connections

sudo vi /etc/postgresql/9.1/main/pg_hba.conf

Ensure the contents of the config file look something like the following:

local all postgres peer
local all all peer
host all all md5
host all all ::1/128 md5

The first two security lines specify “local” as the scope that they apply to. This means they are using Unix/Linux domain sockets.
The second two declarations are remote, but the hosts that they apply to ( and ::1/128) are interfaces that specify the local machine.

What If You Need To Access the Databases Remotely?

To access PostgreSQL from a remote location, consider using SSH to connect to the database machine and then using a local connection to the database from there.
It is also possible to tunnel access to PostgreSQL through SSH so that the client machine can connect to the remote database as if it were local. Visit the PostgreSQL official documentation pages.

Use roles to lock down access to individual databases

Log into PostgreSQL:

sudo su - postgres

To create a new role, type the following:

CREATE ROLE role_name WITH optional_permissions;

To see the permissions you can assign, type:


You can alter the permissions of any role by typing:

ALTER ROLE role_name WITH optional_permissions;

List the current roles and their attributes by typing:

List of roles
Role name | Attributes | Member of
hello | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
testuser | | {}

Create a new user and assign appropriate permissions for every new application that will be using PostgreSQL.