Grant privileges for a specific database in PostgreSQL

Question

I switch from MySQL to PostgreSQL and hit the wall with user privileges. I'm used to assigning the user all privileges for all database tables with the following command:

# MySQL grant all privileges on mydatabase.* to 'myuser'@'localhost' identified by 'mypassword'; 

It seems to me that the solution of PostgreSQL 9.x involves the assignment of privileges to the "schema", but the effort required from me to find out exactly what SQL to issue is excessive. I know that a few more hours of research will give an answer, but I think that everyone moving from MySQL to PostgreSQL can benefit from having at least one page on the Internet that provides a simple and complete recipe. This is the only team that I have ever had to issue to users. I would prefer not to enter a command for each new table.

I don’t know which scripts need to be processed differently in PostgreSQL, so I’ll talk about some of the scripts that I usually had to handle in the past. Suppose we mean only a change in the rights to one created database.

(1a) Not all tables are already created, or (1b) tables are already created.

(2a) The user has not been created yet or (2b) the user has already been created.

(3a) Privileges have not yet been assigned to the user, or (3b) privileges have previously been assigned to the user.

(4a) The user only needs to insert, update, select and delete rows, or (4b), the user should also be able to create and delete tables.

I have seen answers that provide all privileges for all databases, but that is not what I want here. Please, I am looking for a simple recipe, although I would not mind the explanation either.

I do not want to grant rights to all users and all databases, it seems, as an ordinary shortcut, because this approach compromises all databases when any one user is hacked. I host several database clients and assign each client a different login.

It looks like I also need the USAGE privilege to get incremental values ​​of the serial column, but I have to provide it on some sort of sequence. My problem got complicated.

Answer 1

The basic concept in Postgres

Roles are global objects that can access all the databases in the db cluster, taking into account the required privileges.

A cluster stores multiple databases that contain multiple schemas. Schemas (even with the same name) in different databases are not connected. Granting privileges for a schema applies only to this particular schema in the current database (the current database at the time of granting).

Each database starts with a default public schema. This is an agreement, and many settings begin with it. In addition, a public schema is simply a schema like any other.

Based on MySQL, you can start with a single public scheme, virtually ignoring the level of the scheme. I regularly use dozens of schemas for each database.
Schemas are few (but not completely) like directories in the file system.

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

• How it affects the identifier of the search identifier and the "current scheme"

default privileges

In the GRANT documentation:

PostgreSQL provides default privileges for some types of public objects. By default, tables do not have the privileges of public columns, schemas, or table spaces. For other types, the default privileges granted by public are: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privileges for languages.

All of these default values ​​can be changed with ALTER DEFAULT PRIVILEGES :

• Provide everything in a specific schema in db for a group role in PostgreSQL

Group role

As @Craig commented , it is best to use GRANT privileges for a group role, and then create a specific user member for that role ( GRANT group role to user role). this way, it’s easier to figure out and revoke privilege packages needed for specific tasks.

A group role is another role without logging in. Add a login to convert it to a user role. More details:

• Why did PostgreSQL bring users and groups together in roles?

Recipe

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

When connecting to the database in question as a superuser (for example, postgres ):

 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; 

Assign a user all privileges to all tables as you wrote (I could 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 each role that creates objects in this scheme:

 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 highlight the group to the user:

 GRANT mygrp TO myusr; 

Related answer:

• PostgreSQL - the database user should be allowed only call functions

Alternative (non-standard) setting

Coming from MySQL, and since you want to keep privileges for shared databases, you might like this custom db_user_namespace setting. In the documentation:

This option allows usernames for each database to be used. By default it is disabled.

Read the manual carefully. I do not use this setting. This does not cancel the above.

Answer 2

Maybe you could give me an example that provides a specific user to select / insert / update / delete on all tables - existing and not yet created - of a specific database?

What you call a database in MySQL closely resembles a PostgreSQL schema than a PostgreSQL database.

Connect to the "test" database as root. This

 $ psql -U postgres test 

Change the default privileges for an existing user tester.

 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES TO tester; 

Changing default privileges does not affect existing tables. This is by design. For existing tables, use the standard GRANT and REVOKE syntax.

You cannot assign privileges to a user who does not exist.

Answer 3

I do not want to grant rights to all users and all databases, it seems, as an ordinary shortcut, because this approach compromises all databases when any one user is hacked. I host several database clients and assign each client a different login.

OK When you assign tables to the correct role, the privileges granted will be role-based, not for all users! Then you can decide to whom to give roles .

• Create a role for each database. A role can contain many users.
• Then assign client-username correct role.
• Also assign your-username for each role, if necessary.

(1a) Not all tables are already created, or (1b) tables are already created.

OK You can create tables later.
When you are ready, assign the tables to the correct role client.

 CREATE TABLE tablename(); CREATE ROLE rolename; ALTER TABLE tablename OWNER TO rolename; 

(2a) The user has not been created yet or (2b) the user has already been created.

OK Create usernames when you're ready. If your client requires more than one username, just create a second client-username .

 CREATE USER username1; CREATE USER username2; 

(3a) Privileges have not yet been assigned to the user, or (3b) privileges have previously been assigned to the user.

OK When you are ready to grant privileges, create a user and assign her the correct role.
Use the GRANT-TO command to assign roles to users.

 GRANT rolename TO username1; GRANT rolename TO username2; 

(4a) The user only needs to insert, update, select and delete rows, or (4b), the user should also be able to create and delete tables.

OK You run these commands to add permissions to your users.

 GRANT SELECT, UPDATE, INSERT, DELETE ON dbname TO role-or-user-name; ALTER USER username1 CREATEDB; 

Answer 4

You can forget about the scheme if you use only PUBLIC. Then you do something like this: ( see the document 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 ]