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

Use the concept of roles to manage database access. Roles can be database users and a group of database users, and can have database objects. -1. The database role is independent of the operating system user. Create roles: create role name; Delete roles: drop role name; identifies existing roles: select * from pg_roles; or\ du lists existing roles. The newly installed database has a predefined role by default: postgres. The set of database roles that can be connected to a given client connection is determined by client authentication settings.-2. Role attributes these attributes define their permissions and interact with the client authentication system. Login permission (login privilege): only roles with the login attribute can be used as the initial role name for the database connection. Create role name login; superuser status (superuser status): database superuser bypasses all permission checks except login permissions. Createrole name superuser; database creation gives the role permission to create a database: createrole name createdb; role creation grants the role permission to create more roles: createrole name createrole; initiating replication grants the role the right to initiate stream replication: specify the password when the role is created when the createrole name REPLICATION LOGIN; password database password is used separately from the operating system password. CREATEROLE name PASSWORD 'string'; good practice: create a role that has CREATEDB and CREATEROLE privileges but is not a superuser, and then use this role for all day-to-day management of the database and roles.-3 role members: permissions can be granted to or revoked from the entire group. Create roles first: after the create role name; group role exists, you can use the grant and revoke commands to add and remove members. The grant grouprole to role1...; revoke grouprole from role1...; database does not allow you to set up a circular membership loop. In addition, the granting of role membership PUBLIC is not allowed. For example, members of the CREATE ROLE joe LOGIN INHERIT; CREATE ROLE admin NOINHERIT; CREATE ROLE wheel NOINHERIT; GRANT admin TO joe; GRANT wheel TO admin; group role can use the permissions of the role in two ways. Set temporary group role: set role name; reset role: reset role; delete group role: drop role name; database session can access the permissions of the group role, and any database objects created are owned by the group role. The member role inherits the permissions of the group role. Users do not automatically inherit permissions when the role is executed. PostgreSQL defaults to the INHERIT attribute provided by all roles.

-4 remove roles: roles can have database objects and can have access to other objects. Before you delete a role, delete any objects owned by the role or reassign them to another owner; and you must revoke any permissions granted to the role. The ALTER TABLE bobs_table OWNER TO alice; or: REASSIGN OWNED command can be used to reassign ownership of all objects owned by the role to be deleted to a single other role. It must be run in each database that contains the objects owned by the role. DROP OWNED does not delete the entire database or tablespace. The most common way to delete roles that have been used to own objects is: REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role;-- in cluster DROP ROLE doomed_role;-5. Default role: access to commonly used functions and information. The role allows access to pg_read_all_settings to read all configuration variables, only those that are visible to the superuser. Pg_read_all_stats reads all pg_stat_ * views and uses various statistics-related extensions that are only visible to superusers. Pg_stat_scan_tables executes monitoring functions that ACCESS SHARE may lock on the table. Pg_signal_backend sends the signal to other backends (for example, cancel the query, terminate). Pg_monitor reads / executes various monitoring views and functions. Pg_read_all_settings,pg_read_all_stats and pg_stat_scan_tables, members of this role. Administrators monitor the purpose configuration of the database server: the pg_monitor,pg_read_all_settings,pg_read_all_stats and pg_stat_scan_tables roles, which grant a set of public permissions that allow roles to read a variety of useful configuration settings, statistics, and other system information that are usually limited to superusers. Administrators can use the GRANT command to grant users access to these roles: GRANT pg_signal_backend TO admin_user

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.