설명

• developer Role 생성
• test_developer User 생성하여 developer Role 부여
• developer Role은 public 스키마에 접근 불가, 뷰 전용 스키마인 v 스키마에만 접근 허용

SQL

-- 1. 새로운 Role 생성
CREATE ROLE developer;

-- 2. 새로운 사용자 생성
CREATE USER test_developer WITH PASSWORD '1q2w3e4r';

-- 3. test_developer 사용자에 developer Role 부여
GRANT developer TO test_developer;

-- 공통 Role인 PUBLIC에 대해 public 스키마 권한 제거
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- developer Role의 public 스키마 접근 권한 제거
REVOKE USAGE ON SCHEMA public FROM developer;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM developer;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM developer;

-- developer Role의 v 스키마 접근 권한 부여
GRANT USAGE ON SCHEMA v TO developer;
GRANT ALL ON ALL TABLES IN SCHEMA v TO developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA v GRANT ALL ON TABLES TO developer;

확인용 SQL

-- test_developer User에 부여된 Role 목록 확인
SELECT r.rolname AS role_name
  FROM pg_user u
  JOIN pg_auth_members m ON u.usesysid = m.member
  JOIN pg_roles r ON m.roleid = r.oid
 WHERE u.usename = 'test_developer';

-- developer Role에 부여된 테이블 권한 목록 확인
SELECT table_schema, table_name, privilege_type
  FROM information_schema.role_table_grants
 WHERE grantee = 'developer';