赋予查询指定列的权限
postgres=# create table test(name text,credit text,passwd text);
CREATE TABLE
postgres=# insert into test values('zhangsan','11111123','asdf');
INSERT 0 1
postgres=# create user customer;
CREATE ROLE
postgres=# grant connect on database postgres to customer;
GRANT
postgres=# \c postgres customer;
You are now connected to database "postgres" as user "customer".
postgres=>
postgres=>
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke select on table test from customer;
REVOKE
postgres=# grant select(name,credit) on test to customer;
GRANT
postgres=# \c postgres customer;
You are now connected to database "postgres" as user "customer".
postgres=> select * from test;
ERROR: permission denied for table test
postgres=> select name,credit from test;
name | credit
----------+----------
zhangsan | 11111123
(1 row)
赋予查询限定行权限
postgres=# create table test(id int ,name text);
CREATE TABLE
postgres=# insert into test values (1,'zhangshan');
INSERT 0 1
postgres=# insert into test values (2,'lisi');
INSERT 0 1
postgres=# select * from test;
id | name
----+-----------
1 | zhangshan
2 | lisi
(2 rows)
postgres=# \h create 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 ) ]
URL: https://www.postgresql.org/docs/12/sql-createpolicy.html
postgres=#
postgres=# create policy mypolicy on test using (name=current_user);
CREATE POLICY
postgres=# create user zhangshan;
CREATE ROLE
postgres=# create user lisi;
CREATE ROLE
postgres=# select * from test;
id | name
----+-----------
1 | zhangshan
2 | lisi
(2 rows)
postgres=# alter table test enable row level security;
ALTER TABLE
postgres=# grant select on table test to lisi;
GRANT
postgres=# grant select on table test to zhangshan;
GRANT
postgres=# \c postgres lisi;
You are now connected to database "postgres" as user "lisi".
postgres=> select * from test;
id | name
----+------
2 | lisi
(1 row)
postgres=> \c postgres zhangshan;
You are now connected to database "postgres" as user "zhangshan".
postgres=> select * from test;
id | name
----+-----------
1 | zhangshan
(1 row)
如何查看表被赋予的权限
ostgres=# \z test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | test | table | postgres=arwdDxt/postgres | info: +|
| | | | u2=r/postgres |
(1 row)
a -- INSERT ("插入，针对表或视图")
r -- SELECT ("查询，针对表或视图")
w -- UPDATE ("更新，针对表或视图")
d -- DELETE("删除，针对表或视图")
D – TRUNCATE("清空记录，针对表")
x – REFERENCES("参照引用，针对表")
t -- TRIGGER("创建触发器，针对表")
X -- EXECUTE("执行，针对存储过程等pl/sql对象")
U -- USAGE("使用权，针对模式")
C -- CREATE("创建对象，针对模式或者数据库")
c -- CONNECT("创建连接，针对数据库")
T -- TEMPORARY("创建临时表，针对数据库")