行级别安全

1
create policy "Individuals can view their own todos."
2
on todos for select
3
using ( (select auth.uid()) = user_id );

1
select *
2
from todos
3
where auth.uid() = todos.user_id;
4
-- Policy is implicitly added.

1
alter table "table_name" enable row level security;

1
CREATE OR REPLACE FUNCTION rls_auto_enable()
2
RETURNS EVENT_TRIGGER
3
LANGUAGE plpgsql
4
SECURITY DEFINER
5
SET search_path = pg_catalog
6
AS $$
7
DECLARE
8
  cmd record;
9
BEGIN
10
  FOR cmd IN
11
    SELECT *
12
    FROM pg_event_trigger_ddl_commands()
13
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
14
      AND object_type IN ('table','partitioned table')
15
  LOOP
16
     IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
17
      BEGIN
18
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
19
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
20
      EXCEPTION
21
        WHEN OTHERS THEN
22
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
23
      END;
24
     ELSE
25
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
26
     END IF;
27
  END LOOP;
28
END;
29
$$;
30
31
DROP EVENT TRIGGER IF EXISTS ensure_rls;
32
CREATE EVENT TRIGGER ensure_rls
33
ON ddl_command_end
34
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
35
EXECUTE FUNCTION rls_auto_enable();

1
create policy "Profiles are viewable by everyone"
2
on profiles for select
3
to authenticated, anon
4
using ( true );
5
6
-- OR
7
8
create policy "Public profiles are viewable only by authenticated users"
9
on profiles for select
10
to authenticated
11
using ( true );

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Public profiles are visible to everyone."
13
on profiles for select
14
to anon         -- the Postgres Role (recommended)
15
using ( true ); -- the actual Policy

1
create policy "User can see their own profile only."
2
on profiles
3
for select using ( (select auth.uid()) = user_id );

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can create a profile."
13
on profiles for insert
14
to authenticated                          -- the Postgres Role (recommended)
15
with check ( (select auth.uid()) = user_id );      -- the actual Policy

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can update their own profile."
13
on profiles for update
14
to authenticated                    -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id )       -- checks if the existing row complies with the policy expression
16
with check ( (select auth.uid()) = user_id ); -- checks if the new row complies with the policy expression

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can delete a profile."
13
on profiles for delete
14
to authenticated                     -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id );      -- the actual Policy

1
create view <VIEW_NAME>
2
with(security_invoker = true)
3
as select <QUERY>

1
create policy "User is in team"
2
on my_table
3
to authenticated
4
using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));

1
create policy "Restrict updates."
2
on profiles
3
as restrictive
4
for update
5
to authenticated using (
6
  (select auth.jwt()->>'aal') = 'aal2'
7
);

1
alter role "role_name" with bypassrls;

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

1
create index userid
2
on test_table
3
using btree (user_id);

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( auth.uid() = user_id );

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

1
const { data } = supabase
2
  .from('table')
3
  .select()

1
const { data } = supabase
2
  .from('table')
3
  .select()
4
  .eq('user_id', userId)

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  exists (
5
    select 1 from roles_table
6
    where (select auth.uid()) = user_id and role = 'good_role'
7
  )
8
);

1
create function private.has_good_role()
2
returns boolean
3
language plpgsql
4
security definer -- will run as the creator
5
as $$
6
begin
7
  return exists (
8
    select 1 from roles_table
9
    where (select auth.uid()) = user_id and role = 'good_role'
10
  );
11
end;
12
$$;
13
14
-- Update our policy to use this function:
15
create policy "rls_test_select"
16
on test_table
17
to authenticated
18
using ( (select private.has_good_role()) );

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  (select auth.uid()) in (
5
    select user_id
6
    from team_user
7
    where team_user.team_id = team_id -- joins to the source "test_table.team_id"
8
  )
9
);

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  team_id in (
5
    select team_id
6
    from team_user
7
    where user_id = (select auth.uid()) -- no join
8
  )
9
);

1
create policy "rls_test_select" on rls_test
2
using ( auth.uid() = user_id );

1
create policy "rls_test_select" on rls_test
2
to authenticated
3
using ( (select auth.uid()) = user_id );