本文转自社区谭峰(francs)的博客,原文地址:


"Row-Level Security (RLS) support" 是9.5版本的主要特性之一,提供了基于行的安全策略,限制数据库用户的查看表数据权限, 先来看以下例子。

  1. 创建测试表,插入测试数据
    fdb=> create table test_row(id serial primary key, username text, log_event text, create_time timestamp(0) without time zone default clock_timestamp());
    CREATE TABLE
    
    fdb=> insert into test_row(username,log_event) values('user1','user1:aaa');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user1','user1:aadsfdfa');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user2','user2:aadsfdfa');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user2','user2:test');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user3','user3:test3');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user3','user3:test3333');
    INSERT 0 1
    fdb=> insert into test_row(username,log_event) values('user4','user4:test3333');
    INSERT 0 1
    
  2. 创建 user1,user2,user3 测试用户
    [pg95@db1 ~]$ psql fdb
    psql (9.5alpha1)
    Type "help" for help.
    
    fdb=# create role user1 with login;
    CREATE ROLE
    fdb=# create role user2 with login;
    CREATE ROLE
    fdb=# create role user3 with login;
    CREATE ROLE
    
    fdb=> grant select on test_row to user1,user2,user3;
    GRANT
    
    fdb=> grant usage on schema fdb to user1,user2,user3;
    GRANT
    

  3. 以 user1 登陆可以查询全部数据
    fdb=> \c fdb user1
    You are now connected to database "fdb" as user "user1".
    
    fdb=> select * from fdb.test_row;
    id | username | log_event | create_time
    ----+----------+----------------+---------------------
    1 | user1 | user1:aaa | 2015-07-30 14:48:49
    2 | user1 | user1:aadsfdfa | 2015-07-30 14:48:54
    3 | user2 | user2:aadsfdfa | 2015-07-30 14:48:59
    4 | user2 | user2:test | 2015-07-30 14:49:06
    5 | user3 | user3:test3 | 2015-07-30 14:49:15
    6 | user3 | user3:test3333 | 2015-07-30 14:49:24
    7 | user4 | user4:test3333 | 2015-07-30 14:49:29
    (7 rows)
    

    备注:之前版本只要给数据库用户赋予 SELECT 权限,那么用户可以查看全表数据。

  4. 给表添加 policy
    [pg95@db1 ~]$ psql fdb fdb
    psql (9.5alpha1)
    Type "help" for help.
    
    fdb=> CREATE POLICY policy_test_row ON test_row
    fdb-> FOR SELECT
    fdb-> TO PUBLIC
    fdb-> USING (username = current_user);
    CREATE POLICY
    
    fdb=> select relname,relrowsecurity from pg_class where relname='test_row';
    relname | relrowsecurity
    ----------+----------------
    test_row | f
    (1 row)
    
    fdb=> ALTER TABLE test_row ENABLE ROW LEVEL SECURITY;
    ALTER TABLE
    
    fdb=> select relname,relrowsecurity from pg_class where relname='test_row';
    relname | relrowsecurity
    ----------+----------------
    test_row | t
    (1 row)
    

    备注:给表 test_row 添加 policy ,限制数据库登陆用户仅允许查看当前用户的日志记录。

  5. 测试
    user1 用户登陆
    fdb=> \c fdb user1
    You are now connected to database "fdb" as user "user1".
    
    fdb=> select * from fdb.test_row;
    id | username | log_event | create_time
    ----+----------+----------------+---------------------
    1 | user1 | user1:aaa | 2015-07-30 14:48:49
    2 | user1 | user1:aadsfdfa | 2015-07-30 14:48:54
    (2 rows)
    

    user2 用户登陆
    fdb=> \c fdb user2
    You are now connected to database "fdb" as user "user2".
    
    fdb=> select * from fdb.test_row;
    id | username | log_event | create_time
    ----+----------+----------------+---------------------
    3 | user2 | user2:aadsfdfa | 2015-07-30 14:48:59
    4 | user2 | user2:test | 2015-07-30 14:49:06
    (2 rows) 
    

    备注:user1 用户仅能查看 username 值为 'user1' 的记录,user2 用户仅能查看 username 值为 'user2' 的记录。

  6. 参考:
    Waiting for 9.5 – Row-Level Security Policies (RLS)
    CREATE POLICY

请在登录后发表评论,否则无法保存。
1楼 GUEST
2016-01-15 20:03:06+08

© 2010 PostgreSQL中文社区