PostgreSQL | Role (User) ROLE Creation | Remove Role Privileges (REVOKE)
Privileges added to a role by using the GRANT command can be removed with the REVOKE command. This page explains how to remove privileges by using the REVOKE command.
Remove Privileges with the REVOKE Command
The REVOKE command can remove privileges added to a role. Several forms are available depending on the target object.
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE }
function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
There are several forms, but the basic structure is as follows.
REVOKE privilege ON target FROM role
Here, privilege, target, and role are the same as in the GRANT command. For details, see Grant Privileges on Tables, Views, and Other Objects (GRANT).
If REVOKE is executed with GRANT OPTION FOR, only the right to grant the privilege to other roles is removed. This is the right added by WITH GRANT OPTION in the GRANT command. If it is not specified, both the privilege itself and the privileges granted to other roles are removed.
The REVOKE command can specify CASCADE or RESTRICT at the end. If a role has granted the same privilege to another role and you try to remove the privilege from the original role with the default setting, RESTRICT, the removal fails. If CASCADE is specified, the privilege on the original role and the same privilege granted by that role to other roles are removed together.
Only the object owner and a superuser can remove privileges from another role. Other roles can only remove privileges that they granted to other roles by using WITH GRANT OPTION in the GRANT command.
Now look at the method more concretely.
Remove Privileges on a Table
Use table privileges as an example to check how to use the REVOKE command. The syntax is as follows.
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
For example, to remove SELECT and INSERT privileges on table_name from role_name, run the following.
REVOKE SELECT, INSERT ON table_name FROM role_name;
To remove only the right for role_name to grant SELECT privilege on table_name to other roles, run the following.
REVOKE GRANT OPTION FOR SELECT ON table_name FROM role_name;
To remove SELECT privilege on table_name from role_name and also remove SELECT privileges that role_name granted to other roles, run the following.
REVOKE SELECT ON table_name FROM role_name CASCADE;
To remove all privileges on table_name from role_name, run the following.
REVOKE ALL PRIVILEGES ON table_name FROM role_name;
–
Now try it in practice. In the current public schema, a memo table has been created, and SELECT, INSERT, and UPDATE privileges have been added for the mykuma role. Check them with the psql meta-command \dp.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=arw/postgres | |
(1개 행)
devkuma=#
The access privileges above were added with GRANT SELECT,INSERT,UPDATE ON memo TO mykuma;.
The access privileges for the memo table contain the following values.
postgres=arwdDxt/postgres
mykuma=arw/postgres
The values mean the following. For the mykuma role, the postgres role has granted a (INSERT), r (SELECT), and w (UPDATE) privileges.
rolename=xxxx -- privileges granted to the role
=xxxx -- privileges granted to PUBLIC
r -- SELECT (read)
w -- UPDATE (write)
a -- INSERT (append)
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- all privileges for tables. Other objects differ.
* -- grant option for the preceding privilege
/ yyyy -- role that granted the privilege
Remove SELECT privilege on the memo table from the mykuma role. Connect as a superuser and run the following.
devkuma=# revoke select on memo from mykuma;
REVOKE
devkuma=#
The SELECT privilege has been removed. Run \dp again to confirm.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=aw/postgres | |
(1개 행)
devkuma=#
The access privilege value changed from mykuma=arw/postgres to mykuma=aw/postgres, confirming that r (SELECT) has been removed.
Next, remove all privileges on the memo table from the mykuma role. Connect as a superuser and run the following.
devkuma=# revoke all privileges on memo from mykuma;
REVOKE
devkuma=#
The remaining INSERT and UPDATE privileges have also been removed. Run \dp again to confirm.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres | |
(1개 행)
devkuma=#
The mykuma role entry has been completely removed from the access privileges.
Specify CASCADE and Remove Privileges
If role A grants to role B a privilege that had been added to role A, removing the privilege from role A fails. In this case, specify CASCADE when executing the REVOKE command. CASCADE removes the privilege from role A and also removes the privilege that role A granted to role B.
Now try it in practice. First, connect to PostgreSQL as a superuser and add SELECT privilege on the memo table in the public schema to the mykuma role. Add WITH GRANT OPTION so that mykuma can grant the privilege to other roles.
devkuma=# grant select on memo to mykuma with grant option;
GRANT
devkuma=#
Next, connect to PostgreSQL as the mykuma role and add SELECT privilege on the memo table in the public schema to the superkuma role.
C:\Users\kimkc>psql -U mykuma -d devkuma
mykuma 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.
devkuma=> grant select on memo to superkuma;
GRANT
devkuma=>
Use the psql meta-command \dp to check privileges added to the table.
devkuma=> \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=r*/postgres +| |
| | | superkuma=r/mykuma | |
(1개 행)
devkuma=>
The access privileges for the memo table contain the following values.
postgres=arwdDxt/postgres+
mykuma=r*/postgres +
superkuma=r/mykuma
For the mykuma role, the postgres role has added r (SELECT) and * (grant option). For the superkuma role, the mykuma role has added r (SELECT).
In this state, try removing SELECT privilege on the memo table from the mykuma role. Connect as a superuser and run the following.
devkuma=# revoke select on memo from mykuma;
오류: ???의존(적인) 권한이 존재합니다
힌트: 그것들을 취소하려면 "CASCADE"를 사용하세요.
devkuma=#
The error “오류: ???의존(적인) 권한이 존재합니다” occurs and removing the privilege fails. This is because the privilege being removed from the mykuma role was also granted by mykuma to another role.
In this case, execute the REVOKE command with CASCADE. Connect as a superuser and run the following.
devkuma=# revoke select on memo from mykuma cascade;
REVOKE
devkuma=#
This time, the privilege removal succeeds. Run \dp again to confirm.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres | |
(1개 행)
devkuma=#
The privilege added to mykuma has been removed, and the privilege added to superkuma has also been removed. As shown here, specifying CASCADE when executing REVOKE can remove privileges recursively when the privilege being removed has been granted to another role.
–
This page explained how to remove privileges by using the REVOKE command.