Managing privileges in PostgreSQL with grant and revoke
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.
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.
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.
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.
SELECTprivilege gives a role the ability to select, or read, from a database object. This privilege is also necessary for any
DELETEoperations 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
SELECTprivilege will also be required for most
DELETE: Lets a role delete a row from a table or view. Like
DELETEoperations require the
SELECTprivilege as well in order to target the correct rows.
TRUNCATEprivilege 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
CREATEprivilege 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,
USAGEon schemas allows the role look up objects within it, while
USAGEon sequences allows the role to call the
ALL PRIVILEGES: A shorthand giving the role in question all privileges on the specified object.
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];
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
GRANT <provider_role> TO <role_member> [WITH ADMIN OPTION];
<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
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.
sam CRUD privileges on the
GRANT SELECT,INSERT,UPDATE,DELETE ON "books" TO "sam";
sam to delegate CRUD behavior on the
GRANT SELECT,INSERT,UPDATE,DELETE ON "books" TO "sam" WITH GRANT OPTION;
jasmine have full privileges on the
GRANT ALL PRIVILEGES ON "customers" TO "jasmine";
keisha to execute execute the
GRANT EXECUTE ON FUNCTION order_count(int) TO "keisha";
calin to the
GRANT "admin" TO "calin";
sofie to the
salesperson role and allow her to manage membership:
GRANT "salesperson" TO "sofie" WITH ADMIN OPTION;
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
WITH GRANT OPTION,
pete could then give the ability to update content to
ada revokes the
GRANT OPTION from
pete, it is unclear what should happen to the update privilege passed on to
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 the ability for
eddy to remove lines from the
REVOKE DELETE ON "logs" FROM "eddy";
Remove all access to
REVOKE ALL PRIVILEGES ON "finances" FROM "jerry";
Remove the ability for
alice to grant the
DELETE privilege to other roles on the
snacks table. Keep in mind that
alice will still have the
DELETE privilege following this command, but will no longer be able to pass that privilege on:
REVOKE GRANT OPTION FOR DELETE ON "snacks" FROM "alice";
natasha from the
REVOKE "moderators" FROM "natasha";
Revoke the ability to administer membership for the
hr role from
tony. Keep in mind that
tony will still be a member of
REVOKE ADMIN OPTION FOR "hr" FROM "tony";
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.