Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to assign accounts and permissions to users who log in in the PostgreSQL database". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to assign accounts and permissions to users who log in in the PostgreSQL database".

Accounts and permissions should be assigned to logged-in users

For this evaluation item, first of all, we need to find out the corresponding permissions of the postgresql database.

In the postgresqll database, the user is considered to be a corresponding role,PostgreSQL that uses the concept of roles to manage database access. A role can be thought of as a database user or a database user group, depending on how the role is set. Roles can own database objects (for example, tables and functions) and can assign permissions on those objects to other roles to control who can access which objects. In addition, membership in one role can be granted to another role, allowing member roles to use permissions assigned to another role.

1. System permissions

1.1 role Properties

It can be thought of as the system permissions that this user has.

LOGIN-with login permission

SUPERUSER-Super user with all system privileges except login authentication

CREATEDB-create database permissions

CREATEROLE-create role permissions

PASSWORD-set password

1.2 modify properties

Create a test role

Create role test login

Alter role test createdb createrole password'* * @ 123'

Alter role test nocreatedb nocreaterole superuser

Logging in through the psql program allows you to list existing roles and corresponding role permissions using the\ du command

2.role member permissions

Role membership (role member) for administrative convenience, we can create a role group, and then we can organize each user or role with special permissions together, and each role is the membership of this role group.

Role group is a role without login, because pg uses role to represent all roles, users, and user groups, so don't be confused, the creation statements are all create role.

2.1 inherit role group

1) Direct inheritance

We create a user, two roles, respectively, with query permissions directly to a table, create role jack login inherit;create role R1 position create role R2.

Then create the corresponding table

Grant query permission

Do grant authorization to make jack the membership of R1 and R2.

Test role switching

Jack inherits the permissions of R1 and R2.

2) indirect inheritance

Remove membership

You can use revoke group_role from role1,... Command

At this point, R1 belongs to R2, and then jack belongs to R1

Turn off inheritance for R1

Alter role r1 noinherit

Then try to access tab, and R1 does not have the right to restrict access when it is found that tab2 cannot be accessed and the inheritance permission is cancelled.

There will be no permission after removal.

Note: authorization cannot form a loop

3) system permissions will not be inherited actively at any time

System permissions will not be inherited, only active set will take effect

After using the set command, the session has temporary system permissions for group_role and is considered to be a group_role role, and the tables created are all set role permissions

No permissions after reopening the session

There are three ways to revert to the original jack role:

To use these attributes, you must actually set role to a specific role with one of these attributes.

4) role deletion

Delete cannot be deleted if you have permission under role,role or if the object belongs to this role.

Remove the relevant permission association and delete it, and then involve members of R1 or group_role to automatically release.

5) role summary

Role in pg contains all the meanings of users, roles, role groups, members, etc., such as using create role to create

A role can become a member of multiple role and decide whether to integrate various permissions of other role according to the inherit attribute of the role.

Inheritance relationship cannot form a loop

To delete a role, you need to first clean up the various permissions associated with this role.

3. Wait for the guarantee to check the corresponding points

Check what roles are in the current database, which can be viewed through the tool

Then check the permissions corresponding to the corresponding roles.

The command line interface can list existing roles and permissions through the\ du command

Personally, according to the first clause, no matter whether the permission assignment is reasonable or not, as long as the user is assigned permission, it is considered to be in accordance with the first clause.

The main purpose of this clause is to prevent anonymous users from logging in, such as NTP anonymous login

As to whether the authority is reasonable, it has been reflected in the following minimum authority, the separation of the authority of the administrative user, and the granularity of access control, which should be judged in those terms. So for the first item, we just need to see whether he has assigned the corresponding account and permission to the user.

The default account should be renamed or deleted and the default password of the default account should be changed. Default role

You can view it through the tool

PostgreSQL provides a default set of roles that can access specific and commonly required privileged functions and information. Administrators can GRANT these roles to users and / or other roles in their environment, providing these users with access to specified functions and information. Note that the specific permissions for each default role may change as additional features are added in the future. The administrator should monitor the release notes for changes, as shown in the following figure:

The permissions allowed by the role pg_read_all_settings read all configuration variables, even those that are usually visible only to superusers. Pg_read_all_stats reads all the pg_stat_* views and uses various statistics-related extensions, even those that are usually visible only to superusers. Pg_stat_scan_tables performs monitoring functions that may take a long time for ACCESS SHARE locking on tables. Pg_signal_backend sends signals to other backends (such as canceling queries, terminating). Pg_monitor reads / executes various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables.

The pg_mointor, pg_read_all_settings, pg_read_all_stats, and pg_scan_tables roles are designed to allow administrators to easily configure roles to see the database server. They grant a set of general permissions that allow roles to read a variety of useful configuration settings, statistics, and other system information that are usually limited to superusers. You should be careful to grant these roles to ensure that they are used only if you need to perform the required monitoring. Administrators can use the grant command to grant access to these users: the default roles mentioned above in grant pg_signal_backend to admin_user; are not logged in by default.

two。 Wait for the guarantee to check the corresponding points

The default account is postgres. Check whether it is set to disable login, or rename it under the psql program. You can view it through the\ du command. If you cannot log in, you will have the word cannot login.

The above is all the contents of the article "how to assign accounts and permissions to logged-in users in the PostgreSQL database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.