I have not found a convenient presentation of the default privileges for
different objects, and how to display them (if possible, not always).
The information is partly provided within the GRANT description, and not
very explicit: eg it is said that owners have all possible perms, but
which they are is not said explicitely, although they are implied by the
different GRANT sysnopsys. Then some objects are given perms for the
PUBLIC.
The attached patch tries to improve the documentation, in particular with
an added table to summarizes my findings, so that they are recorded
somewhere.
--
Fabien.
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index ff64c7a3ba..5a10a50c60 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -175,6 +175,8 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
<literal>EXECUTE</literal> privilege for functions and procedures; and
<literal>USAGE</literal> privilege for languages and data types
(including domains).
+ <xref linkend="default-privileges"/> summarizes the hardcoded
+ default privileges granted to all object's types.
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
@@ -205,6 +207,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
<function>currval</function> function.
For large objects, this privilege allows the object to be read.
</para>
+ <para>
+ This privilege is abbreviated <literal>r</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -218,6 +223,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
command (other columns will therefore receive default values).
Also allows <xref linkend="sql-copy"/> FROM.
</para>
+ <para>
+ This privilege is abbreviated <literal>a</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -240,6 +248,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
For large objects, this privilege allows writing or truncating the
object.
</para>
+ <para>
+ This privilege is abbreviated <literal>w</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -253,6 +264,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
<literal>SELECT</literal> privilege as well, since it must reference
table
columns to determine which rows to delete.)
</para>
+ <para>
+ This privilege is abbreviated <literal>d</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -263,6 +277,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
Allows <xref linkend="sql-truncate"/> on
the specified table.
</para>
+ <para>
+ This privilege is abbreviated <literal>D</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -274,6 +291,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
table, or specified column(s) of the table. (See the
<xref linkend="sql-createtable"/> statement.)
</para>
+ <para>
+ This privilege is abbreviated <literal>x</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -284,6 +304,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
Allows the creation of a trigger on the specified table. (See the
<xref linkend="sql-createtrigger"/> statement.)
</para>
+ <para>
+ This privilege is abbreviated <literal>t</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -304,6 +327,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
have the tablespace as their default tablespace. (Note that revoking
this privilege will not alter the placement of existing objects.)
</para>
+ <para>
+ This privilege is abbreviated <literal>C</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -315,6 +341,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
privilege is checked at connection startup (in addition to checking
any restrictions imposed by <filename>pg_hba.conf</filename>).
</para>
+ <para>
+ This privilege is abbreviated <literal>c</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -325,6 +354,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
<para>
Allows temporary tables to be created while using the specified
database.
</para>
+ <para>
+ This privilege is abbreviated <literal>T</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -339,6 +371,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
functions. Alternatively, use <literal>ROUTINE</literal> to refer to a
function,
aggregate function, or procedure regardless of what it is.
</para>
+ <para>
+ This privilege is abbreviated <literal>X</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -382,6 +417,9 @@ GRANT <replaceable
class="parameter">role_name</replaceable> [, ...] TO <replace
the server. Grantees may also create, alter, or drop their own
user mappings associated with that server.
</para>
+ <para>
+ This privilege is abbreviated <literal>U</literal> when displayed.
+ </para>
</listitem>
</varlistentry>
@@ -627,6 +665,95 @@ GRANT ALL PRIVILEGES ON kinds TO manuel;
<programlisting>
GRANT admins TO joe;
</programlisting></para>
+
+ <table id="default-privileges">
+ <title>Default hardcoded access privileges per object's type</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Object's type</entry>
+ <entry><command>psql</command> <literal>\</literal>-command</entry>
+ <entry>Owner</entry>
+ <entry><literal>PUBLIC</literal></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>DATABASE</literal></entry>
+ <entry><literal>\l</literal></entry>
+ <entry><literal>CTc</literal></entry>
+ <entry><literal>Tc</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DOMAIN</literal></entry>
+ <entry><literal>\dD+</literal></entry>
+ <entry><literal>U</literal></entry>
+ <entry><literal>U</literal></entry>
+ </row>
+ <row>
+ <entry><literal>FUNCTION</literal> or
<literal>PROCEDURE</literal></entry>
+ <entry><literal>\df+</literal></entry>
+ <entry><literal>X</literal></entry>
+ <entry><literal>X</literal></entry>
+ </row>
+ <row>
+ <entry><literal>FOREIGN DATA WRAPPER</literal></entry>
+ <entry><literal>\dew+</literal></entry>
+ <entry><literal>U</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>FOREIGN SERVER</literal></entry>
+ <entry><literal>\des+</literal></entry>
+ <entry><literal>U</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>LANGUAGE</literal></entry>
+ <entry><literal>\dL+</literal></entry>
+ <entry><literal>U</literal></entry>
+ <entry><literal>U</literal></entry>
+ </row>
+ <row>
+ <entry><literal>LARGE OBJECT</literal></entry>
+ <entry></entry>
+ <entry><literal>rw</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>SCHEMA</literal></entry>
+ <entry><literal>\dn+</literal></entry>
+ <entry><literal>UC</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>SEQUENCE</literal></entry>
+ <entry><literal>\dp</literal></entry>
+ <entry><literal>rwU</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE</literal> and relation-like objects</entry>
+ <entry><literal>\dp</literal></entry>
+ <entry><literal>arwdDxt</literal></entry>
+ <entry></entry>
+ </row>
+ <row>
+ <entry><literal>TABLESPACE</literal></entry>
+ <entry><literal>\db+</literal></entry>
+ <entry><literal></literal></entry>
+ <entry><literal></literal></entry>
+ </row>
+ <row>
+ <entry><literal>TYPE</literal></entry>
+ <entry><literal>\dT+</literal></entry>
+ <entry><literal>U</literal></entry>
+ <entry><literal>U</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</refsect1>
<refsect1 id="sql-grant-compatibility">
