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

This article mainly explains "how to understand the PostgreSQL line security policy". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand the PostgreSQL line security policy".

In addition to using the SQL standard privileged system through GRANT, a table can also have a row security policy that restricts which rows can be returned by a normal query or inserted, updated, or deleted by data modification commands for each user. This feature is also known as row-level security. By default, the table does not have any policy, so the user has access to the table according to the SQL privilege system, and all rows are equal for queries or updates.

When row security is enabled on a table (using ALTER TABLE... ENABLE ROW LEVEL SECURITY), all normal access to select or modify rows of the table must be allowed by a row security policy (however, the owner of the table usually disobeys the row security policy). If no policy exists on the table, a default negative policy is used, that is, all rows are invisible or cannot be modified. Operations applied to the entire table do not obey row security, such as TRUNCATE and REFERENCES.

Row security policies can be specific to specific commands, roles, or both. A policy can be specified to apply to the ALL command, or SELECT, INSERT, UPDATE, or DELETE. Multiple roles can be assigned to a given policy, and the usual role membership and inheritance rules also apply.

To specify which rows are visible or modifiable according to a policy, you need an expression that returns a Boolean result. For each row, the expression is counted before any condition or function from the user query is evaluated (the only exception to this rule is the leakproof function, which is guaranteed not to disclose information, and the optimizer may choose to apply such functions before the row security check). Lines that prevent the expression from returning true will not be processed. You can specify independent expressions to independently control which lines are visible and which lines are allowed to be modified. The policy expression runs as part of the query and has the privileges of the user running the query, but the security definer function can be used to access data that is not available to the calling user.

Superusers and roles with the BYPASSRLS attribute can always bypass the row security system when accessing a table. Table owners can usually bypass row security, but table owners can choose to use ALTER TABLE. FORCE ROW LEVEL SECURITY to obey row security.

Using and disabling row security and adding policies to the table are privileges that only table owners have.

The policy can be created using the CREATE POLICY command, the policy can be modified using the ALTER POLICY command, and the policy can be deleted using the DROP POLICY command. To enable or disable row security for a given table, use the ALTER TABLE command.

Each policy has a name and can define multiple policies for a table. Because policies are table-dependent, each policy of a table must have a unique name. Different tables can have policies with the same name.

When multiple policies apply to a given query, they are combined with OR so that rows are accessible as long as either policy allows. This is similar to the rule that a given role has the privileges of all roles to which it belongs.

As a simple example, here is how to create a policy on the account relationship to allow only members of the managers role to access rows and only lines of their accounts:

CREATE TABLE accounts (manager text, company text, contact_email text)

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY

CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user)

The above policy implicitly provides a with check clause to identify its using clause, so this constraint applies to selected rows selected by commands (so a manager cannot select,update or delete existing lines that belong to different management capitals) and lines that can be modified by commands (so rows belonging to different managers cannot be created through insert or update).

If no role is specified or the specified user name is public, this smoke will be applied to all users in the system. To allow all users to access only row records in one user table, you can use the following simple and policy:

CREATE POLICY user_policy ON users USING (user_name = current_user)

This is similar to the previous example

To use different policies for rows and visible exercises added to the table, you can combine multiple policies. This pair of policies will allow all users to view all rows in the users table, but only modify their own row records:

CREATE POLICY user_sel_policy ON users FOR SELECT USING (true)

CREATE POLICY user_mod_policy ON users USING (user_name = current_user)

In the SELECT command, use a combination of OR to use these two strategies, and the end result is that all rows can be selected. In other command types, only the second policy is applied, so the effect is the same as before.

Line security policies can also be disabled using the alter table command. Disabling row security policies does not delete any policies defined on the table, they are simply ignored. Then all rows in the table are visible and can be modified, subject to the standard SQL privileged system.

The following is a larger example of how this feature can be used in a production environment. Table passwd simulates a Unix password file:

A simple example of a password file

Jydb=# CREATE TABLE passwd (jydb (# user_name text UNIQUE NOT NULL,jydb (# pwhash text,jydb) # uid int PRIMARY KEY,jydb (# gid int NOT NULL,jydb (# home_phone text,jydb) (# extra_info text,jydb (# shell text NOT NULLjydb (#); CREATE TABLE

-- create a user:

Jydb=# CREATE ROLE admin;CREATE ROLEjydb=# CREATE ROLE bob;CREATE ROLEjydb=# CREATE ROLE alice;CREATE ROLE

