PostgreSQL Roles And Rights

Most databases have users and groups to handle access rights. PostgreSQL is different as there are only roles.

The Application User Story

A new app has to be developed. The choosen RDBMS is PostgreSQL.

The application does need different roles:

• A user to create objects
• A user for the application with rights to add and change data
• A group for different users for reporting with read only rights

Creating Users

PostgreSQL does not have users, there are only roles.

A user role needs to be able to login and a password. In addition the a superuser or a role with the right CREATEROLE has to be used.
In this example a connection to a local PostgreSQL cluster will be used and the name of our role will be app.

# Becoming postgres
sudo su postgres
# psql does not need any parameters, standard settings are used
psql
postgres=>

-- We chreate the role without a password, that way it is not ssroed in logs or history
CREATE ROLE app WITH LOGIN;

-- Add a password to the user app, it prompts you to add and repeat a password
\password app

This user does not have any rights by now, except that a login to the database postgres is possible and this user is a member of the role public.
As access to databases will later be given by adding a user to group, the login to the database postgres has to be removed.

REVOKE CONNECT ON DATABASE postgres FROM app;

-- To exit psql you can use either, exit, \q or CTRL+d
\q

Now the login can be tested:

psql -h localhost -p 5432 -U app postgres
postgres=>
\q

The role “app” still can connect to the database postgres.
This is because all users are memebers of the the role public. This role also needs to be changed. But that will solve the problem for all future roles, that might be created.

psql

REVOKE CONNECT ON DATABASE postgres FROM public;
\q

We test the connection against the database postgres again.

psql -h localhost -p 5432 -U app postgres
psql: error: connection to server at "localhost" (127.0.0.1), port 5433 failed: FATAL: permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

This solved the problem to disallow the usage of the database postgres to normal users.

Database Owner Roles

To handle different databases with different user rights, one role per database for administraion purposes will be created.
This role can be used for database change management systems like Sqitch or Liquibase.

# Remember, the shell user is still postgres
psql

-- Creating a database ownere role
CREATE ROLE testdbowner WITH LOGIN;

-- Add a password to the user app, it prompts you to add and repeat a password
\password testdbowner

-- Crating a datgabase owned by the previously created role
CREATE DATABASE testdb OWNER testdbowner;
\q

The database owner can create and own all objects inside the database testdb.
We test to connect with that newly created role to the new database.

psql -h localhost -p 5432 -U testdbowner testdb
postgres=>
\q

Defining Groups

Groups are usually defined with roles, which cannot login into PostgreSQL.
This role will only have permissions, to read data.

Roles can only be created by superusers, or by roles with the option CREATEROLE

# Remember, the shell user is still postgres
psql

CREATE ROLE dbread NOLOGIN;
\q

We test, that the new role cannot login.

psql -h localhost -p 5432 -U testdbowner testdb

\q

Schema Public

The schema public is always added to new databases. Until PostgreSQL 15 every user can create objects in this schema. This should be changed by revoking permissions to the schema public from the role public where every other role is a member of.

# Remember, the shell user is still postgres
psql

REVOKE ALL ON SCHEMA public FROM public;
\q

Since PostgreSQL 15 this the standard, you do not have to think about it any more.

Creting A Schema For Database Objects

The schema public should not be used. Therefore a schema for the database tables and other objects are needed.

psql -h localhost -p 5432 -U testdbowner testdb

CREATE SCHEMA dataobjects;
\q

Adding Permisisions

Allowing Logins To The Database

Roles need the allowance to login to the database. The group role dbread gets the access rights:

psql -h localhost -p 5432 -U testdbowner testdb

-- As DBOWNER we can grant permissions
-- The group role needs to have the right to connect to the database
GRANT CONNECT ON DATABASE testdb TO dbread;
\q

Allowing To Use The Schema

The role does not have the rights to use any objects within the schema dataobjects.

psql -h localhost -p 5432 -U testdbowner testdb

-- The group role also needs right to use the schema
GRANT USAGE ON SCHEMA dataobjects to dbread;
\q

Granting Rights On All New Objects

The group role dbread will not have any rights to use any object inside the schema dataobjects by now. Also objects created in the future would not be accessible.
This can be changed with changing default privileges.

psql -h localhost -p 5432 -U testdbowner testdb

-- By default the role dbread will be able to read all tables
ALTER DEFAULT PRIVILEGES
  IN SCHEMA dataobjects
  GRANT SELECT ON TABLES TO dbread;
\q

This does also include views and foreign tables.

Adding User Roles To Group Roles

The group role does not have any members. We can create new roles and specify the group role, they belong to. That way they have the rights. that they need.

In addtion we change the search path, meaning that the new user will automatically will use the schema dataobjects instead of public. That way the user does not have to use the schema name when selecting tables.

# Remember, the shell user is still postgres
psql

-- By default the role dbread will be able to read all tables
CREATE ROLE someone WITH LOGIN IN GROUP dbread;

-- Add a password to the user app, it prompts you to add and repeat a password
\password someone

-- Changing the default schema
ALTER ROLE someone SET SEARCH_PATH = 'dataobjects';
\q

With the previous statements we can add new users with read only access.

Application User Role

The user that will connect from the app does need some more rights than read. But with what we have learned so far, we should be able to do the job.

# Remember, the shell user is still postgres
psql testdb

-- By adding the app role to the role group app has SELECT rights granted,
-- can conect to the database and can use the schema dataobjects
CREATE ROLE app WITH LOGIN IN GROUP dbread;

-- Add a password to the user app, it prompts you to add and repeat a password
\password app

-- Changing the default schema
ALTER ROLE someone SET SEARCH_PATH = 'dataobjects';

-- Adding default rights to tables to add and change data
-- And as we are running these commands as postgres, we need to name the creator
-- with FOR ROLE
ALTER DEFAULT PRIVILEGES FOR ROLE testdbowner
  IN SCHEMA dataobjects
  GRANT UPDATE ON TABLES TO dbread;
ALTER DEFAULT PRIVILEGES FOR ROLE testdbowner
  IN SCHEMA dataobjects
  GRANT INSERT ON TABLES TO dbread;

-- Adding default privileges for sequences
ALTER DEFAULT PRIVILEGES FOR ROLE testdbowner
  IN SCHEMA dataobjects
  GRANT SELECT ON SEQUENCES TO dbread;
ALTER DEFAULT PRIVILEGES FOR ROLE testdbowner
  IN SCHEMA dataobjects
  GRANT USAGE ON SEQUENCES TO dbread;

-- Adding default rights to functions and procedures
ALTER DEFAULT PRIVILEGES FOR ROLE testdbowner
  IN SCHEMA dataobjects
  GRANT EXECUTE ON FUNCTIONS TO dbread;
\q

With SELECT for a SEQUENCE the role can read the current value, USAGE does add the right to increment them.

Without FOR ROLE the command ALTER DEFAULT PRIVILEGES will use the current user here.

Conclusion

Now we have learned how to harden roles in PostgreSQL by removing some default rights. In addition we learned how roles can get rights to SELECT, INSERT, etc. automatically for new objects.

Hopefully you have noticed, that the role app does not have rights to delete records. I am usually a bit rigid here. The role app will only get rights to delete in those tables, where it is needed. This might help if your app gets hacked because the hackers would not be able to delete records everywhere.
There is also a right to use TRUNCATE, as an owner you have the right to empty a table with TRUNCATE, but others should not have that right.

With our previous work hackers won’t be able to get more access to the database and do nasty things like creating objecs, or deleting them.