Privileges#

When an object is created, it is assigned an owner (usually the role that executed the creation statement).

You can assign ownership to another role if you are:

  • a superuser or

  • the current owner (or member of the owning role) and a member of the new owning role.

ALTER TABLE table_name OWNER TO new_owner;

Initially, only the owner or superusers can work with an object, unless privileges are granted. We use the GRANT command to assign privileges:

GRANT SELECT, UPDATE (details) ON staff_info TO hr_assistant;

Applicable privileges depend on the object’s type. Writing ALL instead of a specific privilege grants all privileges relevant to the object type.

The special role PUBLIC can be used to grant a privilege to every role on the system. You can set up “group” roles to help manage privileges.

Use the REVOKE command to revoke previously granted privileges:

REVOKE SELECT, UPDATE (details) ON staff_info FROM hr_assistant;

Note

Ordinarily, only the object’s owner (or a superuser) can grant or revoke privileges on an object. But if a privilege is granted “with grant option”, the recipient is allowed to grant it in turn to others.

If the grant option is revoked, all who received the privilege from that recipient (directly or through a chain of grants) will lose the privilege.

Owners are always treated as holding all grant options, and so can revoke and re-grant their own privileges.

  • SELECT

    • Allows SELECT on all (or specific) columns of a table, view, materialized view or other table-like object.

    • Allows COPY TO.

    • Required to reference existing column values in UPDATE, DELETE or MERGE.

    • Allows currval function in sequences.

    • Allows large objects to be read.

  • INSERT

    • Allows INSERT of new rows to all / specified columns.

    • Allows COPY FROM.

  • UPDATE

    • Allows UPDATE on any / specified columns.

    • Typically requires the SELECT privilege to determine rows to update.

    • Required in SELECT ... FOR UPDATE and SELECT ... FOR SHARE.

    • Allows nextval and setval functions in sequences.

    • Allows writing and truncating large objects.

  • DELETE

    • Allows DELETE on rows from a table, view, …

    • Typically requires SELECT privilege - to reference table columns and determine what rows to delete.

  • TRUNCATE

    • Allows TRUNCATE on a table.

  • REFERENCES

    • Allows creation of a foreign key constraint.

  • TRIGGER

    • Allows creation of a trigger on a table, view, …

  • CREATE

    • In databases:

      • allows creation of schemas and publications

      • allows installation of trusted extensions.

    • In schemas:

      • allows creation of new objects

      • allows renaming of existing objects you own.

    • In tablespaces:

      • allows creation of tables, indexes and temporary files

      • allows creation of databases with the tablespace as default.

    Note

    Revoking this privilege will not alter the existence or location of existing objects.

  • CONNECT

    • Allows connection to the database.

    • Checked at connection startup.

  • TEMPORARY

    • Allows creation of temporary tables.

  • EXECUTE

    • Allows calling a function / procedure, including use of any operators implemented on top of the function.

  • USAGE

    • For procedural languages:

      • allows use of the languages to create functions.

    • For schemas:

      • allows access to contained objects, assuming the objects’ privilege requrements are met.

        Caution

        One can view object names even without this privilege e.g. by querying system catalogs.

        Revoking this permission is not a secure way to prevent object access since existing sessions might have statements that have previously performed the “look up”.

    • For sequences:

      • allows use of currval and nextval functions.

    • For types and domains:

      • allows use in creation of tables, functions and other schema objects.

    • For foreign-data wrappers:

      • allows creation of new servers.

    • For foreign servers:

      • allows creation of foreign tables

      • allows creation, alteration or dropping of user mappings associated with the server.

  • SET

    • Allows setting a server configuration parameter within the current session.

  • ALTER SYSTEM

    • Allows setting a server configuration parameter using the ALTER SYSTEM command.

PostgreSQL grants default privileges when objects are created. These can be overridden using the ALTER DEFAULT PRIVILEGES command.

Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type.

Demo#

Creating users ‘luther’ and ‘ethan’ with the role ‘agents’. luther can create databases (createdb).#
$ createuser agents
$ createuser luther --role=agents --createdb --pwprompt 
Enter password for new role: 
Enter it again: 
$ createuser ethan --role=agents --pwprompt 
Enter password for new role: 
Enter it again:
luther creates the ‘top-secret’ database and assigns it to agents. This allows all agents to access it (currently just luther and ethan).#
$ createdb top-secret --username=luther --host=localhost --owner=agents
Password: 
$ psql top-secret --username=luther --host=localhost
Password for user luther: 
psql (15.3)
Type "help" for help.

top-secret=>
luther creates the ‘agent_archive’ table, becoming it’s owner. ethan cannot access the agent_archive table until luther (or a superuser) grants him the necessary privileges.#
top-secret=> CREATE TABLE agent_archive(
top-secret->   agent_id     serial PRIMARY KEY,
top-secret->   first_name   text,
top-secret->   last_name    text,
top-secret->   details      text
);
CREATE TABLE
top-secret=> INSERT INTO agent_archive (first_name, last_name, details)
top-secret->   VALUES ('Benjamin', 'Dunn', 'IT & logistics expert.');
INSERT 0 1
top-secret=> SELECT * FROM agent_archive;
 agent_id | first_name | last_name |        details        
----------+------------+-----------+-----------------------
        1 | Benjamin   | Dunn      | IT & logistics expert.
(1 row)

top-secret=> \connect top-secret ethan
Password for user ethan: 
You are now connected to database "top-secret" as user "ethan".
top-secret=> SELECT * FROM agent_archive;
ERROR:  permission denied for table agent_archive
luther grants the SELECT privilege to ethan. Now ethan can read from the agent_archive table. But ethan cannot add new values just yet.#
top-secret=> \connect top-secret luther
Password for user luther: 
You are now connected to database "top-secret" as user "luther".
top-secret=> GRANT SELECT ON agent_archive TO ethan;
GRANT
top-secret=> \connect top-secret ethan
Password for user ethan: 
You are now connected to database "top-secret" as user "ethan".
top-secret=> SELECT * FROM agent_archive;
 agent_id | first_name | last_name |        details        
----------+------------+-----------+-----------------------
        1 | Benjamin   | Dunn      | IT & logistics expert.
(1 row)

top-secret=> INSERT INTO agent_archive (first_name, last_name, details)
top-secret->   VALUES ('Ilsa', 'Faust', 'Ally. Mission Specialist.');
ERROR:  permission denied for table agent_archive
luther assigns ownership of the agent_archive table to the role agents. All agents inherit full rights to the table. ethan can now add info about his close ally.#
top-secret=> \connect top-secret luther
Password for user luther: 
You are now connected to database "top-secret" as user "luther".
top-secret=> ALTER TABLE agent_archive OWNER TO agents;
ALTER TABLE
top-secret=> \connect top-secret ethan
Password for user ethan: 
You are now connected to database "top-secret" as user "ethan".
top-secret=> INSERT INTO agent_archive (first_name, last_name, details)
top-secret->   VALUES ('Ilsa', 'Faust', 'Ally. Mission Specialist.');
INSERT 0 1
top-secret=> SELECT * FROM agent_archive;
 agent_id | first_name | last_name |          details          
----------+------------+-----------+---------------------------
        1 | Benjamin   | Dunn      | IT & logistics expert
        2 | Ilsa       | Faust     | Ally. Mission Specialist.
(2 rows)

Access Control List (ACL) Privilege Abbreviations#

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

Summary of Access Privileges#

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

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+

Assigned privileges are displayed as a list of aclitem entries. A * appears only when grant options have been explicitly granted.