GRANT v17

Suggest edits

Name

GRANT — Define access privileges.

Synopsis

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES }
  [,...] | ALL [ PRIVILEGES ] }
  ON tablename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { { INSERT | UPDATE | REFERENCES } (column [, ...]) }
  [, ...]
  ON tablename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { { CREATE | DROP | READ | WRITE } }
  ON DIRECTORY dirname 
  TO { username } 
  [ WITH GRANT OPTION ]

GRANT { SELECT | ALL [ PRIVILEGES ] }
  ON sequencename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON FUNCTION progname
    ( [ [ argmode ] [ argname ] argtype ] [, ...] )
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON PROCEDURE progname
    [ ( [ [ argmode ] [ argname ] argtype ] [, ...] ) ]
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
  ON PACKAGE packagename
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH GRANT OPTION ]

GRANT role [, ...]
  TO { username | groupname | PUBLIC } [, ...]
  [ WITH ADMIN OPTION ]

GRANT { CONNECT | RESOURCE | DBA } [, ...]
  TO { username | groupname } [, ...]
  [ WITH ADMIN OPTION ]

GRANT CREATE [ PUBLIC ] DATABASE LINK
  TO { username | groupname }

GRANT DROP PUBLIC DATABASE LINK
  TO { username | groupname }

GRANT EXEMPT ACCESS POLICY
  TO { username | groupname }

Description

The GRANT command has the following basic variants:

  • One that grants privileges on a database object (table, view, sequence, or program)
  • One that grants privileges on a directory
  • 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 GRANT command.

GRANT on database objects

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.

The keyword 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 PUBLIC.

If you specify the WITH GRANT OPTION, the recipient of the privilege can grant it to others. Grant options aren't granted to PUBLIC.

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.

Note

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

Allows SELECT from any column of the specified table, view, or sequence. For sequences, this privilege also allows the use of the currval function.

INSERT

Allows you to insert a new row into the specified table.

UPDATE

Allows UPDATE of a column of the specified table. SELECT ... FOR UPDATE also requires this privilege in addition to the SELECT privilege.

DELETE

Allows you to delete a row from the specified table.

REFERENCES

To create a foreign key constraint, you need this privilege on both the referencing and referenced tables.

EXECUTE

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 FUNCTION, PROCEDURE, or PACKAGE. You must omit these keywords in Oracle.

For functions, EDB Postgres Advanced Server requires all input (IN, 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.

ALL PRIVILEGES

Grants all of the available privileges at once.

GRANT on directories

This variant of the GRANT command gives specific privileges on a directory to a user. The possible privileges are:

CREATE

Allows the user to create files in a directory.

DROP

Allows the user to remove files in a directory.

READ

Allows the user to open files in read mode.

WRITE

Allows the user to create or open a file in read or append mode.

GRANT on roles

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

Granting the CONNECT role is equivalent to giving the grantee the LOGIN privilege. The grantor must have the CREATEROLE privilege.

RESOURCE

Granting the RESOURCE role is equivalent to granting the CREATE and 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 CREATE or USAGE privileges on this schema to the grantee.

DBA

Granting the DBA role is equivalent to making the grantee a superuser. The grantor must be a superuser.

Notes

Use the 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 GRANT or 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.)

GRANT and 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 OPTION on 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 t1 to 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 GRANT as.

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.

Examples

Grant insert privilege to all users on table emp:

GRANT INSERT ON emp TO PUBLIC;

Grant all available privileges to user mary on view salesemp:

Grant write privileges to user betty on directory test1. 

GRANT WRITE ON test1 FROM betty;
GRANT ALL PRIVILEGES ON salesemp TO mary;

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 joe:

GRANT admins TO joe;

Grant CONNECT privilege to user joe:

GRANT CONNECT TO joe;

See also

REVOKE, SET ROLE

GRANT on system privileges

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

The 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

The 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

The 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.

The 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:

GRANT EXEMPT ACCESS POLICY TO enterprisedb;
GRANT enterprisedb TO joe;

The rolpolicyexempt column of the system catalog table pg_authid is set to true if a role has the EXEMPT ACCESS POLICY privilege.

Examples

Grant CREATE PUBLIC DATABASE LINK privilege to user joe:

GRANT CREATE PUBLIC DATABASE LINK TO joe;

Grant DROP PUBLIC DATABASE LINK privilege to user joe:

GRANT DROP PUBLIC DATABASE LINK TO joe;

Grant the EXEMPT ACCESS POLICY privilege to user joe:

GRANT EXEMPT ACCESS POLICY TO joe;

Using the ALTER ROLE command to assign system privileges

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.
  • The EXEMPT ACCESS POLICY privilege.

The ALTER ROLE syntax is equivalent to the respective commands compatible with Oracle databases.

See also

REVOKE, ALTER ROLE

← Prev

EXEC

↑ Up

Compatible SQL commands

Next →

INSERT