PostgreSQL | Role (User) ROLE Creation | Grant Privileges on Tables, Views, and Other Objects (GRANT)

Tags:

• PostgreSQL
• Database

You can add privileges to a role by using the GRANT command. Privileges include the ability to create schemas, query data from tables and columns, and insert data. This page explains how to add privileges with the GRANT command.

Add Privileges with the GRANT Command

The GRANT command can add privileges to roles. Several forms are available depending on the target object.

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE }
       routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
       | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

There are several forms, but the basic structure is as follows.

GRANT privilege ON target TO role

The available privileges differ depending on the target. They can be summarized as follows.

Privilege	Description
SELECT	Allows querying data from a table or view. Also required for UPDATE and DELETE.
INSERT	Allows adding new data to a table.
UPDATE	Allows updating table data.
DELETE	Allows deleting data from a table.
TRUNCATE	Allows emptying a table.
REFERENCES	Allows creating foreign key constraints on tables and columns.
TRIGGER	Allows creating triggers on a table.
CREATE	For a database target, allows creating schemas. For a schema target, allows creating objects such as tables.
CONNECT	Allows connecting to the specified database.
TEMPORARY	Allows creating temporary tables in a database.
EXECUTE	Allows use of functions, procedures, or operators.
USAGE	For a schema target, allows access to objects. For other targets, the meaning depends on the target.
ALL PRIVILEGES	Grants all available privileges for the target.

The target can be a table, a table column, a database, a schema, and so on.

The role is specified by role name. CURRENT_USER and SESSION_USER can also be specified. If PUBLIC is specified, privileges are added for all roles, both existing and future roles.

Privileges on an object can be granted by the object owner or a superuser. If WITH GRANT OPTION is specified when executing GRANT, the role receiving the privilege can grant the same privilege to other roles. This option cannot be used when PUBLIC is specified.

Now look at more concrete examples.

Add Privileges on a Table

First, here is how to add privileges on a table.

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

For example, to add SELECT and INSERT privileges on table_name to role_name, run the following.

GRANT SELECT, INSERT ON table_name To role_name;

To add all privileges on table_name to role_name, run the following. In this form, all privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.

GRANT ALL PRIVILEGES ON table_name To role_name;

To add SELECT privilege on all tables in the specified schema schema_name to role_name, run the following.

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name To role_name;

To add SELECT and INSERT privileges on table_name to role_name and also allow that role to grant the same privileges to other users, run the following.

GRANT SELECT, INSERT ON table_name To role_name WITH GRANT OPTION;

–

Now try it in practice. In the current devkuma database, there is a memo table in the public schema and a book table in the myschema schema. To get privilege information for tables and columns, use the psql meta-command \dp. \dp displays tables, views, and sequences with their access privileges.

devkuma=# \dp
                         액세스 권한
 스키마 | 이름 |  종류  | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+-------------+---------------+------
 public | memo | 테이블 |             |               |
(1개 행)


devkuma=# \dp myschema.*
                          액세스 권한
  스키마  | 이름 |  종류  | 액세스 권한 | 칼럼 접근권한 | 정책
----------+------+--------+-------------+---------------+------
 myschema | book | 테이블 |             |               |
(1개 행)


devkuma=#

Try querying data from the memo table as the non-superuser role mykuma before granting privileges.

devkuma=> select * from memo;
오류:  memo 테이블에 대한 접근 권한 없음
devkuma=>

Because the role does not have SELECT privilege on the memo table, the error “오류: memo 테이블에 대한 접근 권한 없음” is displayed and retrieving data fails.

Grant SELECT privilege on the memo table and the myschema.book table to the mykuma role. Connect as a superuser and run the following.

devkuma=# grant select on memo to mykuma;
GRANT
devkuma=# grant select on myschema.book to mykuma;
GRANT
devkuma=#

The privileges have been added. Run \dp again to confirm.

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres+|               |
        |      |        | mykuma=r/postgres         |               |
