PostgreSQL learning Manual (roles and permissions)

PostgreSQL manages database access permissions through roles. we can regard a role as a database user or a group of database users. Roles can have database objects, such as tables and indexes, or grant permissions on these objects to other roles to control which users have permissions on which objects.
1. database role:

1. Create a role:
Create roleRole_name;

2. delete a role:
Drop roleRole_name;

3. query role:
Check the system table pg_role, for example:
Select usename fromPg_role;
You can also run the \ du command in Psql to list all roles.

Ii. Role attributes:

A database role can have a series of attributes that define its permissions and interact with the customer authentication system.
1. logon permission:
Only a role with the login attribute can be used for database connection. Therefore, we can regard a role with this attribute as a logon user. The creation method is as follows:
Create roleNameLogin Password'123 ';
Create userNamePassword'123 ';

2. superuser:
Database superusers have all permissions for the database. For security reasons, we recommend that you use non-superusers to complete our normal operations. Unlike creating a common user, a Super User must be created as a Super User and run the following command:
Create roleName Superuser ;

3. Create a database:
To create a database, the role must explicitly grant the database creation attributes, as shown in the following command:
Create roleNameCreatedb;

4. Create a role:
To create more roles for a role, you must specify the attributes of the role to be created. For details, see the following command:
Create roleName Createrole ;

Iii. Permissions:

When a database object is created, it is assigned to an owner. Generally, the owner is the role that executes the object creation statement. For most types of objects, the initial state is that only the owner (or Super User) can do anything to the object. To allow other users to use this object, you must grant appropriate permissions. PostgreSQL pre-defines many different types of built-in permissions, such:Select, insert, update, delete, rule, references, trigger, create, temporary, execute, and usage.
You can use the grant command to grant permissions, for example:
Grant update onAccountsToJoe;
The preceding command indicates that the update permission of the accounts table is granted to the Joe role. In addition, we can also assign the object permission to all roles in the system with a special name "public. Write "all" at the position where the permission is declared to grant all permissions for the object to the target role.
To revoke permissions, use the appropriate revoke command:
Revoke all onAccountsFrom public;
The meaning is: revoke all permissions (all) on the accounts object for all roles (public ).

4. Role members:

In the user management of the system, multiple users are usually assigned to a group. In this way, you only need to set the group when setting permissions. The revocation permission is also revoked from the group when setting permissions. In PostgreSQL, you must first create a role that represents the group, and then grant the membership permission of this role to an independent user role.
1. Create a group role. Generally, this role should not have the login attribute, for example:
Create role name;
2. Use the grant and revoke commands to add and revoke permissions:
Grant group_role to role1 ,...;
Revoke group_role from role1 ,...;
A role member can use the permissions of a group role in two ways, for example:
1. Each group member can use the set role command to temporarily "change himself" to "this group member. The owner of any objects created later will belong to this group, rather than the original login users.
2. Role members with the inherit attribute automatically inherit the permissions of their roles.
See the following example:
Create role Joe LoginInherit; -- Inherit is the default attribute.
Create role AdminNoinherit;
Create role WheelNoinherit;
Grant admin to Joe;
Grant wheel to admin;
Now we establish a connection with the database as the role Joe, so the database session will have permissions for both the role Joe and the role admin at the same time, because Joe "inherits (inherit) "Admin permission. However, the permission granted to the wheel role is not available in this session, because the Joe role is only an indirect Member of the wheel role and is indirectly transferred through the admin role, however, the admin role contains the noinherit attribute, so that the permissions of the wheel role cannot be inherited by Joe.
In this way, the permissions of the wheel role cannot be inherited by Joe. At this point, we can execute the following command in this session:
Set role admin;
After the session is executed, the session will only have the permissions of the admin role, instead of the permissions granted to the Joe role. Similarly, after executing the following command, the session can only use the permission granted to wheel.
Set role wheel;
After a period of execution, if you still want to restore the session to the original permissions, you can use one of the following methods to restore the session:
Set role Joe;
Set role none;
Reset role;
Note: The set role command always allows you to select the direct or indirect group role of the current logon role. Therefore, you do not need to change it to admin before changing it to wheel.
Role attributes Login, superuser And Createrole They are considered as special permissions and will not be inherited as the normal permissions of other database objects. If necessary, the role with this attribute must be displayed when set role is called. For example, we can also grant the createdb and createrole permissions to the admin role, and then connect to the database with the Joe role. In this case, the session will not immediately have these special permissions, the current session has these permissions only after the set role admin command is executed.
To delete a group role, run Drop role group_role Command. However, after a role is deleted, the relationship between the role and its member roles will be immediately revoked (the member roles themselves will not be affected ). However, before deleting an object, any object that belongs to the role in this group must be deleted or the object owner must be assigned to another role. At the same time, any permissions granted to this group role must also be revoked.