Restrict and grant privileges on public schema to users

Before we get to the topic, first things first you might have to know answer to the following questions.

What is access control?

Access control is a fundamental component in security compliance where we control who can access what.

Why do we want to do access control ?

Access control might sound a bit scary like as if we are control freaks, right? That is not the case. We need to ensure that we provide only the set of privileges that a particular user will need. Because a Postgres user could be used by a human and an application. In case of system compromise or remote code execution by an attacker, user access control definitely protects against major damage.

What are the prerequisites to understand this blog ?

You need to know what Postgres is, and the commands associated with SQL and privileges at each level for a user in Postgres.

Who might find this useful ?

Those who wants to apply user access control to an existing database with tables in public schema that cannot be moved to a different schema.

What are the default access privileges for a user in Postgres ?

By default, all users are given connect access to any database and public schema of a database allows creation of new data objects and usage of them within it unless revoked.

Let's get started

For demonstration we can consider the following scenario :

There is a web application that uses PostgreSQL, and database migration tool like Liquibase or Flyway. There is a database admin who maintains the database.
We have created different users for application application_user, migration tool migration_user and the admin admin.

application_user requires privilege to run only DML commands. migration_user requires both DDL and DML. admin has all the privileges.

Grant privileges works in such a way that if a user has higher privilege to an object already, granting a lower privilege will only retain the existing privilege, so they have to be revoked before granting.

There are various layers in database access which is illustrated in the diagram below.

Database accesses

1. REVOKE ALL ON DATABASE FROM PUBLIC;

Here public is the pseudo user group that all users in Postgres belong to by default.

1. GRANT CONNECT ON DATABASE to migration_user;

2. GRANT CONNECT ON DATABASE to application_user;

Schema accesses

1. Revokes all privileges (CREATE/USAGE) on public schema from all existing and future users(public role). By default public schema has all privileges unless revoked.

REVOKE ALL ON SCHEMA public FROM PUBLIC

1. Grants CREATE and USAGE (all) access to migration user as they need to create tables inside public schema and also use them.'

GRANT ALL ON SCHEMA public to migration_user;

1. Grants only usage access to app user as they should not be able to create new tables and only need to access the tables inside public schema.

GRANT USAGE ON SCHEMA public to application_user;

Table accesses

1. Revokes all privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER) on all tables in public schema from all existing & future users(public), default privilege for tables in public schema is all privileges unless revoked.'

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

1. Grants (SELECT, INSERT, UPDATE, DELETE) access to application_user by default when migration user creates a table in future or else the grant command has to be executed everytime a new object is created by migration_user.

ALTER DEFAULT PRIVILEGES FOR USER migration_user IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO application_user

Sequence accesses

1. Grants usage access to app user by default when migration user creates a sequence in future

ALTER DEFAULT PRIVILEGES FOR USER migration_user IN SCHEMA public GRANT USAGE ON SEQUENCES TO application_user;