(1개 행)


devkuma=# \dp myschema.*
                                 액세스 권한
  스키마  | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
----------+------+--------+---------------------------+---------------+------
 myschema | book | 테이블 | postgres=arwdDxt/postgres+|               |
          |      |        | mykuma=r/postgres         |               |
(1개 행)


devkuma=#

The access privileges for each table contain values like the following.

postgres=arwdDxt/postgres+
 mykuma=r/postgres

The values have the following meanings. For the mykuma role, the postgres role has granted the r privilege, which means SELECT.

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

Connect to PostgreSQL again as the regular role mykuma and query data from the memo table.

devkuma=> select * from memo;
 id | memo
----+------
(0개 행)


devkuma=>

The memo table can now be queried.

Now try querying the myschema.book table.

devkuma=> select * from myschema.book;
오류:  myschema 스키마(schema) 접근 권한 없음
줄 1: select * from myschema.book;
                    ^
devkuma=>

This time, the error “오류: myschema 스키마(schema) 접근 권한 없음” is displayed and the query fails. In the public schema, access to objects such as tables is allowed by default, but for schemas other than public, objects in the schema cannot be accessed unless USAGE privilege on the schema is explicitly granted.

Details on granting privileges on schema objects are explained later. For now, connect as a superuser and run the following.

devkuma=# grant usage on schema myschema to mykuma;
GRANT
devkuma=#

Access to objects in the myschema schema has been allowed.

Query the myschema.book table again.

devkuma=> select * from myschema.book;
 id
----
(0개 행)


devkuma=>

The myschema.book table can now be queried.

Add Privileges on Table Columns

Next, here is how to add privileges on table columns.

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

For example, to add SELECT privilege on the column_name column of table_name to role_name, run the following.

GRANT SELECT (column_name) ON table_name To role_name;

Now try it in practice. Use the psql meta-command \dp to check table and column privileges. At this point, no privileges have been added to mykuma.

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres |               |
(1개 행)


devkuma=#

Grant SELECT privilege on the memo column of the memo table to the mykuma role. Connect as a superuser and run the following.

devkuma=# grant select (memo) on memo to mykuma;
GRANT
devkuma=#

The privilege has been added. Run \dp again to confirm.

devkuma=# \dp
                                   액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        |    칼럼 접근권한    | 정책
--------+------+--------+---------------------------+---------------------+------
 public | memo | 테이블 | postgres=arwdDxt/postgres | memo:              +|
        |      |        |                           |   mykuma=r/postgres |
(1개 행)


devkuma=#

The column privileges contain the following values.

memo:              +
 mykuma=r/postgres 

The meaning is the same as access privileges. It means the postgres role has granted r, or SELECT privilege, to the mykuma role.

Connect to PostgreSQL again as the regular role mykuma and query data from the memo table.

devkuma=> select * from memo;
오류:  memo 테이블에 대한 접근 권한 없음
devkuma=>

The error “오류: memo 테이블에 대한 접근 권한 없음” is displayed and the query fails. The memo table has two columns, id and memo, and the mykuma role has SELECT privilege only on the memo column.

Now query only the memo column of the memo table.

devkuma=> select memo from memo;
 memo
------
(0개 행)


devkuma=>

This time, the query succeeds.

To query columns of tables created in schemas other than public, USAGE privilege on the target schema is also required.

Add Permission to Create Schemas

Next, here is how to add permission to create schemas in a specified database.

GRANT {{CREATE | CONNECT | TEMPORARY | TEMP} [...] | ALL [PRIVILEGES]}
    ON DATABASE database_name [...]
    TO role_specification [...] [WITH GRANT OPTION]

For example, to add permission to create schemas in database_name to role_name, run the following.

GRANT CREATE ON DATABASE database_name To role_name;

Now try it in practice. Use the psql meta-command \l to check privileges on the devkuma database. The mykuma role currently has no privileges on the devkuma database.

