Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit249724c

Browse files
committed
Create an ALTER DEFAULT PRIVILEGES command, which allows users to adjust
the privileges that will be applied to subsequently-created objects.Such adjustments are always per owning role, and can be restricted to objectscreated in particular schemas too. A notable benefit is that users canoverride the traditional default privilege settings, eg, the PUBLIC EXECUTEprivilege traditionally granted by default for functions.Petr Jelinek
1 parent41f89e3 commit249724c

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

48 files changed

+2240
-180
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 93 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.207 2009/09/22 23:43:37 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.208 2009/10/05 19:24:32 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -113,6 +113,11 @@
113113
<entry>databases within this database cluster</entry>
114114
</row>
115115

116+
<row>
117+
<entry><link linkend="catalog-pg-default-acl"><structname>pg_default_acl</structname></link></entry>
118+
<entry>default privileges for object types</entry>
119+
</row>
120+
116121
<row>
117122
<entry><link linkend="catalog-pg-depend"><structname>pg_depend</structname></link></entry>
118123
<entry>dependencies between database objects</entry>
@@ -2155,6 +2160,93 @@
21552160
</sect1>
21562161

21572162

2163+
<sect1 id="catalog-pg-default-acl">
2164+
<title><structname>pg_default_acl</structname></title>
2165+
2166+
<indexterm zone="catalog-pg-default-acl">
2167+
<primary>pg_default_acl</primary>
2168+
</indexterm>
2169+
2170+
<para>
2171+
The catalog <structname>pg_default_acl</> stores initial
2172+
privileges to be assigned to newly created objects.
2173+
</para>
2174+
2175+
<table>
2176+
<title><structname>pg_default_acl</> Columns</title>
2177+
2178+
<tgroup cols="4">
2179+
<thead>
2180+
<row>
2181+
<entry>Name</entry>
2182+
<entry>Type</entry>
2183+
<entry>References</entry>
2184+
<entry>Description</entry>
2185+
</row>
2186+
</thead>
2187+
2188+
<tbody>
2189+
<row>
2190+
<entry><structfield>defaclrole</structfield></entry>
2191+
<entry><type>oid</type></entry>
2192+
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.oid</literal></entry>
2193+
<entry>The OID of the role associated with this entry</entry>
2194+
</row>
2195+
2196+
<row>
2197+
<entry><structfield>defaclnamespace</structfield></entry>
2198+
<entry><type>oid</type></entry>
2199+
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.oid</literal></entry>
2200+
<entry>The OID of the namespace associated with this entry,
2201+
or 0 if none</entry>
2202+
</row>
2203+
2204+
<row>
2205+
<entry><structfield>defaclobjtype</structfield></entry>
2206+
<entry><type>char</type></entry>
2207+
<entry></entry>
2208+
<entry>
2209+
Type of object this entry is for:
2210+
<literal>r</> = relation (table, view),
2211+
<literal>S</> = sequence,
2212+
<literal>f</> = function
2213+
</entry>
2214+
</row>
2215+
2216+
<row>
2217+
<entry><structfield>defaclacl</structfield></entry>
2218+
<entry><type>aclitem[]</type></entry>
2219+
<entry></entry>
2220+
<entry>
2221+
Access privileges that this type of object should have on creation
2222+
</entry>
2223+
</row>
2224+
</tbody>
2225+
</tgroup>
2226+
</table>
2227+
2228+
<para>
2229+
A <structname>pg_default_acl</> entry shows the initial privileges to
2230+
be assigned to an object belonging to the indicated user. There are
2231+
currently two types of entry: <quote>global</> entries with
2232+
<structfield>defaclnamespace</> = 0, and <quote>per-schema</> entries
2233+
that reference a particular schema. If a global entry is present then
2234+
it <emphasis>overrides</> the normal hard-wired default privileges
2235+
for the object type. A per-schema entry, if present, represents privileges
2236+
to be <emphasis>added to</> the global or hard-wired default privileges.
2237+
</para>
2238+
2239+
<para>
2240+
Note that when an ACL entry in another catalog is NULL, it is taken
2241+
to represent the hard-wired default privileges for its object,
2242+
<emphasis>not</> whatever might be in <structname>pg_default_acl</>
2243+
at the moment. <structname>pg_default_acl</> is only consulted during
2244+
object creation.
2245+
</para>
2246+
2247+
</sect1>
2248+
2249+
21582250
<sect1 id="catalog-pg-depend">
21592251
<title><structname>pg_depend</structname></title>
21602252

