Postgres version: 11

I've got 2 roles - one used for read and one for write on the same database.

CREATE ROLE reader WITH login;
CREATE ROLE writer WITH login;

CREATE DATABASE thedatabase;

GRANT SELECT ON all tables in schema public TO reader;
GRANT INSERT ON all tables in schema public TO writer;

Everything is fine for all the existing objects in the database, but as soon as another object is created both users will need a GRANT SELECT/INSERT to be able to perform their duties.

Is there a config to overcome this?

  • 1
    You are looking for ALTER DEFAULT PRIVILEGES, see e.g. here or here
    – user1822
    Feb 19, 2020 at 10:45
  • This is beautiful, thank you. Feb 19, 2020 at 10:48


Browse other questions tagged or ask your own question.