PostgreSQL manages database access through roles, and we can treat a role as a database user or a group of database users. Roles can have database objects, such as tables, indexes, or permissions on those objects to other roles to control which users have what permissions on which objects.
First, Database role:
1. Create a role:
CREATE ROLE Role_name;
2. Remove the role:
DROP ROLE Role_name;
3. Query roles:
Check the system table Pg_role, such as:
SELECT usename from Pg_role;
You can also perform \DU commands in psql to list all roles.
Second, role attributes:
A database role can have a range of properties that define his permissions and interact with the client authentication system.
1. Login Privileges:
Only roles with the login property can be used for database connections, so we can treat a role with that attribute as a logged-on user with the following two methods of creation:
CREATE ROLE name LOGIN PASSWORD ' 123456 ';
CREATE USER name PASSWORD ' 123456 ';
2. Super users:
The super user of the database has all the permissions of the database, and for security reasons, we'd better use non-superuser to do our work properly. Unlike creating a normal user, creating a superuser must be a superuser who executes the following command:
CREATE ROLE name SUPERUSER;
3. Create a database:
To create a database, you must explicitly give the attribute to create the database, as shown in the following command:
CREATE ROLE name Createdb;
4. Create a role:
To create more roles for a role, you must explicitly give the attributes to create the role, as shown in the following command:
CREATE ROLE name Createrole;
Third, the authority:
A database object is assigned to an owner when it is created, and 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 about the object. If you want to allow other users to use the object, you must give the 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 give permissions, such as:
GRANT UPDATE on accounts to Joe;
For the above command, the meaning is to assign the update permission of the accounts table to the Joe role. In addition, we can assign the permissions of the object to all the roles in the system with the special name public. Write all at the location of the permission declaration, which means that all permissions that apply to the object are assigned to the target role.
To revoke permissions, use the appropriate REVOKE command:
REVOKE all on accounts from public;
This means that all permissions on the Accounts object (all) are revoked on all roles (public).
Iv. Role Members:
In user management of the system, it is common to assign multiple users to a group so that only the group can be set when the permission is set, and revoke the permission from that group. In PostgreSQL, you first need to 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, in general, the role should not have the login property, 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 the permissions of a group role in two ways, such as:
1. Each group member can temporarily "become" a member of the group with the set role command, and the owner of any objects created thereafter will belong to that group, not the original logged-on user.
2. Role members with the inherit attribute automatically inherit the permissions of the role they belong to.
See the example below:
CREATE ROLE Joe LOGIN INHERIT; --inherit is a lack of provincial nature.
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin to Joe;
GRANT wheel to admin;
Now that we have a connection to the database as role Joe, the database session will have both the role Joe and the role Admin permissions, because Joe Inherits (INHERIT) the permissions of the admin. However, unlike this, the permissions given to the wheel role will not be available in that session because the Joe role is only an indirect member of the wheel role, passed indirectly through the admin role, and the admin role has the NoInherit attribute. This way, the permissions of the wheel role cannot be inherited by Joe.
This way, the permissions of the wheel role cannot be inherited by Joe. At this point, we can execute the following command in the session:
SET ROLE Admin;
After execution, the session will only have the permissions of the Admin role, and no longer include the permissions assigned to the Joe role. Similarly, after executing the following command, the session can only use the permissions given to wheel.
SET ROLE wheel;
After a period of execution, if you still want to restore the session to its original permissions, you can use one of the following recovery methods:
SET ROLE Joe;
SET ROLE NONE;
RESET ROLE;
Note: The Set role command always allows direct or indirect group roles to be selected for the current login role. Therefore, it is not necessary to become admin before becoming wheel.
Role attributes login, Superuser, and Createrole are treated as special permissions and are not inherited as normal permissions for other database objects. If necessary, the role that specifies the owning property must be displayed when set role is called. For example, we can also give the admin role createdb and Createrole permissions, and then connect to the database with Joe's role, where the session does not immediately have these special permissions, only if the current session has these permissions after the Set role Admin command is executed.
To delete a group role, execute the drop role group_role command. However, after the group role is deleted, the relationship between it and its member roles is immediately revoked (the member role itself will not be affected). It is important to note, however, that any objects that belong to the group role must be deleted or assigned to other roles before being deleted, and any permissions assigned to that group role must be revoked.
PostgreSQL Roles and Permissions
