diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 54 | ||||
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 529 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 41 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_default_privileges.sgml | 7 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/grant.sgml | 312 | ||||
| -rw-r--r-- | doc/src/sgml/ref/psql-ref.sgml | 14 | ||||
| -rw-r--r-- | doc/src/sgml/ref/revoke.sgml | 20 |
8 files changed, 589 insertions, 390 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c134bca8096..18c38e42de6 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1973,10 +1973,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> @@ -2679,10 +2676,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> </tbody> @@ -3491,10 +3485,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> @@ -3587,10 +3578,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> @@ -4052,9 +4040,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry></entry> <entry> The initial access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + <xref linkend="ddl-priv"/> for details </entry> </row> @@ -4179,10 +4165,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> </tbody> @@ -4319,10 +4302,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> @@ -4386,10 +4366,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> </tbody> @@ -5396,10 +5373,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> </tbody> @@ -6810,10 +6784,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> @@ -7923,10 +7894,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <entry><type>aclitem[]</type></entry> <entry></entry> <entry> - Access privileges; see - <xref linkend="sql-grant"/> and - <xref linkend="sql-revoke"/> - for details + Access privileges; see <xref linkend="ddl-priv"/> for details </entry> </row> </tbody> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index bfe89ef8ae0..676a87aeb9c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1396,6 +1396,10 @@ ALTER TABLE products RENAME TO items; <primary>REVOKE</primary> </indexterm> + <indexterm zone="ddl-priv"> + <primary>ACL</primary> + </indexterm> + <para> When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement. @@ -1413,11 +1417,9 @@ ALTER TABLE products RENAME TO items; <literal>EXECUTE</literal>, and <literal>USAGE</literal>. The privileges applicable to a particular object vary depending on the object's type (table, function, etc). - For complete information on the different types of privileges - supported by <productname>PostgreSQL</productname>, refer to the - <xref linkend="sql-grant"/> reference - page. The following sections and chapters will also show you how - those privileges are used. + More detail about the meanings of these privileges appears below. + The following sections and chapters will also show you how + these privileges are used. </para> <para> @@ -1427,15 +1429,17 @@ ALTER TABLE products RENAME TO items; <para> An object can be assigned to a new owner with an <command>ALTER</command> - command of the appropriate kind for the object, e.g. <xref - linkend="sql-altertable"/>. Superusers can always do - this; ordinary roles can only do it if they are both the current owner - of the object (or a member of the owning role) and a member of the new - owning role. + command of the appropriate kind for the object, for example +<programlisting> +ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owner</replaceable>; +</programlisting> + Superusers can always do this; ordinary roles can only do it if they are + both the current owner of the object (or a member of the owning role) and + a member of the new owning role. </para> <para> - To assign privileges, the <command>GRANT</command> command is + To assign privileges, the <xref linkend="sql-grant"/> command is used. For example, if <literal>joe</literal> is an existing role, and <literal>accounts</literal> is an existing table, the privilege to update the table can be granted with: @@ -1456,7 +1460,7 @@ GRANT UPDATE ON accounts TO joe; <para> To revoke a privilege, use the fittingly named - <command>REVOKE</command> command: + <xref linkend="sql-revoke"/> command: <programlisting> REVOKE ALL ON accounts FROM PUBLIC; </programlisting> @@ -1478,6 +1482,507 @@ REVOKE ALL ON accounts FROM PUBLIC; privilege. For details see the <xref linkend="sql-grant"/> and <xref linkend="sql-revoke"/> reference pages. </para> + + <para> + The available privileges are: + + <variablelist> + <varlistentry> + <term><literal>SELECT</literal></term> + <listitem> + <para> + Allows <xref linkend="sql-select"/> from + any column, or specific column(s), of a table, view, materialized + view, or other table-like object. + Also allows use of <xref linkend="sql-copy"/> TO. + This privilege is also needed to reference existing column values in + <xref linkend="sql-update"/> or <xref linkend="sql-delete"/>. + For sequences, this privilege also allows use of the + <function>currval</function> function. + For large objects, this privilege allows the object to be read. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INSERT</literal></term> + <listitem> + <para> + Allows <xref linkend="sql-insert"/> of a new row into a table, view, + etc. Can be granted on specific column(s), in which case + only those columns may be assigned to in the <command>INSERT</command> + command (other columns will therefore receive default values). + Also allows use of <xref linkend="sql-copy"/> FROM. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>UPDATE</literal></term> + <listitem> + <para> + Allows <xref linkend="sql-update"/> of any + column, or specific column(s), of a table, view, etc. + (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 on at least one column, in addition to the + <literal>SELECT</literal> privilege. For sequences, this + privilege allows use of the <function>nextval</function> and + <function>setval</function> functions. + For large objects, this privilege allows writing or truncating the + object. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DELETE</literal></term> + <listitem> + <para> + Allows <xref linkend="sql-delete"/> of a row from a table, view, etc. + (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><literal>TRUNCATE</literal></term> + <listitem> + <para> + Allows <xref linkend="sql-truncate"/> on a table, view, etc. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>REFERENCES</literal></term> + <listitem> + <para> + Allows creation of a foreign key constraint referencing a + table, or specific column(s) of a table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>TRIGGER</literal></term> + <listitem> + <para> + Allows creation of a trigger on a table, view, etc. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CREATE</literal></term> + <listitem> + <para> + For databases, allows new schemas and publications 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, indexes, and temporary files 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><literal>CONNECT</literal></term> + <listitem> + <para> + Allows the grantee to connect to the 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><literal>TEMPORARY</literal></term> + <listitem> + <para> + Allows temporary tables to be created while using the database. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>EXECUTE</literal></term> + <listitem> + <para> + Allows calling a function or procedure, including use of + any operators that are implemented on top of the function. This is the + only type of privilege that is applicable to functions and procedures. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>USAGE</literal></term> + <listitem> + <para> + For procedural languages, allows use of the 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 + 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 system catalogs. + Also, after revoking this permission, existing sessions 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, allows use of the + <function>currval</function> and <function>nextval</function> functions. + </para> + <para> + For types and domains, allows use of the type or domain in the + creation of tables, functions, and other schema objects. (Note that + this privilege does not control all <quote>usage</quote> of the + type, such as values of the type appearing in queries. It only + prevents objects from being created that depend on the type. The + main purpose of this privilege is controlling which users can create + dependencies on a type, which could prevent the owner from changing + the type later.) + </para> + <para> + For foreign-data wrappers, allows creation of new servers using the + foreign-data wrapper. + </para> + <para> + For foreign servers, allows creation of foreign tables using the + server. Grantees may also create, alter, or drop their own user + mappings associated with that server. + </para> + </listitem> + </varlistentry> + </variablelist> + + The privileges required by other commands are listed on the + reference page of the respective command. + </para> + + <para> + PostgreSQL grants privileges on some types of objects to + <literal>PUBLIC</literal> by default when the objects are created. + No privileges are granted to <literal>PUBLIC</literal> by default on + tables, + table columns, + sequences, + foreign data wrappers, + foreign servers, + large objects, + schemas, + or tablespaces. + For other types of objects, the default privileges + granted to <literal>PUBLIC</literal> are as follows: + <literal>CONNECT</literal> and <literal>TEMPORARY</literal> (create + temporary tables) privileges for databases; + <literal>EXECUTE</literal> privilege for functions and procedures; and + <literal>USAGE</literal> privilege for languages and data types + (including domains). + The object owner can, of course, <command>REVOKE</command> + both default and expressly granted 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 + can use the object.) + Also, these default privilege settings can be overridden using the + <xref linkend="sql-alterdefaultprivileges"/> command. + </para> + + <para> + <xref linkend="privilege-abbrevs-table"/> shows the one-letter + abbreviations that are used for these privilege types in + <firstterm>ACL</firstterm> (Access Control List) values. + You will see these letters in the output of the <xref linkend="app-psql"/> + commands listed below, or when looking at ACL columns of system catalogs. + </para> + + <table id="privilege-abbrevs-table"> + <title>ACL Privilege Abbreviations</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Privilege</entry> + <entry>Abbreviation</entry> + <entry>Applicable Object Types</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>SELECT</literal></entry> + <entry><literal>r</literal> (<quote>read</quote>)</entry> + <entry> + <literal>LARGE OBJECT</literal>, + <literal>SEQUENCE</literal>, + <literal>TABLE</literal> (and table-like objects), + table column + </entry> + </row> + <row> + <entry><literal>INSERT</literal></entry> + <entry><literal>a</literal> (<quote>append</quote>)</entry> + <entry><literal>TABLE</literal>, table column</entry> + </row> + <row> + <entry><literal>UPDATE</literal></entry> + <entry><literal>w</literal> (<quote>write</quote>)</entry> + <entry> + <literal>LARGE OBJECT</literal>, + <literal>SEQUENCE</literal>, + <literal>TABLE</literal>, + table column + </entry> + </row> + <row> + <entry><literal>DELETE</literal></entry> + <entry><literal>d</literal></entry> + <entry><literal>TABLE</literal></entry> + </row> + <row> + <entry><literal>TRUNCATE</literal></entry> + <entry><literal>D</literal></entry> + <entry><literal>TABLE</literal></entry> + </row> + <row> + <entry><literal>REFERENCES</literal></entry> + <entry><literal>x</literal></entry> + <entry><literal>TABLE</literal>, table column</entry> + </row> + <row> + <entry><literal>TRIGGER</literal></entry> + <entry><literal>t</literal></entry> + <entry><literal>TABLE</literal></entry> + </row> + <row> + <entry><literal>CREATE</literal></entry> + <entry><literal>C</literal></entry> + <entry> + <literal>DATABASE</literal>, + <literal>SCHEMA</literal>, + <literal>TABLESPACE</literal> + </entry> + </row> + <row> + <entry><literal>CONNECT</literal></entry> + <entry><literal>c</literal></entry> + <entry><literal>DATABASE</literal></entry> + </row> + <row> + <entry><literal>TEMPORARY</literal></entry> + <entry><literal>T</literal></entry> + <entry><literal>DATABASE</literal></entry> + </row> + <row> + <entry><literal>EXECUTE</literal></entry> + <entry><literal>X</literal></entry> + <entry><literal>FUNCTION</literal>, <literal>PROCEDURE</literal></entry> + </row> + <row> + <entry><literal>USAGE</literal></entry> + <entry><literal>U</literal></entry> + <entry> + <literal>DOMAIN</literal>, + <literal>FOREIGN DATA WRAPPER</literal>, + <literal>FOREIGN SERVER</literal>, + <literal>LANGUAGE</literal>, + <literal>SCHEMA</literal>, + <literal>SEQUENCE</literal>, + <literal>TYPE</literal> + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + <xref linkend="privileges-summary-table"/> summarizes the privileges + available for each type of SQL object, using the abbreviations shown + above. + It also shows the <application>psql</application> command + that can be used to examine privilege settings for each object type. + </para> + + <table id="privileges-summary-table"> + <title>Summary of Access Privileges</title> + <tgroup cols="4"> + <thead> + <row> + <entry>Object Type</entry> + <entry>All Privileges</entry> + <entry>Default <literal>PUBLIC</literal> Privileges</entry> + <entry><application>psql</application> Command</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>DATABASE</literal></entry> + <entry><literal>CTc</literal></entry> + <entry><literal>Tc</literal></entry> + <entry><literal>\l</literal></entry> + </row> + <row> + <entry><literal>DOMAIN</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>\dD+</literal></entry> + </row> + <row> + <entry><literal>FUNCTION</literal> or <literal>PROCEDURE</literal></entry> + <entry><literal>X</literal></entry> + <entry><literal>X</literal></entry> + <entry><literal>\df+</literal></entry> + </row> + <row> + <entry><literal>FOREIGN DATA WRAPPER</literal></entry> + <entry><literal>U</literal></entry> + <entry>none</entry> + <entry><literal>\dew+</literal></entry> + </row> + <row> + <entry><literal>FOREIGN SERVER</literal></entry> + <entry><literal>U</literal></entry> + <entry>none</entry> + <entry><literal>\des+</literal></entry> + </row> + <row> + <entry><literal>LANGUAGE</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>\dL+</literal></entry> + </row> + <row> + <entry><literal>LARGE OBJECT</literal></entry> + <entry><literal>rw</literal></entry> + <entry>none</entry> + <entry></entry> + </row> + <row> + <entry><literal>SCHEMA</literal></entry> + <entry><literal>UC</literal></entry> + <entry>none</entry> + <entry><literal>\dn+</literal></entry> + </row> + <row> + <entry><literal>SEQUENCE</literal></entry> + <entry><literal>rwU</literal></entry> + <entry>none</entry> + <entry><literal>\dp</literal></entry> + </row> + <row> + <entry><literal>TABLE</literal> (and table-like objects)</entry> + <entry><literal>arwdDxt</literal></entry> + <entry>none</entry> + <entry><literal>\dp</literal></entry> + </row> + <row> + <entry>Table column</entry> + <entry><literal>arwx</literal></entry> + <entry>none</entry> + <entry><literal>\dp</literal></entry> + </row> + <row> + <entry><literal>TABLESPACE</literal></entry> + <entry><literal>C</literal></entry> + <entry>none</entry> + <entry><literal>\db+</literal></entry> + </row> + <row> + <entry><literal>TYPE</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>U</literal></entry> + <entry><literal>\dT+</literal></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + <indexterm> + <primary><type>aclitem</type></primary> + </indexterm> + The privileges that have been granted for a particular object are + displayed as a list of <type>aclitem</type> entries, where each + <type>aclitem</type> describes the permissions of one grantee that + have been granted by a particular grantor. For example, + <literal>calvin=r*w/hobbes</literal> specifies that the role + <literal>calvin</literal> has the privilege + <literal>SELECT</literal> (<literal>r</literal>) with grant option + (<literal>*</literal>) as well as the non-grantable + privilege <literal>UPDATE</literal> (<literal>w</literal>), both granted + by the role <literal>hobbes</literal>. If <literal>calvin</literal> + also has some privileges on the same object granted by a different + grantor, those would appear as a separate <type>aclitem</type> entry. + An empty grantee field in an <type>aclitem</type> stands + for <literal>PUBLIC</literal>. + </para> + + <para> + As an example, suppose that user <literal>miriam</literal> creates + table <literal>mytable</literal> and does: +<programlisting> +GRANT SELECT ON mytable TO PUBLIC; +GRANT SELECT, UPDATE, INSERT ON mytable TO admin; +GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; +</programlisting> + Then <application>psql</application>'s <literal>\dp</literal> command + would show: +<programlisting> +=> \dp mytable + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------+-------+-----------------------+-----------------------+---------- + public | mytable | table | miriam=arwdDxt/miriam+| col1: +| + | | | =r/miriam +| miriam_rw=rw/miriam | + | | | admin=arw/miriam | | +(1 row) +</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 entry in the relevant system catalog is null). Default + privileges always include all privileges for the owner, and can 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=arwdDxt/miriam</literal>) and then modify them + per the specified request. Similarly, entries are shown in <quote>Column + privileges</quote> only for columns with nondefault privileges. + (Note: for this purpose, <quote>default privileges</quote> always means + the built-in default privileges for the object's type. An object whose + privileges have been affected by an <command>ALTER DEFAULT + PRIVILEGES</command> command will always be shown with an explicit + privilege entry that includes the effects of + the <command>ALTER</command>.) + </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> </sect1> <sect1 id="ddl-rowsecurity"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 112d9628246..b3336ea9bed 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16932,22 +16932,12 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); <para> <xref linkend="functions-aclitem-fn-table"/> shows the operators - available for the <type>aclitem</type> type, which is the internal - representation of access privileges. An <type>aclitem</type> entry - describes the permissions of a grantee, whether they are grantable - or not, and which grantor granted them. For instance, - <literal>calvin=r*w/hobbes</literal> specifies that the role - <literal>calvin</literal> has the grantable privilege - <literal>SELECT</literal> (<literal>r*</literal>) and the non-grantable - privilege <literal>UPDATE</literal> (<literal>w</literal>), granted by - the role <literal>hobbes</literal>. An empty grantee stands for - <literal>PUBLIC</literal>. + available for the <type>aclitem</type> type, which is the catalog + representation of access privileges. See <xref linkend="ddl-priv"/> + for information about how to read access privilege values. </para> <indexterm> - <primary>aclitem</primary> - </indexterm> - <indexterm> <primary>acldefault</primary> </indexterm> <indexterm> @@ -17015,9 +17005,9 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); <tbody> <row> <entry><literal><function>acldefault</function>(<parameter>type</parameter>, - <parameter>ownerId</parameter>)</literal></entry> + <parameter>ownerId</parameter>)</literal></entry> <entry><type>aclitem[]</type></entry> - <entry>get the hardcoded default access privileges for an object belonging to <parameter>ownerId</parameter></entry> + <entry>get the default access privileges for an object belonging to <parameter>ownerId</parameter></entry> </row> <row> <entry><literal><function>aclexplode</function>(<parameter>aclitem[]</parameter>)</literal></entry> @@ -17034,16 +17024,14 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); </table> <para> - <function>acldefault</function> returns the hardcoded default access privileges - for an object of <parameter>type</parameter> belonging to role <parameter>ownerId</parameter>. - Notice that these are used in the absence of any pg_default_acl - (<xref linkend="catalog-pg-default-acl"/>) entry. Default access privileges are described in - <xref linkend="sql-grant"/> and can be overwritten with - <xref linkend="sql-alterdefaultprivileges"/>. In other words, this function will return - results which may be misleading when the defaults have been overridden. - Type is a <type>CHAR</type>, use + <function>acldefault</function> returns the built-in default access + privileges for an object of type <parameter>type</parameter> belonging to + role <parameter>ownerId</parameter>. These represent the access + privileges that will be assumed when an object's ACL entry is null. + (The default access privileges are described in <xref linkend="ddl-priv"/>.) + The <parameter>type</parameter> parameter is a <type>CHAR</type>: write 'c' for <literal>COLUMN</literal>, - 'r' for relation-like objects such as <literal>TABLE</literal> or <literal>VIEW</literal>, + 'r' for <literal>TABLE</literal> and table-like objects, 's' for <literal>SEQUENCE</literal>, 'd' for <literal>DATABASE</literal>, 'f' for <literal>FUNCTION</literal> or <literal>PROCEDURE</literal>, @@ -17053,15 +17041,16 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); 't' for <literal>TABLESPACE</literal>, 'F' for <literal>FOREIGN DATA WRAPPER</literal>, 'S' for <literal>FOREIGN SERVER</literal>, + or 'T' for <literal>TYPE</literal> or <literal>DOMAIN</literal>. </para> <para> <function>aclexplode</function> returns an <type>aclitem</type> array - as a set rows. Output columns are grantor <type>oid</type>, + as a set of rows. Output columns are grantor <type>oid</type>, grantee <type>oid</type> (<literal>0</literal> for <literal>PUBLIC</literal>), granted privilege as <type>text</type> (<literal>SELECT</literal>, ...) - and whether the prilivege is grantable as <type>boolean</type>. + and whether the privilege is grantable as <type>boolean</type>. <function>makeaclitem</function> performs the inverse operation. </para> diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index 0c09f1db5cd..583f65fad6c 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -112,7 +112,7 @@ REVOKE [ GRANT OPTION FOR ] </para> <para> - As explained under <xref linkend="sql-grant"/>, + As explained in <xref linkend="ddl-priv"/>, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to <literal>PUBLIC</literal> as well. However, this behavior can be changed by @@ -173,9 +173,8 @@ REVOKE [ GRANT OPTION FOR ] <para> Use <xref linkend="app-psql"/>'s <command>\ddp</command> command to obtain information about existing assignments of default privileges. - The meaning of the privilege values is the same as explained for - <command>\dp</command> under - <xref linkend="sql-grant"/>. + The meaning of the privilege display is the same as explained for + <command>\dp</command> in <xref linkend="ddl-priv"/>. </para> <para> diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 06be04eb5c5..40725431842 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -761,7 +761,7 @@ $$ LANGUAGE plpgsql <para> Another point to keep in mind is that by default, execute privilege is granted to <literal>PUBLIC</literal> for newly created functions - (see <xref linkend="sql-grant"/> for more + (see <xref linkend="ddl-priv"/> for more information). Frequently you will wish to restrict use of a security definer function to only some users. To do that, you must revoke the default <literal>PUBLIC</literal> privileges and then grant execute diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index ff64c7a3bae..e98fe860528 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -113,16 +113,6 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace </para> <para> - There is also an option to grant privileges on all objects of the same - type within one or more schemas. This functionality is currently supported - only for tables, sequences, functions, and procedures. <literal>ALL - TABLES</literal> also affects views and foreign tables, just like the - specific-object <command>GRANT</command> command. <literal>ALL - FUNCTIONS</literal> also affects aggregate functions, but not procedures, - again just like the specific-object <command>GRANT</command> command. - </para> - - <para> The key word <literal>PUBLIC</literal> indicates that the privileges are to be granted to all roles, including those that might be created later. <literal>PUBLIC</literal> can be thought of as an @@ -157,230 +147,34 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace </para> <para> - PostgreSQL grants default privileges on some types of objects to - <literal>PUBLIC</literal>. No privileges are granted to - <literal>PUBLIC</literal> by default on - tables, - table columns, - sequences, - foreign data wrappers, - foreign servers, - large objects, - schemas, - or tablespaces. - For other types of objects, the default privileges - granted to <literal>PUBLIC</literal> are as follows: - <literal>CONNECT</literal> and <literal>TEMPORARY</literal> (create - temporary tables) privileges for databases; - <literal>EXECUTE</literal> privilege for functions and procedures; and - <literal>USAGE</literal> privilege for languages and data types - (including domains). - The object owner can, of course, <command>REVOKE</command> - both default and expressly granted 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 - can use the object.) - Also, these initial default privilege settings can be changed using the - <xref linkend="sql-alterdefaultprivileges"/> - command. - </para> - - <para> The possible privileges are: <variablelist> <varlistentry> <term><literal>SELECT</literal></term> - <listitem> - <para> - Allows <xref linkend="sql-select"/> from - any column, or the specific columns listed, of the specified table, - view, or sequence. - Also allows the use of - <xref linkend="sql-copy"/> TO. - This privilege is also needed to reference existing column values in - <xref linkend="sql-update"/> or - <xref linkend="sql-delete"/>. - For sequences, this privilege also allows the use of the - <function>currval</function> function. - For large objects, this privilege allows the object to be read. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>INSERT</literal></term> - <listitem> - <para> - Allows <xref linkend="sql-insert"/> of a new - row into the specified table. If specific columns are listed, - only those columns may be assigned to in the <command>INSERT</command> - command (other columns will therefore receive default values). - Also allows <xref linkend="sql-copy"/> FROM. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>UPDATE</literal></term> - <listitem> - <para> - Allows <xref linkend="sql-update"/> of any - column, or the specific columns listed, 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 on at least one column, 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. - For large objects, this privilege allows writing or truncating the - object. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>DELETE</literal></term> - <listitem> - <para> - Allows <xref linkend="sql-delete"/> 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><literal>TRUNCATE</literal></term> - <listitem> - <para> - Allows <xref linkend="sql-truncate"/> on - the specified table. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>REFERENCES</literal></term> - <listitem> - <para> - Allows creation of a foreign key constraint referencing the specified - table, or specified column(s) of the table. (See the - <xref linkend="sql-createtable"/> statement.) - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>TRIGGER</literal></term> - <listitem> - <para> - Allows the creation of a trigger on the specified table. (See the - <xref linkend="sql-createtrigger"/> statement.) - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>CREATE</literal></term> - <listitem> - <para> - For databases, allows new schemas and publications 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, indexes, and temporary files 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><literal>CONNECT</literal></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><literal>TEMPORARY</literal></term> - <term><literal>TEMP</literal></term> - <listitem> - <para> - Allows temporary tables to be created while using the specified database. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><literal>EXECUTE</literal></term> + <term><literal>USAGE</literal></term> <listitem> <para> - Allows the use of the specified function or procedure 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 and procedures. - The <literal>FUNCTION</literal> syntax also works for aggregate - functions. Alternatively, use <literal>ROUTINE</literal> to refer to a function, - aggregate function, or procedure regardless of what it is. + Specific types of privileges, as defined in <xref linkend="ddl-priv"/>. </para> </listitem> </varlistentry> <varlistentry> - <term><literal>USAGE</literal></term> + <term><literal>TEMP</literal></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> - <para> - For types and domains, this privilege allows the use of the type or - domain in the creation of tables, functions, and other schema objects. - (Note that it does not control general <quote>usage</quote> of the type, - such as values of the type appearing in queries. It only prevents - objects from being created that depend on the type. The main purpose of - the privilege is controlling which users create dependencies on a type, - which could prevent the owner from changing the type later.) - </para> - <para> - For foreign-data wrappers, this privilege allows creation of - new servers using the foreign-data wrapper. - </para> - <para> - For servers, this privilege allows creation of foreign tables using - the server. Grantees may also create, alter, or drop their own - user mappings associated with that server. + Alternative spelling for <literal>TEMPORARY</literal>. </para> </listitem> </varlistentry> @@ -389,7 +183,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace <term><literal>ALL PRIVILEGES</literal></term> <listitem> <para> - Grant all of the available privileges at once. + Grant all of the privileges available for the object's type. The <literal>PRIVILEGES</literal> key word is optional in <productname>PostgreSQL</productname>, though it is required by strict SQL. @@ -397,9 +191,26 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace </listitem> </varlistentry> </variablelist> + </para> - The privileges required by other commands are listed on the - reference page of the respective command. + <para> + The <literal>FUNCTION</literal> syntax works for plain functions, + aggregate functions, and window functions, but not for procedures; + use <literal>PROCEDURE</literal> for those. + Alternatively, use <literal>ROUTINE</literal> to refer to a function, + aggregate function, window function, or procedure regardless of its + precise type. + </para> + + <para> + There is also an option to grant privileges on all objects of the same + type within one or more schemas. This functionality is currently supported + only for tables, sequences, functions, and procedures. <literal>ALL + TABLES</literal> also affects views and foreign tables, just like the + specific-object <command>GRANT</command> command. <literal>ALL + FUNCTIONS</literal> also affects aggregate and window functions, but not + procedures, again just like the specific-object <command>GRANT</command> + command. Use <literal>ALL ROUTINES</literal> to include procedures. </para> </refsect2> @@ -520,79 +331,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace </para> <para> - Use <xref linkend="app-psql"/>'s <command>\dp</command> command - to obtain information about existing privileges for tables and - columns. For example: -<programlisting> -=> \dp mytable - Access privileges - Schema | Name | Type | Access privileges | Column access privileges ---------+---------+-------+-----------------------+-------------------------- - public | mytable | table | miriam=arwdDxt/miriam | col1: - : =r/miriam : miriam_rw=rw/miriam - : admin=arw/miriam -(1 row) -</programlisting> - The entries shown by <command>\dp</command> are interpreted thus: -<literallayout class="monospaced"> -rolename=xxxx -- privileges granted to a role - =xxxx -- privileges granted to PUBLIC - - r -- SELECT ("read") - w -- UPDATE ("write") - a -- INSERT ("append") - d -- DELETE - D -- TRUNCATE - x -- REFERENCES - t -- TRIGGER - X -- EXECUTE - U -- USAGE - C -- CREATE - c -- CONNECT - T -- TEMPORARY - arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) - * -- grant option for preceding privilege - - /yyyy -- role that granted this privilege -</literallayout> - - 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 admin; -GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; -</programlisting> - </para> - - <para> - For non-table objects there are other <command>\d</command> commands - that can display their privileges. - </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 can 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=arwdDxt/miriam}</literal>) and then modify them per the - specified request. Similarly, entries are shown in <quote>Column access - privileges</quote> only for columns with nondefault privileges. - (Note: for this purpose, <quote>default privileges</quote> always means the - built-in default privileges for the object's type. An object whose - privileges have been affected by an <command>ALTER DEFAULT PRIVILEGES</command> - command will always be shown with an explicit privilege entry that - includes the effects of the <command>ALTER</command>.) - </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. + See <xref linkend="ddl-priv"/> for more information about specific + privilege types, as well as how to inspect objects' privileges. </para> </refsect1> diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 47714eb0c8c..6c76cf2f001 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1324,8 +1324,8 @@ testdb=> <para> The <xref linkend="sql-alterdefaultprivileges"/> command is used to set default access privileges. The meaning of the - privilege display is explained under - <xref linkend="sql-grant"/>. + privilege display is explained in + <xref linkend="ddl-priv"/>. </para> </listitem> </varlistentry> @@ -1372,7 +1372,7 @@ testdb=> specified, only those servers whose name matches the pattern are listed. If the form <literal>\des+</literal> is used, a full description of each server is shown, including the - server's ACL, type, version, options, and description. + server's access privileges, type, version, options, and description. </para> </listitem> </varlistentry> @@ -1425,8 +1425,8 @@ testdb=> If <replaceable class="parameter">pattern</replaceable> is specified, only those foreign-data wrappers whose name matches the pattern are listed. If the form <literal>\dew+</literal> - is used, the ACL, options, and description of the foreign-data - wrapper are also shown. + is used, the access privileges, options, and description of the + foreign-data wrapper are also shown. </para> </listitem> </varlistentry> @@ -1639,8 +1639,8 @@ testdb=> The <xref linkend="sql-grant"/> and <xref linkend="sql-revoke"/> commands are used to set access privileges. The meaning of the - privilege display is explained under - <xref linkend="sql-grant"/>. + privilege display is explained in + <xref linkend="ddl-priv"/>. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 5317f8ccba6..e96d45e7e31 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -178,14 +178,6 @@ REVOKE [ ADMIN OPTION FOR ] <title>Notes</title> <para> - Use <xref linkend="app-psql"/>'s <command>\dp</command> command to - display the privileges granted on existing tables and columns. See <xref - linkend="sql-grant"/> for information about the - format. For non-table objects there are other <command>\d</command> commands - that can display their privileges. - </para> - - <para> A user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted it to user @@ -244,6 +236,11 @@ REVOKE [ ADMIN OPTION FOR ] lead to revoking privileges other than the ones you intended, or not revoking anything at all. </para> + + <para> + See <xref linkend="ddl-priv"/> for more information about specific + privilege types, as well as how to inspect objects' privileges. + </para> </refsect1> <refsect1 id="sql-revoke-examples"> @@ -293,9 +290,10 @@ REVOKE admins FROM joe; <refsect1> <title>See Also</title> - <simpara> - <xref linkend="sql-grant"/> - </simpara> + <simplelist type="inline"> + <member><xref linkend="sql-grant"/></member> + <member><xref linkend="sql-alterdefaultprivileges"/></member> + </simplelist> </refsect1> </refentry> |