PostgreSQL tutorial of xii: introduction to role and permission management

PostgreSQL manages database access rights through roles, which we can think of as either a database user or a group of database users. Roles can own database objects, such as tables and indexes, or they can assign permissions on those objects to other roles to control which users have which permissions on which objects.
     
1. Database role:

1. Create role:
 

    CREATE ROLE role_name;

 

2. Delete role:
 

    DROP ROLE role_name;

    

      3

      check the system table pg_role, e.g.
 

    SELECT usename FROM pg_role;

 

      can also execute the \du command in psql to list all roles.
     
ii. Role attributes:

A database role can have a series of properties that define his permissions and interactions with the customer authentication system.

1. Login permission:

      can only be used for database connections if it has an LOGIN attribute, so we can treat a role with this attribute as a logged user, and we can do this in one of two ways:
 

    CREATE ROLE name LOGIN PASSWORD '123456 ' ;

    CREATE USER name PASSWORD '123456';

    

2. Power user:
The superuser of the       database has all the permissions for that database, and for security reasons, it is best to use a non-superuser for our normal work. Instead of creating a normal user, you must execute the following command as the super user:
 

    CREATE ROLE name SUPERUSER;

    

      3. Create database:
To create a database, the       role must explicitly assign the attributes to create the database, as shown in the following command:
 

    CREATE ROLE name CREATEDB;

    

      4. Create role:
      to create more roles for a role, you must explicitly give the attributes to create roles, as shown in the following command:
 

    CREATE ROLE name CREATEROLE;

 

     
3. Permission:

      database objects are given an owner when they are created. Typically, the owner is the role of executing the object creation statement. For most types of objects, the initial state is that only the owner (or superuser) can do anything with the object. If you want to allow other users to use the object, you must give them appropriate permissions. Many different types of built-in permissions are predefined in PostgreSQL, such as SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, and USAGE.

      we can use the GRANT command to grant permissions, such as
 

    GRANT UPDATE ON accounts TO joe;

 

For the above command, the meaning of       is to give joe role the update permission of accounts table. Alternatively, we can give permission to objects to all roles in the system with the special name PUBLIC. Write ALL on the location of the permission declaration to assign all permissions applicable to the object to the target role.
To revoke permissions, use the appropriate REVOKE command:
 

    REVOKE ALL ON accounts FROM PUBLIC;

 

      means: revoke all permissions on accounts objects (ALL) for all roles (PUBLIC).

iv. Role members:

      in user management of a system, it is common to assign multiple users to a group, so that when setting permissions, you only need to set the group, and when revoking permissions, you also revoke from the group. In PostgreSQL, you first create a role that represents a group, and then assign the role's membership permissions to a separate user role.
1. Create a group role that, in general, should not have LOGIN attributes, such as
 

    CREATE ROLE name;   

 

      2. Add and revoke permissions using the GRANT and REVOKE commands:
 

    GRANT group_role TO role1, ... ;

    REVOKE group_role FROM role1, ... ;

 

A role member can use group role permissions in two ways, such as
1. Each group member can temporarily "become" a member of that group with the SET ROLE command, and the owner of any objects created thereafter will belong to that group, not the original login user.
      2. Role members with INHERIT attributes automatically inherit the permissions of the roles to which they belong.
See the following example for      :
 

    CREATE ROLE joe LOGIN INHERIT;  --INHERIT Is the default property. 

    CREATE ROLE admin NOINHERIT;

    CREATE ROLE wheel NOINHERIT;

    GRANT admin TO joe;

    GRANT wheel TO admin;

 

      now we connect to the database as role joe, so the database session will have the permissions of both role joe and role admin, because joe" inherits (INHERIT)" the permissions of admin. However, the permissions granted to the wheel role are not available in this session because the joe role is only an indirect member of the wheel role, which is passed indirectly through the admin role, while the admin role contains the NOINHERIT attribute, so the permissions of the wheel role cannot be inherited by joe.
The permissions of the wheel role will not be inherited by joe. At this point, we can execute the following command in the session :

    SET ROLE admin;

 

After execution of      , the session will only have the permissions of the admin role, not those granted to the joe role. Again, after executing the following command, the session can only use the permissions granted to wheel.
 

    SET ROLE wheel;

 

If       still wants to restore the session to its original permissions after a period of execution, you can use one of the following recovery methods:
 

    SET ROLE joe;

    SET ROLE NONE;

    RESET ROLE;

 

Note: the       command always allows you to select a direct or indirect group role for the current login role. Therefore, it is not necessary to change to admin before changing to wheel.

      role attributes LOGIN, SUPERUSER, and CREATEROLE are considered special permissions and are not inherited like normal permissions for other database objects. If required, the role that is specified to have this property must be displayed when SET ROLE is called. For example, we can also give admin roles CREATEDB and CREATEROLE permissions, and then connect to the database with joe's role, where the session does not immediately have these special permissions, but only after the SET ROLE command is executed.

To remove a group role, execute the DROP ROLE group_role command. However, after the group of roles is removed, its relationship to its member roles is immediately revoked (the member roles themselves are not affected). It is important to note, however, that any objects belonging to this group of roles must be deleted or have their owners assigned to other roles before they can be deleted, and that any permissions assigned to this group of roles must be revoked.