PostgreSQL: Authorization And Authentication

8 min readNov 19, 2023

official docs : https://www.postgresql.org/docs/14/client-authentication.html

When running any service we want to segregate access to our server based off the privileges and permission to different roles. In this blog we discuss adding authentication and authorization to PostgreSQL. It helps us to isolate the different roles as well as give granularity to roles.

We start with adding roles to PostgreSQL server with different permissions and then adding host restrictions on which vm they can accessed.

If you are looking for setting up a psql cluster with docker checkout article

Database Roles

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database).

For Instance lets create a role infra which is a super user.

Lets Create a simple role

CREATE ROLE infra;
SELECT rolname FROM pg_roles;

Voila! role is created. But this role do not have much permissions.

Tips:
run \du to get list of roles
run \c to get current user and db

Okay but this role do not have much permissions.

Lets give this user login permissions so we can login to shell with this user

We will use ALTER ROLE for this

ALTER ROLE infra WITH LOGIN;

Now lets add password to the user

 ALTER ROLE infra WITH PASSWORD 'superSecretPassword';

Open new shell and login with user infra

Currently infra role do not have much permissions, Lets make infra a SUPER USER. Go to admin role shell and alter infra role to superuser.

ALTER ROLE infra WITH SUPERUSER;

Great our super user infra is ready.

Now, lets say we have a library database and want to segregate the roles into 2 categories.

ReadAccess: A role with read-only permissions which only need to read the data but should not be able to modify the database.
WRITE: A role with write permission who is able to modify the library database but not other databases.

So Lets start by first creating a table as infra role


CREATE TABLE book(
    bookid Serial Primary Key,
    bookName varchar(32) Not NULL,
    genre text[],
    bookAuthor varchar(32) NOT NULL


);

Run \l to get list of databases
Run \dt to get list of tables

Now lets insert few objects in it

INSERT INTO book (bookName, genre, bookAuthor)
VALUES ('The Book', '{The Genre}', 'The Writer');

Table owner is infra as it was created by role infra

ReadAccess

Lets create a readaccess user with some password and grant login access.

CREATE USER is equivalent to CREATE ROLE except that CREATE USER includes LOGIN by default, while CREATE ROLE does not.)

CREATE USER readaccess with PASSWORD 'jw8s0F4';

Lets grant connect permission to readaccess role

GRANT CONNECT ON DATABASE library 
TO readaccess;

We will use a predefined role to grant access.

GRANT pg_read_all_data TO readaccess;

Great now you have read access over db but you cant modify the tables.

WRITE

Similarly lets create write user

CREATE USER writeUser WITH PASSWORD 'jw8s0F4';
GRANT CONNECT ON DATABASE library TO writeUser;
GRANT pg_write_all_data TO readaccess;

Now this user has all the write permissions

Similarly we have other predefined roles, example for monitoring you can add following roles to a user

The pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables roles are intended to allow administrators to easily configure a role for the purpose of monitoring the database server. They grant a set of common privileges allowing the role to read various useful configuration settings, statistics and other system information normally restricted to superusers.

Dropping Roles

To Drop a role you can use

DROP ROLE <ROLE_NAME>

But if this role is connected to some db or object you may get a error. You need to pass this roles permission to a new role

REASSIGN OWNED BY readaccess TO admin;
DROP OWNED BY readaccess;
-- repeat the above commands in each database of the cluster
DROP ROLE readaccess;

Grouping Roles

Some times many permissions are shared by different roles, Example

A data analyst role will require read permissions over a all db whereas a another analyst may require access for single database.

In this case we can create different roles and let new roles inherit form those roles

Syntax

GRANT parentRole TO childRole;

pg_hba.conf

HBA stands for host based authentication. It is really useful if you want to connect based off hosts or add some restrictions based of hosts.

Lets discuss the contents of default file generated at

/var/lib/postgresql/data/pg_hba.conf


# TYPE  DATABASE        USER            ADDRESS                 METHOD

  local   all             all                                   trust

You can see there are various columns (TYPE DATABASE USER ADDRESS METHOD) and below them there are entries. Each entry (or record) serves as a rule which can grant, reject connection access.

