PostgreSQL 유저 생성 및 권한 관리

• 스키마 생성

CREATE SCHEMA test_schema

PostgreSQL ROLE

• PostgreSQL은 ROLE 이라는 개념을 사용, 데이터베이스 접근 권한을 관리

• 설정 방식에 따라 데이터베이스 사용자(user) 또는 사용자들의 그룹(group)으로 간주

• 데이터 베이스 객체(테이블, 함수 등..)을 쇼유 가능, 객체에 대한 권한을 다른 역할에 할당 및 접근 제어 가능

• USER/GROUP의 개념을 모두 포함 -> 8.1 버전 이전에는 달랐지만 현재는 ROLE만 존재

  CREATE ROLE name [ [ WITH ] option [ ... ] ]
  
  where option can be:
  
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
      | BYPASSRLS | NOBYPASSRLS
      | CONNECTION LIMIT connlimit
      | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
      | VALID UNTIL 'timestamp'
      | IN ROLE role_name [, ...]
      | IN GROUP role_name [, ...] // 호환성 허용 IN ROLE
      | ROLE role_name [, ...]
      | ADMIN role_name [, ...]
      | USER role_name [, ...] // 호환성 허용 -> ROLE
      | SYSID uid // 호환성 허용(무시됨)

  • SUPERUSER : 모든 접근 제한 무시, default -> NOSUPERUSER
  • CREATEDB : DB 생성 권한, default -> NOCREATEDB
  • CREATEROLE : ROLE 생성 권한, default -> NOCREATEROLE
  • INHERIT : ROLE이 멤버인 다른 ROLE의 권한 상속 여부, default -> INHERIT
  • LOGIN : ROLE로 로그인 허용에 대한 설정, LOGIN으로 설정할 경우 User로 간주될 수 있음, default -> NOLOGIN
  • REPLICATION : ROLE 생성 권한, default -> NOREPLICATION
  • CONNECTION LIMIT : 로그인 할 수 있다면 동시 로그인 제한 수
  • PASSWORD : 비밀번호 설정
  • VALID UNTIL : ROLE의 비밀번호 유효기간 설정, 설정안하면 무제한
  • IN ROLE : 새로 생성되는 역할을 기존 역할의 멤버로 추가, 새 역할이 해당 기존 역할들의 권한을 상송받게 하려는경우 유용
  • ROLE : 하나 이상의 기존 역할을 새로 생성되는 역할의 멤버로 자동 추가, 새 역할을 그룹처럼 사용하고자 할 때 유용
  • ADMIN : 기존 역할들에게 새 역할을 관리할수 있는 권한을 지정
    

• User 생성(ROLE과 사실상 동일)

  -- example
  CREATE USER test_user WITH PASSWORD 'test_user';
  
  CREATE USER name [ [ WITH ] option [ ... ] ]
  
  where option can be:
  
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
      | BYPASSRLS | NOBYPASSRLS
      | CONNECTION LIMIT connlimit
      | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
      | VALID UNTIL 'timestamp'
      | IN ROLE role_name [, ...]
      | IN GROUP role_name [, ...]
      | ROLE role_name [, ...]
      | ADMIN role_name [, ...]
      | USER role_name [, ...]
      | SYSID uid
  

권한 부여

• 전체 권한 부여

  GRANT ALL PRIVILEGES ON SCHEMA test_schema TO test_user; -- 전체 권한 부여
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA test_schema TO test_user;
   ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA test_schema
      GRANT ALL PRIVILEGES ON TABLES TO test_user; -- 생성될 테이블에도 적용
    -- postgres user가 생성할 객체에 대해서(지금은 스키마의 테이블)의 기본 권한을 test_user에 게 부여

• 읽기 권한 부여

  GRANT USAGE ON SCHEMA schema_name TO test_user2;
   GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO test_user2; -- SELECT 권한만 주기
   ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema_name
      GRANT SELECT ON TABLES TO test_user2;