Navigation

    MemFireDB论坛

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups

    教程36:PostgreSQL知识分享-强大的RLS

    MemFireDB培训
    2
    2
    20
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • M
      MemFireDB Robot last edited by MemFireDB Robot

      RLS是什么?

      全称:Row level security,行级安全,允许系统管理员为数据库表创建访问策略(policy),以约束数据的可见性。

      当为一个表创建了policy后,类似于为该表增加了一个高优先级的过滤器。当用户访问该表时,如果policy生效,则会根据policy中定义的过滤条件来决定用户可操作的数据集合。

      如何开启?

      RLS默认是不开启的,需要针对每个表执行如下语句来对该表开启RLS功能:

      ALTER TABLE <name> ENABLE ROW LEVEL SECURITY
      

      默认策略

      当用户开启了RLS,但没有创建任何policy,系统会启用默认策略,该策略拒绝用户访问该表中的任何数据。

      tips:用户如果在页面上开启了RLS,就需要创建一条策略。

      RLS 和 Grant有什么关系?

      grant是SQL标准语法,定义了一系列的数据访问权限的设置功能,比如允许用户对表执行查询操作,允许用户访问表中的哪些列。但是不能定义允许用户访问哪些行。

      因此:RLS在某种程度上是对GRANT的一种补充。

      RLS生效用户

      三类用户不起作用:

      • superuser
      • owner
      • bypassrls

      举例:

      create table photos(username text, pic text);
      insert into photos values('zhangsan', '1.jpg'), ('lisi', '2.jpg');
      
      create user zhangsan;
      create user lisi;
      
      grant select on table photos to zhangsan;
      grant select on table photos to lisi;
      
      -- 开启rls(没有创建policy的情况下,启用默认策略)
      alter table photos ENABLE ROW LEVEL SECURITY;
      
      -- 以memfire用户查询数据(superuser)
      select * from photos;
      
      -- 以zhangsan用户查询数据
      set role  zhangsan;
      select * from photos;
      
      -- 将zhangsan设置为owner,再查询数据
      set role memfire;
      alter table photos owner to zhangsan;
      set role zhangsan;
      select * from photos;
      
      -- 给lisi赋予bypassrls权限
      alter user lisi with bypassrls;
      set role lisi;
      select * from photos;
      

      如果想对owner也启用rls,语法如下:

      ALTER TABLE ... FORCE ROW LEVEL SECURITY.
      

      Policy

      • 语法

          CREATE POLICY name ON table_name
              [ AS { PERMISSIVE | RESTRICTIVE } ]
              [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
              [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
              [ USING ( using_expression ) ]
              [ WITH CHECK ( check_expression ) ]
        
        • name:同一个表上的policy不能重复,不同表的policy可以重复
        • table_name:为哪个表创建policy
        • AS,policy的生效模式,PERMISSIVE => or,RESTRICTIVE => and,默认PERMISSIVE
        • For,对哪个操作生效,默认ALL
        • TO,对哪个role生效,默认public
        • USING:对表中的已有数据进行检查的语句
        • WITH CHECK:对新数据进行检查的语句
      • 举例

      CREATE POLICY user_policy ON photos
          USING (username = current_user)
          with check(true);
          
       select * from photos where username = current_user;
      
      • 多条policy的生效机制
      CREATE POLICY user_sel_policy ON photos
          FOR SELECT
          USING (true);
      CREATE POLICY user_mod_policy ON photos
          USING (user_name = current_user);
      
      • 上面这两条policy,第一条只对select操作生效,第二条对select、update、insert、delete都生效。

        • 当执行select时,会用 or (PERMISSIVE )/ and(RESTRICTIVE )合并两个policy的条件。
        • 当执行update、insert、delete时,只有第二条policy生效。

      RLS的应用场景

      多租户数据隔离,举例:

      • Memfire cloud中应用开发

      案例讲解

      create table public.todos (
        id bigint generated by default as identity primary key,
        user_id uuid references auth.users not null,
        task text check (char_length(task) > 3),
        is_complete boolean default false,
        inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
      );
      create index idx_todos_uid on public.todos (user_id);
      alter table public.todos enable row level security;
      
      create policy "Individuals can create todos." on public.todos for
          insert with check (auth.uid() = user_id);
      
      create policy "Individuals can view their own todos. " on public.todos for
          select using (auth.uid() = user_id);
      
      create policy "Individuals can update their own todos." on public.todos for
          update using (auth.uid() = user_id);
      
      create policy "Individuals can delete their own todos." on public.todos for
          delete using (auth.uid() = user_id);
      

      so小编留一个问题给小伙伴们,针对public.todo表,上述四条策略实现了什么访问控制呢?
      欢迎评论区留言哟~

      1 Reply Last reply Reply Quote 0
      • A
        admin last edited by

        MemFire教程|PostgreSQL RLS介绍:https://mp.weixin.qq.com/s/4os-VVLeir-hEyqRtFmUGQ

        1 Reply Last reply Reply Quote 0
        • First post
          Last post