9.1 사용자·역할·GRANT/REVOKE

-- 그룹 역할
CREATE ROLE app_readers NOLOGIN;
CREATE ROLE app_writers NOLOGIN;

-- 사람 사용자
CREATE ROLE alice LOGIN PASSWORD 'secret' VALID UNTIL '2027-01-01';
GRANT app_readers, app_writers TO alice;

-- 애플리케이션 사용자
CREATE ROLE svc_app LOGIN PASSWORD 'secret_app';
GRANT app_writers TO svc_app;

-- 스키마 접근
GRANT USAGE ON SCHEMA app TO app_readers, app_writers;

-- 읽기 전용
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readers;

-- 쓰기
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writers;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_writers;

-- 회수
REVOKE INSERT, UPDATE, DELETE ON app.orders FROM app_writers;

-- alice가 app 스키마에 만들 미래 테이블은 자동으로 app_readers에 SELECT 부여
ALTER DEFAULT PRIVILEGES
  FOR ROLE alice
  IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readers;

-- 모든 사용자가 어느 DB에든 접속 가능 (위험)
GRANT CONNECT ON DATABASE app_main TO PUBLIC;

-- 보안 강화: PUBLIC의 CONNECT를 회수
REVOKE CONNECT ON DATABASE app_main FROM PUBLIC;

GRANT app_readers TO alice;        -- alice가 app_readers 멤버
GRANT app_readers TO alice WITH ADMIN OPTION;  -- alice가 다른 사람에게도 부여 가능

-- 현재 사용자가 갖는 역할
SELECT * FROM pg_roles WHERE rolname = current_user;

SET ROLE app_writers;
INSERT INTO app.orders VALUES (...);
RESET ROLE;

-- 모든 객체를 다른 사용자에게
REASSIGN OWNED BY alice TO svc_app;

-- alice가 가진 모든 객체 + 권한 제거
DROP OWNED BY alice;
DROP ROLE alice;

-- 모든 스키마·테이블에 SELECT만
GRANT pg_read_all_data TO auditor;     -- PG 14+

-- 한 테이블의 권한
SELECT grantee, privilege_type
  FROM information_schema.table_privileges
 WHERE table_schema = 'app' AND table_name = 'orders';

-- 한 사용자가 갖는 권한
SELECT * FROM information_schema.role_table_grants
 WHERE grantee = 'alice';

-- 사용자가 어느 그룹의 멤버인지
SELECT r.rolname AS role, m.member::regrole AS member
  FROM pg_auth_members m
  JOIN pg_roles r ON m.roleid = r.oid;