| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.62 2006/09/16 00:30:18 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-GRANT"> |
| <refmeta> |
| <refentrytitle id="sql-grant-title">GRANT</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>GRANT</refname> |
| <refpurpose>define access privileges</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-grant"> |
| <primary>GRANT</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } |
| [,...] | ALL [ PRIVILEGES ] } |
| ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { { USAGE | SELECT | UPDATE } |
| [,...] | ALL [ PRIVILEGES ] } |
| ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } |
| ON DATABASE <replaceable>dbname</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { EXECUTE | ALL [ PRIVILEGES ] } |
| ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { USAGE | ALL [ PRIVILEGES ] } |
| ON LANGUAGE <replaceable>langname</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } |
| ON SCHEMA <replaceable>schemaname</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT { CREATE | ALL [ PRIVILEGES ] } |
| ON TABLESPACE <replaceable>tablespacename</replaceable> [, ...] |
| TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| |
| GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable class="PARAMETER">username</replaceable> [, ...] [ WITH ADMIN OPTION ] |
| |
| GRANT { SELECT | INSERT | ALL [PRIVILEGES] } |
| ON PROTOCOL <replaceable>protocolname</replaceable> |
| TO <replaceable class="PARAMETER">username</replaceable> |
| |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1 id="sql-grant-description"> |
| <title>Description</title> |
| |
| <para> |
| The <command>GRANT</command> 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. |
| </para> |
| |
| <para> |
| As of <productname>PostgreSQL</productname> 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 <literal>GROUP</literal> |
| to identify whether a grantee is a user or a group. <literal>GROUP</literal> |
| is still allowed in the command, but it is a noise word. |
| </para> |
| |
| <refsect2 id="sql-grant-description-objects"> |
| <title>GRANT on Database Objects</title> |
| |
| <para> |
| This variant of the <command>GRANT</command> command gives specific |
| privileges on a database object to |
| one or more roles. These privileges are added |
| to those already granted, if any. |
| </para> |
| |
| <para> |
| The key word <literal>PUBLIC</literal> indicates that the |
| privileges are to be granted to all roles, including those that may |
| be created later. <literal>PUBLIC</literal> 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 |
| <literal>PUBLIC</literal>. |
| </para> |
| |
| <para> |
| If <literal>WITH GRANT OPTION</literal> 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 <literal>PUBLIC</literal>. |
| </para> |
| |
| <para> |
| 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. |
| </para> |
| |
| <para> |
| Depending on the type of object, the initial default privileges may |
| include granting some privileges to <literal>PUBLIC</literal>. |
| The default is no public access for tables, schemas, and tablespaces; |
| <literal>CONNECT</literal> privilege and <literal>TEMP</literal> table creation privilege |
| for databases; |
| <literal>EXECUTE</literal> privilege for functions; and |
| <literal>USAGE</literal> privilege for languages. |
| The object owner may of course revoke these privileges. (For maximum |
| security, issue the <command>REVOKE</command> in the same transaction that |
| creates the object; then there is no window in which another user |
| may use the object.) |
| </para> |
| |
| <para> |
| The possible privileges are: |
| |
| <variablelist> |
| <varlistentry> |
| <term>SELECT</term> |
| <listitem> |
| <para> |
| Allows <xref linkend="sql-select" endterm="sql-select-title"> from |
| any column of the specified table, view, or sequence. |
| Also allows the use of |
| <xref linkend="sql-copy" endterm="sql-copy-title"> TO. |
| This privilege is also needed to reference existing column values in |
| <xref linkend="sql-update" endterm="sql-update-title"> or |
| <xref linkend="sql-delete" endterm="sql-delete-title">. |
| For sequences, this privilege also allows the use of the |
| <function>currval</function> function. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>INSERT</term> |
| <listitem> |
| <para> |
| Allows <xref linkend="sql-insert" endterm="sql-insert-title"> of a new |
| row into the specified table. |
| Also allows <xref linkend="sql-copy" endterm="sql-copy-title"> FROM. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>UPDATE</term> |
| <listitem> |
| <para> |
| Allows <xref linkend="sql-update" endterm="sql-update-title"> of any |
| column of the specified table. |
| (In practice, any nontrivial <command>UPDATE</command> command will require |
| <literal>SELECT</literal> privilege as well, since it must reference table |
| columns to determine which rows to update, and/or to compute new |
| values for columns.) |
| <literal>SELECT ... FOR UPDATE</literal> |
| and <literal>SELECT ... FOR SHARE</literal> |
| also require this privilege, in addition to the |
| <literal>SELECT</literal> privilege. For sequences, this |
| privilege allows the use of the <function>nextval</function> and |
| <function>setval</function> functions. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>DELETE</term> |
| <listitem> |
| <para> |
| Allows <xref linkend="sql-delete" endterm="sql-delete-title"> of a row |
| from the specified table. |
| (In practice, any nontrivial <command>DELETE</command> command will require |
| <literal>SELECT</literal> privilege as well, since it must reference table |
| columns to determine which rows to delete.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>REFERENCES</term> |
| <listitem> |
| <para> |
| To create a foreign key constraint, it is |
| necessary to have this privilege on both the referencing and |
| referenced tables. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>TRIGGER</term> |
| <listitem> |
| <para> |
| Allows the creation of a trigger on the specified table. (See the |
| <xref linkend="sql-createtrigger" endterm="sql-createtrigger-title"> statement.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>CREATE</term> |
| <listitem> |
| <para> |
| For databases, allows new schemas to be created within the database. |
| </para> |
| <para> |
| For schemas, allows new objects to be created within the schema. |
| To rename an existing object, you must own the object <emphasis>and</emphasis> |
| have this privilege for the containing schema. |
| </para> |
| <para> |
| 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.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>CONNECT</term> |
| <listitem> |
| <para> |
| Allows the user to connect to the specified database. This |
| privilege is checked at connection startup (in addition to checking |
| any restrictions imposed by <filename>pg_hba.conf</filename>). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>TEMPORARY</term> |
| <term>TEMP</term> |
| <listitem> |
| <para> |
| Allows temporary tables to be created while using the specified database. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>EXECUTE</term> |
| <listitem> |
| <para> |
| 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.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>USAGE</term> |
| <listitem> |
| <para> |
| 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. |
| </para> |
| <para> |
| 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 <quote>look up</quote> |
| 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. |
| </para> |
| <para> |
| For sequences, this privilege allows the use of the |
| <function>currval</function> and <function>nextval</function> functions. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term>ALL PRIVILEGES</term> |
| <listitem> |
| <para> |
| Grant all of the available privileges at once. |
| The <literal>PRIVILEGES</literal> key word is optional in |
| <productname>PostgreSQL</productname>, though it is required by |
| strict SQL. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| The privileges required by other commands are listed on the |
| reference page of the respective command. |
| </para> |
| </refsect2> |
| |
| <refsect2 id="sql-grant-description-roles"> |
| <title>GRANT on Roles</title> |
| |
| <para> |
| This variant of the <command>GRANT</command> 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. |
| </para> |
| |
| <para> |
| If <literal>WITH ADMIN OPTION</literal> 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 <literal>CREATEROLE</literal> privilege can grant or revoke |
| membership in any role that is not a superuser. |
| </para> |
| |
| <para> |
| Unlike the case with privileges, membership in a role cannot be granted |
| to <literal>PUBLIC</literal>. Note also that this form of the command does not |
| allow the noise word <literal>GROUP</literal>. |
| </para> |
| </refsect2> |
| |
| <refsect2 id="sql-grant-description-protocols"> |
| <title>Grant on Protocols</title> |
| |
| <para> |
| After creating a custom protocol, specify CREATE TRUSTED PROTOCOL to be able |
| to allowing any user besides the owner to access it. If the protocol is not |
| trusted, you cannot give any other user permission to use it to read or write data. |
| After a TRUSTED protocol is created, you can specify which other users can |
| access it with the GRANT command. |
| </para> |
| |
| <para> |
| To allow a user to create a readable external table with a trusted protocol |
| </para> |
| |
| <para> |
| GRANT SELECT ON PROTOCOL protocolname TO username |
| </para> |
| |
| <para> |
| To allow a user to create a writable external table with a trusted protocol |
| </para> |
| |
| <para> |
| GRANT INSERT ON PROTOCOL protocolname TO username |
| </para> |
| |
| <para> |
| To allow a user to create both readable and writable external table with a trusted protocol |
| </para> |
| |
| <para> |
| GRANT ALL ON PROTOCOL protocolname TO username |
| </para> |
| |
| </refsect2> |
| </refsect1> |
| |
| |
| <refsect1 id="SQL-GRANT-notes"> |
| <title>Notes</title> |
| |
| <para> |
| The <xref linkend="sql-revoke" endterm="sql-revoke-title"> command is used |
| to revoke access privileges. |
| </para> |
| |
| <para> |
| When a non-owner of an object attempts to <command>GRANT</command> 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 <command>GRANT ALL |
| PRIVILEGES</command> 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.) |
| </para> |
| |
| <para> |
| It should be noted that database superusers can access |
| all objects regardless of object privilege settings. This |
| is comparable to the rights of <literal>root</literal> in a Unix system. |
| As with <literal>root</literal>, it's unwise to operate as a superuser |
| except when absolutely necessary. |
| </para> |
| |
| <para> |
| If a superuser chooses to issue a <command>GRANT</command> or <command>REVOKE</command> |
| 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.) |
| </para> |
| |
| <para> |
| <command>GRANT</command> and <command>REVOKE</command> 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 |
| <literal>WITH GRANT OPTION</literal> 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 |
| <literal>WITH GRANT OPTION</literal>. For example, if table |
| <literal>t1</literal> is owned by role <literal>g1</literal>, of which role |
| <literal>u1</literal> is a member, then <literal>u1</literal> can grant privileges |
| on <literal>t1</literal> to <literal>u2</literal>, but those privileges will appear |
| to have been granted directly by <literal>g1</literal>. Any other member |
| of role <literal>g1</literal> could revoke them later. |
| </para> |
| |
| <para> |
| If the role executing <command>GRANT</command> 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 <command>SET ROLE</command> to become the |
| specific role you want to do the <command>GRANT</command> as. |
| </para> |
| |
| <para> |
| Granting permission on a table does not automatically extend |
| permissions to any sequences used by the table, including |
| sequences tied to <type>SERIAL</type> columns. Permissions on |
| sequence must be set separately. |
| </para> |
| |
| <para> |
| Currently, <productname>PostgreSQL</productname> 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. |
| </para> |
| |
| <para> |
| Use <xref linkend="app-psql">'s <command>\z</command> command |
| to obtain information about existing privileges, for example: |
| <programlisting> |
| => \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) |
| </programlisting> |
| The entries shown by <command>\z</command> are interpreted thus: |
| <programlisting> |
| =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 |
| </programlisting> |
| |
| The above example display would be seen by user <literal>miriam</literal> after |
| creating table <literal>mytable</literal> and doing |
| |
| <programlisting> |
| GRANT SELECT ON mytable TO PUBLIC; |
| GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos; |
| </programlisting> |
| </para> |
| |
| <para> |
| If the <quote>Access privileges</quote> 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 <literal>PUBLIC</literal> depending on the |
| object type, as explained above. The first <command>GRANT</command> or |
| <command>REVOKE</command> on an object |
| will instantiate the default privileges (producing, for example, |
| <literal>{miriam=arwdxt/miriam}</literal>) and then modify them per the |
| specified request. |
| </para> |
| |
| <para> |
| Notice that the owner's implicit grant options are not marked in the |
| access privileges display. A <literal>*</literal> will appear only when |
| grant options have been explicitly granted to someone. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-grant-examples"> |
| <title>Examples</title> |
| |
| <para> |
| Grant insert privilege to all users on table <literal>films</literal>: |
| |
| <programlisting> |
| GRANT INSERT ON films TO PUBLIC; |
| </programlisting> |
| </para> |
| |
| <para> |
| Grant all available privileges to user <literal>manuel</literal> on view |
| <literal>kinds</literal>: |
| |
| <programlisting> |
| GRANT ALL PRIVILEGES ON kinds TO manuel; |
| </programlisting> |
| |
| Note that while the above will indeed grant all privileges if executed by a |
| superuser or the owner of <literal>kinds</literal>, when executed by someone |
| else it will only grant those permissions for which the someone else has |
| grant options. |
| </para> |
| |
| <para> |
| Grant membership in role <literal>admins</literal> to user <literal>joe</literal>: |
| |
| <programlisting> |
| GRANT admins TO joe; |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-grant-compatibility"> |
| <title>Compatibility</title> |
| |
| <para> |
| According to the SQL standard, the <literal>PRIVILEGES</literal> |
| key word in <literal>ALL PRIVILEGES</literal> is required. The |
| SQL standard does not support setting the privileges on more than |
| one object per command. |
| </para> |
| |
| <para> |
| <productname>PostgreSQL</productname> 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 <productname>PostgreSQL</productname> 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 <quote>_SYSTEM</quote>. Not being |
| <quote>_SYSTEM</quote>, the owner cannot revoke these rights. |
| </para> |
| |
| <para> |
| The SQL standard allows setting privileges for individual columns |
| within a table: |
| |
| <synopsis> |
| GRANT <replaceable class="PARAMETER">privileges</replaceable> |
| ON <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ] [, ...] |
| TO { PUBLIC | <replaceable class="PARAMETER">username</replaceable> [, ...] } [ WITH GRANT OPTION ] |
| </synopsis> |
| </para> |
| |
| <para> |
| The SQL standard provides for a <literal>USAGE</literal> privilege |
| on other kinds of objects: character sets, collations, |
| translations, domains. |
| </para> |
| |
| <para> |
| Privileges on databases, tablespaces, schemas, and languages are |
| <productname>PostgreSQL</productname> extensions. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simpara> |
| <xref linkend="sql-revoke" endterm="sql-revoke-title"> |
| </simpara> |
| </refsect1> |
| |
| </refentry> |