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;

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

Solution 2

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.

Comments

[punkish]

just so you know Erwin, 10 mins after you posted your advice, I needed it. Its like you knew what I was going to do... create a new table and find it didn't have the right privs. Your answer came to the rescue.

[Erwin Brandstetter]

@punkish: I demand my precog badge! Damn, that's already used for something else.

[Ning Liu]

in addition to the two grants above, need one more grant: GRANT USAGE ON SCHEMA public TO some_user_;

[Basil Bourque]

@NingLiu Thanks so much for pointing out GRANT USAGE, and for teaching me that. I added a section to the Answer.

[Basil Musa]

GRANT USAGE ON SCHEMA is what I was looking for.

[J86]

When running ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff; how does it know what database? SCHEMA foo can exist in different database?

[Erwin Brandstetter]

@J86: applies to the current database only - where the command is executed.

[lexeme]

@ErwinBrandstetter Can I grant access for future tables/sequences to the app_user (read-write) provided that tables will be created by another dedicated migration_user automatically (flyway migrations are run at the app startup)?