Managing privileges in PostgreSQL with grant and revoke

Introduction

Controlling what each user is allowed to do within your database is an important part of administrating a database cluster. PostgreSQL provides a suite of tools to control authentication and authorization.

On the authentication front, the pg_hba.conf file controls how people can connect to the database. This includes the exact user, database, connection method, and authentication method combinations that are allowed access to the server. The authorization component starts with PostgreSQL's concept of roles and role attributes which defines the user entities within the system and controls their global privileges.

A further level of authorization is present beyond those offered by role attributes. Managing ownership and grants on specific database objects is the primary way to control which roles can manage, modify, and view databases, tables, sequences, and more. This guide will cover how to use PostgreSQL's grant and revocation mechanisms to lay out exactly what roles have access to each database object.

What are PostgreSQL object privileges?

In the article on roles and role attributes, the concept of defining system-wide privileges for roles was introduced. Using role attributes, administrators can define whether roles can create or modify databases, manage roles, or even login to the system itself. These types of privileges are hold true across the entire database cluster, so they offer no granularity when it comes to controlling access to individual objects within the database.

Instead, PostgreSQL uses a complimentary system of individual grants or rights on specific database entities. This allows the owners of database objects to determine what types of actions are permitted by which roles. This additional flexibility and granularity is what makes multi-user and multitenant deployments both possible and practical.

Database grants or privileges define the specific set of operations or levels of access available for various authenticated roles. PostgreSQL's grant system follows an "allow list" model, meaning that roles are given no access to database objects except for those explicitly granted.

How do object ownership and role membership affect object privileges?

Fundamental to this system are the concepts of object ownership and role membership. In PostgreSQL, every database object has exactly one owner who alone, along with superuser roles, has the unique ability to alter, delete, and manage the object itself. The object owner manages the privileges on the object for other roles by granting privileges. Every privilege granted on a database object can ultimately be controlled by the object owner.

Role membership is a system that gives roles the privileges of the roles they are members of. Roles with the INHERIT attribute that are members of other roles gain access to their privileges automatically, without having to use SET ROLE to change the current role.

For example, imagine a salesadmin role with the ability to modify data within the sales database. If the sally role has the INHERIT role attribute set, then adding sally to the salesadmin role will automatically enable sally to modify data in the sales database. The ability to inherit characteristics enables a flexible style of access management by grouping attributes and access by "functional" roles and then adding actual "user" roles to those functional roles as required.

Every role on the system is a member of the PUBLIC role by default. This makes it synonymous with "everyone" when defining privileges.

An overview of available object privileges

PostgreSQL has a number of privileges that can be given to roles to enable specific functionality.

Privileges are applicable only on a subset of database objects where they make sense. For instance, it would not make sense to "execute" a database. For guidance on which privileges are valid with which database objects, refer to the ACL Privileges Abbreviations table and the Summary of Access Privileges table from the PostgreSQL documentation.

Below is a list of each privilege name and its function. You can find a full explanation of each privilege in the PostgreSQL documentation.

• SELECT: The SELECT privilege gives a role the ability to select, or read, from a database object. This privilege is also necessary for any UPDATE and DELETE operations that reference existing column values.
• INSERT: Provides the ability to add a new row of data to a table, view, or column.
• UPDATE: Gives the ability to update the values stored for columns in a database object. The SELECT privilege will also be required for most UPDATE operations.
• DELETE: Lets a role delete a row from a table or view. Like UPDATE, most DELETE operations require the SELECT privilege as well in order to target the correct rows.
• TRUNCATE: The TRUNCATE privilege allows a role empty a table or view of all data.
• REFERENCES: Provides roles with the ability to create foreign keys referencing a table or table column.
• TRIGGER: Lets a role define a trigger on a table or view.
• CREATE: Allows roles to create child entities of databases, schemas, or tablespaces. For example, on databases the CREATE privilege allows the role to create new schemas, while on schemas it allows the role to create new databases.
• CONNECT: Lets the role connect to a database. This is checked at connection time.
• TEMPORARY: Allows the role to create temporary tables within a database.
• EXECUTE: Gives the role permission to call functions or procedures.
• USAGE: Allows roles basic functionality on objects. For instance, USAGE on schemas allows the role look up objects within it, while USAGE on sequences allows the role to call the currval and nextval functions.
• ALL PRIVILEGES: A shorthand giving the role in question all privileges on the specified object.

