Securing PostgreSQL using Host Based Authentication (HBA)


HBA stands for Host-base Authentication.

The pg_hba.conf config file controls the authentication method.

By default, local connections to Postgres uses peer authentication. That means that instead of asking you for a password, it checks to see if you are currently logged in as a system user that matches the user name in Postgres.

This is where the pg_hba.conf config file comes in.

We are going to change the way we do local authentication and instead tell Postgres to require an encrypted password. First, we need to actually set a password for the postgres user. To do this we need to open up psql as the user postgres.

# Notice no password
sudo -u postgres psql 

A password is not required because there is a local system user named postgres and a Postgres user named postgres.

grep postgres /etc/passwd
postgres:x:113:118:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

Apply a password for user postgres.

ALTER USER postgres WITH ENCRYPTED PASSWORD '12345';

Quit out of Postgres.

\q

Log back in using the now password protected postgres user.

# Notice still no password needed
sudo -u postgres psql

Lets create another user named tommy besides the default postgres user for testing purposes.

Keep in mind that I already have a local system user with the username tommy.

grep tommy /etc/passwd
tommy:x:1002:1003::/home/tommy:/bin/bash

Create Postgres user tommy with access to database tommy.

CREATE USER tommy WITH ENCRYPTED PASSWORD '12345';
CREATE DATABASE tommy;
GRANT ALL PRIVILEGES ON DATABASE tommy to tommy;
\du # View permissions

Quit out of Postgres.

\q

Login to Postgres with user tommy. You will notice that it still does NOT require a password even though one was applied.

# Notice no password
sudo -u tommy psql
psql (9.5.25)
Type "help" for help.

tommy=# \q

Now that we have a password set for both users postgres and tommy, we want to update Postgres to require password authentication when accessing Postres locally. To do this, we need to edit the pg_hba.conf file.

As I said before, Postgres uses peer authentication by default. This means database connections will be granted to local system users that own or have privileges on the database being connected to. Such authentication is useful in cases where a particular system user will be running a local program (e.g. scripts, CGI/FastCGI processes owned by separate users, etc.), but for greater security you may wish to require passwords to access your databases.

To do this, we need to edit the pg_hba.conf file.

Let’s read the pg_hba.conf file first.

cat /etc/postgresql/9.5/main/pg_hba.conf

There are two sections to pay attention to.

Local postrges user security:

Allows user postgres to login locally without a password.

local   all             postgres                                peer

Local all users security:

Allows local system users that have Postgres privileges with the same username to login without password.

local   all             all                                     peer

Let’s update the security settings.

vi /etc/postgresql/9.5/main/pg_hba.conf

Before:

# Database administrative login by Unix domain socket
local all postgres peer

# "local" is for Unix domain socket connections only
local all all peer

After:

Replace peer with md5 on this line to activate password authentication using an MD5 hash. This means that Postgres user postgres and local system users will no longer be able to login without a password.

# Database administrative login by Unix domain socket
local all postgres md5

# "local" is for Unix domain socket connections only
local all all md5

Restart Postgres to apply the changes.

service postgresql restart

Now if we want to connect to Postgres locally, it will require a password.

sudo -u tommy psql
Password:

Quit out of Postgres.

\q
sudo -u postgres psql
Password:

Quit out of Postgres.

\q
,