Thread: doc - improve description of default privileges
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.
Attachment
> 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. The attached fixes the tablespace entry that I forgot to fill in full. -- Fabien.
Attachment
Hi Fabien, Thanks for writing this up - in particular the psql backslash commands. comments on the patch ... 1) I think that adding the "This privilege is abbreviated ... when displayed." lines to the privilege descriptions is redundant. The abbreviations are already listed after the "The entries shown by \dp are interpreted thus:" line. Just change that line to something like "The entries shown by the psql backslash commands, like \dp, are interpreted thus:". 2) I think that the psql command table should go with the current text on "Use psql's \dp command to obtain ..." rather than in the Examples section. It seems like changing the "For non-table objects there are other \d commands ..." line to an introductory comment like "The following table lists the \d commands that are used for non-table objects along with the default privileges granted to the object's owner and PUBLIC. 3) The table title, "Default hardcoded access privileges per object's type", seems incomplete because it does not mention the psql commands part of the table. ------ Original Message ------ From: "Fabien COELHO" <coelho@cri.ensmp.fr> To: "PostgreSQL Developers" <pgsql-hackers@lists.postgresql.org> Sent: 8/4/2018 4:40:33 AM Subject: Re: doc - improve description of default privileges > >>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. > >The attached fixes the tablespace entry that I forgot to fill in full. > >-- Fabien.
Hello Bradley, > comments on the patch ... Thanks for the review. > 1) I think that adding the "This privilege is abbreviated ... when > displayed." lines to the privilege descriptions is redundant. The > abbreviations are already listed after the "The entries shown by \dp are > interpreted thus:" line. Just change that line to something like "The entries > shown by the psql backslash commands, like \dp, are interpreted thus:". Ok, removed. > 2) I think that the psql command table should go with the current text on > "Use psql's \dp command to obtain ..." rather than in the Examples section. > It seems like changing the "For non-table objects there are other \d commands > ..." line to an introductory comment like "The following table lists the \d > commands that are used for non-table objects along with the default > privileges granted to the object's owner and PUBLIC. Ok, moved to the previous section. > 3) The table title, "Default hardcoded access privileges per object's type", > seems incomplete because it does not mention the psql commands part of the > table. Ok, added reference to psql backslash commands in title. Find v3 attached. -- Fabien.
Attachment
on 2018-08-30, Fabien Coelho wrote ... > ... Find v3 attached. ... Hi Fabien, As we're coming up on the end of this commitfest ... Is the reviewer supposed to move this to "ready for committer" or is the author supposed to do that? Is the reviewer supposed to explicitly state "I've looked at your v3 patch and have no further suggestions" (which is true) or is a lack of additional comments normally taken as acceptance? Cheers.
"Bradley DeJong" <bpd0018@gmail.com> writes:
> Is the reviewer supposed to move this to "ready for committer" or is the
> author supposed to do that?
The reviewer does that, indicating signoff.
regards, tom lane
On 2018-09-12 Tom Lane wrote ...
> The reviewer does that, indicating signoff.
Thanks. I have now changed the status to "Ready for Committer".
Some thoughts: We should keep the GRANT reference page about GRANT. There is a section about Privileges in the Data Definition chapter, which we could use to expand on general concepts. The ALTER DEFAULT PRIVILEGES reference page would be another place this could be put. The Owner column is redundant, because it's always all applicable privileges. (Having this column would give the impression that it's not always all privileges, so it would be confusing.) Privileges should be listed using their full name (e.g., "SELECT"), not their internal abbreviation letter. The psql commands seem out of place here. If you want to learn about how to use psql, you can go to the psql documentation. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-Sep-28, Peter Eisentraut wrote: > The psql commands seem out of place here. If you want to learn about > how to use psql, you can go to the psql documentation. There is a legitimate point in doing this, though, since the GRANT page is already explaining how does psql display privileges. Maybe the right solution is move that stuff all to the psql documentation, and alter the GRANT page to list privileges in terms of their names rather than the way psql displays them. (And of course add cross-links, so that it all makes sense.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> The Owner column is redundant, because it's always all applicable
> privileges. (Having this column would give the impression that it's not
> always all privileges, so it would be confusing.)
The reason I put the owner column is to show (1) the privileges that apply
to the objects (i.e. what is under "ALL") and (2) whether public's
privileges are the same or not, because there are subtles differences, so
I think it is interesting to have them side by side somewhere.
> Privileges should be listed using their full name (e.g., "SELECT"), not
> their internal abbreviation letter.
Hmmm... the psql commands listed in the table output the abbreviated
letters. Using the words would result in a large table, but maybe it
could use multiline cells.
> The psql commands seem out of place here. If you want to learn about
> how to use psql, you can go to the psql documentation.
The information about how to display the permissions is currently not
easily available, I had to test/look for it, noticed that it is not
accessible on some objects, so ISTM that it is useful to have it
somewhere.
Basically your points suggest that the table is maybe in the wrong place
and could be improved.
About the place, there is no simple choice:
- backslash commands are "psql" specific
- abbreviated letters are aclitem function specific, which
happend to be used by psql.
- full names are SQL specific (GRANT)
- default permissions are object specific and can be modified...
Which means that the information tends to be scattered everywhere and
overall pretty unclear unless you have read all the pages, hence my
proposal to put some unified summary somewhere with all the relevant
information. Any choice will have its downside, and removing information
to better suit one place means that my point of having some kind of
summary in one place is lost, which is the initial motivation for this
patch.
--
Fabien.
On Fri, Sep 28, 2018 at 04:15:13PM -0300, Alvaro Herrera wrote: > There is a legitimate point in doing this, though, since the GRANT page > is already explaining how does psql display privileges. Maybe the right > solution is move that stuff all to the psql documentation, and alter the > GRANT page to list privileges in terms of their names rather than the > way psql displays them. (And of course add cross-links, so that it all > makes sense.) This point is interesting, and that looks rather right to me. I looked at the patch and listing psql commands on the page for GRANT is a strange concept in my opinion. Moved to next CF. -- Michael
Attachment
Progress on this patch seems to be blocked on the question of whether
we want to keep enlarging the amount of psql-specific information
in the GRANT reference page, or move that all somewhere else.
FWIW, I think I agree with Peter's position that moving it somewhere
else is the better option. Section 5.6 "Privileges" seems like a
reasonable choice.
One reason for preferring that is that I don't think putting a <table> in
a reference page is a great idea. I tried the patch in HEAD, and what
I see is that the table cross-reference renders as "Table 244", which is
a number that has no chance at all of holding still for any long period.
It's especially weird to read that in "man GRANT.7", where you're
supposedly reading a standalone document. And while my version of "man"
makes a valiant effort to render the table in ASCII, it still doesn't look
great, and older man versions might do very poorly with that. So I'm for
moving this to a part of the docs where we only need to worry about two
output formats not three.
A few thoughts on other issues:
* Perhaps we could fix Peter's complaint about the "Owner" column by
relabeling it "All Privileges". I'd be inclined to label the last
column "Default PUBLIC Privileges", too, if we can fit that in.
* The phrase "relation-like objects" seems way too vague, especially since
one has to read it as excluding sequences, which surely are relations for
most purposes. Is there a good reason not to just leave that entry as
"TABLE", full stop? Or maybe it could be "TABLE, VIEW, etc" or some such.
* I don't think the use of "hardcoded" adds anything.
* Is it worth adding another table matching privilege names ("INSERT")
with their aclitem letters ("a"), rather than having the semi-formal
format currently appearing in grant.sgml? There's also some related
material in 9.25 with the aclitem functions; it'd be worth unifying
that too maybe.
regards, tom lane
Hello Tom,
Thanks for this precise feedback.
> Progress on this patch seems to be blocked on the question of whether
> we want to keep enlarging the amount of psql-specific information
> in the GRANT reference page, or move that all somewhere else.
Yep.
> FWIW, I think I agree with Peter's position that moving it somewhere
> else is the better option. Section 5.6 "Privileges" seems like a
> reasonable choice.
Ok.
> * Perhaps we could fix Peter's complaint about the "Owner" column by
> relabeling it "All Privileges".
Ok.
> I'd be inclined to label the last column "Default PUBLIC Privileges",
> too, if we can fit that in.
Ok.
> * The phrase "relation-like objects" seems way too vague, especially since
> one has to read it as excluding sequences, which surely are relations for
> most purposes. Is there a good reason not to just leave that entry as
> "TABLE", full stop? Or maybe it could be "TABLE, VIEW, etc" or some such.
Ok.
> * I don't think the use of "hardcoded" adds anything.
Hmmm. As "default privileges" can be altered, the point is to describe the
"default default privileges", but this looks absurd, hence the look for
something to add the idea that there is another one. ISTM that removing
"hardcoded" without replacing it makes the thing slightly ambiguous.
No big deal.
> * Is it worth adding another table matching privilege names ("INSERT")
> with their aclitem letters ("a"), rather than having the semi-formal
> format currently appearing in grant.sgml?
Indeed I thought about that, because the description is not easy to read.
> There's also some related material in 9.25 with the aclitem functions;
> it'd be worth unifying that too maybe.
I've put a reference to it at least.
Attached v4:
- moves the table to the privileges section
- updates the table column headers
- adds a privilege/aclitem letter mapping table
- adds some appropriate links towards psql & aclitem
--
Fabien.
Attachment
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Attached v4:
> - moves the table to the privileges section
> - updates the table column headers
> - adds a privilege/aclitem letter mapping table
> - adds some appropriate links towards psql & aclitem
TBH, I don't think this goes nearly far enough. It seems like it
is making the fragmentation of aclitem information worse not better.
I feel if we're going to do anything, we should put a unified description
of privileges and aclitem-reading into section 5.6, and take that material
out of the various places where it lives now. Like the attached, in which
I failed to resist the temptation to wordsmith some stuff as well as move
it around.
regards, tom lane
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c134bca..18c38e4 100644
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
*************** SCRAM-SHA-256$<replaceable><iteration
*** 1973,1982 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 1973,1979 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 2679,2688 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
</tbody>
--- 2676,2682 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
</tbody>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 3491,3500 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 3485,3491 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 3587,3596 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 3578,3584 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 4052,4060 ****
<entry></entry>
<entry>
The initial access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 4040,4046 ----
<entry></entry>
<entry>
The initial access privileges; see
! <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 4179,4188 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
</tbody>
--- 4165,4171 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
</tbody>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 4319,4328 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 4302,4308 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 4386,4395 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
</tbody>
--- 4366,4372 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
</tbody>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 5396,5405 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
</tbody>
--- 5373,5379 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
</tbody>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 6810,6819 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
--- 6784,6790 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see <xref linkend="ddl-priv"/> for details
</entry>
</row>
*************** SCRAM-SHA-256$<replaceable><iteration
*** 7923,7932 ****
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! Access privileges; see
! <xref linkend="sql-grant"/> and
! <xref linkend="sql-revoke"/>
! for details
</entry>
</row>
</tbody>
--- 7894,7900 ----
<entry><type>aclitem[]</type></entry>
<entry></entry>
<entry>
! 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 bfe89ef..f0cf75f 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*************** ALTER TABLE products RENAME TO items;
*** 1396,1401 ****
--- 1396,1405 ----
<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.
*************** ALTER TABLE products RENAME TO items;
*** 1413,1423 ****
<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.
</para>
<para>
--- 1417,1425 ----
<literal>EXECUTE</literal>, and <literal>USAGE</literal>.
The privileges applicable to a particular
object vary depending on the object's type (table, function, etc).
! More detail about the meanings of these privileges is provided below.
! The following sections and chapters will also show you how
! these privileges are used.
</para>
<para>
*************** ALTER TABLE products RENAME TO items;
*** 1435,1441 ****
</para>
<para>
! To assign privileges, the <command>GRANT</command> 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:
--- 1437,1443 ----
</para>
<para>
! 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:
*************** GRANT UPDATE ON accounts TO joe;
*** 1456,1462 ****
<para>
To revoke a privilege, use the fittingly named
! <command>REVOKE</command> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
--- 1458,1464 ----
<para>
To revoke a privilege, use the fittingly named
! <xref linkend="sql-revoke"/> command:
<programlisting>
REVOKE ALL ON accounts FROM PUBLIC;
</programlisting>
*************** REVOKE ALL ON accounts FROM PUBLIC;
*** 1478,1483 ****
--- 1480,1983 ----
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 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 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 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 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 a 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 a
+ 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, 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
+ this 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.
+ </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 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
+ 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 112d962..952614a 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT has_function_privilege('joeuser',
*** 16932,16953 ****
<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>.
</para>
<indexterm>
- <primary>aclitem</primary>
- </indexterm>
- <indexterm>
<primary>acldefault</primary>
</indexterm>
<indexterm>
--- 16932,16943 ----
<para>
<xref linkend="functions-aclitem-fn-table"/> shows the operators
! 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>acldefault</primary>
</indexterm>
<indexterm>
*************** SELECT has_function_privilege('joeuser',
*** 17015,17023 ****
<tbody>
<row>
<entry><literal><function>acldefault</function>(<parameter>type</parameter>,
! <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>
</row>
<row>
<entry><literal><function>aclexplode</function>(<parameter>aclitem[]</parameter>)</literal></entry>
--- 17005,17013 ----
<tbody>
<row>
<entry><literal><function>acldefault</function>(<parameter>type</parameter>,
! <parameter>ownerId</parameter>)</literal></entry>
<entry><type>aclitem[]</type></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>
*************** SELECT has_function_privilege('joeuser',
*** 17034,17049 ****
</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
'c' for <literal>COLUMN</literal>,
! 'r' for relation-like objects such as <literal>TABLE</literal> or <literal>VIEW</literal>,
's' for <literal>SEQUENCE</literal>,
'd' for <literal>DATABASE</literal>,
'f' for <literal>FUNCTION</literal> or <literal>PROCEDURE</literal>,
--- 17024,17037 ----
</table>
<para>
! <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 <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>,
*************** SELECT has_function_privilege('joeuser',
*** 17053,17064 ****
't' for <literal>TABLESPACE</literal>,
'F' for <literal>FOREIGN DATA WRAPPER</literal>,
'S' for <literal>FOREIGN SERVER</literal>,
'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>,
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>.
--- 17041,17053 ----
'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 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>.
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index 0c09f1d..583f65f 100644
*** a/doc/src/sgml/ref/alter_default_privileges.sgml
--- b/doc/src/sgml/ref/alter_default_privileges.sgml
*************** REVOKE [ GRANT OPTION FOR ]
*** 112,118 ****
</para>
<para>
! As explained under <xref linkend="sql-grant"/>,
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
--- 112,118 ----
</para>
<para>
! 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
*************** REVOKE [ GRANT OPTION FOR ]
*** 173,181 ****
<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"/>.
</para>
<para>
--- 173,180 ----
<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 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 06be04e..4072543 100644
*** a/doc/src/sgml/ref/create_function.sgml
--- b/doc/src/sgml/ref/create_function.sgml
*************** $$ LANGUAGE plpgsql
*** 761,767 ****
<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
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
--- 761,767 ----
<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="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 ff64c7a..d38c5e2 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*************** GRANT <replaceable class="parameter">rol
*** 157,386 ****
</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>
<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.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><literal>USAGE</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.
</para>
</listitem>
</varlistentry>
--- 157,190 ----
</para>
<para>
The possible privileges are:
<variablelist>
<varlistentry>
<term><literal>SELECT</literal></term>
<term><literal>INSERT</literal></term>
<term><literal>UPDATE</literal></term>
<term><literal>DELETE</literal></term>
<term><literal>TRUNCATE</literal></term>
<term><literal>REFERENCES</literal></term>
<term><literal>TRIGGER</literal></term>
<term><literal>CREATE</literal></term>
<term><literal>CONNECT</literal></term>
<term><literal>TEMPORARY</literal></term>
<term><literal>EXECUTE</literal></term>
+ <term><literal>USAGE</literal></term>
<listitem>
<para>
! Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><literal>TEMP</literal></term>
<listitem>
<para>
! Alternative spelling for <literal>TEMPORARY</literal>.
</para>
</listitem>
</varlistentry>
*************** GRANT <replaceable class="parameter">rol
*** 389,395 ****
<term><literal>ALL PRIVILEGES</literal></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.
--- 193,199 ----
<term><literal>ALL PRIVILEGES</literal></term>
<listitem>
<para>
! 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.
*************** GRANT <replaceable class="parameter">rol
*** 397,405 ****
</listitem>
</varlistentry>
</variablelist>
! The privileges required by other commands are listed on the
! reference page of the respective command.
</para>
</refsect2>
--- 201,215 ----
</listitem>
</varlistentry>
</variablelist>
+ </para>
! <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>
</refsect2>
*************** GRANT <replaceable class="parameter">rol
*** 520,598 ****
</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.
</para>
</refsect1>
--- 330,337 ----
</para>
<para>
! 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 47714eb..6c76cf2 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 1324,1331 ****
<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"/>.
</para>
</listitem>
</varlistentry>
--- 1324,1331 ----
<para>
The <xref linkend="sql-alterdefaultprivileges"/> command is used to set
default access privileges. The meaning of the
! privilege display is explained in
! <xref linkend="ddl-priv"/>.
</para>
</listitem>
</varlistentry>
*************** testdb=>
*** 1372,1378 ****
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.
</para>
</listitem>
</varlistentry>
--- 1372,1378 ----
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 access privileges, type, version, options, and description.
</para>
</listitem>
</varlistentry>
*************** testdb=>
*** 1425,1432 ****
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.
</para>
</listitem>
</varlistentry>
--- 1425,1432 ----
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 access privileges, options, and description of the
! foreign-data wrapper are also shown.
</para>
</listitem>
</varlistentry>
*************** testdb=>
*** 1639,1646 ****
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"/>.
</para>
</listitem>
</varlistentry>
--- 1639,1646 ----
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 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 5317f8c..e96d45e 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*************** REVOKE [ ADMIN OPTION FOR ]
*** 178,191 ****
<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
--- 178,183 ----
*************** REVOKE [ ADMIN OPTION FOR ]
*** 244,249 ****
--- 236,246 ----
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">
*************** REVOKE admins FROM joe;
*** 293,301 ****
<refsect1>
<title>See Also</title>
! <simpara>
! <xref linkend="sql-grant"/>
! </simpara>
</refsect1>
</refentry>
--- 290,299 ----
<refsect1>
<title>See Also</title>
! <simplelist type="inline">
! <member><xref linkend="sql-grant"/></member>
! <member><xref linkend="sql-alterdefaultprivileges"/></member>
! </simplelist>
</refsect1>
</refentry>
On 2018-Nov-30, Tom Lane wrote: > I feel if we're going to do anything, we should put a unified description > of privileges and aclitem-reading into section 5.6, and take that material > out of the various places where it lives now. Like the attached, in which > I failed to resist the temptation to wordsmith some stuff as well as move > it around. I looked at the psql manpage and the HTML rendering of section 5.6 and it all looks good to me. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>> I feel if we're going to do anything, we should put a unified description >> of privileges and aclitem-reading into section 5.6, and take that material >> out of the various places where it lives now. Like the attached, in which >> I failed to resist the temptation to wordsmith some stuff as well as move >> it around. > > I looked at the psql manpage and the HTML rendering of section 5.6 and > it all looks good to me. Indeed, this looks great, a precise and full description of privileges just in one place. -- Fabien.
Fabien COELHO <coelho@cri.ensmp.fr> writes:
>>> I feel if we're going to do anything, we should put a unified description
>>> of privileges and aclitem-reading into section 5.6, and take that material
>>> out of the various places where it lives now. Like the attached, in which
>>> I failed to resist the temptation to wordsmith some stuff as well as move
>>> it around.
>> I looked at the psql manpage and the HTML rendering of section 5.6 and
>> it all looks good to me.
> Indeed, this looks great, a precise and full description of privileges
> just in one place.
Pushed (with a little bit more tweaking).
regards, tom lane
>>> I looked at the psql manpage and the HTML rendering of section 5.6 and >>> it all looks good to me. > >> Indeed, this looks great, a precise and full description of privileges >> just in one place. > > Pushed (with a little bit more tweaking). Thanks for the rewrite, extensions, improvements and final push. -- Fabien.