On Sat, Jul 25, 2009 at 08:41:12PM -0400, Robert Haas wrote: > On Sat, Jul 25, 2009 at 8:39 PM, Joshua Tolley<eggyk...@gmail.com> wrote: > > On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote: > >> while writing some basic docs I found bug in dependency handling when > >> doing SET on object type that already had some default privileges. > >> Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT > >> OPTION behaves like REVOKE now). And there is also initial version of > >> those basic docs included (but you have to pardon my english as I didn't > >> pass it to Stephen for proofreading due to discovery of that bug). > > > > Immediately after concluding I was done with my review, I realized I'd > > completely forgotten to look at the docs. I've made a few changes based > > solely > > on my opinions of what sounds better and what's more consistent with the > > existing documentation. Do with them as you see fit. :) > > Did you intend to attach something to this email? > > ...Robert
Well, yes, now that you mention it :) Trying again... -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 34679d8..3eb92a4 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3130,6 +3130,70 @@
</sect1>
+ <sect1 id="catalog-pg-namespace-default-acl">
+ <title><structname>pg_namespace_default_acl</structname></title>
+
+ <indexterm zone="catalog-pg-namespace-default-acl">
+ <primary>pg_namespace_default_acl</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_namespace_default_acl</> stores default
+ privileges for newly created objects inside the schema.
+ </para>
+
+ <table>
+ <title><structname>pg_namespace</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>defaclnamespace</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
+ <entry>The OID of the namespace associated with this entry</entry>
+ </row>
+
+ <row>
+ <entry><structfield>defaclgrantobjtype</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>r</> = table, <literal>v</> = view,
+ <literal>f</> = function, <literal>S</> = sequence
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>defacllist</structfield></entry>
+ <entry><type>aclitem[]</type></entry>
+ <entry></entry>
+ <entry>
+ Access privileges that the object should have on creation.
+ This is NOT a mask, it's exactly what the object will get.
+ See
+ <xref linkend="sql-alterschema" endterm="sql-alterschema-title">,
+ <xref linkend="sql-grant" endterm="sql-grant-title"> and
+ <xref linkend="sql-revoke" endterm="sql-revoke-title">
+ for details.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+
<sect1 id="catalog-pg-opclass">
<title><structname>pg_opclass</structname></title>
diff --git a/doc/src/sgml/ref/alter_schema.sgml b/doc/src/sgml/ref/alter_schema.sgml
index 2458d19..62f4c2a 100644
--- a/doc/src/sgml/ref/alter_schema.sgml
+++ b/doc/src/sgml/ref/alter_schema.sgml
@@ -23,18 +23,46 @@ PostgreSQL documentation
<synopsis>
ALTER SCHEMA <replaceable>name</replaceable> RENAME TO <replaceable>newname</replaceable>
ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</replaceable>
+
+ALTER SCHEMA <replaceable>name</replaceable> { SET | ADD } DEFAULT PRIVILEGES { { ON default_privileges
+ TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] } [AND ...] } [...]
+
+where <replaceable class="PARAMETER">default_privileges</replaceable> is:
+
+{ { TABLE | VIEW } { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ [,...] | ALL [ PRIVILEGES ] } |
+ SEQUENCE { { USAGE | SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] } |
+ FUNCTION { EXECUTE | ALL [ PRIVILEGES ] } }
+
+ALTER SCHEMA <replaceable>name</replaceable> DROP DEFAULT PRIVILEGES { { ON drop_default_privileges
+ FROM { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] } [AND ...] } [...]
+
+where <replaceable class="PARAMETER">drop_default_privileges</replaceable> is:
+
+{ { TABLE | VIEW } [ GRANT OPTION FOR ]
+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ [,...] | ALL [ PRIVILEGES ] } |
+ SEQUENCE [ GRANT OPTION FOR ]
+ { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } |
+ FUNCTION [ GRANT OPTION FOR ]
+ { EXECUTE | ALL [ PRIVILEGES ] } }
</synopsis>
</refsynopsisdiv>
- <refsect1>
+ <refsect1 id="sql-alterschema-description">
<title>Description</title>
<para>
- <command>ALTER SCHEMA</command> changes the definition of a schema.
+ You must own the schema to use <command>ALTER SCHEMA</>.
</para>
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-description-schemadefinition">
+ <title>Change the definition of a schema</title>
+
<para>
- You must own the schema to use <command>ALTER SCHEMA</>.
To rename a schema you must also have the
<literal>CREATE</literal> privilege for the database.
To alter the owner, you must also be a direct or
@@ -42,7 +70,6 @@ ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</rep
<literal>CREATE</literal> privilege for the database.
(Note that superusers have all these privileges automatically.)
</para>
- </refsect1>
<refsect1>
<title>Parameters</title>
@@ -77,6 +104,100 @@ ALTER SCHEMA <replaceable>name</replaceable> OWNER TO <replaceable>newowner</rep
</listitem>
</varlistentry>
</variablelist>
+ </refsect2>
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-description-defaultprivileges">
+ <title>Change the default privileges for new objects</title>
+
+ <para>
+ The <command>ALTER SCHEMA</> <literal>{ SET | ADD | DROP }</> <literal>DEFAULT PRIVILEGES</> command
+ allows you to define privileges for newly created objects in the schema.
+ </para>
+
+ <para>
+ See the description of the <xref linkend="sql-grant" endterm="sql-grant-title"> and
+ <xref linkend="sql-revoke" endterm="sql-revoke-title"> commands for more detailed
+ explanation of privilege system and the meaning of the privilege types.
+ </para>
+
+ <para>
+ The possible commands are:
+
+ <variablelist>
+ <varlistentry>
+ <term>SET</term>
+ <listitem>
+ <para>
+ Replaces the current default privileges with the newly specified ones.
+ Behaves like <command>GRANT</> on object that had no privileges set before.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ADD</term>
+ <listitem>
+ <para>
+ Adds new default privileges. Behaves like standard <command>GRANT</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>DROP</term>
+ <listitem>
+ <para>
+ Removes specified privileges from defaults. Behaves like <command>REVOKE</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1 id="sql-alterschema-examples">
+ <title>Examples</title>
+
+ <para>
+ Grant select privilege for every new table in schema
+ <literal>public</literal> to everybody:
+
+<programlisting>
+ALTER SCHEMA public SET DEFAULT PRIVILEGES ON TABLE SELECT TO public;
+</programlisting>
+ </para>
+
+ <para>
+ Grant <literal>webuser</literal <literal>SELECT</> privileges on all
+ new tables created in the <literal>users</literal> schema. Also grant
+ <literal>INSERT</>, <literal>UPDATE</>, and <literal>DELETE</> privileges
+ to the <literal>admin</literal> user.
+
+<programlisting>
+ALTER SCHEMA users SET DEFAULT PRIVILEGES ON TABLE SELECT TO webuser, admin AND UPDATE, INSERT, DELETE TO admin;
+</programlisting>
+ </para>
+
+ <para>
+ Give user <literal>webuser</literal> <literal>SELECT</> privilege on all new
+ tables, views, and sequences, and <literal>EXECUTE</> on all new functions in
+ the <literal>public</literal> schema.
+ Allow the <literal>admin</literal> to not only select but also
+ change the contents of new tables in the <literal>public</> schema, and grant those permissions
+ to other users. The <literal>admin</> user will also be allowed to select from new views,
+ execute new functions, and read new sequences like <literal>webuser</literal>.
+ Finally, allow <literal>admin</> to update new sequences.
+
+<programlisting>
+ALTER SCHEMA public SET DEFAULT PRIVILEGES
+ ON TABLE SELECT TO webuser, admin AND UPDATE, INSERT, DELETE TO admin WITH GRANT OPTION
+ ON VIEW SELECT TO webuser, admin
+ ON FUNCTION EXECUTE TO webuser, admin
+ ON SEQUENCE USAGE, SELECT TO webuser AND ALL PRIVILEGES TO admin;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..c60cc4b 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -22,8 +22,8 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
- [,...] | ALL [ PRIVILEGES ] }
- ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
+ [,...] | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
+ ON [ TABLE | VIEW ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
@@ -32,7 +32,7 @@ GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <replaceable class="PARAMETE
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
- [,...] | ALL [ PRIVILEGES ] }
+ [,...] | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
ON SEQUENCE <replaceable class="PARAMETER">sequencename</replaceable> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -48,7 +48,7 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER <replaceable>servername</> [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
-GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+GRANT { EXECUTE | ALL [ PRIVILEGES ] | DEFAULT PRIVILEGES }
ON FUNCTION <replaceable>funcname</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) [, ...]
TO { [ GROUP ] <replaceable class="PARAMETER">rolename</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -132,6 +132,8 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
include granting some privileges to <literal>PUBLIC</literal>.
The default is no public access for tables, columns, schemas, and
tablespaces;
+ For tables, views, functions and sequences the initial default privileges
+ can also be changed using <command>ALTER SCHEMA</command> command;
<literal>CONNECT</> privilege and <literal>TEMP</> table creation privilege
for databases;
<literal>EXECUTE</> privilege for functions; and
@@ -339,6 +341,21 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>DEFAULT PRIVILEGES</term>
+ <listitem>
+ <para>
+ Replace current privileges with the ones specified using
+ <xref linkend="sql-alterschema" endterm="sql-alterschema-title">.
+ The <literal>WITH GRANT OPTION</literal> parameter is not applicable
+ because it is copied from default privileges.
+ Note: this can actually <emphasis role="bold">revoke</emphasis> some
+ privileges because it clears all existing privileges object has and
+ replaces them with the default ones for the schema in which this object lives.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
The privileges required by other commands are listed on the
signature.asc
Description: Digital signature
