Setting up users and permissions in Postgres the right way

When I first started using Postgres, the managing of users and their permissions was not at all intuitive and I couldn’t quite understand why, having seemingly revoked permission on a resource for a user, they were still able to access it.

In my opinion, a lot of this confusion seems to revolve around the PUBLIC role which all new Postgres users inherit from. So to set things up properly you need to tear down the public access to the database to start with and then selectively add permissions and roles from then on in.

In this article, I’ll outline a robust process where you can add users and permissions in Postgres in the correct way to ensure that users only have access to the resources you want them to.

In my example we’re assuming that we have a Postgres database called testdb and we want to set up a new user called tpr_user that has read-only access to certain schemas, let’s call the schemas open_data and test_data.To allow the new user to create their own tables etc… in the database, we’ll also set up a new schema for them called tpr_data and assign ownership of it to them. This will automatically confer read-write access for the user to it.

Apart from the above, the new user will have neither read nor write access to any other schemas in the database such as public, etc …

All of the steps below are run as the master user postgres.

Step-by-step guide

Tear down public access to the database

• REVOKE CREATE ON SCHEMA public FROM PUBLIC;

• REVOKE ALL ON DATABASE testdb FROM PUBLIC;

Create a read-only role and grant permissions on that role to specific schemas

• CREATE ROLE readonly;

• GRANT CONNECT ON DATABASE testdb TO readonly;

• GRANT USAGE ON SCHEMA open_data TO readonly;

• GRANT USAGE ON SCHEMA test_data TO readonly;

To allow existing tables in the specified schemas to be readable do this.

• GRANT SELECT ON ALL TABLES IN SCHEMA open_data TO readonly;

• GRANT SELECT ON ALL TABLES IN SCHEMA test_data TO readonly;

To allow new future tables in specified schemas to be readable do this

• ALTER DEFAULT PRIVILEGES IN SCHEMA open_data

• GRANT SELECT ON TABLES TO readonly;

• ALTER DEFAULT PRIVILEGES IN SCHEMA test_data

• GRANT SELECT ON TABLES TO readonly;

• Now create the user tpr_user.

They will have create and read/write table permissions in their own schema (see below) and read only elsewhere.

# The user will have no access permissions for any schemas

# (except ones they own) that are not explicitly specified in this script

• CREATE USER tpr_user WITH ENCRYPTED PASSWORD 'secret_passwd';

• GRANT readonly TO tpr_user;

Now create a schema for the new user and re-assign ownership. Users should normally be able to read and write to schemas they own.

• create schema tpr_data;

• alter schema tpr_data owner to tpr_user;

That’s it for assigning read-only access. If you needed a user to have read and write access to certain tables in other schemas, just follow a similar series of steps as above but with a read-write role,

e.g create the read-write role, apply the required privileges to the role then grant the role privilege to the user.

# Create a read/write role and grant permissions on

# that role to specific schemas

• CREATE ROLE readwrite;

• GRANT CONNECT ON DATABASE testdb TO readwrite;

• GRANT USAGE ON SCHEMA open_data TO readwrite;

• GRANT USAGE ON SCHEMA test_data TO readwrite;

# Role privileges for existing tables

• GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA open_data TO readwrite;

• GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA test_data TO readwrite;

# Role privileges for new, yet to be created tables

# It's recommended also granting access for sequences for readwrite users

• ALTER DEFAULT PRIVILEGES IN SCHEMA open_data

• GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

• ALTER DEFAULT PRIVILEGES IN SCHEMA open_data G

• GRANT USAGE ON SEQUENCES TO readwrite;

• ALTER DEFAULT PRIVILEGES IN SCHEMA test_data

• GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

• ALTER DEFAULT PRIVILEGES IN SCHEMA test_data

• GRANT USAGE ON SEQUENCES TO readwrite;

# Grant the user the new role

• GRANT readwrite TO tpr_user;