| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/alter_default_privileges.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-alterdefaultprivileges"> |
| 7 |
<indexterm zone="sql-alterdefaultprivileges"> |
| 8 |
<primary>ALTER DEFAULT PRIVILEGES</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>ALTER DEFAULT PRIVILEGES</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>ALTER DEFAULT PRIVILEGES</refname> |
| 19 |
<refpurpose>define default access privileges</refpurpose> |
| 20 |
</refnamediv> |
| 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 | MAINTAIN } |
| 32 |
[, ...] | ALL [ PRIVILEGES ] } |
| 33 |
ON TABLES |
| 34 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 35 |
|
| 36 |
GRANT { { USAGE | SELECT | UPDATE } |
| 37 |
[, ...] | ALL [ PRIVILEGES ] } |
| 38 |
ON SEQUENCES |
| 39 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 40 |
|
| 41 |
GRANT { EXECUTE | ALL [ PRIVILEGES ] } |
| 42 |
ON { FUNCTIONS | ROUTINES } |
| 43 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 44 |
|
| 45 |
GRANT { USAGE | ALL [ PRIVILEGES ] } |
| 46 |
ON TYPES |
| 47 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 48 |
|
| 49 |
GRANT { { USAGE | CREATE } |
| 50 |
[, ...] | ALL [ PRIVILEGES ] } |
| 51 |
ON SCHEMAS |
| 52 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 53 |
|
| 54 |
GRANT { { SELECT | UPDATE } |
| 55 |
[, ...] | ALL [ PRIVILEGES ] } |
| 56 |
ON LARGE OBJECTS |
| 57 |
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
| 58 |
|
| 59 |
REVOKE [ GRANT OPTION FOR ] |
| 60 |
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN } |
| 61 |
[, ...] | ALL [ PRIVILEGES ] } |
| 62 |
ON TABLES |
| 63 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 64 |
[ CASCADE | RESTRICT ] |
| 65 |
|
| 66 |
REVOKE [ GRANT OPTION FOR ] |
| 67 |
{ { USAGE | SELECT | UPDATE } |
| 68 |
[, ...] | ALL [ PRIVILEGES ] } |
| 69 |
ON SEQUENCES |
| 70 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 71 |
[ CASCADE | RESTRICT ] |
| 72 |
|
| 73 |
REVOKE [ GRANT OPTION FOR ] |
| 74 |
{ EXECUTE | ALL [ PRIVILEGES ] } |
| 75 |
ON { FUNCTIONS | ROUTINES } |
| 76 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 77 |
[ CASCADE | RESTRICT ] |
| 78 |
|
| 79 |
REVOKE [ GRANT OPTION FOR ] |
| 80 |
{ USAGE | ALL [ PRIVILEGES ] } |
| 81 |
ON TYPES |
| 82 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 83 |
[ CASCADE | RESTRICT ] |
| 84 |
|
| 85 |
REVOKE [ GRANT OPTION FOR ] |
| 86 |
{ { USAGE | CREATE } |
| 87 |
[, ...] | ALL [ PRIVILEGES ] } |
| 88 |
ON SCHEMAS |
| 89 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 90 |
[ CASCADE | RESTRICT ] |
| 91 |
|
| 92 |
REVOKE [ GRANT OPTION FOR ] |
| 93 |
{ { SELECT | UPDATE } |
| 94 |
[, ...] | ALL [ PRIVILEGES ] } |
| 95 |
ON LARGE OBJECTS |
| 96 |
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] |
| 97 |
[ CASCADE | RESTRICT ] |
| 98 |
</synopsis> |
| 99 |
</refsynopsisdiv> |
| 100 |
|
| 101 |
<refsect1 id="sql-alterdefaultprivileges-description"> |
| 102 |
<title>Description</title> |
| 103 |
|
| 104 |
<para> |
| 105 |
<command>ALTER DEFAULT PRIVILEGES</command> allows you to set the |
| 106 |
privileges that will be applied to objects created in the future. |
| 107 |
(It does not affect privileges assigned to already-existing objects.) |
| 108 |
Privileges can be set globally (i.e., for all objects created in the |
| 109 |
current database), or just for objects created in specified schemas. |
| 110 |
</para> |
| 111 |
|
| 112 |
<para> |
| 113 |
While you can change your own default privileges and the defaults of |
| 114 |
roles that you are a member of, at object creation time, new object |
| 115 |
permissions are only affected by the default privileges of the current |
| 116 |
role, and are not inherited from any roles in which the current role |
| 117 |
is a member. |
| 118 |
</para> |
| 119 |
|
| 120 |
<para> |
| 121 |
As explained in <xref linkend="ddl-priv"/>, |
| 122 |
the default privileges for any object type normally grant all grantable |
| 123 |
permissions to the object owner, and may grant some privileges to |
| 124 |
<literal>PUBLIC</literal> as well. However, this behavior can be changed by |
| 125 |
altering the global default privileges with |
| 126 |
<command>ALTER DEFAULT PRIVILEGES</command>. |
| 127 |
</para> |
| 128 |
|
| 129 |
<para> |
| 130 |
Currently, |
| 131 |
only the privileges for schemas, tables (including views and foreign |
| 132 |
tables), sequences, functions, types (including domains), and large objects |
| 133 |
can be altered. For this command, functions include aggregates and procedures. |
| 134 |
The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are |
| 135 |
equivalent in this command. (<literal>ROUTINES</literal> is preferred |
| 136 |
going forward as the standard term for functions and procedures taken |
| 137 |
together. In earlier PostgreSQL releases, only the |
| 138 |
word <literal>FUNCTIONS</literal> was allowed. It is not possible to set |
| 139 |
default privileges for functions and procedures separately.) |
| 140 |
</para> |
| 141 |
|
| 142 |
<para> |
| 143 |
Default privileges that are specified per-schema are added to whatever |
| 144 |
the global default privileges are for the particular object type. |
| 145 |
This means you cannot revoke privileges per-schema if they are granted |
| 146 |
globally (either by default, or according to a previous <command>ALTER |
| 147 |
DEFAULT PRIVILEGES</command> command that did not specify a schema). |
| 148 |
Per-schema <literal>REVOKE</literal> is only useful to reverse the |
| 149 |
effects of a previous per-schema <literal>GRANT</literal>. |
| 150 |
</para> |
| 151 |
|
| 152 |
<refsect2> |
| 153 |
<title>Parameters</title> |
| 154 |
|
| 155 |
<variablelist> |
| 156 |
<varlistentry> |
| 157 |
<term><replaceable>target_role</replaceable></term> |
| 158 |
<listitem> |
| 159 |
<para> |
| 160 |
Change default privileges for objects created by the |
| 161 |
<replaceable>target_role</replaceable>, or the current |
| 162 |
role if unspecified. |
| 163 |
</para> |
| 164 |
</listitem> |
| 165 |
</varlistentry> |
| 166 |
|
| 167 |
<varlistentry> |
| 168 |
<term><replaceable>schema_name</replaceable></term> |
| 169 |
<listitem> |
| 170 |
<para> |
| 171 |
The name of an existing schema. If specified, the default privileges |
| 172 |
are altered for objects later created in that schema. |
| 173 |
If <literal>IN SCHEMA</literal> is omitted, the global default privileges |
| 174 |
are altered. |
| 175 |
<literal>IN SCHEMA</literal> is not allowed when setting privileges |
| 176 |
for schemas and large objects, since schemas can't be nested and |
| 177 |
large objects don't belong to a schema. |
| 178 |
</para> |
| 179 |
</listitem> |
| 180 |
</varlistentry> |
| 181 |
|
| 182 |
<varlistentry> |
| 183 |
<term><replaceable>role_name</replaceable></term> |
| 184 |
<listitem> |
| 185 |
<para> |
| 186 |
The name of an existing role to grant or revoke privileges for. |
| 187 |
This parameter, and all the other parameters in |
| 188 |
<replaceable class="parameter">abbreviated_grant_or_revoke</replaceable>, |
| 189 |
act as described under |
| 190 |
<xref linkend="sql-grant"/> or |
| 191 |
<xref linkend="sql-revoke"/>, |
| 192 |
except that one is setting permissions for a whole class of objects |
| 193 |
rather than specific named objects. |
| 194 |
</para> |
| 195 |
</listitem> |
| 196 |
</varlistentry> |
| 197 |
</variablelist> |
| 198 |
</refsect2> |
| 199 |
</refsect1> |
| 200 |
|
| 201 |
<refsect1 id="sql-alterdefaultprivileges-notes"> |
| 202 |
<title>Notes</title> |
| 203 |
|
| 204 |
<para> |
| 205 |
Use <xref linkend="app-psql"/>'s <command>\ddp</command> command |
| 206 |
to obtain information about existing assignments of default privileges. |
| 207 |
The meaning of the privilege display is the same as explained for |
| 208 |
<command>\dp</command> in <xref linkend="ddl-priv"/>. |
| 209 |
</para> |
| 210 |
|
| 211 |
<para> |
| 212 |
If you wish to drop a role for which the default privileges have been |
| 213 |
altered, it is necessary to reverse the changes in its default privileges |
| 214 |
or use <command>DROP OWNED BY</command> to get rid of the default privileges entry |
| 215 |
for the role. |
| 216 |
</para> |
| 217 |
</refsect1> |
| 218 |
|
| 219 |
<refsect1 id="sql-alterdefaultprivileges-examples"> |
| 220 |
<title>Examples</title> |
| 221 |
|
| 222 |
<para> |
| 223 |
Grant SELECT privilege to everyone for all tables (and views) you |
| 224 |
subsequently create in schema <literal>myschema</literal>, and allow |
| 225 |
role <literal>webuser</literal> to INSERT into them too: |
| 226 |
|
| 227 |
<programlisting> |
| 228 |
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC; |
| 229 |
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser; |
| 230 |
</programlisting> |
| 231 |
</para> |
| 232 |
|
| 233 |
<para> |
| 234 |
Undo the above, so that subsequently-created tables won't have any |
| 235 |
more permissions than normal: |
| 236 |
|
| 237 |
<programlisting> |
| 238 |
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC; |
| 239 |
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser; |
| 240 |
</programlisting> |
| 241 |
</para> |
| 242 |
|
| 243 |
<para> |
| 244 |
Remove the public EXECUTE permission that is normally granted on functions, |
| 245 |
for all functions subsequently created by role <literal>admin</literal>: |
| 246 |
<programlisting> |
| 247 |
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; |
| 248 |
</programlisting> |
| 249 |
Note however that you <emphasis>cannot</emphasis> accomplish that effect |
| 250 |
with a command limited to a single schema. This command has no effect, |
| 251 |
unless it is undoing a matching <literal>GRANT</literal>: |
| 252 |
<programlisting> |
| 253 |
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; |
| 254 |
</programlisting> |
| 255 |
That's because per-schema default privileges can only add privileges to |
| 256 |
the global setting, not remove privileges granted by it. |
| 257 |
</para> |
| 258 |
</refsect1> |
| 259 |
|
| 260 |
<refsect1> |
| 261 |
<title>Compatibility</title> |
| 262 |
|
| 263 |
<para> |
| 264 |
There is no <command>ALTER DEFAULT PRIVILEGES</command> statement in the SQL |
| 265 |
standard. |
| 266 |
</para> |
| 267 |
</refsect1> |
| 268 |
|
| 269 |
<refsect1> |
| 270 |
<title>See Also</title> |
| 271 |
|
| 272 |
<simplelist type="inline"> |
| 273 |
<member><xref linkend="sql-grant"/></member> |
| 274 |
<member><xref linkend="sql-revoke"/></member> |
| 275 |
</simplelist> |
| 276 |
</refsect1> |
| 277 |
|
| 278 |
</refentry> |