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

by

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.

More Related Contents:

  1. Permission denied for relation
  2. Calculating and saving space in PostgreSQL
  3. Store common query as column?
  4. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  5. Making sense of Postgres row sizes
  6. Do nullable columns occupy additional space in PostgreSQL?
  7. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  8. CONSTRAINT to check values from a remotely related table (via join etc.)
  9. Does a Postgres UNIQUE constraint imply an index?
  10. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  11. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  12. Is there a way to disable updates/deletes but still allow triggers to perform them?
  13. PostgreSQL – set a default cell value according to another cell value
  14. Generating time series between two dates in PostgreSQL
  15. ‘password authentication failed for user “postgres”‘
  16. PostgreSQL error: Fatal: role “username” does not exist
  17. How to do an update + join in PostgreSQL?
  18. How to drop a PostgreSQL database if there are active connections to it?
  19. “psql: could not connect to server: Connection refused” Error when connecting to remote database
  20. postgresql duplicate key violates unique constraint
  21. ClassNotFoundException with PostgreSQL and JDBC
  22. Refresh a materialized view automatically using a rule or notify
  23. How do I login and authenticate to Postgresql after a fresh install?
  24. pgadmin4 : postgresql application server could not be contacted.
  25. eliminate duplicate array values in postgres
  26. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  27. input file appears to be a text format dump. Please use psql
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. Format specifier for integer variables in format() for EXECUTE?
  30. PostgreSQL composite primary key

Leave a Comment