Postgres. How to grant SELECT permissions automatically for new tables?

Question

I am setting up a Postgres DB (AWS/RDS) with multiple schemas and want to have granular access control.

Each schema correlates to an application. Typically an application will have a "write" user (INSERT, UPDATE, DELETE etc.), but some applications only needs to read (SELECT) from different schemas.

Inspired by this AWS blog: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ I face problems with "permission denied" for my readusers querying tables created by writeusers, even though I ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA someschema GRANT SELECT ON TABLES TO some_read_role;

Steps to reproduce:

Create AWS RDS postgres (10.6) instance with masteruser postgres

(DB postgres) As user postgres:

CREATE DATABASE somedb LC_COLLATE 'da_DK.utf8' LC_CTYPE 'da_DK.utf8' ENCODING 'UTF8' TEMPLATE template0;

(DB somedb) As user postgres:

REVOKE ALL ON DATABASE somedb FROM PUBLIC;
CREATE SCHEMA clients;
CREATE ROLE clients_read_role;
GRANT CONNECT ON DATABASE somedb TO clients_read_role;
CREATE ROLE clients_write_role;
GRANT CONNECT ON DATABASE somedb TO clients_write_role;
GRANT USAGE ON SCHEMA clients TO clients_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA clients TO clients_read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT ON TABLES TO clients_read_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA clients TO clients_read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT ON SEQUENCES TO clients_read_role;
GRANT USAGE, CREATE ON SCHEMA clients TO clients_write_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA clients TO clients_write_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO clients_write_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA clients TO clients_write_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA clients GRANT USAGE ON SEQUENCES TO clients_write_role;

(DB somedb) As user postgres:

CREATE USER clients_read WITH PASSWORD 'xxx';
GRANT clients_read_role TO clients_read;
CREATE USER clients_write WITH PASSWORD 'yyy';
GRANT clients_write_role TO clients_write;

(DB somedb) As user clients_write (via LiquiBase):

CREATE TABLE clients.sometable
(
    id   serial primary key,
    name varchar(50) not null
);

(DB somedb) as user clients_read:
```
SELECT * FROM clients.sometable;
```
[42501] ERROR: permission denied for relation sometable

Nick Barnes Nick Barnes · Accepted Answer · 2019-09-11T23:05:21

From the docs:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

In other words, running ALTER DEFAULT PRIVILEGES as user postgres only affects tables created by postgres.

To change the defaults for another user's tables, you need to specify which user:

ALTER DEFAULT PRIVILEGES FOR ROLE clients_write ...

Note that the defaults are not inherited, so the target role is clients_write (i.e. the user actually running the CREATE TABLE command, who will become the new table's owner). Defaults for clients_write_role will have no effect unless your users SET ROLE clients_write_role; before creating a table.

Postgres. How to grant SELECT permissions automatically for new tables?

1 Answers