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?.


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.

  • 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 '14 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; – Erwin Brandstetter Jul 1 '14 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 '14 at 0:56

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.