행 수준 보안이 조용히 뚫리는 다섯 가지 경우

CREATE ROLE demo_owner NOLOGIN;
CREATE ROLE demo_app   NOLOGIN;
CREATE ROLE demo_admin NOLOGIN BYPASSRLS;
CREATE SCHEMA demo AUTHORIZATION demo_owner;
GRANT USAGE ON SCHEMA demo TO demo_app;
GRANT USAGE, CREATE ON SCHEMA demo TO demo_admin;

SET ROLE demo_owner;
CREATE TABLE demo.notes (id serial PRIMARY KEY, user_id text NOT NULL, body text NOT NULL);
INSERT INTO demo.notes (user_id, body)
  VALUES ('alice', 'a1'), ('alice', 'a2'), ('bob', 'b1'), ('', 'orphan');

ALTER TABLE demo.notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE demo.notes FORCE ROW LEVEL SECURITY;
CREATE POLICY own ON demo.notes FOR ALL
  USING      (user_id = current_setting('app.user_id', true))
  WITH CHECK (user_id = current_setting('app.user_id', true));
RESET ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON demo.notes TO demo_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA demo TO demo_app;
GRANT SELECT ON demo.notes TO demo_admin;

SET ROLE demo_app;
BEGIN;
SELECT set_config('app.user_id', 'alice', true);  -- true: 이 트랜잭션에만 적용
SELECT body FROM demo.notes;                      -- a1, a2
COMMIT;

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes;                       -- 2   (정책이 적용됨)
COMMIT;
SET ROLE demo_admin; SELECT count(*) FROM demo.notes;  -- 4   (BYPASSRLS: 모든 행)
RESET ROLE;          SELECT count(*) FROM demo.notes;  -- 4   (슈퍼유저: 모든 행)

GRANT demo_admin TO demo_app;
SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes;                       -- 2   (멤버십만으로는 우회하지 않음)
COMMIT;
SET ROLE demo_admin; SELECT count(*) FROM demo.notes;  -- 4

SET ROLE demo_app;
SELECT rolsuper OR rolbypassrls AS bypasses_rls FROM pg_roles WHERE rolname = current_user;  -- f

SET ROLE demo_owner;
CREATE TABLE demo.notes2 (id serial PRIMARY KEY, user_id text NOT NULL, body text NOT NULL);
INSERT INTO demo.notes2 (user_id, body) VALUES ('alice', 'x'), ('bob', 'y');
ALTER TABLE demo.notes2 ENABLE ROW LEVEL SECURITY;          -- 활성화만 하고 강제하지 않음
CREATE POLICY own ON demo.notes2 FOR ALL
  USING      (user_id = current_setting('app.user_id', true))
  WITH CHECK (user_id = current_setting('app.user_id', true));

SELECT count(*) FROM demo.notes2;   -- 2  (alice와 bob의 행: 정책을 건너뜀)

ALTER TABLE demo.notes2 FORCE ROW LEVEL SECURITY;
SELECT count(*) FROM demo.notes2;   -- 0  (이제 소유자도 정책의 적용을 받음)
RESET ROLE;

ALTER TABLE demo.notes2 NO FORCE ROW LEVEL SECURITY;
GRANT SELECT ON demo.notes2 TO demo_app, demo_admin;

SET ROLE demo_owner;
CREATE VIEW demo.v_notes2 AS SELECT * FROM demo.notes2;
CREATE FUNCTION demo.count_notes2() RETURNS bigint
  LANGUAGE sql SECURITY DEFINER SET search_path = demo, pg_temp
  AS $$ SELECT count(*) FROM demo.notes2 $$;
RESET ROLE;
GRANT SELECT ON demo.v_notes2 TO demo_app;
GRANT EXECUTE ON FUNCTION demo.count_notes2() TO demo_app;

SET ROLE demo_app;
BEGIN;
SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes2;     -- 1
SELECT count(*) FROM demo.v_notes2;   -- 2   유출
SELECT demo.count_notes2();           -- 2   유출
COMMIT;

RESET ROLE;
ALTER TABLE demo.notes2 FORCE ROW LEVEL SECURITY;
SET ROLE demo_admin;
CREATE VIEW demo.v_admin AS SELECT * FROM demo.notes2;     -- BYPASSRLS 역할이 소유함
RESET ROLE;
GRANT SELECT ON demo.v_admin TO demo_app;

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.v_admin;    -- 2   테이블에 FORCE가 있어도 유출
COMMIT;

CREATE VIEW demo.v_safe WITH (security_invoker = true) AS SELECT * FROM demo.notes2;
ALTER VIEW demo.v_admin SET (security_invoker = true);   -- 기존 뷰를 수정함. 이제 alice에게는 1개 행이 보임

-- psql: 다시 연결해 새 세션을 얻음
\c
SET ROLE demo_app;
SELECT coalesce(current_setting('app.user_id', true), '<NULL>');       -- <NULL>

BEGIN; SELECT set_config('app.user_id', 'alice', true); COMMIT;

SELECT '[' || coalesce(current_setting('app.user_id', true), '<NULL>') || ']';  -- []
SELECT user_id || '/' || body FROM demo.notes;                         -- /orphan

SET ROLE demo_app;
SELECT set_config('app.user_id', 'alice', false);   -- 세션 전체에 적용
SELECT count(*) FROM demo.notes;                    -- 2, 이 연결을 다음에 쓰는 누구에게든

