Roles

Ownership

Ownership answers who controls and can drop the object.

Privileges

Privileges defines who may read or modify the object.

Database Objects

A POPO (Plain Ole PostgreSQL Object) or Database Object for short, is any named, persistent entity on which the database system stores, manages and enforces ownwership and privileges. These POPO's fall into several categories:

1. Structural Objects
2. Programmable Objects
3. Security and Access Control Objects
4. Dependency and Integration Objects

Structural Objects

These objects define how data is organized and stored. These objects include databases themselves, schemas, tables, partioned tables, views, and materialized views, indexes, sequences and foreign tables.

Programmable Objects

These objects encapsulate logic that runs inside the database engine itself. These objects include functions, procedures, aggregates, operators, operator classes, triggers, and trigger functions. These objects are owned by a role and execute with defined security and execution semantics.

Security and Access Control Objects

Third, security and access-control objects manage authentication, authorization, and access paths. Roles (both login and group roles), privileges, default privileges, and row-level security (RLS) policies fall into this category. While roles themselves are objects, privileges are properties attached to other objects.

Dependency and Integration Objects

Fourth, dependency and integration objects support data relationships and extensions. Examples include constraints (primary keys, foreign keys, check constraints), collations, conversions, text search configurations, dictionaries, parsers, templates, extensions, and event triggers.

Ownership

When a db object is created, it is assigned an owner. The owner is the Role that executed the creation statement. At the initial creation of the object, no one can do anything with the object except the Role that created the object and the Superuser. In order to allow other roles to use the db object, privileges must be granted. Almost every database object has an owner, which is a role. The owner has implicit full control over the object. If a role owns any object, PostgreSQL will not allow that role to be dropped until ownership is transferred or the object is removed.

Privileges

Objects can depend on one another and have privileges granted to roles. Even if a role does not own an object, having privileges or default privileges associated with it can still block certain administrative actions (for example, dropping a role).

Privilege Examples

• SELECT

  • Allows SELECT from any column, or specific column(s), of a table, view, materialized view, or other table-like object. Also allows use of COPY TO. This privilege is also needed to reference existing column values in UPDATE, DELETE, or MERGE. For sequences, this privilege also allows use of the currval function. For large objects, this privilege allows the object to be read.

• INSERT

  • Allows INSERT of a new row into a table, view, etc. Can be granted on specific column(s), in which case only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). Also allows use of COPY FROM.

• UPDATE

  • Allows UPDATE of any column, or specific column(s), of a table, view, etc. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege. For sequences, this privilege allows use of the nextval and setval functions. For large objects, this privilege allows writing or truncating the object.

• DELETE

  • Allows DELETE of a row from a table, view, etc. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)

• TRUNCATE

  • Allows TRUNCATE on a table.

• REFERENCES

  • Allows creation of a foreign key constraint referencing a table, or specific column(s) of a table.

• TRIGGER

  • Allows creation of a trigger on a table, view, etc.

• CREATE

  • For databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database. For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema. For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. Note that revoking this privilege will not alter the existence or location of existing objects.

• CONNECT

  • Allows the grantee to connect to the database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).

• TEMPORARY

  • Allows temporary tables to be created while using the database.

• EXECUTE

  • Allows calling a function or procedure, including use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions and procedures.

• USAGE

  • For procedural languages, allows use of the language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.
  • For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema. Without this permission, it is still possible to see the object names, e.g., by querying system catalogs. Also, after revoking this permission, existing sessions might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.
  • For sequences, allows use of the currval and nextval functions.
  • For types and domains, allows use of the type or domain in the creation of tables, functions, and other schema objects. (Note that this privilege does not control all “usage” of the type, such as values of the type appearing in queries. It only prevents objects from being created that depend on the type. The main purpose of this privilege is controlling which users can create dependencies on a type, which could prevent the owner from changing the type later.)
  • For foreign-data wrappers, allows creation of new servers using the foreign-data wrapper.
  • For foreign servers, allows creation of foreign tables using the server. Grantees may also create, alter, or drop their own user mappings associated with that server.

• SET

  • Allows a server configuration parameter to be set to a new value within the current session. (While this privilege can be granted on any parameter, it is meaningless except for parameters that would normally require superuser privilege to set.)

• ALTER SYSTEM

  • Allows a server configuration parameter to be configured to a new value using the ALTER SYSTEM command.

• MAINTAIN

  • Allows VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, LOCK TABLE, and database object statistics manipulation functions on a relation.

Postgres Roles

PostgreSQL manages access to the database by assigning permissions using Roles. A Role is either a database user, group of users or both. Roles can own database objects and can assign privileges on those objects to other roles to control who has access to which objects. PostgreSQL also lets you grant membership in a role to another role allowing the DBA to define whatever measure of granularity he/she wishes.

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
MAINTAIN	m	TABLE

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	\dl+
PARAMETER	sA	none	\dconfig+
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxtm	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

The privileges applicable to a particular object vary depending on the object's type
--- PostgreSQL Docs

The right to modify or destroy an object is inherent in being the object's owner and cannot be granted or revoked in itself, however it can be inherited by members of the owning role.