1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>ALTER DEFAULT PRIVILEGES</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
16
HREF="sql-commands.html"><LINK
18
TITLE="ALTER DATABASE"
19
HREF="sql-alterdatabase.html"><LINK
22
HREF="sql-alterdomain.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="ALTER DATABASE"
57
HREF="sql-alterdatabase.html"
66
TITLE="ALTER DATABASE"
67
HREF="sql-alterdatabase.html"
81
HREF="sql-alterdomain.html"
90
HREF="sql-alterdomain.html"
101
NAME="SQL-ALTERDEFAULTPRIVILEGES"
103
>ALTER DEFAULT PRIVILEGES</H1
111
>ALTER DEFAULT PRIVILEGES -- define default access privileges</DIV
113
CLASS="REFSYNOPSISDIV"
121
>ALTER DEFAULT PRIVILEGES
122
[ FOR { ROLE | USER } <TT
137
>abbreviated_grant_or_revoke</I
148
>abbreviated_grant_or_revoke</I
154
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
155
[, ...] | ALL [ PRIVILEGES ] }
162
> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
164
GRANT { { USAGE | SELECT | UPDATE }
165
[, ...] | ALL [ PRIVILEGES ] }
172
> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
174
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
181
> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
183
REVOKE [ GRANT OPTION FOR ]
184
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
185
[, ...] | ALL [ PRIVILEGES ] }
193
[ CASCADE | RESTRICT ]
195
REVOKE [ GRANT OPTION FOR ]
196
{ { USAGE | SELECT | UPDATE }
197
[, ...] | ALL [ PRIVILEGES ] }
205
[ CASCADE | RESTRICT ]
207
REVOKE [ GRANT OPTION FOR ]
208
{ EXECUTE | ALL [ PRIVILEGES ] }
216
[ CASCADE | RESTRICT ]</PRE
221
NAME="SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION"
228
>ALTER DEFAULT PRIVILEGES</TT
229
> allows you to set the privileges
230
that will be applied to objects created in the future. (It does not
231
affect privileges assigned to already-existing objects.) Currently,
232
only the privileges for tables (including views and foreign tables),
233
sequences, and functions can be altered.
236
> You can change default privileges only for objects that will be created by
237
yourself or by roles that you are a member of. The privileges can be set
238
globally (i.e., for all objects created in the current database),
239
or just for objects created in specified schemas. Default privileges
240
that are specified per-schema are added to whatever the global default
241
privileges are for the particular object type.
244
> As explained under <A
245
HREF="sql-grant.html"
248
the default privileges for any object type normally grant all grantable
249
permissions to the object owner, and may grant some privileges to
253
> as well. However, this behavior can be changed by
254
altering the global default privileges with
257
>ALTER DEFAULT PRIVILEGES</TT
281
> The name of an existing role of which the current role is a member.
285
> is omitted, the current role is assumed.
297
> The name of an existing schema. Each <TT
306
> privileges for each specified schema.
310
> is omitted, the global default privileges
323
> The name of an existing role to grant or revoke privileges for.
324
This parameter, and all the other parameters in
328
>abbreviated_grant_or_revoke</I
331
act as described under
333
HREF="sql-grant.html"
337
HREF="sql-revoke.html"
340
except that one is setting permissions for a whole class of objects
341
rather than specific named objects.
351
NAME="SQL-ALTERDEFAULTPRIVILEGES-NOTES"
366
to obtain information about existing assignments of default privileges.
367
The meaning of the privilege values is the same as explained for
373
HREF="sql-grant.html"
378
> If you wish to drop a role for which the default privileges have been
379
altered, it is necessary to reverse the changes in its default privileges
383
> to get rid of the default privileges entry
390
NAME="SQL-ALTERDEFAULTPRIVILEGES-EXAMPLES"
395
> Grant SELECT privilege to everyone for all tables (and views) you
396
subsequently create in schema <TT
403
> to INSERT into them too:
406
CLASS="PROGRAMLISTING"
407
>ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
408
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;</PRE
412
> Undo the above, so that subsequently-created tables won't have any
413
more permissions than normal:
416
CLASS="PROGRAMLISTING"
417
>ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
418
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;</PRE
422
> Remove the public EXECUTE permission that is normally granted on functions,
423
for all functions subsequently created by role <TT
429
CLASS="PROGRAMLISTING"
430
>ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;</PRE
444
>ALTER DEFAULT PRIVILEGES</TT
445
> statement in the SQL
457
HREF="sql-grant.html"
460
HREF="sql-revoke.html"
468
SUMMARY="Footer navigation table"
479
HREF="sql-alterdatabase.html"
497
HREF="sql-alterdomain.html"
513
HREF="sql-commands.html"
b'\\ No newline at end of file'