-- insert data into the table

Jydb=# INSERT INTO passwd VALUES ('admin','xxx',0,0,'Admin','111-2222-3333); INSERT 0 1jydb=# INSERT INTO passwd VALUES (' bob','xxx',1,1,'Bob','123-456-7890)) and INSERT 0 dash.' INSERT 0 1jydb=# INSERT INTO passwd VALUES ('alice','xxx',2,1,'Alice','098-765-4321); INSERT 0 1

Make sure row-level security is enabled on the table

Jydb=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;ALTER TABLE

Create a policy

-- the administrator can see all lines and add any lines

Jydb=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); CREATE POLICY

-- ordinary users can see all lines

Jydb=# CREATE POLICY all_view ON passwd FOR SELECT USING (true); CREATE POLICY

-- ordinary users can update their own records, but limit the shell available to ordinary users

Jydb=# CREATE POLICY user_mod ON passwd FOR UPDATEjydb-# USING (current_user = user_name) jydb-# WITH CHECK (# current_user = user_name ANDjydb (# shell IN ('/ bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') jydb (#); CREATE POLICY

-- allow admin to have all normal permissions

Jydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;GRANT

-- ordinary users only get selective access on public columns

Jydb=# GRANT SELECTjydb-# (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) jydb-# ON passwd TO public;GRANT

Allow ordinary users to update specific lines

Jydb=# GRANT UPDATEjydb-# (pwhash, real_name, home_phone, extra_info, shell) jydb-# ON passwd TO public;GRANT

For any security setting, it is important to test and ensure that the system behaves as expected. Using the above example, the following shows that the permission system works correctly:

-- admin can see all rows and fields

Jydb=# set role admin;SETjydb= > table passwd User_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell-+ -admin | xxx | 0 | Admin | 1112222-3333 | | / root | / bin/dash bob | xxx | 1 | 1 | Bob | 123,456-7890 | | / home/bob | / bin/zsh alice | xxx | 2 | 1 | Alice | 098765-4321 | | / home/ Alice | / bin/zsh (3 rows) jydb= > select * from passwd User_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell-+ -admin | xxx | 0 | Admin | 1112222-3333 | | / root | / bin/dash bob | xxx | 1 | 1 | Bob | 123,456-7890 | | / home/bob | / bin/zsh alice | xxx | 2 | 1 | Alice | 098765-4321 | | / home/alice | / bin/zsh (3 rows)

-- testing what Alice can do

Jydb= > set role alice;SETjydb= > table passwd;ERROR: permission denied for relation passwdjydb= > select * from passwd;ERROR: permission denied for relation passwdjydb= > select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd User_name | real_name | home_phone | extra_info | home_dir | shell-+-admin | Admin | 111222-3333 | | | / root | / bin/dash bob | Bob | 123,456-7890 | | / home/bob | / bin/zsh alice | Alice | 098765-4321 | | / home/alice | / bin/zsh (3 rows) jydb= > update passwd set user_name = 'joe' | ERROR: permission denied for relation passwd

Alice is allowed to change her own real_name, but not others.

Jydb= > update passwd set real_name = 'Alice Doe';UPDATE 1jybb = > update passwd set real_name =' John Doe' where user_name = 'admin';UPDATE 0jybb = > update passwd set shell =' / bin/xx';ERROR: new row violates row-level security policy for table "passwd" jydb= > delete from passwd;ERROR: permission denied for relation passwdjydb= > insert into passwd (user_name) values ('xxx'); ERROR: permission denied for relation passwd

-- Alice can change her own password; row-level security quietly prevents other lines from being updated

Jydb= > update passwd set pwhash = 'abc';UPDATE 1

Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row-level security policies to ensure that data integrity is maintained. Care must be taken to prevent "covert channels" from leaking information through such referential integrity checks when developing patterns and row-level security policies.

It is important to ensure that row-level security policies are not applied in some environments. For example, when performing a backup, it would be catastrophic if the row-level security policy silently caused the backup operation to ignore some rows of data. In this situation, you can set the row_security configuration parameter to off. This in itself does not bypass the row-level security policy, and if any query results are filtered out of the record because of the row-level security policy, an error is thrown, and then you can find the cause of the error and fix it.

In the above example, the policy expression only considers the current value in the row to be accessed or updated. This is the simplest and best-performing situation. If possible, it is best to design row-level security policy applications to work in this way. If you need to refer to other rows or other tables to make a policy decision, you can do so in the policy expression by using the child-SELECTs or a function that contains SELECT. It is important to note, however, that such visits may lead to competitive conditions, which can lead to information disclosure if not careful. As an example, consider the following table design:

