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

User · 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

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

To Be Conservative

Access to the Schema

Existing Objects Versus Future

Examples related to postgresql

Examples related to database-design

Examples related to roles

Examples related to privileges

Examples related to grant