PostgreSQL의 Schema

PostgreSQL에는 schema(스키마)라는 개념이 있습니다. schema는 하나의 데이터베이스에 다수 존재할 수 있으며 table, sequence 등의 객체가 특정 schema에 속해지게 됩니다. 이를 통해 테이블들을 분류하고 사용자가 특정 schema에만 접근할 수 있도록하면 테이블 하나하나에 권한을 설정 할 필요없이 용도에 따른 접근제한을 걸 수 있습니다. 참고로 데이터베이스에는 기본적으로 public schema가 만들어져 있으며 여기에는 데이터베이스에 연결 가능한 role 이라면 마음대로 테이블을 생성할 수 있습니다.

 

schema를 생성하기 위해서는 해당 데이터베이스에 CREATE 권한이 있어야 합니다. 그리고 schema에 대한 권한은 CREATE와 USAGE가 있습니다. CREATE는 해당 스키마를 사용하는 테이블, 시퀀스같은 객체를 만들 수 있는 권한이고, USAGE는 이 스키마에 속한 객체에 접근할 수 있는 권한입니다. 주의해야 할 것은 USAGE 권한이 없다 하더라도 해당 스키마에 어떤 객체가 속해있는지는 확인할 수 있습니다. 특정 role에 schema에 대한 권한을 부여하려면 아래와 같이 하면 됩니다.

postgres=# GRANT CREATE,USAGE ON SCHEMA www TO test1;
GRANT
postgres=# REVOKE CREATE,USAGE ON SCHEMA www FROM test1;
REVOKE

 

이 schema를 사용할 때 중요한 것들 중 하나가 바로 search_path입니다. 이 search_path는 SQL 명령을 실행할 때 schema가 명시되지 않았다면 어떤 schema들에서 테이블같은 객체를 찾아볼 지 지정합니다. 따로 설정을 변경하지 않았다면 "$user", public으로 지정되어 있을겁니다. 현재 세션의 search_path를 보려면  show search_path  로 확인가능합니다.

 

postgres=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=>

 

이것은 먼저 role 이름과 같은 schema에서 찾고 그래도 없다면 public schema에서 찾는다는 의미입니다.

저는 SQL을 짤 때 일일이 schema를 적어주는걸 선호합니다만 그게 불가능하고 특정 schema만 사용하겠다 하시는 분은 ALTER DATABASE 나 ALTER ROLE 을 사용하여 변경할 수 있습니다. 참고로 database와 role 둘 다 설정이 있다면 role 쪽 설정이 우선됩니다.

postgres=# ALTER DATABASE postgres SET search_path TO www,"$user",public;
ALTER DATABASE
postgres=# ALTER ROLE test1 SET search_path TO public;
ALTER ROLE

 

만약 설정을 원래대로 돌리고 싶다면 아래와 같이 하면 됩니다.

postgres=# ALTER DATABASE postgres RESET search_path;
ALTER DATABASE
postgres=# ALTER ROLE test1 RESET search_path;
ALTER ROLE

 

만약 search_path에 특정 스키마를 포함시키지 않았거나 search_path를 지정하더라도 다른 스키마에 속한 동일한 이름의 테이블과 함께 사용하면 schema 지정이 필수입니다. schema를 지정할 때는 단순히 해당 객체 이름 앞에 {schema}. 을 붙히면 됩니다. 예를들어 test 라는 테이블이 각각 a,b 스키마에 들어있고 이 둘을 JOIN하고 싶다면 아래와 같이 하면 됩니다.

> SELECT a.test.col1, b.test.col2 FROM a.test JOIN b.test ON a.test.col1 = b.test.col1;

 

만약 테이블 이름이 다르면 FROM과 JOIN 바로 뒤의 테이블을 나타내는 부분만 스키마를 지정해주고 나머지 컬럼을 나타내는 부분은 스키마 부분을 생략해도 됩니다.