Search code examples
postgresqldatabase-schema

postgresql - `Access priviledges` in `List of schemas`


I am running \dn on a PostgreSQL instance and am wondering what exactly the content of the Access privileges column shows?

postgres=# \dn+
                              List of schemas
    Name     |    Owner    |  Access privileges   |      Description
-------------+-------------+----------------------+------------------------
 keycloap    | keycloap    |                      |
 novebackend | novebackend |                      |
 public      | postgres    | postgres=UC/postgres+| standard public schema
             |             | =UC/postgres         |

Solution

  • This is well documented in the manual

    Table 5.1 shows the one-letter abbreviations that are used for these privilege types in ACL (Access Control List) values. You will see these letters in the output of the psql commands listed below, or when looking at ACL columns of system catalogs.

    +============+==============+================================================================================+
    | 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 |
    +============+==============+================================================================================+
    

    And then further down:

    [...] where each aclitem describes the permissions of one grantee that have been granted by a particular grantor
    For example, calvin=r*w/hobbes specifies that the role calvin has the privilege SELECT (r) with grant option (*) as well as the non-grantable privilege UPDATE (w), both granted by the role hobbes