39. PostgreSQL: Accounts and privileges

A PostgreSQL-language instance keeps the MIRAJ system of accounts, privileges and roles. Clients authenticate to it as they do on a PostgreSQL server (SCRAM-SHA-256, TLS), and the pg_roles catalog shows the accounts; but the statements that manage accounts and rights are those of MIRAJ, written with PostgreSQL lexical rules (identifiers in double quotes, strings in single quotes). The usual PostgreSQL role options (CREATE USER … WITH PASSWORD '…', CREATE ROLE … LOGIN PASSWORD '…', ALTER ROLE …) are recognized and translated into this system (39.2). A reader coming from PostgreSQL should keep three differences in mind from the start:

• an account is a name + host pair (app@'%', compta@localhost), created by CREATE USER … IDENTIFIED BY '…'; the PostgreSQL form CREATE USER app WITH PASSWORD '…' creates app@'%';
• a privilege is granted on a whole schema with schema.* (present and future): there is no GRANT … ON SCHEMA, no ON ALL TABLES IN SCHEMA, and no ALTER DEFAULT PRIVILEGES;
• there is no object owner: privileges, and only privileges, say what an account can do.

This chapter covers authentication and TLS, accounts, privileges at their five levels, roles, rights auditing and privilege errors. The examples use the ventes schema from chapter 32. Getting started.

39.1 Authentication#

Mechanisms#

The mechanism offered to the client depends on the account:

SHOW password_encryption returns scram-sha-256. The server keeps only a verifier of the password, never the password itself: there is no way to recover a forgotten password, only to set a new one. To give a SCRAM verifier to an account that has only a hash, set its password again.

A client that requires TLS channel binding (channel_binding=require) is refused by its own driver, because the server announces only SCRAM-SHA-256; the default value (channel_binding=prefer) works.

Connection failures#

Refusals arrive as FATAL, with a PostgreSQL SQLSTATE:

Nothing distinguishes an unknown account from a wrong password: both receive the same SCRAM exchange and the same refusal. Repeated failures from one address are counted and slowed down, then the address is blocked (16.5).

Connection database#

The client's dbname parameter chooses the session's current schema (38.8):

• the name of a MIRAJ schema (dbname=ventes) makes it the current schema;
• miraj, postgres or the user name (the default values of clients) are ignored: the session opens without a current schema (current_schema returns NULL);
• an unknown name opens the connection without a current schema, with a notice:

NOTICE:  database "nexistepas" does not exist; connected without a current database   (3D000)

TLS encryption#

The server encrypts connections when it is started with a certificate (--tls-cert and --tls-key, see 16.10.2). As soon as a certificate is given, TLS is required (unless --allow-plain-with-tls is set), which the startup announces:

MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, langage PostgreSQL, dossier "D:\donnees", TLS exigé, 12 fils.

The client sets encryption with its sslmode parameter, as for a PostgreSQL server:

import psycopg2
conn = psycopg2.connect(host="serveur.exemple", port=7007, user="app", password="…",
                        dbname="ventes", sslmode="verify-full", sslrootcert="ca.pem")
print(conn.info.ssl_in_use, conn.info.ssl_attribute("protocol"))    # True TLSv1.3

psql "host=serveur.exemple port=7007 user=app dbname=ventes sslmode=verify-full sslrootcert=ca.pem"

The encryption state is read on the client side (conn.info.ssl_in_use, \conninfo in psql): the pg_stat_ssl view does not exist.

39.2 Accounts#

Account identity: name and host#

An account is identified by the name@host pair. The same name can designate different accounts, with different passwords and rights, depending on the machine the connection comes from:

The name and the host are written bare, in double quotes or in single quotes (app, "app", 'app' designate the same account). When several accounts with the same name match, the most specific wins: an exact host or localhost comes before a pattern, and % comes last.

The PostgreSQL client gives only the name; the server chooses the account from the address the connection comes from. current_user, session_user and user return this name, without the host.

Creating an account#

CREATE USER [IF NOT EXISTS] account [IDENTIFIED BY 'password'] [, account …];

CREATE USER app IDENTIFIED BY 'Appli-2026';
CREATE USER lecteur@'%' IDENTIFIED BY 'Lecture-2026',
            compta@localhost IDENTIFIED BY 'Compta-2026';
