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