RESET ROLE;
ALTER POLICY own ON demo.notes
  USING      (nullif(current_setting('app.user_id', true), '') IS NOT NULL
              AND user_id = current_setting('app.user_id', true))
  WITH CHECK (nullif(current_setting('app.user_id', true), '') IS NOT NULL
              AND user_id = current_setting('app.user_id', true));

ALTER TABLE demo.notes ADD CONSTRAINT user_id_not_empty CHECK (user_id <> '') NOT VALID;

-- psql: 다시 연결해 새 세션을 얻음
\c
SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true); COMMIT;
SELECT count(*) FROM demo.notes;                         -- 0   (이제 빈 설정은 어떤 행과도 일치하지 않음)
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes;                         -- 2   (alice의 정상 요청은 여전히 작동함)
COMMIT;

RESET ROLE;
CREATE POLICY pub ON demo.notes FOR SELECT USING (true);

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes;    -- 4, alice에게 모든 것이 보임
COMMIT;

RESET ROLE;
CREATE POLICY tenant_guard ON demo.notes AS RESTRICTIVE FOR SELECT
  USING (nullif(current_setting('app.user_id', true), '') IS NOT NULL
         AND user_id = current_setting('app.user_id', true));

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes;    -- `pub`이 있어도 다시 2
COMMIT;

RESET ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE demo_owner IN SCHEMA demo GRANT SELECT ON TABLES TO demo_app;

SET ROLE demo_owner;
CREATE TABLE demo.notes_new (user_id text NOT NULL, body text NOT NULL);   -- 아무도 ENABLE ROW LEVEL SECURITY를 실행하지 않음
INSERT INTO demo.notes_new VALUES ('alice', 'n1'), ('bob', 'n2');
RESET ROLE;

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
SELECT count(*) FROM demo.notes_new;    -- 2, alice에게 bob의 행도 보임
COMMIT;

SET ROLE demo_owner;
CREATE TABLE demo.slugs (id serial PRIMARY KEY, user_id text NOT NULL, slug text NOT NULL UNIQUE);
INSERT INTO demo.slugs (user_id, slug) VALUES ('alice', 'secret-project');
ALTER TABLE demo.slugs ENABLE ROW LEVEL SECURITY;
ALTER TABLE demo.slugs FORCE ROW LEVEL SECURITY;
CREATE POLICY own ON demo.slugs FOR ALL
  USING      (user_id = current_setting('app.user_id', true))
  WITH CHECK (user_id = current_setting('app.user_id', true));
RESET ROLE;
GRANT SELECT, INSERT ON demo.slugs TO demo_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA demo TO demo_app;

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'bob', true);
SELECT count(*) FROM demo.slugs WHERE slug = 'secret-project';              -- 0
INSERT INTO demo.slugs (user_id, slug) VALUES ('bob', 'secret-project');
-- ERROR:  duplicate key value violates unique constraint "slugs_slug_key"
ROLLBACK;

RESET ROLE;
ALTER TABLE demo.slugs DROP CONSTRAINT slugs_slug_key, ADD UNIQUE (user_id, slug);

SET ROLE demo_owner;
BEGIN; SELECT set_config('app.user_id', 'alice', true);
CREATE MATERIALIZED VIEW demo.m_notes AS SELECT * FROM demo.notes;   -- alice의 행을 저장함
COMMIT;
RESET ROLE;
GRANT SELECT ON demo.m_notes TO demo_app;

SET ROLE demo_app;
BEGIN; SELECT set_config('app.user_id', 'bob', true);
SELECT count(*) FROM demo.m_notes;    -- 2   bob이 alice의 행을 읽음
COMMIT;

-- rls_audit.sql: 한 사용자의 행이 다른 사용자에게 닿을 수 있게 하는 것 (스키마 "public")
WITH tbl AS (
  SELECT c.oid, c.relname, c.relrowsecurity, c.relforcerowsecurity
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
)
SELECT 'connected role bypasses RLS' AS problem, current_user::text AS object
  FROM pg_roles WHERE rolname = current_user AND (rolsuper OR rolbypassrls)
UNION ALL
SELECT 'table without RLS', relname FROM tbl WHERE NOT relrowsecurity
UNION ALL
SELECT 'RLS enabled but not forced', relname FROM tbl WHERE relrowsecurity AND NOT relforcerowsecurity
UNION ALL
SELECT 'view without security_invoker', c.relname
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = 'public' AND c.relkind = 'v'
    AND NOT EXISTS (SELECT 1 FROM unnest(coalesce(c.reloptions, '{}')) o
                    WHERE o ~* '^security_invoker=(true|t|yes|y|on|1)$')
UNION ALL
SELECT 'materialized view or foreign table (RLS does not apply)', c.relname
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = 'public' AND c.relkind IN ('m', 'f')
UNION ALL
SELECT 'SECURITY DEFINER function', p.proname
  FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
  WHERE n.nspname = 'public' AND p.prosecdef
UNION ALL
SELECT 'permissive policy that allows every row', p.polname
  FROM pg_policy p
  WHERE p.polrelid IN (SELECT oid FROM tbl) AND p.polpermissive AND pg_get_expr(p.polqual, p.polrelid) = 'true'
ORDER BY 1, 2;

set -euo pipefail
out=$(psql "$DATABASE_URL" -X -A -t -v ON_ERROR_STOP=1 -f rls_audit.sql)   # 연결 또는 SQL 오류가 나면 빌드가 중단됩니다
if [ -n "$out" ]; then echo "$out"; exit 1; fi