Grant privileges for a particular database in PostgreSQL

by

Basic concept in Postgres

Roles are global objects that can access all databases in a db cluster – given the required privileges.

A cluster holds many databases, which hold many schemas. Schemas (even with the same name) in different DBs are unrelated. Granting privileges for a schema only applies to this particular schema in the current DB (the current DB at the time of granting).

Every database starts with a schema public by default. That’s a convention, and many settings start with it. Other than that, the schema public is just a schema like any other.

Coming from MySQL, you may want to start with a single schema public, effectively ignoring the schema layer completely. I am using dozens of schema per database regularly.
Schemas are a bit (but not completely) like directories in the file system.

Once you make use of multiple schemas, be sure to understand search_path setting:

Default privileges

Per documentation on GRANT:

PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default
privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE
for databases; EXECUTE privilege for functions; and USAGE privilege for languages.

All of these defaults can be changed with ALTER DEFAULT PRIVILEGES:

Group role

Like @Craig commented, it’s best to GRANT privileges to a group role and then make a specific user member of that role (GRANT the group role to the user role). This way it is simpler to deal out and revoke bundles of privileges needed for certain tasks.

A group role is just another role without login. Add a login to transform it into a user role. More:

Predefined roles

Update: Postgres 14 or later adds the new predefined roles (formally “default roles”) pg_read_all_data and pg_write_all_data to simplify some of the below. See:

Recipe

Say, we have a new database mydb, a group mygrp, and a user myusr …

While connected to the database in question as superuser (postgres for instance):

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

To assign “a user all privileges to all tables” like you wrote (I might be more restrictive):

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those

To set default privileges for future objects, run for every role that creates objects in this schema:

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

Now, grant the group to the user:

GRANT mygrp TO myusr;

Related answer:

Alternative (non-standard) setting

Coming from MySQL, and since you want to keep privileges on databases separated, you might like this non-standard setting db_user_namespace. Per documentation:

This parameter enables per-database user names. It is off by default.

Read the manual carefully. I don’t use this setting. It does not void the above.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. How to update selected rows with values from a CSV file in Postgres?
  5. Optimise PostgreSQL for fast testing
  6. Transpose latest rows per user to columns
  7. How to delete duplicate rows without unique identifier
  8. Best way to check for “empty or null value”
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. PostgreSQL parameterized Order By / Limit in table function
  11. How can I send email from PostgreSQL trigger?
  12. SQL Between clause with strings columns
  13. NOT NULL constraint over a set of columns
  14. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  15. How do I alter the position of a column in a PostgreSQL database table?
  16. Query that ignore the spaces
  17. How to find a table having a specific column in postgresql
  18. Continuing a transaction after primary key violation error
  19. INSERT COMMAND :: ERROR: column “value” does not exist
  20. Use email address as primary key?
  21. Bulk/batch update/upsert in PostgreSQL
  22. SQL query to find record with ID not in another table
  23. Postgres Error: More than one row returned by a subquery used as an expression
  24. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  25. Upsert with a transaction
  26. How to compare dates in datetime fields in Postgresql?
  27. PostgreSQL how to create a copy of a database or schema?
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment