Grant all on a specific schema in the db to a group role in PostgreSQL

Question

Using PostgreSQL 9 0  I have a group role called  staff  and would like to grant all  or certain  privileges to this role on tables in a  particular schema  None of the following work  GRANT ALL ON SCHEMA foo TO staff  GRANT ALL ON DATABASE mydb TO staff    Members of  staff  are still unable to SELECT or UPDATE on the individual tables in the schema  foo  or  in the case of the second command  to any table in the database unless I grant all on that specific table   What can I do make my and my users  lives easier   Update  Figured it out with the help of a similar question on serverfault com   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff

User · Accepted Answer

You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies:

(but note that ALL TABLES is considered to include views and foreign tables).

Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual:

For sequences, this privilege allows the use of the currval and nextval functions.

So if there are serial columns, you'll also want to grant USAGE (or ALL PRIVILEGES) on sequences

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Note: identity columns in Postgres 10 or later use implicit sequences that don't require additional privileges. (Consider upgrading serial columns.)

What about new objects?

You'll also be interested in DEFAULT PRIVILEGES for users or schemas:

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

This sets privileges for objects created in the future automatically - but not for pre-existing objects.

Default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user executing ALTER DEFAULT PRIVILEGES. To be explicit:

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Note also that all versions of pgAdmin III have a subtle bug and display default privileges in the SQL pane, even if they do not apply to the current role. Be sure to adjust the FOR ROLE clause manually when copying the SQL script.

User · Answer

My answer is similar to this one on ServerFault com   To Be Conservative  If you want to be more conservative than granting  all privileges   you might want to try something more like these   GRANT SELECT  INSERT  UPDATE  DELETE ON ALL TABLES IN SCHEMA public TO some user   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some user     The use of public there refers to the name of the default schema created for every new database catalog  Replace with your own name if you created a schema   Access to the Schema  To access a schema at all  for any action  the user must be granted  usage  rights  Before a user can select  insert  update  or delete  a user must first be granted  usage  to a schema   You will not notice this requirement when first using Postgres  By default every database has a first schema named public  And every user by default has been automatically been granted  usage  rights to that particular schema  When adding additional schema  then you must explicitly grant usage rights    GRANT USAGE ON SCHEMA some schema  TO some user      Excerpt from the Postgres doc      For schemas  allows access to objects contained in the specified schema  assuming that the objects  own privilege requirements are also met   Essentially this allows the grantee to  look up  objects within the schema  Without this permission  it is still possible to see the object names  e g  by querying the system tables  Also  after revoking this permission  existing backends might have statements that have previously performed this lookup  so this is not a completely secure way to prevent object access    For more discussion see the Question  What GRANT USAGE ON SCHEMA exactly do   Pay special attention to the Answer by Postgres expert Craig Ringer   Existing Objects Versus Future  These commands only affect existing objects  Tables and such you create in the future get default privileges until you re-execute those lines above  See the other answer by Erwin Brandstetter to change the defaults thereby affecting future objects

[postgresql] Grant all on a specific schema in the db to a group role in PostgreSQL

What about new objects?

Examples related to postgresql

Examples related to database-design

Examples related to roles

Examples related to privileges

Examples related to grant