[OpenSQL] Privilege

오브젝트가 생성되면 소유자가 할당됩니다.
소유자는 일반적으로 오브젝트 생성문을 실행한 Role 이 됩니다.
대부분의 오브젝트 유형의 초기 상태는 소유자 또는 슈퍼유저만이 오브젝트에 대해 무엇이든 할 수 있습니다.

소유자나 슈퍼유저 외에 다른 Role 이 오브젝트를 사용하려면 Privilege 가 부여되어야 합니다.
아래 내용엔 다양한 종류의 Privilege 가 있습니다.
특정 오브젝트에 적용할 수 있는 Privilege 는 오브젝트의 유형에 따라 다릅니다.

오브젝트를 수정하거나 삭제하는 권리는 오브젝트 소유자에게 내제되어 있고 GRANT 되거나 REVOKE 될 수 없습니다.
그러나 모든 Privilege 와 마찬가지로 해당 소유자의 구성원이 상속할 수 있습니다.
예를 들어, 아래와 같은 명령으로 오브젝트를 새로운 소유자에게 할당할 수 있습니다.

ALTER TABLE hans_table OWNER TO new_owner;

슈퍼유저는 항상 해당 작업을 수행할 수 있습니다.
일반 Role 은 오브젝트의 소유자 또는 소유자의 구성원이거나 새로운 소유자의 구성원인 경우에만 해당 작업을 수행할 수 있습니다.

Privilege 를 할당하기 위해 GRANT 명령이 사용됩니다.
예를 들어, hans 라는 Role 있고 hans_table 이라는 테이블이 있을 때, 테이블을 업데이트할 수 있는 Privilege 는 다음과 같이 부여할 수 있습니다.

GRANT UPDATE ON hans_table TO hans;

특정 Privilege 대신 ALL 을 주게 되면 오브젝트와 관련된 모든 Privilege 를 부여합니다.
특별한 Role 인 ‘PUBLIC’ 에게 Privilege 를 부여하여 시스템 상의 모든 Role 에게 부여할 수도 있습니다.

PUBLIC Role??
현재부터 차후에 생성될 모든 Role 을 포함하여 Privilege 가 부여됨을 나타냅니다. 항상 모든 Role 을 포함하는 암시적으로 정의된 그룹으로 생각할 수도 있습니다.

부여한 Privilege 를 취소하려면 REVOKE 명령을 사용합니다.

REVOKE ALL ON accounts FROM PUBLIC;

일반적으로 오브젝트의 소유자 또는 슈퍼유저만 오브젝트에 대한 Privilege 를 부여하거나 취소할 수 있습니다.
오브젝트의 소유자는 자신과 다른 Role 을 위해 테이블을 읽기 전용으로 만들기 위해 자신의 Privilege 를 취소하도록 선택할 수 있습니다.

그러나 소유자는 항상 모든 부여 옵션을 보유한 것으로 간주되므로 항상 자신의 Privilege 를 다시 부여할 수 있습니다.
사용 가능한 권한은 아래와 같습니다.

