I'm moving from MySQL to PostgreSQL and have hit a wall with user privileges. I am used to assigning a user all privileges to all tables of a database with the following command:
# MySQL grant all privileges on mydatabase.* to 'myuser'@'localhost' identified by 'mypassword';
It appears to me that the PostgreSQL 9.x solution involves assigning privileges to a "schema", but the effort required of me to figure out exactly what SQL to issue is proving excessive. I know that a few more hours of research will yield an answer, but I think everyone moving from MySQL to PostgreSQL could benefit from having at least one page on the web that provides a simple and complete recipe. This is the only command I have ever needed to issue for users. I'd rather not have to issue a command for every new table.
I don't know what scenarios have to be handled differently in PostgreSQL, so I'll list some of the scenarios that I have typically had to handle in the past. Assume that we only mean to modify privileges to a single database that has already been created.
(1a) Not all of the tables have been created yet, or (1b) the tables have already been created.
(2a) The user has not yet been created, or (2b) the user has already been created.
(3a) Privileges have not yet been assigned to the user, or (3b) privileges were previously assigned to the user.
(4a) The user only needs to insert, update, select, and delete rows, or (4b) the user also needs to be able to create and delete tables.
I have seen answers that grant all privileges to all databases, but that's not what I want here. Please, I am looking for a simple recipe, although I wouldn't mind an explanation as well.
I don't want to grant rights to all users and all databases, as seems to be the conventional shortcut, because that approach compromises all databases when any one user is compromised. I host multiple database clients and assign each client a different login.
It looks like I also need the USAGE
privilege to get the increasing values of a serial
column, but I have to grant it on some sort of sequence. My problem got more complex.
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
Check PostgreSQL User Privileges Once you're connected to your database cluster, you can use the \du command to list users that currently exist and see their roles.
GRANT ALL PRIVILEGES ON DATABASE grants the CREATE , CONNECT , and TEMPORARY privileges on a database to a role (users are properly referred to as roles). None of those privileges actually permits a role to read data from a table; SELECT privilege on the table is required for that.
To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';
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:
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
:
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:
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:
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With