CREATE USER IF NOT EXISTS app IDENTIFIED BY 'Appli-2026';

CREATE USER
CREATE USER
WARNING:  Authorization ID 'app'@'%' already exists.
CREATE USER

• IDENTIFIED BY 'text' sets the password (immediately converted into verifiers); IDENTIFIED BY PASSWORD '*…' sets an already computed hash, as written by SHOW CREATE USER (such an account has no SCRAM verifier, see 39.1).
• A newly created account has no rights on data: only USAGE, the right to connect. Give it privileges with GRANT (39.3).
• Creating an account that exists returns role "app" already exists (42710), or a simple warning with IF NOT EXISTS.

Remote accounts without a password are refused. As long as the require_password parameter is 1 (default), an account reachable from another machine must have a password:

CREATE USER sansmdp;

ERROR:  Account 'sansmdp'@'%' can connect from other hosts and has no password: give it one
        (IDENTIFIED BY) or set require_password = OFF   (XX000)

An account limited to the loopback address (CREATE USER outil@localhost) may have none.

PostgreSQL role options#

The PostgreSQL forms are accepted for an account named without a host (name@'%'):

CREATE {USER | ROLE} [IF NOT EXISTS] name [WITH] option [option …];
ALTER  {USER | ROLE} name [WITH] option [option …];

CREATE USER app WITH PASSWORD 'Appli-2026';
CREATE ROLE outil WITH LOGIN ENCRYPTED PASSWORD 'Outil-2026' NOSUPERUSER IN ROLE role_lecture;
CREATE ROLE role_lecture NOLOGIN;              -- a MIRAJ role (8.4)
ALTER USER app WITH PASSWORD 'Appli-2027';
ALTER ROLE app NOLOGIN;                        -- ACCOUNT LOCK

Each option is honored by the MIRAJ mechanism that matches it, or refused (0A000, the message names the option): none is silently ignored.

