PostgreSQL is a role to manage database access, we can see a role as a database user, or a group of database users. A role can have database objects, such as tables, indexes, or other roles on those objects to control which users have permissions on which objects.
One, database role:
1. Create a role:
Copy Code code as follows:
2. Delete roles:
Copy Code code as follows:
3. Query role:
Check system table Pg_role, such as:
Copy Code code as follows:
SELECT usename from Pg_role;
You can also perform the \du command in Psql to list all roles.
Second, role attributes:
A database role can have a series of attributes that define his or her permissions and interact with the customer authentication system.
1. Logon rights:
Only a role with the login attribute can be used for a database connection, so we can treat a role with this property as a logged-on user, with two methods:
Copy Code code as follows:
CREATE role name LOGIN PASSWORD ' 123456 ';
CREATE USER name PASSWORD ' 123456 ';
2. Super User:
The database's superuser has all the permissions on the database, and for security reasons, we'd better use a non-superuser to do our job properly. Unlike creating a normal user, you must create a superuser by executing the following command as Superuser:
Copy Code code as follows:
CREATE role name Superuser;
3. Create a database:
For a role to create a database, you must explicitly give the properties of the database to be created, as shown in the following command:
Copy Code code as follows:
CREATE role name Createdb;
4. Create a role:
For a role to create more roles, you must explicitly give the attributes that create the role, as follows:
Copy Code code as follows:
CREATE role name Createrole;
Third, the authority:
When a database object is created, it is assigned to an owner, usually 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 superuser) can do anything with 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 permission, such as:
Copy Code code as follows:
GRANT UPDATE on accounts to Joe;
For the above command, the meaning is to give the Joe role the update permission for the accounts table. In addition, we can use the special name public to assign the object's permissions to all the roles in the system. Write all on 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:
Copy Code code as follows:
REVOKE all in accounts from public;
The implication is to revoke all permissions (all) on the accounts object for all roles (public).
Iv. members of the role:
In the system's user management, more than one user is usually assigned to a group, which can be set only when permissions are set, or revoked from the group when revoking permissions. 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 that, in general, should not have the login attribute, such as:
Copy Code code as follows:
2. Add and Revoke permissions using the GRANT and REVOKE commands:
Copy Code code as follows:
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 object created thereafter will belong to that group, not to the original logged-on user.
2. Role members with the inherit attribute automatically inherit the permissions of the roles they belong to.
See the following example:
Copy Code code as follows:
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, then the database session will have both role Joe and role Admin permissions, because Joe Inherits (INHERIT) the permissions of admin. However, unlike this, the permissions given to the wheel role are not available in the 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. The permissions of the wheel role will not 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:
Copy Code code as follows:
After execution, the session will have only the permissions of the Admin role and no longer include the permissions assigned to the Joe role. Similarly, after the following command is executed, the session can only use the permissions given to wheel.
Copy Code code as follows:
After performing a period of time, if you still want to revert the session to its original permissions, you can use one of the following recovery methods:
Copy Code code as follows:
SET role Joe;
SET role NONE;
RESET role;
Note: The Set role command always allows you to select direct or indirect group roles for the current login role. Therefore, it is not necessary to become admin before becoming wheel.
Role Properties Login, Superuser, and Createrole are considered special permissions and are not inherited as normal permissions for other database objects. If necessary, the role that specifies the attribute must be displayed when the set is called. For example, we can also give the admin role createdb and Createrole permissions, and then connect to the database in Joe's role, when the session does not immediately have these special permissions, only if the current session does not have these permissions after the Set Roles Admin command is executed.
To delete a group role, execute the drop roles group_role command. However, after the group role is deleted, its relationship to its member roles is immediately revoked (the member roles themselves are not affected). However, it is important to note that any object belonging to the group role must be deleted before deletion or assigned to another role by the owner of the object, while any permissions assigned to the group role must be revoked.
