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

PostgreSQL provides Row Level Security (RLS) to control which rows can be accessed or modified.

I. brief introduction

Its standard syntax is:

[pg12@localhost] $psql-d testdbTiming is on.Expanded display is used automatically.psql Type "help" for help. [local]: 5432 pg12@testdb=#\ help create policyCommand: CREATE POLICYDescription: define a new row level security policy for a tableSyntax:CREATE POLICY name ON table_name [AS {PERMISSIVE | RESTRICTIVE}] [FOR {ALL | SELECT | INSERT | UPDATE | DELETE}] [TO {role_name | PUBLIC | CURRENT_USER | SESSION_USER} [ [USING (using_expression)] [WITH CHECK (check_expression)] URL: https://www.postgresql.org/docs/12/sql-createpolicy.html[local]:5432 pg12@testdb=#

Where USING indicates that only lines that satisfy the using_expression expression T can be seen or manipulated, otherwise these lines will be hidden (hidden)

WITH CHECK means that when performing an INSERT/UPDATE operation, it is considered illegal if the new data fails to pass the validation of the check_expression expression.

II. Points for attention

1.RLS must pass ALTER TABLE … ENABLE ROW LEVEL SECURITY is explicitly enabled

two。 If RLS is enabled, at least one policy exists

Create datasheets, insert data, enable RLS

[local]: 5432 pg12@testdb=# drop table if exists tactirs1: notee: table "t_rls1" does not exist, skippingDROP TABLETime: 37.363 ms [local]: 5432 pg12@testdb=# create table t_rls1 (id int,c1 int); CREATE TABLETime: 137.931 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_rls1 values (1jue 1); INSERT 0 1Time: 1.820 ms [local]: 5432 pg12@testdb=# insert into t_rls1 values (2jue 2) INSERT 0 1Time: 0.582 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# ALTER TABLE t_rls1 ENABLE ROW LEVEL SECURITY;ALTER TABLETime: 1.714 ms [local]: 5432 pg12@testdb=# select * from tweerls1; id | C1-+-1 | 12 | 2 (2 rows)

Create a new user and query the data table, because there is no policy, so no data is returned (but why can the user pg12? There is an explanation later)

Time: 23.349 ms [local]: 5432 pg12@testdb=# create user testuser with passwod 'test';ERROR: unrecognized role option "passwod" LINE 1: create user testuser with passwod' test'; ^ Time: 1.165 ms [local]: 5432 pg12@testdb=# create user testuser with password 'test';CREATE ROLETime: 15.721 ms [local]: 5432 pg12@testdb=# grant all on t_rls1 to testuser;GRANTTime: 8.125 ms [local]: 5432 pg12@testdb=#

3. The relationship between multiple policys of the same command and the same type is OR, that is, if a line satisfies one of the policy, it can be returned (such as the select command); if the same command is of different command types, the relationship between multiple policys is AND, and all policys must be satisfied before it can be returned (for example, if there is a where clause in the update).

4. Only the owner of the table can create a policys

5. Superusers or users with BYPASSRLS privileges skip the RLS check. This explains why there was no policy previously, but the pg12 query returned data but the testuser user did not.

[local]: 5432 pg12@testdb=#\ du List of roles Role name | Attributes | Member of-+- -+-pg12 | Superuser Create role, Create DB, Replication, Bypass RLS | {} testuser |

6. Owner users of data tables skip the RLS check by default

[local]: 5432 testuser@testdb= > create table t_rls2 (id int,c1 int); CREATE TABLETime: 10.256 ms [local]: 5432 testuser@testdb= > [local]: 5432 testuser@testdb= > insert into t_rls2 values (1 INSERT 1); INSERT 0 1Time: 3.422 ms [local]: 5432 testuser@testdb= > insert into t_rls2 values (2 Magazine 2); INSERT 0 1Time: 2.580 ms [local]: 5432 testuser@testdb= > [local]: 5432 testuser@testdb= > ALTER TABLE t_rls2 ENABLE ROW LEVEL SECURITY ALTER TABLETime: 2.124 ms [local]: 5432 testuser@testdb= > select * from t_rls2 Id | C1-+-1 | 12 | 2 (2 rows) Time: 1.127 ms [local]: 5432 testuser@testdb= >\ d t_rls2 Table "public.t_rls2" Column | Type | Collation | Nullable | Default-+-id | | integer | C1 | integer | Policies (row security enabled): (none) |

Testuser1&testuser2 are all ordinary users, but testuser1 query data is returned, but testuser2 does not.

[pg12@localhost] $psql-d testdb-U testuser2Timing is on.Expanded display is used automatically.psql (12.0) Type "help" for help. [local]: 5432 testuser2@testdb= > select * from tweerls2; id | C1-+-(0 rows) Time: 3.808 ms [local]: 5432 testuser2@testdb= >

Available through ALTER TABLE... The FORCE ROW LEVEL SECURITY command forces enable

[local]: 5432 testuser@testdb= > ALTER TABLE t_rls2 FORCE ROW LEVEL SECURITY;ALTER TABLETime: 1.967 ms [local]: 5432 testuser@testdb= > select * from tweerls2; id | C1-(0 rows) Time: 2.369 ms

Specific application cases can be found in the links in Resources.

III. Reference materials

PG Conf of US 2019-Row Level Security

Using "Row Level Security" to make large companies more secure

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.