Grant privileges for a particular database in PostgreSQL
Solution 1:
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:
- How does the search_path influence identifier resolution and the "current schema"
Default privileges
Per documentation on GRANT
:
PostgreSQL grants default privileges on some types of objects to
PUBLIC
. No privileges are granted toPUBLIC
by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted toPUBLIC
are as follows:CONNECT
andCREATE TEMP TABLE
for databases;EXECUTE
privilege for functions; andUSAGE
privilege for languages.
All of these defaults can be changed with ALTER DEFAULT PRIVILEGES
:
- Grant all on a specific schema in the db to a group role in PostgreSQL
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:
- Why did PostgreSQL merge users and groups into roles?
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:
- Grant access to all tables of a database
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:
- PostgreSQL - DB user should only be allowed to call functions
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.
Solution 2:
Maybe you could give me an example that grants a specific user select/insert/update/delete on all tables -- those existing and not yet created -- of a specific database?
What you call a database in MySQL more closely resembles a PostgreSQL schema than a PostgreSQL database.
Connect to database "test" as a superuser. Here that's
$ psql -U postgres test
Change the default privileges for the existing user "tester".
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES
TO tester;
Changing default privileges has no effect on existing tables. That's by design. For existing tables, use standard GRANT and REVOKE syntax.
You can't assign privileges for a user that doesn't exist.
Solution 3:
You can forget about the schema if you only use PUBLIC. Then you do something like this: (see doc here)
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]