1

I'm trying to get my head around postgres roles.

I want to create a database and have the owner role have all privileges on the database.

I then want another role which cannot create tables but able to read/write from any tables that may be added to the db in the future.

I have done REVOKE ALL ON schema public FROM public and then GRANT ALL ON schema public TO admin_role and the admin role works.

But I can't figure out how to create a limited role with the create table permission revoked and it still able to see and read to tables within the database. If I do the GRANT ALL ON schema public it has all privileges which I don't want.

Any help much appreciated.

1

You don't need to GRANT only on a schema. You can get more specific and GRANT specifically to a table, like this:

GRANT ALL ON TABLE xyz.items TO admin_role;

but note that GRANT ALL and REVOKE ALL are very large sledgehammers for building very small spice racks.

Consider reviewing the excellent GRANT documentation which covers this in detail.

For your specific case, you could do something as simple as:

REVOKE CREATE ON SCHEMA public FROM admin_role;

which would remove the right to create new objects in the schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.