devkuma=# \l devkuma
                                데이터베이스 목록
  이름   |  소유주  | 인코딩 |     Collate      |      Ctype       | 액세스 권한
---------+----------+--------+------------------+------------------+-------------
 devkuma | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
(1개 행)


devkuma=#

Grant the mykuma role permission to create schemas in the devkuma database. Connect as a superuser and run the following.

devkuma=# grant create on database devkuma to mykuma;
GRANT
devkuma=#

The privilege has been added. Run \l again to confirm.

devkuma=# \l devkuma
                                     데이터베이스 목록
  이름   |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
---------+----------+--------+------------------+------------------+-----------------------
 devkuma | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =Tc/postgres         +
         |          |        |                  |                  | postgres=CTc/postgres+
         |          |        |                  |                  | mykuma=C/postgres
(1개 행)


devkuma=#

The access privileges contain the following values.

=Tc/postgres         +
postgres=CTc/postgres+
mykuma=C/postgres

The meaning is the same as access privileges. For the mykuma role, it means the postgres role has granted the C, or CREATE, privilege.

Now connect to PostgreSQL as the regular role mykuma and create a mykuma schema in the devkuma database.

devkuma=> create schema mykuma;
CREATE SCHEMA
devkuma=>

The mykuma schema can now be created in the devkuma database. Run the psql meta-command \dn to confirm.

devkuma=> \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 mykuma   | mykuma
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=>

You can confirm that the mykuma schema has been created.

Add Permission to Create Objects Such as Tables in a Schema

Finally, here is how to add permission to create objects such as tables in a specified schema.

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

For example, to add permission to create tables and other objects in the schema_name schema to role_name, run the following.

GRANT CREATE ON SCHEMA schema_name To role_name;

To add access permission for tables and other objects in the schema_name schema to role_name, run the following.

GRANT USAGE ON SCHEMA schema_name To role_name;

Now try it in practice. Use the psql meta-command \dn+ to check privileges on the myschema schema. The mykuma role currently has no privileges on the myschema schema.

devkuma=# \dn+
                         스키마(schema) 목록
   이름   |  소유주  |     액세스 권한      |          설명
----------+----------+----------------------+------------------------
 mykuma   | mykuma   |                      |
 myschema | postgres | postgres=UC/postgres+|
          |          | mykuma=UC/postgres   |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
(3개 행)


devkuma=#

Grant the mykuma role permission to create objects in the myschema schema. Connect as a superuser and run the following.

devkuma=# grant create on schema myschema to mykuma;
GRANT
devkuma=#

The privilege has been added. Run \dn+ again to confirm.

devkuma=# \dn+
                         스키마(schema) 목록
   이름   |  소유주  |     액세스 권한      |          설명
----------+----------+----------------------+------------------------
 mykuma   | mykuma   |                      |
 myschema | postgres | postgres=UC/postgres+|
          |          | mykuma=UC/postgres   |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
(3개 행)


devkuma=#

The access privileges contain the following values.

postgres=UC/postgres+
mykuma=C/postgres

The meaning is the same as access privileges. For the mykuma role, it means the postgres role has granted the C, or CREATE, privilege.

Now connect to PostgreSQL as the regular role mykuma and create a product table in the myschema schema of the devkuma database.

devkuma=> create table myschema.product (id integer, name varchar (10));
CREATE TABLE
devkuma=>

The product table has been created in the myschema schema. Run the psql meta-command \dp to confirm.

devkuma=> \dp myschema.*
                                  액세스 권한
  스키마  |  이름   |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
----------+---------+--------+---------------------------+---------------+------
 myschema | book    | 테이블 | postgres=arwdDxt/postgres+|               |
          |         |        | mykuma=r/postgres         |               |
 myschema | product | 테이블 |                           |               |
(2개 행)


devkuma=>

You can confirm that the myschema.product table has been created.

–

This page explained how to add privileges by using the GRANT command.