The statement is applied as a whole: if one of the GRANT statements it entails fails (unknown IN ROLE role, privilege that the session's account cannot grant), the account is not created. The same privileges as with the MIRAJ forms are required: CREATE USER to create or modify an account, and, for SUPERUSER, CREATEDB or CREATEROLE, the right to grant the corresponding privileges (42501 otherwise). A role received through IN ROLE is activated like any granted role (39.4). require_password applies: CREATE USER app without a password is refused.

The MIRAJ forms (IDENTIFIED BY, account@host, several accounts, ACCOUNT LOCK) remain the ones to use as soon as a host is specified.

Modifying, locking, dropping#

ALTER USER account IDENTIFIED BY 'new password';
ALTER USER account ACCOUNT {LOCK | UNLOCK};
ALTER USER account DEFAULT ROLE role;
DROP USER [IF EXISTS] account [, account …];

ALTER USER app IDENTIFIED BY 'Appli-2027';
ALTER USER app ACCOUNT LOCK;        -- connection refused (28000), rights kept
ALTER USER app ACCOUNT UNLOCK;
DROP USER lecteur, compta@localhost;

ACCOUNT LOCK suspends an account without dropping it or touching its password or rights. DROP USER removes the account and all its privileges; a session already open under this account stays connected, but has no rights left (42501 at the next statement). As in PostgreSQL, DROP ROLE drops an account (a role with LOGIN) as well as a role, and DROP USER a role.

Changing a password#

SET PASSWORD = 'new password';                -- the session's account
SET PASSWORD FOR app = 'new password';        -- another account
ALTER USER lecteur IDENTIFIED BY 'new password';

An account can always change its own password, with SET PASSWORD or with ALTER USER on itself. Managing other accounts (CREATE USER, ALTER USER, DROP USER, SET PASSWORD FOR) requires the global CREATE USER privilege:

ERROR:  Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation   (42501)

An account that holds the SYSTEM_USER privilege, as root does, can be modified, dropped or given rights only by an account that also holds it: an administrator who has been given CREATE USER cannot take over root.

Viewing accounts#

SELECT rolname, rolsuper, rolcanlogin FROM pg_roles ORDER BY rolname;

   rolname    | rolsuper | rolcanlogin
--------------+----------+-------------
 app          | f        | t
 compta       | f        | t
 lecteur      | f        | t
 role_lecture | f        | f
 root         | t        | t
(5 lignes)

pg_roles, pg_user and pg_authid show one role per account name (two accounts app@'%' and app@localhost make only one row there); root is the superuser there, with OID 10. The password is always masked (******** in pg_roles and pg_user, NULL in pg_authid). This is what \du in psql and graphical tools read.

The attributes there follow the account's global privileges, those set by the PostgreSQL role options (39.2):

pg_roles and pg_user are readable by any account, as in PostgreSQL: they carry no secret. pg_authid is reserved for whoever administers accounts (CREATE USER privilege on *.*); any other account receives 42501 (permission denied for table pg_authid). pg_shadow (the accounts that can connect, same columns as pg_user) is reserved in the same way; its passwd column, like pg_authid.rolpassword, is always NULL: neither a password nor a verifier leaves the account vault.

SHOW CREATE USER app;

CREATE USER "app"@"%" IDENTIFIED BY PASSWORD '*D7B9…7062'

SHOW CREATE USER account returns the statement that recreates the account, with its password hash and its lock (… ACCOUNT LOCK); the account must be named (SHOW CREATE USER alone is a syntax error). The account is written there as identifiers, in double quotes, as in SHOW GRANTS (single quotes are reserved for strings); a role gives CREATE ROLE "name"@"%". The text can be replayed as is.

39.3 Privileges: GRANT and REVOKE#

Levels#

A privilege held at one level applies to everything that level covers: SELECT on ventes.* covers the tables that will be created tomorrow in ventes, without a new grant. This replaces PostgreSQL's GRANT … ON ALL TABLES IN SCHEMA and ALTER DEFAULT PRIVILEGES.

The schema name can be a pattern, written in quotes: GRANT SELECT ON "vent%".* TO ….

Syntax#

GRANT privilege [(columns)] [, …] ON [TABLE] target TO account [, account …] [WITH GRANT OPTION];
REVOKE [IF EXISTS] privilege [(columns)] [, …] ON [TABLE] target FROM account [, account …];
REVOKE ALL [PRIVILEGES], GRANT OPTION FROM account [, account …];

Common privileges:

• WITH GRANT OPTION allows the grantee to pass these privileges on.
• To grant or revoke a privilege, you must hold it yourself at the target level with the GRANT option.
• GRANT does not create an account: the grantee must exist (42000, You are not allowed to create a user with GRANT).
• Rights apply from the statement that changes them, including to sessions already open. FLUSH PRIVILEGES is accepted with no effect.
• REVOKE of a privilege that was not granted at that level is an error (42000, There is no such grant defined for user 'lecteur' on host '%' on table 'article'), downgraded to a warning by IF EXISTS. A privilege granted on ventes.* is revoked on ventes.*, not table by table.

Examples#

Read-only account on a schema:

CREATE USER lecteur IDENTIFIED BY 'Lecture-2026';
GRANT SELECT ON ventes.* TO lecteur;

Application account that reads, writes and calls routines, without touching the schema:

CREATE USER app IDENTIFIED BY 'Appli-2026';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON ventes.* TO app;

Account limited to a few tables:

GRANT SELECT ON ventes.client TO compta@localhost;
GRANT SELECT, UPDATE ON TABLE ventes.commande TO compta@localhost;

Schema administrator, who can delegate:

GRANT ALL ON ventes.* TO app WITH GRANT OPTION;

Revoking:

REVOKE INSERT, UPDATE, DELETE ON ventes.* FROM app;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM app;        -- everything, at every level

What the lecteur account sees:

SELECT count(*) FROM client;                 -- 3
INSERT INTO client (nom, email) VALUES ('x', '[email protected]');
CREATE TABLE t (a int);
SELECT * FROM archives.client;               -- schema on which lecteur has no rights

ERROR:  permission denied for table client   (42501)
ERROR:  permission denied for table t        (42501)
ERROR:  permission denied for table client   (42501)

Column privileges#

-- Customer service reads customer identity and corrects their city, never their address
CREATE USER support@localhost IDENTIFIED BY 'Support-2026';
GRANT SELECT (id, nom, ville), UPDATE (ville) ON ventes.client TO support@localhost;

Every column used by a statement is checked: SELECT list, WHERE, ORDER BY, joins, values of an UPDATE, RETURNING clause. SELECT * requires all columns; count(*) requires none.

-- support account session
SELECT id, nom, ville FROM client ORDER BY id;          -- 3 rows
SELECT count(*) FROM client;                            -- 3
UPDATE client SET ville = 'Lyon' WHERE id = 3;          -- UPDATE 1
SELECT * FROM client;
SELECT nom FROM client WHERE email LIKE '%@example.com';
UPDATE client SET nom = 'X' WHERE id = 1;
UPDATE client SET ville = 'Lyon' WHERE id = 3 RETURNING email;

ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)
ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)
ERROR:  UPDATE command denied to user 'support'@'localhost' for column 'nom' in table 'client'     (42501)
ERROR:  SELECT command denied to user 'support'@'localhost' for column 'email' in table 'client'   (42501)

