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:

psql

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:

\q
exit

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             127.0.0.1/32            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 (127.0.0.1/32 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
psql

To create a new role, type the following:

CREATE ROLE role_name WITH optional_permissions;

To see the permissions you can assign, type:

\h CREATE ROLE

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:

\du
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.