[ PostgreSQL ] SCHEMA - 권한 확인하고 설정 및 삭제하기

1. 스키마 권한 확인 방법

PostgreSQL에서 특정 스키마의 권한을 확인하려면 pg_namespace 시스템 카탈로그 테이블을 조회할 수 있습니다.

SELECT nspname AS schema_name, nspacl AS access_privileges
FROM pg_namespace;

이 쿼리는 다음 정보를 반환합니다:

• schema_name: 데이터베이스 내의 스키마 이름
• access_privileges: 해당 스키마에 대한 권한 목록

access_privileges 컬럼은 {username=privileges} 형식으로 표시되며, 특정 사용자의 권한을 나타냅니다. 예를 들어:

 schema_name |       access_privileges
-------------+---------------------------------
 public      | {postgres=UC/postgres,=U/postgres}
 my_schema   | {user1=UC/user1}

이와 같은 결과를 얻을 수 있으며, 이는 user1이 my_schema에 대해 UC 권한을 가지고 있음을 나타냅니다.

---

 

2. PostgreSQL 스키마 권한의 종류

PostgreSQL에서는 다음과 같은 스키마 권한이 제공됩니다.

권한	설명
USAGE (U)	스키마 내부의 개체(테이블, 뷰 등)에 접근 가능 (단, SELECT 등의 개별 권한은 필요)
CREATE (C)	해당 스키마에 새로운 개체(테이블, 뷰, 함수 등) 생성 가능
ALL	USAGE + CREATE 포함 모든 권한을 의미

✅ 예제: 스키마 권한 확인하기

SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'my_schema';

결과:

 nspname   |         nspacl
-----------+------------------------
 my_schema | {user1=UC/user1}

user1은 USAGE(U)와 CREATE(C) 권한을 가지고 있습니다.

---

 

3. 스키마 권한 변경 (GRANT)

스키마 권한을 부여하려면 GRANT 명령어를 사용합니다.

1. 특정 사용자에게 권한 부여

GRANT USAGE ON SCHEMA my_schema TO user1;

• user1은 my_schema의 개체를 사용할 수 있습니다.

GRANT CREATE ON SCHEMA my_schema TO user1;

• user1은 my_schema 내에서 새로운 테이블 또는 뷰를 생성할 수 있습니다.

2. 모든 권한 부여

GRANT ALL ON SCHEMA my_schema TO user1;

• user1에게 USAGE와 CREATE 권한을 모두 부여합니다.

3. PUBLIC(모든 사용자)에게 권한 부여

GRANT USAGE ON SCHEMA my_schema TO PUBLIC;

• 모든 사용자가 my_schema 내부의 개체에 접근할 수 있습니다.

---

 

4. 스키마 권한 회수 (REVOKE)

스키마의 권한을 회수하려면 REVOKE 명령어를 사용합니다.

1. 특정 권한 회수하기

REVOKE CREATE ON SCHEMA my_schema FROM user1;

• user1은 더 이상 my_schema에서 테이블이나 뷰를 생성할 수 없습니다.

REVOKE USAGE ON SCHEMA my_schema FROM user1;

• user1은 my_schema 내부의 개체에 접근할 수 없습니다.

2. 모든 권한 회수하기

REVOKE ALL ON SCHEMA my_schema FROM user1;

• user1은 my_schema에서 모든 권한을 잃습니다.

---

 

5. 요약

1. 권한 확인: pg_namespace 테이블을 조회하여 확인 (nspacl 필드 확인)
2. 권한 종류
   • USAGE: 스키마 접근 권한
   • CREATE: 스키마 내 개체 생성 권한
   • ALL: 모든 권한 포함
3. 권한 회수 (REVOKE)
   • 특정 권한 회수: REVOKE USAGE ON SCHEMA my_schema FROM user1;
   • 모든 권한 회수: REVOKE ALL ON SCHEMA my_schema FROM user1;
4. 권한 부여 (GRANT)
   • 특정 권한 부여: GRANT USAGE ON SCHEMA my_schema TO user1;
   • 모든 권한 부여: GRANT ALL ON SCHEMA my_schema TO user1;

이제 PostgreSQL에서 스키마 권한을 쉽게 관리할 수 있을 거예요! 🚀