Index for Section 5

Alphabetical listing for G

Bottom of page

GRANT()
NAME
  GRANT - define access privileges

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

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

  GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE dbname [, ...]
      TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

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

  GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON LANGUAGE langname [, ...]
      TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
      ON SCHEMA schemaname [, ...]
      TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  GRANT { CREATE | ALL [ PRIVILEGES ] }
      ON TABLESPACE tablespacename [, ...]
      TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

  GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]

DESCRIPTION
  The GRANT command has two basic variants: one that grants privileges on a
  database object (table, view, sequence, database, function, procedural
  language, schema, or tablespace), and one that grants membership in a role.
  These variants are similar in many ways, but they are different enough to
  be described separately.

  As of PostgreSQL 8.1, the concepts of users and groups have been unified
  into a single kind of entity called a role.  It is therefore no longer
  necessary to use the keyword GROUP to identify whether a grantee is a user
  or a group. GROUP is still allowed in the command, but it is a noise word.

  GRANT ON DATABASE OBJECTS

  This variant of the GRANT command gives specific privileges on a database
  object to one or more roles. These privileges are added to those already
  granted, if any.

  The key word PUBLIC indicates that the privileges are to be granted to all
  roles, including those that may be created later. PUBLIC may be thought of
  as an implicitly defined group that always includes all roles.  Any
  particular role will have the sum of privileges granted directly to it,
  privileges granted to any role it is presently a member of, and privileges
  granted to PUBLIC.

  If WITH GRANT OPTION is specified, the recipient of the privilege may in
  turn grant it to others. Without a grant option, the recipient cannot do
  that. Grant options cannot be granted to PUBLIC.

  There is no 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 could, however, choose to revoke some of his own privileges for
  safety.) The right to drop an object, or to alter its definition in any way
  is not described by a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. The owner implicitly has all grant options
  for the object, too.

  Depending on the type of object, the initial default privileges may include
  granting some privileges to PUBLIC.  The default is no public access for
  tables, schemas, and tablespaces; CONNECT privilege and TEMP table creation
  privilege for databases; EXECUTE privilege for functions; and USAGE
  privilege for languages.  The object owner may of course revoke these
  privileges. (For maximum security, issue the REVOKE in the same transaction
  that creates the object; then there is no window in which another user may
  use the object.)

  The possible privileges are:

  SELECT
       Allows SELECT [select(5)] from any column of the specified table,
       view, or sequence. Also allows the use of COPY [copy(5)] TO. For
       sequences, this privilege also allows the use of the currval function.

  INSERT
       Allows INSERT [insert(5)] of a new row into the specified table. Also
       allows COPY [copy(5)] FROM.

  UPDATE
       Allows UPDATE [update(5)] of any column of the specified table. SELECT
       ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege
       (besides the SELECT privilege). For sequences, this privilege allows
       the use of the nextval and setval functions.

  DELETE
       Allows DELETE [delete(5)] of a row from the specified table.

  REFERENCES
       To create a foreign key constraint, it is necessary to have this
       privilege on both the referencing and referenced tables.

  TRIGGER
       Allows the creation of a trigger on the specified table. (See the
       CREATE TRIGGER [create_trigger(5)] statement.)

  CREATE
       For databases, allows new schemas to be created 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 and indexes 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 placement of existing objects.)

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

  TEMPORARY

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

  EXECUTE
       Allows the use of the specified function and the use of any operators
       that are implemented on top of the function. This is the only type of
       privilege that is applicable to functions.  (This syntax works for
       aggregate functions, as well.)

  USAGE
       For procedural languages, allows the use of the specified 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 specified
       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 the system tables.  Also, after
       revoking this permission, existing backends might have statements that
       have previously performed this lookup, so this is not a completely
       secure way to prevent object access.

       For sequences, this privilege allows the use of the currval and
       nextval functions.

  ALL PRIVILEGES
       Grant all of the available privileges at once.  The PRIVILEGES key
       word is optional in PostgreSQL, though it is required by strict SQL.

  The privileges required by other commands are listed on the reference page
  of the respective command.

  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 WITH ADMIN OPTION is specified, the member may in turn grant membership
  in the role to others, and revoke membership in the role as well. Without
  the admin option, ordinary users cannot do that. However, database
  superusers can grant or revoke membership in any role to anyone.  Roles
  having CREATEROLE privilege can grant or revoke membership in any role that
  is not a superuser.

  Unlike the case with privileges, membership in a role cannot be granted to
  PUBLIC. Note also that this form of the command does not allow the noise
  word GROUP.