Precedence is from top to bottom. so be careful while adding rules

Lets take a quick look into the each column and discuss few usecases:

Type: Each record specifies a connection type. it can be local , host. Depending on Type next columns can specify properties. Below is a chart with all types. we will only discuss few of them. You can find all here.

local         database  user  auth-method [auth-options]
host          database  user  address     auth-method  [auth-options]
hostssl       database  user  address     auth-method  [auth-options]
hostnossl     database  user  address     auth-method  [auth-options]
hostgssenc    database  user  address     auth-method  [auth-options]
hostnogssenc  database  user  address     auth-method  [auth-options]
host          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl       database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl     database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc    database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc  database  user  IP-address  IP-mask      auth-method  [auth-options]

a. local: This record matches connection attempts using Unix-domain sockets.

b. host: connections made with TCP/IP. This is the way how people access the psql server without logging into host machine

2. DATABASE: specify the database for which rule is applied to.

3. USER: user or role on which rules applies to.

4. ADDRESS: Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

5. AUTH METHODS: Specifies the authentication method to use when a connection matches this record. All the options are lower case and treated case sensitively, so even acronyms like ldap must be specified as lower case.

trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.

reject

Reject the connection unconditionally. This is useful for “filtering out” certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.

scram-sha-256

Perform SCRAM-SHA-256 authentication to verify the user’s password.

md5

Perform SCRAM-SHA-256 or MD5 authentication to verify the user’s password.

password

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks.

Now lets take an example:

Lets say you have a postgresql server running in a network. You do not want anyone to access admin role outside the vm itself so that admin role is only accessible by those having access to vm directly. Also you want your infra team to have all the access and have already created a infra role. you also want to ensure both require a password to access the vm.

Also you have 2 roles for application purposes. ‘readonly’ and ‘write’ access. anyone can connect as readonly user from any machine but for write access you only want your production subnet to have access.

As connections needs to be decided based on host we can use pg_hba.conf

also remember to run SELECT pg_reload_conf() to make changes.

First default record is

# TYPE  DATABASE        USER            ADDRESS                 METHOD

  local   all             all                                   trust

which allows any one with access to vm to login as any user without password, which is not ideal.

Inside VM run psql -Uadmin , you will be able to connect without password.

Lets change it to

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

Now only admin and infra user can login directly but require the password to login. Now lets deny all other users to use VM directly.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

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

remember to run SELECT pg_reload_conf() to make changes.

Cool now all other role connections from local are rejected.

you now need to use -h ( — host) flag to connect to server even with local vm
Now psql -Ureadonly will not work whereas psql -Ureadonly -h hostname will. also you will be prompted for password

Now for write user, we can add address for production subnet (in example 10.22.0.0/24 is prod subnet.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

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

host    all             write           10.22.0.0/24            scram-sha-256

Remember ordering matters, the first record which is matched is used by psql to grant access

Example, in case you want to restrict access to postgres database,

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

# "local" is for Unix domain socket connections only
local   all             all                                     reject
host    all             write           10.22.0.0/24            scram-sha-256
host    postgres        all             all                     reject

above record will still grant write user access to postgres server as 1st record takes precedence. whereas access to all other users to postgres database outside localhost will blocked.

To make postgres database not accessible outside vm you have to add them in proper precedence

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

# "local" is for Unix domain socket connections only
local   all             all                                     reject
host    postgres        all             all                     reject
host    all             write           10.22.0.0/24            scram-sha-256

remember to run SELECT pg_reload_conf() to make changes.

By the last record in default pg_hba.conf prevents any outside connections without the password.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow admin to access from local
local   all             admin                                   scram-sha-256
local   all             infra                                   scram-sha-256

# "local" is for Unix domain socket connections only
local   all             all                                     reject
host    postgres        all             all                     reject
host    all             write           10.22.0.0/24            scram-sha-256
host all all all scram-sha-256

There is also replication database which used for replication and above config can break it.

Hope you liked the article, If there are any suggestions or mistakes i have done please let me know.