A view is another way to expose only some columns or some rows: the account receives SELECT on the view, not on the table.

CREATE VIEW v_client_public AS SELECT id, nom, ville FROM client;
GRANT SELECT ON ventes.v_client_public TO support@localhost;

Sequences#

A sequence is designated like a table: INSERT allows nextval and setval, SELECT allows currval and reading the sequence.

GRANT SELECT, INSERT ON ventes.seq_facture TO app;

Without this right, SELECT nextval('seq_facture') returns permission denied for table seq_facture (42501). PostgreSQL's GRANT USAGE ON SEQUENCE … forms are not recognized. A serial or identity column requires no more than INSERT on its table; reading the assigned value with INSERT … RETURNING id, however, requires SELECT.

Routines#

Calling a function or a procedure requires EXECUTE on the routine, or on its schema (all its routines, present and future):

GRANT EXECUTE ON outils.* TO support@localhost;                       -- the whole schema
GRANT EXECUTE ON FUNCTION outils.nb_articles() TO compta@localhost;   -- a single function
GRANT EXECUTE ON PROCEDURE outils.relancer(integer) TO compta@localhost;
GRANT EXECUTE ON ROUTINE outils.purger TO exploitation WITH GRANT OPTION;
REVOKE EXECUTE ON FUNCTION outils.nb_articles() FROM compta@localhost;

Without it, the call fails before the body runs:

ERROR:  permission denied for function nb_articles   (42501)
ERROR:  permission denied for procedure relancer   (42501)      -- CALL outils.relancer(1)

• The list of types in parentheses is accepted and ignored: MIRAJ has only one routine per name and per schema (no overloading). ROUTINE designates the function of that name, otherwise the procedure.
• The routine must exist (42883, FUNCTION outils.absente does not exist); only one routine per statement (GRANT … ON FUNCTION a, b: 0A000).
• EXECUTE and ALTER ROUTINE (which allows DROP FUNCTION / DROP PROCEDURE of that routine) are admitted at this level; any other privilege returns 42000 (Illegal GRANT/REVOKE command). Passing the right on requires the GRANT option on the routine or its schema (42501).
• REVOKE of an absent right: 42000 (There is no such grant defined for user 'compta' on host 'localhost' on routine 'nb_articles').
• Dropping the routine removes the rights granted on it; once recreated, it carries none.
• A right on a routine makes its schema visible (pg_namespace, \dn).
• SHOW GRANTS shows them (GRANT EXECUTE ON FUNCTION "outils"."nb_articles" TO "compta"@"localhost"), information_schema.routine_privileges and has_function_privilege read them (39.5).

The body of a routine runs with the rights of the caller (SECURITY INVOKER, the default as in PostgreSQL) or with those of its creator (SECURITY DEFINER). A SECURITY DEFINER function therefore lets an account read, through it alone, a table on which it has no rights:

-- created by root in the outils schema
CREATE FUNCTION outils.nb_articles() RETURNS integer LANGUAGE sql SECURITY DEFINER
  AS $$ SELECT count(*) FROM ventes.article $$;
CREATE FUNCTION outils.nb_articles_inv() RETURNS integer LANGUAGE sql
  AS $$ SELECT count(*) FROM ventes.article $$;

-- support account session, which has no rights on ventes.article
SELECT outils.nb_articles();          -- 3
SELECT outils.nb_articles_inv();
-- ERROR:  permission denied for table article   (42501)

In the body of a SECURITY DEFINER routine, current_user returns the creator and session_user remains the session's account, as in PostgreSQL: a SECURITY DEFINER function created by root that returns current_user || '/' || session_user returns root/support to the support account.