-- define permission groups

Jydb= > CREATE TABLE groups (group_id int PRIMARY KEY,group_name text NOT NULL); CREATE TABLEjydb= > INSERT INTO groups VALUESjydb- > (1, 'low'), jydb- > (2,' medium'), jydb- > (5, 'high'); INSERT 0 3jydb= > GRANT ALL ON groups TO alice;GRANTjydb= > GRANT SELECT ON groups TO public;GRANTjydb= > select * from groups Group_id | group_name-+-1 | low 2 | medium 5 | high (3 rows)

-- define the permission level of the user

Jydb=# CREATE TABLE users (user_name text PRIMARY KEY,jydb (# group_id int NOT NULL REFERENCES groups); CREATE TABLEjydb=# INSERT INTO users VALUESjydb-# ('alice', 5), jydb-# (' bob', 2), jydb-# ('mallory', 2); INSERT 0 3jydb=# GRANT ALL ON users TO alice;GRANTjydb=# GRANT SELECT ON users TO public;GRANTjydb=# CREATE ROLE mallory;CREATE ROLEjydb=# select * from users User_name | group_id-+-alice | 5 bob | 2 mallory | 2 (3 rows)

-- tables of saved information will be protected

Jydb=# CREATE TABLE information (info text,jydb (# group_id int NOT NULL REFERENCES groups); CREATE TABLEjydb=# INSERT INTO information VALUESjydb-# ('barely secret', 1), jydb-# (' slightly secret', 2), jydb-# ('very secret', 5); INSERT 0 3jydb=# ALTER TABLE information ENABLE ROW LEVEL SECURITY;ALTER TABLE

If the user's security policy group_id is greater than or equal to the group_id of the line, this line of record should be visible or updatable

Jydb=# CREATE POLICY fp_s ON information FOR SELECTjydb-# USING (group_id

< = (SELECT group_id FROM users WHERE user_name = current_user));CREATE POLICYjydb=# CREATE POLICY fp_u ON information FOR UPDATEjydb-# USING (group_id select * from users; user_name | group_id -----------+---------- alice | 5 bob | 2 mallory | 1(3 rows)jydb=>

Select * from information; info | group_id-+-barely secret | 1 very secret | 5 secret from mallory | 2 (3 rows)

-- check whether user mallory can view the records of group_id=2 in the information table

Jydb= > set role mallory; SETjydb= > SELECT * FROM information WHERE group_id = 2; info | group_id-+-(0 rows) jydb= > SELECT * FROM information; info | group_id-+-barely secret | 1 (1 row)

You can see that the existing user mallory cannot view the record with group_id 2 in the table information because the group_id in the users table has been modified to 1.

This looks safe, and there is no window for the user mallory to see the "secret from mallory" string. But there is a competitive condition. If mallory is doing this in parallel:

SELECT * FROM information WHERE group_id = 2 FOR UPDATE

And her transaction is in READ COMMITTED mode, she may see the "secret from mallory" string. This happens if her transaction reaches the row record of the information table after alice is done. It blocks the transaction waiting for alice to commit, and then gets the updated row content thanks to the FOR UPDATE clause. However, for an implicit SELECT from users, it does not get an updated row because the child-SELECT does not have a FOR UPDATE, but instead reads the users row using the snapshot taken at the beginning of the query. So the policy expression tests the old value of mallory's permission level and allows her to see the rows being updated.

There are many ways to solve this problem. A simple answer is to use SELECT in the sub-SELECT of the row security policy. FOR SHARE . However, this requires that UPDATE privileges be granted to affected users on the referenced table (in this case users), which may not be what we want (but another row security policy may be applied to prevent them from actually using this privilege, or the sub-SELECT may be embedded in a security definer function). Also, using too many row shared locks concurrently on referenced tables can cause performance problems, especially if the table is updated frequently. Another solution (which is feasible if updates on the referenced table are infrequent) is to take an exclusive lock on the referenced table when it is updated so that no concurrent transactions can check the old row values. Or we can wait for all concurrent transactions to end after we commit updates to the referenced table and before making changes that depend on the new security situation.

Thank you for reading, the above is the content of "how to understand PostgreSQL line security policy". After the study of this article, I believe you have a deeper understanding of how to understand PostgreSQL line security policy, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.