Using the GRANT command

The GRANT command is used for two separate but related purposes in role management:

• Granting specific privileges on database objects to a role
• Adding roles as members of other roles

These two functions are reflected in the two structures found in the command syntax.

To grant privileges on a specific database object to a given role, use following form:

GRANT <privilege> ON <database_object> TO <role> [WITH GRANT OPTION];

The optional WITH GRANT OPTION clause additionally gives the receiving role the ability to pass on this capability to other roles. For instance, if adam is granted the ability to DELETE data from customers with the WITH GRANT OPTION, he can, in turn, optionally grant that capability to delores. Practically speaking, this gives you the ability to let roles administer certain capabilities on specific objects.

The other syntax, used to add a role to another role, looks like this. In this context, the <role_member> is given the privileges of the <provider_role>:

GRANT <provider_role> TO <role_member> [WITH ADMIN OPTION];

If the <role_member> has the INHERIT attribute set, it will immediately have access to the privileges of the <provider_role>. If this attribute is missing, the <role_member> can access the privileges of the <provider_role> by changing the current role with SET ROLE.

Similar to the WITH GRANT OPTION clause in the other syntax, when granting membership to a role, you can optionally add a WITH ADMIN OPTION clause. This clause additionally gives the role member the ability to add new members.

GRANT SELECT,INSERT,UPDATE,DELETE ON "books" TO "sam";

GRANT SELECT,INSERT,UPDATE,DELETE ON "books" TO "sam" WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON "customers" TO "jasmine";

GRANT EXECUTE ON FUNCTION order_count(int) TO "keisha";

GRANT "admin" TO "calin";

GRANT "salesperson" TO "sofie" WITH ADMIN OPTION;

Using the REVOKE command

The REVOKE command revokes privileges from roles on a database object. For the most part, it mirrors the syntax of the GRANT command, with two formats to cover the two use cases.

To revoke a specific privilege on a database object from a given role, use the following format:

REVOKE [GRANT OPTION FOR] <privilege> ON <database_object> FROM <role> [CASCADE | RESTRICT];

In this case, the optional GRANT OPTION FOR clause can be added to remove the ability for the specified role to pass on the given privilege. A conflict can occur when attempting to revoke the GRANT OPTION from a role who has already passed on the privilege to another role. In that case, removing the GRANT OPTION from the first role would break the chain of grants that gives the secondary role their privileges.

For example, if ada has granted the ability to update content on a records database to pete using WITH GRANT OPTION, pete could then give the ability to update content to simone. If ada revokes the GRANT OPTION from pete, it is unclear what should happen to the update privilege passed on to simone.

The optional CASCADE or RESTRICT clauses resolve this conflict by specifying explicitly what REVOKE should do in this scenario. The default behavior is RESTRICT, which will cause the REVOKE GRANT OPTION FOR command to fail if a privilege has been passed on to another role. The CASCADE option changes this behavior to revoke the privilege from any "downstream" roles in addition to the specified role, resolving the conflict by removing the broken chain.

The other syntax, used to revoke role membership, looks like this:

REVOKE <provider_role> FROM <member_role>;

In this case, the <member_role> will no longer have the privileges given to the <provider_role> unless granted through other avenues.

REVOKE DELETE ON "logs" FROM "eddy";

REVOKE ALL PRIVILEGES ON "finances" FROM "jerry";

REVOKE GRANT OPTION FOR DELETE ON "snacks" FROM "alice";

REVOKE "moderators" FROM "natasha";

REVOKE ADMIN OPTION FOR "hr" FROM "tony";

Conclusion

PostgreSQL's grant and privilege system allows you to define granular privileges to individual roles on specific database objects. The grant system extends PostgreSQL's authorization controls down to individual objects that can be managed by their owners.

This arrangement allows individual users to exercise control over their own database objects. They can grant and revoke access, as well as delegate certain management functions to other roles. In addition, the same arrangement is used to implement role membership in order to simplify privilege management.