Triggers always run with the rights of their creator. See 40. Routines and triggers (PL/pgSQL).

39.4 Roles#

A role groups privileges under a name, to grant them at once to several accounts. Unlike PostgreSQL, a role and an account are two different things: a role does not connect (rolcanlogin = f). CREATE ROLE r and CREATE ROLE r NOLOGIN create a role; CREATE ROLE r LOGIN PASSWORD '…' creates an account (39.2).

Creating a role and granting it#

CREATE ROLE [IF NOT EXISTS] role;
DROP ROLE [IF EXISTS] role;
GRANT role TO account [WITH ADMIN OPTION];
REVOKE role FROM account;

CREATE ROLE role_lecture;
GRANT SELECT ON ventes.* TO role_lecture;
CREATE ROLE role_ecriture;
GRANT INSERT, UPDATE, DELETE ON ventes.* TO role_ecriture;

GRANT role_lecture  TO compta@localhost;
GRANT role_ecriture TO compta@localhost;

CREATE ROLE requires the CREATE ROLE privilege (or CREATE USER), DROP ROLE the DROP ROLE privilege (or CREATE USER). Granting or revoking a role requires ROLE_ADMIN (or SUPER), or the ADMIN option on that role (WITH ADMIN OPTION). Dropping a role immediately removes it from all the accounts that held it.

Activating a role#

A granted role is not active at connection time, unless it is a default role of the account: another difference from PostgreSQL, where a member inherits its roles.

SET DEFAULT ROLE role_lecture TO compta@localhost;     -- active at every connection
SET DEFAULT ROLE ALL TO compta@localhost;              -- all granted roles
SET DEFAULT ROLE NONE TO compta@localhost;
ALTER USER compta@localhost DEFAULT ROLE role_lecture; -- same effect

In the session, SET ROLE chooses the active roles among those granted to the account:

SET ROLE takes only one role, as in PostgreSQL: SET ROLE a, b returns 42601 (SET role takes only one argument).

-- compta session: role_lecture by default, role_ecriture granted
SELECT count(*) FROM article;                        -- 3 (role_lecture)
INSERT INTO commande (client_id) VALUES (1);         -- 42501: role_ecriture is not active
SET ROLE role_ecriture;
INSERT INTO commande (client_id) VALUES (1);         -- INSERT 0 1
SELECT count(*) FROM article;                        -- 42501: role_lecture is no longer active
SET ROLE DEFAULT;
SELECT count(*) FROM article;                        -- 3

The parameter form is the same statement: SET [SESSION | LOCAL] role {TO | =} … (SET role TO role_ecriture, SET role = 'none', SET role TO DEFAULT) and RESET role. SHOW role, current_setting('role') and pg_settings return the chosen role (none with no active role, the default role after SET ROLE DEFAULT).

SET ROLE is a transaction setting, like SET: done inside a BEGIN … block, it is undone by ROLLBACK (and by ROLLBACK TO SAVEPOINT if it was done after the savepoint), kept by COMMIT. It therefore does not commit the open transaction. SET LOCAL ROLE … (or set_config('role', …, true)) lasts only until the end of the transaction: this is the form used by API servers that take a role per request; outside a transaction, it has no effect, with the 25P01 warning.

BEGIN;
SET LOCAL ROLE role_ecriture;
INSERT INTO commande (client_id) VALUES (2);         -- INSERT 0 1
COMMIT;                                              -- the roles from before BEGIN come back

An unknown role: role "…" does not exist (42704); a role that exists but is not granted to the account: 42501 ("role_ecriture"@"%" is not granted to "lecteur"@"%"). current_user and current_role always return the account name; the active roles are read in information_schema.enabled_roles.

Changing identity: SET SESSION AUTHORIZATION#

SET SESSION AUTHORIZATION account;      -- or 'account'
SET SESSION AUTHORIZATION DEFAULT;     -- go back to the connection's account
RESET SESSION AUTHORIZATION;           -- same
SET LOCAL SESSION AUTHORIZATION account; -- until the end of the transaction
SET session_authorization TO 'account';  -- parameter form: the same statement

The parameter form is the same statement, with the same rules: SET [SESSION | LOCAL] session_authorization {TO | =} {account | 'account' | DEFAULT}, RESET session_authorization, set_config('session_authorization', 'account', is_local).