‎doc/src/sgml/ref/allfiles.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.75 2009/09/22 23:43:37 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.76 2009/10/05 19:24:33 tgl Exp $
33
PostgreSQL documentation
44
Complete list of usable sgml source files in this directory.
55
-->
@@ -9,6 +9,7 @@ Complete list of usable sgml source files in this directory.
99
<!entity alterAggregate system "alter_aggregate.sgml">
1010
<!entity alterConversion system "alter_conversion.sgml">
1111
<!entity alterDatabase system "alter_database.sgml">
12+
<!entity alterDefaultPrivileges system "alter_default_privileges.sgml">
1213
<!entity alterDomain system "alter_domain.sgml">
1314
<!entity alterForeignDataWrapper system "alter_foreign_data_wrapper.sgml">
1415
<!entity alterFunction system "alter_function.sgml">
Lines changed: 211 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,211 @@
1+
<!--
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_default_privileges.sgml,v 1.1 2009/10/05 19:24:33 tgl Exp $
3+
PostgreSQL documentation
4+
-->
5+
6+
<refentry id="SQL-ALTERDEFAULTPRIVILEGES">
7+
<refmeta>
8+
<refentrytitle id="SQL-ALTERDEFAULTPRIVILEGES-TITLE">ALTER DEFAULT PRIVILEGES</refentrytitle>
9+
<manvolnum>7</manvolnum>
10+
<refmiscinfo>SQL - Language Statements</refmiscinfo>
11+
</refmeta>
12+
13+
<refnamediv>
14+
<refname>ALTER DEFAULT PRIVILEGES</refname>
15+
<refpurpose>define default access privileges</refpurpose>
16+
</refnamediv>
17+
18+
<indexterm zone="sql-alterdefaultprivileges">
19+
<primary>ALTER DEFAULT PRIVILEGES</primary>
20+
</indexterm>
21+
22+
<refsynopsisdiv>
23+
<synopsis>
24+
ALTER DEFAULT PRIVILEGES
25+
[ FOR { ROLE | USER } <replaceable>target_role</replaceable> [, ...] ]
26+
[ IN SCHEMA <replaceable>schema_name</replaceable> [, ...] ]
27+
<replaceable class="parameter">abbreviated_grant_or_revoke</replaceable>
28+
29+
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
30+
31+
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
32+
[,...] | ALL [ PRIVILEGES ] }
33+
ON TABLE
34+
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
35+
36+
GRANT { { USAGE | SELECT | UPDATE }
37+
[,...] | ALL [ PRIVILEGES ] }
38+
ON SEQUENCE
39+
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
40+
41+
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
42+
ON FUNCTION
43+
TO { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
44+
45+
REVOKE [ GRANT OPTION FOR ]
46+
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
47+
[,...] | ALL [ PRIVILEGES ] }
48+
ON TABLE
49+
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
50+
[ CASCADE | RESTRICT ]
51+
52+
REVOKE [ GRANT OPTION FOR ]
53+
{ { USAGE | SELECT | UPDATE }
54+
[,...] | ALL [ PRIVILEGES ] }
55+
ON SEQUENCE
56+
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
57+
[ CASCADE | RESTRICT ]
58+
59+
REVOKE [ GRANT OPTION FOR ]
60+
{ EXECUTE | ALL [ PRIVILEGES ] }
61+
ON FUNCTION
62+
FROM { [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable> | PUBLIC } [, ...]
63+
[ CASCADE | RESTRICT ]
64+
</synopsis>
65+
</refsynopsisdiv>
66+
67+
<refsect1 id="sql-alterdefaultprivileges-description">
68+
<title>Description</title>
69+
70+
<para>
71+
<command>ALTER DEFAULT PRIVILEGES</> allows you to set the privileges
72+
that will be applied to objects created in the future. (It does not
73+
affect privileges assigned to already-existing objects.) Currently,
74+
only the privileges for tables (including views), sequences, and
75+
functions can be altered.
76+
</para>
77+
78+
<para>
79+
You can change default privileges only for objects that will be created by
80+
yourself or by roles that you are a member of. The privileges can be set
81+
globally (i.e., for all objects created in the current database),
82+
or just for objects created in specified schemas. Default privileges
83+
that are specified per-schema are added to whatever the global default
84+
privileges are for the particular object type.
85+
</para>
86+
87+
<para>
88+
As explained under <xref linkend="sql-grant" endterm="sql-grant-title">,
89+
the default privileges for any object type normally grant all grantable
90+
permissions to the object owner, and may grant some privileges to
91+
<literal>PUBLIC</> as well. However, this behavior can be changed by
92+
altering the global default privileges with
93+
<command>ALTER DEFAULT PRIVILEGES</>.
94+
</para>
95+
96+
<refsect2>
97+
<title>Parameters</title>
98+
99+
<variablelist>
100+
<varlistentry>
101+
<term><replaceable>target_role</replaceable></term>
102+
<listitem>
103+
<para>
104+
The name of an existing role of which the current role is a member.
105+
If <literal>FOR ROLE</> is omitted, the current role is assumed.
106+
</para>
107+
</listitem>
108+
</varlistentry>
109+
110+
<varlistentry>
111+
<term><replaceable>schema_name</replaceable></term>
112+
<listitem>
113+
<para>
114+
The name of an existing schema. Each <replaceable>target_role</>
115+
must have <literal>CREATE</> privileges for each specified schema.
116+
If <literal>IN SCHEMA</> is omitted, the global default privileges
117+
are altered.
118+
</para>
119+
</listitem>
120+
</varlistentry>
121+
122+
<varlistentry>
123+
<term><replaceable>role_name</replaceable></term>
124+
<listitem>
125+
<para>
126+
The name of an existing role to grant or revoke privileges for.
127+
This parameter, and all the other parameters in
128+
<replaceable class="parameter">abbreviated_grant_or_revoke</>,
129+
act as described under
130+
<xref linkend="sql-grant" endterm="sql-grant-title"> or
131+
<xref linkend="sql-revoke" endterm="sql-revoke-title">,
132+
except that one is setting permissions for a whole class of objects
133+
rather than specific named objects.
134+
</para>
135+
</listitem>
136+
</varlistentry>
137+
</variablelist>
138+
</refsect2>
139+
</refsect1>
140+
141+
<refsect1 id="sql-alterdefaultprivileges-notes">
142+
<title>Notes</title>
143+
144+
<para>
145+
Use <xref linkend="app-psql">'s <command>\ddp</command> command
146+
to obtain information about existing assignments of default privileges.
147+
The meaning of the privilege values is the same as explained for
148+
<command>\dp</command> under
149+
<xref linkend="sql-grant" endterm="sql-grant-title">.
150+
</para>
151+
152+
<para>
153+
If you wish to drop a role that has had its global default privileges
154+
altered, it is necessary to use <command>DROP OWNED BY</> first,
155+
to get rid of the default privileges entry for the role.
156+
</para>
157+
</refsect1>
158+
159+
<refsect1 id="sql-alterdefaultprivileges-examples">
160+
<title>Examples</title>
161+
162+
<para>
163+
Grant SELECT privilege to everyone for all tables (and views) you
164+
subsequently create in schema <literal>myschema</literal>, and allow
165+
role <literal>webuser</> to INSERT into them too:
166+
167+
<programlisting>
168+
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLE TO PUBLIC;
169+
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLE TO webuser;
170+
</programlisting>
171+
</para>
172+
173+
<para>
174+
Undo the above, so that subsequently-created tables won't have any
175+
more permissions than normal:
176+
177+
<programlisting>
178+
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLE FROM PUBLIC;
179+
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLE FROM webuser;
180+
</programlisting>
181+
</para>
182+
183+
<para>
184+
Remove the public EXECUTE permission that is normally granted on functions,
185+
for all functions subsequently created by role <literal>admin</>:
186+
187+
<programlisting>
188+
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTION FROM PUBLIC;
189+
</programlisting>
190+
</para>
191+
</refsect1>
192+
193+
<refsect1>
194+
<title>Compatibility</title>
195+
196+
<para>
197+
There is no <command>ALTER DEFAULT PRIVILEGES</command> statement in the SQL
198+
standard.
199+
</para>
200+
</refsect1>
201+
202+
<refsect1>
203+
<title>See Also</title>
204+
205+
<simplelist type="inline">
206+
<member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
207+
<member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
208+
</simplelist>
209+
</refsect1>
210+
211+
</refentry>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp