GRANT — Define access privileges.
GRANT command has three basic variants:
- One that grants privileges on a database object (table, view, sequence, or program)
- One that grants membership in a role
- One that grants system privileges
In EDB Postgres Advanced Server, the concept of users and groups was unified into a single type of entity called a role. In this context, a user is a role that has the
LOGIN attribute. The role can be used to create a session and connect to an application. A group is a role that doesn't have the
LOGIN attribute. You can't use the role to create a session or connect to an application.
A role can be a member of one or more other roles, so the traditional concept of users belonging to groups is still valid. However, with the generalization of users and groups, users can “belong” to users, groups can “belong” to groups, and groups can “belong” to users, forming a general multi-level hierarchy of roles. User names and group names share the same namespace. Therefore, you don't need to specify whether a grantee is a user or a group in the
This variant of the
GRANT command gives specific privileges on a database object to a role. These privileges are added to those already granted, if any.
PUBLIC grants the privileges to all roles, including those that you create later.
PUBLIC is an implicitly defined group that always includes all roles. Any role has the sum of privileges granted directly to it, privileges granted to any role it is a member of, and privileges granted to
If you specify the
WITH GRANT OPTION, the recipient of the privilege can grant it to others. Grant options aren't granted to
You don't need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. The owner can, however, revoke some of their own privileges for safety. The right to drop an object or to alter its definition isn't described by a grantable privilege. It's inherent in the owner and can't be granted or revoked. The owner implicitly has all grant options for the object as well.
Depending on the type of object, the initial default privileges can include granting some privileges to
PUBLIC. The default is no public access for tables and
EXECUTE privilege for functions, procedures, and packages. The object owner can revoke these privileges.
For maximum security, issue the
REVOKE in the same transaction that creates the object. This approach prevents a window from occurring in which another user can use the object.
The possible privileges are:
SELECT from any column of the specified table, view, or sequence. For sequences, this privilege also allows the use of the
Allows you to insert a new row into the specified table.
UPDATE of a column of the specified table.
SELECT ... FOR UPDATE also requires this privilege in addition to the
Allows you to delete a row from the specified table.
To create a foreign key constraint, you need this privilege on both the referencing and referenced tables.
Allows the use of the specified package, procedure, or function. When applied to a package, allows the use of all of the package’s public procedures, public functions, public variables, records, cursors, and other public objects and object types. This is the only type of privilege that applies to functions, procedures, and packages.
The EDB Postgres Advanced Server syntax for granting the
EXECUTE privilege isn't fully compatible with Oracle databases. EDB Postgres Advanced Server requires that you qualify the program name with one of the keywords
PACKAGE. You must omit these keywords in Oracle.
For functions, EDB Postgres Advanced Server requires all input (
IN OUT) argument data types after the function name, including an empty parenthesis if there are no function arguments. For procedures, you must specify all input argument data types if the procedure has any input arguments. In Oracle, omit function and procedure signatures. All programs share the same namespace in Oracle, whereas functions, procedures, and packages have their own namespaces in EDB Postgres Advanced Server to allow program name overloading to a certain extent.
Grants all of the available privileges at once.
This variant of the
GRANT command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members.
If you specify the
WITH ADMIN OPTION, the member can grant membership in the role to others and revoke membership in the role.
Database superusers can grant or revoke membership in any role to anyone. Roles having the
CREATEROLE privilege can grant or revoke membership in any role that isn't a superuser.
There are three predefined roles.
CONNECT role is equivalent to giving the grantee the
LOGIN privilege. The grantor must have the
RESOURCE role is equivalent to granting the
USAGE privileges on a schema that has the same name as the grantee. This schema must exist before you give the grant. The grantor must have the privilege to grant
USAGE privileges on this schema to the grantee.
DBA role is equivalent to making the grantee a superuser. The grantor must be a superuser.
REVOKE command to revoke access privileges.
When a non-owner of an object attempts to grant privileges on the object, the command fails if the user has no privileges on the object. As long as a privilege is available, the command proceeds, but it grants only those privileges for which the user has grant options. The
GRANT ALL PRIVILEGES forms issue a warning if no grant options are held, while the other forms issue a warning if grant options for any of the privileges named in the command aren't held. In principle, these statements apply to the object owner as well. However, since the owner is always treated as holding all grant options, the cases can never occur.
Database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of
root in a Unix system. As with
root, only operate as a superuser when you have to.
If a superuser issues a
REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted by such a command appear as if granted by the object owner. (For role membership, the membership appears as if granted by the containing role.)
REVOKE can also be done by:
- A role that isn't the owner of the affected object but is a member of the role that owns the object.
- A role that is a member of a role that holds privileges
WITH GRANT OPTIONon the object. In this case, the privileges are recorded as having been granted by the role that owns the object or holds the privileges
WITH GRANT OPTION.
For example, if table
t1 is owned by role
g1, of which role
u1 is a member, then
u1 can grant privileges on
u2. However, those privileges appear as if granted directly by
g1. Any other member of role
g1 can revoke them later.
If the role executing
GRANT holds the required privileges indirectly by way of more than one role membership path, the containing role recorded as having done the grant is unspecified. In such cases, best practice is to use
SET ROLE to become the specific role you want to do the
Currently, EDB Postgres Advanced Server doesn't support granting or revoking privileges for individual columns of a table. One workaround is to create a view having just the desired columns and then grant privileges to that view.
Grant insert privilege to all users on table
Grant all available privileges to user
mary on view
While this example does grant all privileges if executed by a superuser or the owner of
emp, when executed by someone else it grants only those permissions for which that user has grant options.
Grant membership in role
admins to user
CONNECT privilege to user
This variant of the
GRANT command gives a role the ability to perform certain system operations in a database. System privileges relate to the ability to create or delete certain database objects that aren't necessarily in the confines of one schema. Only database superusers can grant system privileges.
CREATE [PUBLIC] DATABASE LINK
CREATE [PUBLIC] DATABASE LINK privilege allows the specified role to create a database link. Include the
PUBLIC keyword to allow the role to create public database links. Omit the
PUBLIC keyword to allow the specified role to create private database links.
DROP PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK privilege allows a role to drop a public database link. You don't need system privileges to drop a private database link. The link owner or a database superuser can drop a private database link.
EXEMPT ACCESS POLICY
EXEMPT ACCESS POLICY privilege allows a role to execute a SQL command without invoking any policy function that's associated with the target database object. The role is exempt from all security policies in the database.
EXEMPT ACCESS POLICY privilege can't be inherited by membership to a role that has the
EXEMPT ACCESS POLICY privilege. For example, the following sequence of
GRANT commands doesn't result in user
joe obtaining the
EXEMPT ACCESS POLICY privilege. This is true even though
joe is granted membership to the
enterprisedb role, which was granted the
EXEMPT ACCESS POLICY privilege:
rolpolicyexempt column of the system catalog table
pg_authid is set to
true if a role has the
EXEMPT ACCESS POLICY privilege.
CREATE PUBLIC DATABASE LINK privilege to user
DROP PUBLIC DATABASE LINK privilege to user
EXEMPT ACCESS POLICY privilege to user
The EDB Postgres Advanced Server
ALTER ROLE command also supports syntax that you can use to assign:
- The privilege required to create a public or private database link.
- The privilege required to drop a public database link.
EXEMPT ACCESS POLICYprivilege.
ALTER ROLE syntax is equivalent to the respective commands compatible with Oracle databases.