SELECT	테이블, 뷰, 동적 뷰 또는 기타 테이블과 유사한 오브젝트의 모든 열 또는 특정 열에서 SELECT를 허용합니다. 또한 COPY TO를 사용할 수 있습니다. 이 권한은 UPDATE 또는 DELETE에서 기존 열 값을 참조하는 데 필요합니다.
INSERT	테이블, 뷰 등에 새 행을 삽입할 수 있습니다. 특정 컬럼에 부여할 수 있습니다. 이 경우 INSERT 명령에서 해당 컬럼만 할당할 수 있습니다(따라서 다른 컬럼은 기본값을 받습니다). 또한 COPY FROM을 사용할 수 있습니다.
UPDATE	테이블, 뷰 등의 모든 열 또는 특정 컬럼에 대한 UPDATE를 허용합니다. (실제로 UPDATE 명령은 SELECT 권한도 필요합니다. 업데이트할 행을 결정하기 위해 테이블 ​컬럼을 참조해야 하기 때문입니다. 또는 컬럼에 대한 새 값을 계산합니다.) SELECT …​ FOR UPDATE 및 SELECT …​ FOR SHARE는 SELECT 권한 외에 최소한 하나의 열에 대한 이 권한도 필요합니다.
DELETE	테이블, 뷰 등에서 행의 DELETE를 허용합니다. (실제로 모든 DELETE 명령에는 SELECT 권한도 필요합니다. 삭제할 행을 결정하기 위해 테이블 ​​열을 참조해야 하기 때문입니다.)
TRUNCATE	테이블에서 TRUNCATE를 허용합니다.
REFERENCES	테이블 또는 테이블의 특정 열을 참조하는 외래 키 제약 조건을 생성할 수 있습니다.
TRIGGER	테이블, 뷰 등에 대한 트리거 생성을 허용합니다.
CREATE	데이터베이스의 경우 데이터베이스 내에서 새 스키마 및 Publication 를 생성할 수 있으며 데이터베이스 내에서 Extension 을 설치할 수 있습니다. 스키마의 경우 스키마 내에서 새 오브젝트를 만들 수 있습니다. 기존 오브젝트의 이름을 바꾸려면 오브젝트를 소유하고 포함하는 스키마에 대해 이 권한이 있어야 합니다. 테이블스페이스의 경우 테이블스페이스 내에서 테이블, 인덱스 및 임시 파일을 생성할 수 있으며 테이블스페이스를 기본 테이블스페이스로 포함하는 데이터베이스 생성을 허용합니다. 이 권한을 취소해도 기존 오브젝트의 존재 또는 위치가 변경되지 않습니다.
CONNECT	피부여자가 데이터베이스에 연결할 수 있도록 합니다. 이 권한은 연결 시작 시 확인됩니다(pg_hba.conf에 의해 부과된 제한 사항 확인 외에도).
TEMPORARY	데이터베이스를 사용하는 동안 임시 테이블을 생성할 수 있습니다.
EXECUTE	함수 위에 구현된 연산자의 사용을 포함하여 함수 또는 프로시저를 호출할 수 있습니다. 이것은 기능 및 프로시저에 적용할 수 있는 유일한 유형의 권한입니다.
USAGE	스키마의 경우 스키마에 포함된 오브젝트에 대한 액세스를 허용합니다. 시퀀스의 경우 currval 및 nextval 함수를 사용할 수 있습니다. FDW 의 경우 FDW를 사용하여 새 서버를 만들 수 있습니다. 외부 서버의 경우 서버를 사용하여 외부 테이블을 생성할 수 있습니다. 피부여자는 해당 서버와 관련된 자체 사용자 매핑을 생성, 변경 또는 삭제할 수도 있습니다.
SET	현재 세션 내에서 서버 구성 매개변수를 새 값으로 설정할 수 있습니다.
ALTER SYSTEM	서버 구성 매개변수를 새 값으로 구성할 수 있습니다.

PostgreSQL은 오브젝트가 생성될 때 기본적으로 일부 유형의 오브젝트에 대한 Privilege 를 PUBLIC에 부여합니다.
PUBLIC 에는 기본적으로 테이블, 테이블 컬럼, 시퀀스, FDW, 외부 서버, 대형 오브젝트, 스키마, 테이블스페이스 또는 구성 매개변수에 대한 권한이 부여되지 않습니다.

다른 유형의 개체에 대해 PUBLIC 에 부여된 기본 Privilege 는 다음과 같습니다.
데이터베이스에 대한 CONNECT 및 TEMPORARY( 임시 테이블 생성 ) Privilege

함수 및 프로시저에 대한 EXECUTE Privilege

언어 및 데이터 유형(도메인 포함)에 대한 USAGE Privilege.

물론 오브젝트 소유자는 기본 권한과 명시적으로 부여된 Privilege 를 모두 REVOKE할 수 있습니다.
보안을 위해 오브젝트를 생성하는 동일한 트랜잭션에서 REVOKE 를 실행하면 다른 사용자가 오브젝를 사용할 수 있는 방법이 없습니다.
또한 이러한 기본 Privilege 설정은 ALTER DEFAULT PRIVILEGES 명령을 사용하여 재정의할 수 있습니다.

아래 표는 ACL(Access Control List) 에서 이러한 Privilege 유형에 사용되는 약어를 보여줍니다.

Privilege	Abbreviation	Applicable Object Types
SELECT	r (“read”)	LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT	a (“append”)	TABLE, table column
UPDATE	w (“write”)	LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE, table column
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
SET	s	PARAMETER
ALTER SYSTEM	A	PARAMETER

아래 표는 위 표에 표시된 약어를 사용하여 SQL 오브젝트의 각 유형에 사용할 수 있는 Privilege 를 요약한 것입니다.
또한, 각 오브젝트 유형에 대한 Privilege 설정을 검사하는데 사용할 수 있는 psql 명령을 보여줍니다.

