CREATE ROLE name [ [ WITH ] option [ ... ] ]where option can be:SUPERUSER | NOSUPERUSER| CREATEDB | NOCREATEDB| CREATEROLE | NOCREATEROLE| INHERIT | NOINHERIT| LOGIN | NOLOGIN| REPLICATION | NOREPLICATION| BYPASSRLS | NOBYPASSRLS| CONNECTION LIMIT connlimit| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL| VALID UNTIL 'timestamp'| IN ROLE role_name [, ...]| IN GROUP role_name [, ...]| ROLE role_name [, ...]| ADMIN role_name [, ...]| USER role_name [, ...]| SYSID uid
Role | Allowed Access |
pg_execute_server_program | Allow executing programs on the database server as the user the database runs as with COPY and other functions which allow executing a server-side program. |
pg_monitor | Read/Execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables. |
pg_read_all_settings | Read all configuration variables, even those normally visible only to superusers. |
pg_read_all_stats | Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers. |
pg_read_server_files | Allow reading files from any location the database can access on the server with COPY and other file-access functions. |
pg_signal_backend | Signal another backend to cancel a query or terminate its session. |
pg_stat_scan_tables | Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time. |
pg_write_server_files | Allow writing to files in any location the database can access on the server with COPY and other file-access functions. |
public | An implicitly defined group that always includes all roles. Any particular role will have the sum of permissions granted directly to public. PostgreSQL grants default permissions on some types of objects to public. |
Permissions | Abbreviation | Supported Object |
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 |
Object Type | Permissions | Permissions of Default Role (public) | psql Command to Query Permissions |
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 | - |
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+ |
postgres=# \\dpAccess privilegesSchema | Name | Type | Access privileges | Column privileges | Policies--------+------+-------+-------------------+-------------------+----------public | t1 | table | test1=arwdDxt/test1 | |(1 rows)postgres=# grant select on t1 to normal_user;GRANTpostgres=# grant insert on t1 to normal_user with grant option;GRANTpostgres=# grant update on t1 to public;GRANTpostgres=# grant select (a) on t1 to test2;GRANTpostgres=# \\dpAccess privilegesSchema | Name | Type | Access privileges | Column privileges | Policies--------+------+-------+-----------------------+-------------------+----------public | t1 | table | test1=arwdDxt/test1 +| a: +|| | | normal_user=a*r/test1+| test2=r/test1 || | | =w/test1 | |(1 rows)-- Where, "=w/test1" specifies that test1 grants public the UPDATE privilege.
Was this page helpful?