database design - 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;

Answer 1

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.