Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to achieve user and rights management in PostgreSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Create a user

CREATE ROLE rolename;CREATE USER username; Note: the difference between CREATE USER and CREATE ROLE is that users created by the CREATE USER directive have login privileges by default, while CREATE ROLE does not.

Set user rights attributes when creating a user

Basic grammatical format CREATE ROLE role_name WITH optional_permissions

Example: set login permissions when you create a user. CREATE ROLE username WITH LOGIN; Note: you can view all the configurable administrative permissions through the\ h CREATE ROLE directive

Modify user rights properties

Modify the command format of permissions ALTER ROLE username WITH attribute_options

For example, users can be prohibited from logging in to ALTER ROLE username WITH NOLOGIN in the following ways

Set access permissions

The syntax format is as follows: GRANT permission_type ON table_name TO role_name

Example: GRANT UPDATE ON demo TO demo_role;-- update permissions for demo_role demo tables GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role;-- Special symbols for SELECT permissions for all tables in demo_role: ALL for access permissions, PUBLIC for all users GRANT ALL ON demo TO demo_role;-- all permissions GRANT SELECT ON demo TO PUBLIC for users -- assign SELECT permissions to all users Note:\ z or\ dp directive shows user access rights. \ h GRANT displays all settable access permissions

Revoke user access

The syntax format is as follows: REVOKE permission_type ON table_name FROM user_name; where permission_type and table_name have the same meaning as in the GRANT instruction.

User groups in postgres the user is actually role, and the group is also role. The role that contains other role is the group.

Create a group example: CREATE ROLE temporary_users; GRANT temporary_users TO demo_role; GRANT temporary_users TO test_user

Switch ROLE SET ROLE role_name;-- switch to role_name user RESET ROLE;-- switch back to the original role

INHERIT permissions: this attribute enables group members to have all the permissions of the group ALTER ROLE test_user INHERIT

Delete users and groups

Deleting users and groups is simple:

After reading the above content in DROP ROLE role_name;DROP ROLE IF EXISTS role_name;, do you have any further understanding of how to implement user and rights management in PostgreSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.