CREATE DATABASE appdb
OWNER appowner
ENCODING 'UTF8'
LC_COLLATE 'ko_KR.UTF-8'
LC_CTYPE 'ko_KR.UTF-8'
TEMPLATE template0;
로케일을 기본값과 다르게 주려면 TEMPLATE template0 이 필요하다. template1 은 이미 특정 로케일로 초기화되어 있어 다른 로케일로 복제할 수 없고, 빠뜨리면 new collation is incompatible with the collation of the template database 오류가 난다.
PostgreSQL 15 부터 ICU 로케일 제공자를 쓸 수 있고, 16 부터는 LOCALE_PROVIDER icu 와 ICU_LOCALE 'ko-KR' 형태가 권장된다. OS 로케일에 의존하지 않으므로 정렬 순서가 서버마다 달라지는 문제를 피할 수 있다.
\c appdb
CREATE SCHEMA app AUTHORIZATION appowner;
이미 있는 스키마의 소유자를 바꾸려면 다음과 같다.
ALTER SCHEMA app OWNER TO appowner;
PostgreSQL 15 부터 public 스키마에 대한 CREATE 권한이 PUBLIC 롤에서 회수됐다. 14 이하에서 잘 돌던 초기화 스크립트가 15 이상에서 permission denied for schema public 으로 실패하는 이유가 대개 이것이다.
GRANT USAGE, CREATE ON SCHEMA public TO appowner;
권한은 데이터베이스 · 스키마 · 객체 세 층으로 나뉘고, 아래층 권한만 줘서는 접근할 수 없다.
-- 1) 데이터베이스에 붙을 수 있게
GRANT CONNECT ON DATABASE appdb TO reader;
-- 2) 스키마를 들여다볼 수 있게
GRANT USAGE ON SCHEMA app TO reader;
-- 3) 객체에 대한 권한
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reader;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO reader;
GRANT SELECT ON DATABASE appdb TO reader 는 동작하지 않는다. 데이터베이스에 줄 수 있는 권한은 CONNECT · CREATE · TEMPORARY 뿐이라 SELECT 권한은 데이터베이스에는 사용할 수 없는 권한 형태 라는 오류가 난다.
GRANT ... ON ALL TABLES 는 실행 시점에 존재하는 테이블에만 적용된다. 이후 새로 만드는 테이블에는 권한이 붙지 않는다.
ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA app
GRANT SELECT ON TABLES TO reader;
ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO reader;
FOR ROLE 을 빠뜨리면 명령을 실행한 롤이 만든 객체에만 적용된다. 테이블을 만드는 주체가 따로 있으면 그 롤을 명시해야 한다. 이 지점을 놓쳐 "분명히 줬는데 새 테이블만 안 보인다"는 상황이 자주 생긴다.
information_schema.schemata 는 현재 롤이 권한을 가진 스키마만 보여 준다. 스키마가 분명히 있는데 여기서 안 보이면 권한이 없는 것이다. 실제 목록은 카탈로그를 본다.
SELECT nspname, pg_get_userbyid(nspowner) AS owner FROM pg_namespace ORDER BY nspname;
\dn+
relation "hive" 이름의 relation이 없습니다 같은 오류는 스키마 이름을 테이블 자리에 쓴 경우다. 스키마에 권한을 주는 문장은 GRANT ... ON SCHEMA hive, 테이블에 주는 문장은 GRANT ... ON ALL TABLES IN SCHEMA hive 로 서로 다르다.
대소문자도 자주 걸린다. 따옴표 없이 쓴 식별자는 소문자로 접힌다. 대문자로 만든 스키마는 "AAA" 처럼 큰따옴표로 감싸야 한다.
appowner (DDL 소유)
├─ writer : INSERT/UPDATE/DELETE/SELECT
└─ reader : SELECT
응용 계정에 SUPERUSER 를 주지 않는다. CREATEDB · CREATEROLE 같은 개별 속성으로 충분한 경우가 대부분이고, SUPERUSER 는 모든 권한 검사를 건너뛰므로 행 수준 보안과 감사도 무력해진다.