3

It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command.

But, will it make it permanent for the schema that on all tables within it, they will be granted the indicated privileges to the indicated roles, so new tables will be affected in the future?.

Or it works as a batch command so after a new table is created in the schema I have to GRANT the indicated privileges to the indicated roles again?.

1 Answer 1

3

so new tables will be affected in the future?

No, to affect new tables set DEFAULT PRIVILEGES:
PostgreSQL CREATE TABLE creates with incorrect owner

Note that default privileges are set per role.

3
  • Erwin Brandstetter: I'm setting up Postgis. I let people create layers (tables) with a SIG: QGIS. When each one create a layer, only that one can see it (the owner of the database can't neither). I was thinking about using a role to group them all, and allow them to see each other layer, but without the need to me updating the privileges on the new layer. I don't find a way to do this. Can you suggest me a solution?.
    – jgomo3
    Jul 1, 2014 at 23:01
  • @jgomo3: I don't know SIG: QGIS, but I might use a common role to create tables with. Either use SET role or create objects with functions that are created with SECURITY DEFINER and owned by said role ... Or you set default privileges for each role and grant to a common role where every other role is member ... GRANT group_role TO role13; Jul 1, 2014 at 23:14
  • Sorry, i mean a GIS[1] named QGIS. I'll try with the second option and let you know how it did. Thank you a lot [1] Geographic Information System.
    – jgomo3
    Jul 2, 2014 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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