NOTES
  The REVOKE [revoke(5)] command is used to revoke access privileges.

  When a non-owner of an object attempts to GRANT privileges on the object,
  the command will fail outright if the user has no privileges whatsoever on
  the object. As long as some privilege is available, the command will
  proceed, but it will grant only those privileges for which the user has
  grant options. The GRANT ALL PRIVILEGES forms will issue a warning message
  if no grant options are held, while the other forms will issue a warning if
  grant options for any of the privileges specifically named in the command
  are not held.	 (In principle these statements apply to the object owner as
  well, but since the owner is always treated as holding all grant options,
  the cases can never occur.)

  It should be noted that 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, it's unwise to operate as a
  superuser except when absolutely necessary.

  If a superuser chooses to issue 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 via such a command will appear to have been
  granted by the object owner.	(For role membership, the membership appears
  to have been granted by the containing role itself.)

  GRANT and REVOKE can also be done by a role that is not the owner of the
  affected object, but is a member of the role that owns the object, or is a
  member of a role that holds privileges WITH GRANT OPTION on the object. In
  this case the privileges will be recorded as having been granted by the
  role that actually 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, but those privileges will
  appear to have been granted directly by g1. Any other member of role g1
  could revoke them later.

  If the role executing GRANT holds the required privileges indirectly via
  more than one role membership path, it is unspecified which containing role
  will be recorded as having done the grant. In such cases it is best
  practice to use SET ROLE to become the specific role you want to do the
  GRANT as.

  Granting permission on a table does not automatically extend permissions to
  any sequences used by the table, including sequences tied to SERIAL
  columns. Permissions on sequence must be set separately.

  Currently, PostgreSQL does not support granting or revoking privileges for
  individual columns of a table.  One possible workaround is to create a view
  having just the desired columns and then grant privileges to that view.

  Use psql(1)'s \z command to obtain information about existing privileges,
  for example:

  => \z mytable

			  Access privileges for database "lusitania"
   Schema |  Name   | Type  |			  Access privileges
  --------+---------+-------+-----------------------------------------------------------
   public | mytable | table | {miriam=arwdxt/miriam,=r/miriam,"group todos=arw/miriam"}
  (1 row)

  The entries shown by \z are interpreted thus:

		=xxxx -- privileges granted to PUBLIC
	   uname=xxxx -- privileges granted to a user
     group gname=xxxx -- privileges granted to a group

		    r -- SELECT ("read")
		    w -- UPDATE ("write")
		    a -- INSERT ("append")
		    d -- DELETE
		    x -- REFERENCES
		    t -- TRIGGER
		    X -- EXECUTE
		    U -- USAGE
		    C -- CREATE
		    c -- CONNECT
		    T -- TEMPORARY
	       arwdxt -- ALL PRIVILEGES (for tables)
		    * -- grant option for preceding privilege

		/yyyy -- user who granted this privilege

  The above example display would be seen by user miriam after creating table
  mytable and doing

  GRANT SELECT ON mytable TO PUBLIC;
  GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;

  If the ``Access privileges'' column is empty for a given object, it means
  the object has default privileges (that is, its privileges column is null).
  Default privileges always include all privileges for the owner, and may
  include some privileges for PUBLIC depending on the object type, as
  explained above. The first GRANT or REVOKE on an object will instantiate
  the default privileges (producing, for example, {miriam=arwdxt/miriam}) and
  then modify them per the specified request.

  Notice that the owner's implicit grant options are not marked in the access
  privileges display. A * will appear only when grant options have been
  explicitly granted to someone.

EXAMPLES
  Grant insert privilege to all users on table films:

  GRANT INSERT ON films TO PUBLIC;

  Grant all available privileges to user manuel on view kinds:

  GRANT ALL PRIVILEGES ON kinds TO manuel;

  Note that while the above will indeed grant all privileges if executed by a
  superuser or the owner of kinds, when executed by someone else it will only
  grant those permissions for which the someone else has grant options.

  Grant membership in role admins to user joe:

  GRANT admins TO joe;

COMPATIBILITY
  According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is
  required. The SQL standard does not support setting the privileges on more
  than one object per command.

  PostgreSQL allows an object owner to revoke his own ordinary privileges:
  for example, a table owner can make the table read-only to himself by
  revoking his own INSERT, UPDATE, and DELETE privileges. This is not
  possible according to the SQL standard. The reason is that PostgreSQL
  treats the owner's privileges as having been granted by the owner to
  himself; therefore he can revoke them too. In the SQL standard, the owner's
  privileges are granted by an assumed entity ``_SYSTEM''. Not being
  ``_SYSTEM'', the owner cannot revoke these rights.

  The SQL standard allows setting privileges for individual columns within a
  table:

  GRANT privileges
      ON table [ ( column [, ...] ) ] [, ...]
      TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]

  The SQL standard provides for a USAGE privilege on other kinds of objects:
  character sets, collations, translations, domains.

  Privileges on databases, tablespaces, schemas, and languages are PostgreSQL
  extensions.

SEE ALSO
  REVOKE [revoke(5)]

Index for Section 5

Alphabetical listing for G

Top of page