PostgreSQL:RowSecurityPolicies

CREATE POLICY 이름 ON 테이블이름
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { 롤이름 | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( 사용식 ) ]
    [ WITH CHECK ( 검사식 ) ]

CREATE POLICY "org_members_update_permission"
    ON public.org_members
    FOR UPDATE
    -- 1단계: 이 row를 수정할 수 있는가? (OLD 기준)
    USING (public.has_org_perm(org_id, 'members:update'))
    -- 2단계: 수정 결과가 허용되는가? (NEW 기준)
    WITH CHECK (
        role_id != (SELECT id FROM public.org_roles WHERE name = 'owner')
        OR public.has_org_perm(org_id, 'delete')
    );

CREATE POLICY "org_perms_manage_admin"
    ON public.org_perms
    FOR ALL
    USING ((SELECT public.is_admin()))
    WITH CHECK ((SELECT public.is_admin()));

-- 아래 두 정책은 동일하게 동작한다
CREATE POLICY "explicit"
    ON public.orgs FOR UPDATE
    USING (public.has_org_perm(id, 'update'))
    WITH CHECK (public.has_org_perm(id, 'update'));

CREATE POLICY "implicit"
    ON public.orgs FOR UPDATE
    USING (public.has_org_perm(id, 'update'));
    -- WITH CHECK 생략 → USING 조건이 자동 적용

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,

  primary key (id)
);

alter table public.profiles enable row level security;

create policy "Public profiles are viewable by everyone."
  on profiles for select
  using ( true );

create policy "Users can insert their own profile."
  on profiles for insert
  with check ( auth.uid() = id );

create policy "Users can update own profile."
  on profiles for update
  using ( auth.uid() = id );

create policy "Profiles are viewable by users who created them."
  on profiles for select
  using ( auth.uid() = id );

create policy "Individuals can delete their own posts."
  on posts for delete
  using ( auth.uid() = id );

DROP POLICY "Admin can control admin records" ON public.admins;

CREATE POLICY "Admin can control admin records" ON public.admins
    FOR ALL
    USING (
        EXISTS (
            SELECT 1 FROM public.admins  -- 자기 자신을 참조 → 무한 재귀
            WHERE user_id = auth.uid()
        )
    );

-- 1. SECURITY DEFINER 함수 생성
CREATE OR REPLACE FUNCTION public.is_admin()
    RETURNS BOOLEAN
    LANGUAGE plpgsql
    SECURITY DEFINER
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.admins
        WHERE user_id = auth.uid()
    );
END;
$$;

-- 2. 정책에서 함수 호출
CREATE POLICY "Admin can control admin records" ON public.admins
    FOR ALL
    USING (public.is_admin())
    WITH CHECK (public.is_admin());

-- 1. Create a table of teams
create table teams (
  id serial primary key,
  name text
);

-- 2. Create many to many join
create table members (
  team_id bigint references teams,
  user_id uuid references auth.users
);

-- 2.  Enable RLS
alter table teams enable row level security;
alter table members enable row level security;

-- 3.  Create security definer function, which should be run as "postgres"
create function private.get_teams_for_authenticated_user()
returns setof bigint
language sql
security definer -- 실행 권한을 실행한 측이 아닌 함수 정의한 측(definer)로 지정.
set search_path = public -- 검색 경로 'public' schema 로 제한
stable -- 내용을 수정하지 않는다.
as $$
  select team_id
  from members
  where user_id = auth.uid()
$$;

-- 4. Create Policy
create policy "Team members can update team members if they belong to the team."
on members
for all using (
  team_id in (
    select private.get_teams_for_authenticated_user()
  )
);

CREATE OR REPLACE FUNCTION rls_auto_enable()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$
DECLARE
  cmd record;
BEGIN
  FOR cmd IN
    SELECT *
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
      AND object_type IN ('table','partitioned table')
  LOOP
     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
      BEGIN
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
      EXCEPTION
        WHEN OTHERS THEN
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
      END;
     ELSE
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
     END IF;
  END LOOP;
END;
$$;

DROP EVENT TRIGGER IF EXISTS ensure_rls;
CREATE EVENT TRIGGER ensure_rls
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
EXECUTE FUNCTION rls_auto_enable();