PostgreSQL group roles

This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get buried in questions about group by, which isn't the problem.

I have been using postgres to manage a series of research projects. These databases are not connected to any web front-ends. I import all of the data from CSV. For the past few months, I have been the only user. This has worked really well. I am the db owner and nobody else needed to care. Recently, I built a complex database containing data from several sources. Because of the number of tables involved, each data source has its own schema and I have created a series of convenience views to make it easier to work with these various data sets. These schemas should be read only to all users, except for myself.

This project requires several other people to have access to this database. Each user has a username / password and an individual schema, where they can work without cluttering up the public schema. I want to be able to control access to the schemas such that the schemas holding the original data are "select only" to the other analysts. However, I want the all other schemas in the database to more or less function like the public schema in terms of access. These schemas exist for logical structure, not to control access. I want everyone to be able to read/write/create/drop/etc. in these other schemas that don't hold the original data.

Unfortunately, postgres roles are rather tricky or I've been rather slow to understand. Setting this up has been tricky as new tables are created by different users. I keep having to re-run the grant access commands manually as we add new tables.

The alter default privileges seems to imply that I can do this using group roles, but when I have tried to do so, other users were unable to access new tables. This seems to imply that I can set up group roles that will allow me

It sounds like what you probably want is to:

-
Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.

-
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.

-
Now GRANT each user membership of the read-only access role with INHERIT.

-
For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.

StackExchange Database Administrators Q#42377, answer score: 4