SET SESSION AUTHORIZATION makes the session really act under another account: session_user and current_user return its name, its privileges replace those of the connection's account, and its default roles become the active roles. This is the way to check an account's rights without knowing its password.

-- session opened by root
SET SESSION AUTHORIZATION lecteur;
SELECT session_user, current_user;                 -- lecteur | lecteur
INSERT INTO client (nom, email)
  VALUES ('essai', '[email protected]');           -- 42501: lecteur can only read
RESET SESSION AUTHORIZATION;
SELECT session_user;                               -- root

BEGIN;
SET LOCAL SESSION AUTHORIZATION lecteur;
SELECT current_user;                               -- lecteur
COMMIT;
SELECT current_user;                               -- root

The client is kept informed: at every identity change (end of transaction included), the server sends back the session_authorization and is_superuser parameters (ParameterStatus), which SHOW session_authorization, SHOW is_superuser and current_setting(…) also return. is_superuser is on when the account under which the session acts has all privileges on *.*, active roles included (SET ROLE can therefore change it).

Worth knowing: the statement is refused (42501) in a routine or a trigger run under its definer; SHOW PROCESSLIST and pg_stat_activity.usename keep showing the connection's account, as in PostgreSQL.

39.5 Viewing and auditing rights#

SHOW GRANTS#

SHOW GRANTS;                                         -- the session's account, active roles merged
SHOW GRANTS FOR app;                                 -- another account (CREATE USER privilege)
SHOW GRANTS FOR compta@localhost USING role_lecture; -- with the rights of this role

                             Grants for app@%
--------------------------------------------------------------------------
 GRANT USAGE ON *.* TO "app"@"%"
 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON "ventes".* TO "app"@"%"
 GRANT REFERENCES, TRIGGER ON "ventes"."client" TO "app"@"%"
 GRANT INSERT ON "ventes"."ligne_commande" TO "app"@"%" WITH GRANT OPTION
 GRANT SELECT, INSERT ON "ventes"."seq_facture" TO "app"@"%"
(5 lignes)

Granted roles appear there too (GRANT "role_lecture"@"%" TO "compta"@"localhost"); with USING role, the role's privileges are added to those of the account. An unknown account returns 42000 (There is no such grant defined for user 'inconnu' on host '%').

Each line is a statement that can be replayed as is: this is how the miraj-dump --users tool exports accounts and rights (41. Catalog and tools).

information_schema#

SELECT grantee, table_name, privilege_type, is_grantable
  FROM information_schema.table_privileges
 WHERE table_schema = 'ventes' ORDER BY 1, 2, 3;

       grantee        |   table_name   | privilege_type | is_grantable
----------------------+----------------+----------------+--------------
 'app'@'%'            | client         | REFERENCES     | NO
 'app'@'%'            | client         | TRIGGER        | NO
 'app'@'%'            | ligne_commande | INSERT         | YES
 'app'@'%'            | seq_facture    | INSERT         | NO
 'app'@'%'            | seq_facture    | SELECT         | NO
 'compta'@'localhost' | client         | SELECT         | NO
 'compta'@'localhost' | commande       | SELECT         | NO
 'compta'@'localhost' | commande       | UPDATE         | NO
(8 lignes)

These tables show only the privileges granted at that level: a SELECT received on ventes.* appears in schema_privileges, not in table_privileges. The grantee is written there as 'name'@'host'. An ordinary account sees only what concerns it.

What an account sees of the catalog#

The catalog is filtered according to rights: pg_namespace, pg_class, information_schema.tables or \dt show an account only the schemas and tables it can reach, with no error message for the rest. The support account, which has rights only on client and v_client_public, sees only these two objects in ventes. information_schema.columns follows privileges column by column: with privileges granted on some columns only, only those are described, and privileges returns those the account holds on each one (select,update, for example).

The has_table_privilege, has_column_privilege, has_any_column_privilege and has_schema_privilege functions return the real privileges, those that statements check:

SELECT has_table_privilege('client', 'SELECT'),                 -- t
       has_table_privilege('client', 'INSERT, UPDATE'),         -- t if either is held
       has_column_privilege('client', 'nom', 'UPDATE'),
       has_schema_privilege('ventes', 'CREATE'),
       has_table_privilege('lecteur', 'ventes.client', 'DELETE'); -- another account

