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

This paper briefly introduces the authority of PostgreSQL and the basic knowledge of user management. The original text is detailed in PostgreSQL Privileges & User Management-What You Should Know, which has been deleted and adjusted.

Roles

PostgreSQL uses a role-based rights management system.

The user user and the role role in PostgreSQL are the same thing, the difference is that the user has the LOGIN permission while the role does not, so it is no longer mentioned that the user is described as a role.

Testdb=# create role testrole with password 'test';CREATE ROLEtestdb=# create user testuser with password' test';CREATE ROLE

Log out of psql and log in as testrole and testuser respectively

Testdb=#\ Q [pg12@localhost ~] $psql-U testrole-d testdbpsql: error: could not connect to server: FATAL: role "testrole" is not permitted to log in [pg12@localhost ~] $psql-U testuser-d testdbpsql (12beta1) Type "help" for help.testdb= >

The following permissions are common options when creating roles:

SUPERUSER-Super user, SUPERUSER can create a new SUPERUSER,SUPERUSER to skip all permission checks.

CREATEDB-can create databases.

CREATEROLE-other roles can be created.

LOGIN-login is available.

In fact, even SUPERUSER cannot log in if you don't have LOGIN permission

Testdb=# create role user1 with password 'test'SUPERUSER CREATEROLE NOLOGIN;CREATE ROLEtestdb=#\ Q [pg12@localhost ~] $psql-U user1-d testdbpsql: error: could not connect to server: FATAL: role "user1" is not permitted to log in [pg12@localhost ~] $

Under psql, use the\ du command to view role information

Testdb=#\ du List of roles Role name | Attributes | Member of-+-- -+-pg12 | Superuser Create role, Create DB, Replication, Bypass RLS | {} replicator | Replication | {} testrole | Cannot login | {} testuser | | {} user1 | Superuser, Create role Cannot login | {} Informational (options: s = show system objects, + = additional detail).\ du [S+] [PATTERN] list roles.

Pg_hba.conf

Configure the connection between the server and the client and query the pg_setting view to retrieve where the current hba file is

Testdb=# SELECT name, settingtestdb-# FROM pg_settings WHERE name LIKE'% hba%'; name | setting-+-- hba_file | / data/pgsql/pg12db1/pg_hba.conf (1 row)

The entry for the hba file looks like the following settings

Local database user address auth-method [auth-options]

Where:

The first item refers to the connection mode. Local is Unix-domain sockets,host and TCP/IP connection.

The second item is the database, where all represents all

The third item is the user, and all represents all

The fourth item is the address, such as 192.168.0.0Universe 16

The fifth auth-method is the authentication method, including trust,reject,scram-sha-256,md5,password,gss,sspi,ident,peer,ldap,radius,cert,pam,bsd. As detailed, trust means that there is no need for password,password to represent plaintext passwords, md5 means to use md5 to encrypt password transmission, and so on.

You can view the current hba configuration by querying the pg_hba_file_rules view

Testdb=# SELECT * FROM pg_hba_file_rules Line_number | type | database | user_name | address | netmask | auth_method | options | error-+- -- +-84 | local | {all} | {all} | | trust | | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 87 | host | {all} | {all} | 192.168.0.0 | 255.255.0.0 | | | md5 | | 89 | host | {all} | {all} |:: 1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 92 | local | {replication} | {all} | | trust | | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 94 | host | {replication} | {all} |:: 1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | | 95 | host | {replication} | {all} | 192.168.26.26 | 255.255.255.255 | trust | | 96 | host | {replication} | {all} | 192.168.26.27 | 255.255.255.255 | trust | 97 | host | {replication} | {all} | 192.168.26.29 | 255.255.255.255 | trust | | (10 rows)

After modifying the pg_hba.conf file, you can refresh the configuration file to pg_hba_file_rules by pg_ctl reload command.

For example, delete the entry with line_number = 97 and refresh it.

Host replication all 192.168.26.26/32 trusthost replication all 192.168.26.27/32 trust~ X [pg12@localhost pg12db1] $pg_ctl reloadserver signaledtestdb=# SELECT * FROM pg_hba_file_rules Line_number | type | database | user_name | address | netmask | auth_method | options | error-+- -- +-84 | local | {all} | {all} | | trust | | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 87 | host | {all} | {all} | 192.168.0.0 | 255.255.0.0 | | | md5 | | 89 | host | {all} | {all} |:: 1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 92 | local | {replication} | {all} | | trust | | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 94 | host | {replication} | {all} |:: 1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | | 95 | host | {replication} | {all} | 192.168.26.26 | 255.255.255.255 | trust | | 96 | host | {replication} | {all} | 192.168.26.27 | 255.255.255.255 | trust | | (9 rows) |

Database, Table, and Column level privileges

Once Role is created, it has LOGIN permission, and the configuration in hba can access the database, then it has the right to manipulate the database, including creating data tables / inserting data, etc. DDL/DML, but if you need to access other objects created by owner, you need authorization.

For example, user pg12 creates the data table T1, but does not authorize demouser. Although demouser can access T1, it cannot query it.

[pg12@localhost] $psql-h 192.168.26.28-U demouser-d testdbPassword for user demouser: psql (12beta1) Type "help" for help.testdb= > create table T2 (id int); CREATE TABLEtestdb= > drop table T2 DROP TABLEtestdb= >\ d+ T1 Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description-+-- -+-id | integer | plain | | C1 | integer | plain | | c2 | integer | plain | | Access method: heaptestdb= > select * from T1 Psql: ERROR: permission denied for table t1

In addition, in order to achieve fine permission management, PostgreSQL provides column-level access authorization. The syntax of its GRANT statement is as follows, and column permissions can be specified in the column_name section:

GRANT {{SELECT | INSERT | UPDATE | REFERENCES} (column_name [,...]) [,...] | ALL [PRIVILEGES] (column_name [,...])} ON [TABLE] table_name [,...] TO role_specification [,...] [WITH GRANT OPTION]

Specify that t1.id can be accessed by demouser:

Testdb=# GRANT SELECT (id) ON TABLE T1 TO demouser;GRANT

Demouser can access the id column

Testdb= > select * from T1 politics psql: ERROR: permission denied for table T1 testDB = > select id from T1; id-(0 rows)

references

PostgreSQL Privileges & User Management-What You Should Know

CREATE ROLE

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.