|
PG行安全策略其实就是对不同用户，不同行数据的可见性，和可修改性。在我看来其实就是权限的维度不同，PG在9.5之前提供表级别，列级别的权限控制，9.5之后添加行级策略，这样用户就可以更加灵活的控制数据，对不同用户之间的数据隐私起到了保护作用。
先举一个列权限的例子,也可以通过视图的方式进行权限限制：
\c postgres postgres
postgres=> \d sex
Table "public.sex"
Column | Type | Collation | Nullable | Default
sex | character(1) | | |
otherinfo | text | | |
Indexes:
"idx_sex_1" btree (sex)
grant select (otherinfo) on sex to hank;
\c postgres hank
select * from sex;
ERROR: permission denied for table sex
select otherinfo from sex;
otherinfo
4157362this is test
表默认是没有任何行安全策略的， 行安全策略可以针对命令和角色指定。如命令SELECT、INSERT、UPDATE或DELETE。
CREATE POLICY
ALTER POLICY
DROP POLICY
命令帮助如下：
Command: CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
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 ) ]
这里需要注意USING和WITH CHECK选项:
- USING语句用于检查现有表中行的策略表达式
- WITH CHECK语句用于检查新行,也就是insert和update
postgres=> \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
id | integer | | not null | generated by default as identity
number | text | | |
client | text | | |
amount | numeric | | |
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_number_key" UNIQUE CONSTRAINT, btree (number)
postgres=
id | number | client | amount
1 | 1001 | alice | 1000.00
2 | 2001 | bob | 910.0000
3 | 2002 | bob | 0.00
4 | a | a | 3
(4 rows)
CREATE POLICY account_ids ON accounts TO hank USING (id = 1 or id =2);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY
postgres=
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
id | number | client | amount
1 | 1001 | alice | 1000.00
2 | 2001 | bob | 910.0000
如果启用行策略，但是实际没有行策略，那么查询不到数据
postgres=> \c postgres postgres
postgres=
Table "public.accounts"
Column | Type | Collation | Nullable | Default
id | integer | | not null | generated by default as identity
number | text | | |
client | text | | |
amount | numeric | | |
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_number_key" UNIQUE CONSTRAINT, btree (number)
Policies (forced row security enabled): (none)
postgres=
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
(0 rows)
如果用户想要访问所有数据，不受行策略影响,也可以修改role属性为bypassrls，绕过行安全策略，示例如下：
postgres=> \c postgres postgres
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "postgres".
postgres=
ALTER ROLE
postgres=
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
id | number | client | amount
1 | 1001 | alice | 1000.00
2 | 2001 | bob | 910.0000
3 | 2002 | bob | 0.00
4 | a | a | 3
可查询策略相关视图：
postgres=> select * from pg_policies ;
schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
public | accounts | account_ids | PERMISSIVE | {hank} | ALL | ((id = 1) OR (id = 2)) |
(1 row)
ostgres=> select * from pg_policy;
-[ RECORD 1 ]-+
oid | 106391
polname | account_ids
polrelid | 48472
polcmd | *
polpermissive | t
polroles | {16449}
polqual | {BOOLEXPR :boolop or :args ({OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 54} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 59 :constvalue 4 [ 1 0 0 0 0 0 0 0 ]}) :location 57} {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 64} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 69 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location 67}) :location 61}
polwithcheck |
另外一下两个解释一下：
PERMISSIVE：默认选项，适用于一个给定查询的所有宽容性策略将被使用布尔“OR”操作符组合在一起。通过创该策略，可以在能被访问的记录集合中进行增加。如：
CREATE POLICY account_ids on accounts TO hank USING (id = 1 or id =2);
CREATE POLICY account_ids on accounts TO hank USING (id = 1 or id =2 or id =3);
RESTRICTIVE：限制性策略。适用于一个给定查询的所有限制性策略将被使用布
尔“AND”操作符组合在一起。通过创建限制性策略，管理员可以减少能被访问的记录集合，因为每一条记录都必须通过所有的限制性策略。在限制性策略作用之前，需要至少一条PERMISSIVE策略授予行的访问权限。如果只有限制性策略存在，则所有记录都不能被访问。当PERMISSIVE和RESTRICTIVE混合存在时，只有当一个记录能通过至少一条PERMISSIVE策略以及所有的限制性策略时，该记录才是可访问的。
以上说明是官方文档解释，一下难以理解，下面举个例子说明：
只有RESTRICTIVE的policy,记录都不显示
postgres=
CREATE POLICY
postgres=
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
id | number | client | amount
(0 rows)
添加PERMISSIVE属性的policy
postgres=> \c postgres postgres
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=
CREATE POLICY
postgres=
psql (13.4, server 12.6)
postgres=> select * from accounts;
id | number | client | amount
1 | 1001 | alice | 1000.00
postgres=> \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
id | integer | | not null | generated by default as identity
number | text | | |
client | text | | |
amount | numeric | | |
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_number_key" UNIQUE CONSTRAINT, btree (number)
Policies (forced row security enabled):
POLICY "account_ids"
TO hank
USING (((id = 1) OR (id = 2)))
POLICY "account_ids_restric" AS RESTRICTIVE
TO hank
USING (((id = 1) AND (client = 'alice'::text)))
postgres=> select * from pg_policies ;
schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
public | accounts | account_ids_restric | RESTRICTIVE | {hank} | ALL | ((id = 1) AND (client = 'alice'::text)) |
public | accounts | account_ids | PERMISSIVE | {hank} | ALL | ((id = 1) OR (id = 2)) |
参考：
https://www.postgresql.org/docs/13/sql-createpolicy.html
https://www.postgresql.org/docs/13/ddl-rowsecurity.html
|