Object Type	All Privileges	Default PUBLIC Privileges	psql Command
DATABASE	CTc	Tc	l
DOMAIN	U	U	dD+
FUNCTION or PROCEDURE	X	X	df+
FOREIGN DATA WRAPPER	U	none	dew+
FOREIGN SERVER	U	none	des+
LANGUAGE	U	U	dL+
LARGE OBJECT	rw	none	dl+
PARAMETER	sA	none	dconfig+
SCHEMA	UC	none	dn+
SEQUENCE	rwU	none	dp
TABLE (and table-like objects)	arwdDxt	none	dp
Table column	arwx	none	dp
TABLESPACE	C	none	db+
TYPE	U	U	dT+

특정 오브젝트에 대해 부여된 Privilege 는 aclitem 항목 목록으로 표시됩니다.
aclitem 항목은 특정 Privilege 부여자가 부여한 피부여자의 Privilege 를 설명합니다.
예를 들어, 아래와 같은 aclitem 항목은 다음과 같습니다.

calvin = r*w/hobbes

calvin 이라는 Role 은 다른 Role 에게 부여할 수 옵션( * )이 있는 SELECT Privilege( r ) 를 부여받고,
부여할 수 없는 UPDATE Privilege ( w ) 를 가졌으며 앞의 옵션들은 hobbes 라는 Role 에 의해 부여되었다.

calvin 이 다른 Privilege 부여자가 부여한 동일한 오브젝트에 대한 일부 Privilege 도 가지고 있는 경우 해당 Privilege 는 별도의 aclitem 항목으로 나타납니다.
aclitem의 빈 피부여자 필드는 PUBLIC을 나타냅니다.

더 예를 들어 보겠습니다.
Role hans 가 hans_table 을 생성하고 다음을 수행한다고 가정합니다.

$ psql -U postgres -d postgres
psql (14.6)
Type “help” for help.

postgres=# CREATE USER hans;
CREATE ROLE
postgres=# CREATE USER james;
CREATE ROLE
postgres=# CREATE USER jons;
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA public to hans;
GRANT
postgres=# c postgres hans
You are now connected to database “postgres” as user “hans”.
postgres=> CREATE TABLE hans_table(i text);
CREATE TABLE

postgres=> GRANT SELECT ON hans_table TO PUBLIC;
GRANT
postgres=> GRANT SELECT, UPDATE, INSERT ON hans_table TO james;
GRANT
postgres=> GRANT SELECT (i), UPDATE (i) ON hans_table TO jons;
GRANT

postgres=> dp hans_table
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
——–+————+——-+——————-+——————-+———-
public | hans_table | table | hans=arwdDxt/hans+| i: +|
| | | =r/hans +| jons=rw/hans |
| | | james=arw/hans | |
(1 row)

오브젝트에 대해 “Access Privileges” 가 비어 있다면 오브젝트에 대한 기본 Privilege 가 있음을 의미합니다.

즉, 관련 시스템 카탈로그의 Privilege 항목이 NULL 입니다.
기본 Privilege 는 항상 소유자에 대한 모든 Privilege 를 포함하며 위에서 설명한 것처럼 오브젝트 유형에 따라 PUBLIC 에 대한 일부 Privilege 을 포함할 수 있습니다.

오브젝트에 대한 첫 번째 GRANT 또는 REVOKE는 기본 Privilege 를 인스턴스화(예: miriam=arwdDxt/miriam 생성)한 다음 지정된 요청에 따라 수정합니다.

마찬가지로 “Column Privileges” 항목은 기본 Privilege 이 아닌 컬럼에 대해서만 표시됩니다.
(참고: 이 목적을 위해 “Default Privilege” 는 항상 오브젝트 유형에 대한 기본 제공 Privilege 을 의미합니다.

Privilege 이 ALTER DEFAULT PRIVILEGES 명령에 의해 영향을 받은 개체는 항상 명시적 권한 항목과 함께 표시됩니다. 알터의.)
소유자의 암시적 Privilege 부여 옵션은 액세스 Privilege 표시에 표시되지 않습니다.

*는 Privilege 부여 옵션이 누군가에게 명시적으로 부여된 경우에만 나타납니다.

지금까지 ‘PostgreSQL의 Privilege’에 관해 알아보았습니다

‘PostgreSQL의 Backup’를 바로 이어서 확인해보세요!