has_function_privilege([role,] function, 'EXECUTE') also returns the real right, the one the call checks (on the routine, its schema or at global level); the function is written by its name, possibly qualified and followed by its types ('outils.nb_articles()'), or by its OID. A routine the account cannot see is unknown (42883); a system function (now(), pg_catalog.lower(text)) is executable by everyone.

has_database_privilege, has_sequence_privilege and has_type_privilege always return t: the PostgreSQL database is unique, and sequences are protected by the privileges of their schema (has_schema_privilege).

Roles, for their part, are read as in PostgreSQL. pg_auth_members carries one row per granted role (GRANT role TO account):

SELECT r.rolname AS role, m.rolname AS membre, a.admin_option, a.inherit_option
  FROM pg_auth_members a
  JOIN pg_roles r ON r.oid = a.roleid
  JOIN pg_roles m ON m.oid = a.member
 ORDER BY 1, 2;

pg_has_role([account,] role, privilege) returns true or false; the account (the session's by default) and the role are given by name or OID:

A superuser has all roles. An unknown role returns 42704, an unknown privilege 22023.

39.6 Privilege errors#

Two points to know:

• when an account has no rights on a schema, a query on one of its tables returns 42501 even if the table does not exist, never 42P01, which would reveal its absence;
• in a schema where the account has rights, an unknown table does return 42P01 (relation "inexistante" does not exist).

SET search_path TO archives by an account with no rights on archives is accepted without error, but the current schema does not change; USE archives returns 42501.

An ordinary account sees only its own sessions in SHOW PROCESSLIST and pg_stat_activity, and can interrupt (KILL, pg_cancel_backend, pg_terminate_backend) only those: pg_terminate_backend of another session returns 42501 (permission denied to terminate process), KILL returns You are not owner of thread 1.

39.7 The root account and best practices#

The root@localhost account is created with the data folder, with all privileges and the GRANT option. Its initial password is drawn at random and displayed only once at the server's first start, or given by --initial-root-password (16.5). As its host is localhost, root connects only from the server machine.

SHOW GRANTS;       -- root session

 GRANT ALL PRIVILEGES ON *.* TO "root"@"localhost" WITH GRANT OPTION
 GRANT ROLE_ADMIN,CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,BACKUP_ADMIN,ENCRYPTION_KEY_ADMIN ON *.* TO "root"@"localhost" WITH GRANT OPTION
(2 lignes)

Best practices:

• change the initial root password at the first connection (ALTER USER root@localhost IDENTIFIED BY '…');
• create one account per use (one per application, one per person) rather than sharing root: an account can then be traced, limited and locked without touching the others;
• give the minimum: SELECT on only the schemas consulted for a read account, never a global privilege for an application;
• restrict the host when the application runs on a known machine (app@'10.0.0.%');
• require TLS as soon as the server listens on a non-local address (39.1).

39.8 Unsupported features and differences#

PostgreSQL forms that are not recognized, and what replaces them:

Behavior differences:

• an account is a name + host pair; pg_roles shows only the name (39.2);
• a granted role is active only if it is a default role or after SET ROLE (39.4); pg_auth_members.inherit_option and pg_has_role(…, 'USAGE') tell which (39.5);
• no object owner: pg_tables.tableowner returns root for all tables;
• has_table_privilege, has_schema_privilege, has_column_privilege and has_function_privilege tell the truth, like pg_has_role; an object the account cannot see is unknown to them, and the privileges of another account are reserved to whoever administers accounts (39.5); has_database_privilege, has_sequence_privilege and has_type_privilege always return t;
• denial messages are those of MIRAJ for a column or a routine (… command denied to user …), with SQLSTATE 42501 like the others;
• SHOW CREATE USER and SHOW GRANTS write the account in double quotes ("app"@"%"); the single-quote notation ('app'@'%') is still accepted as input.

See also chapter 15, which describes the same account system in the MIRAJ language.

See also#

• 32. Getting started for the first connection.
• 38. Transactions and session for the current schema and session parameters.
• 40. Routines and triggers (PL/pgSQL) for SECURITY DEFINER.
• 16. Server administration, for the account vault, the initial root password and the TLS certificate.