| 1 |
<!-- doc/src/sgml/user-manag.sgml --> |
| 2 |
|
| 3 |
<chapter id="user-manag"> |
| 4 |
<title>Database Roles</title> |
| 5 |
|
| 6 |
<para> |
| 7 |
<productname>PostgreSQL</productname> manages database access permissions |
| 8 |
using the concept of <firstterm>roles</firstterm>. A role can be thought of as |
| 9 |
either a database user, or a group of database users, depending on how |
| 10 |
the role is set up. Roles can own database objects (for example, tables |
| 11 |
and functions) and can assign privileges on those objects to other roles to |
| 12 |
control who has access to which objects. Furthermore, it is possible |
| 13 |
to grant <firstterm>membership</firstterm> in a role to another role, thus |
| 14 |
allowing the member role to use privileges assigned to another role. |
| 15 |
</para> |
| 16 |
|
| 17 |
<para> |
| 18 |
The concept of roles subsumes the concepts of <quote>users</quote> and |
| 19 |
<quote>groups</quote>. In <productname>PostgreSQL</productname> versions |
| 20 |
before 8.1, users and groups were distinct kinds of entities, but now |
| 21 |
there are only roles. Any role can act as a user, a group, or both. |
| 22 |
</para> |
| 23 |
|
| 24 |
<para> |
| 25 |
This chapter describes how to create and manage roles. |
| 26 |
More information about the effects of role privileges on various |
| 27 |
database objects can be found in <xref linkend="ddl-priv"/>. |
| 28 |
</para> |
| 29 |
|
| 30 |
<sect1 id="database-roles"> |
| 31 |
<title>Database Roles</title> |
| 32 |
|
| 33 |
<indexterm zone="database-roles"> |
| 34 |
<primary>role</primary> |
| 35 |
</indexterm> |
| 36 |
|
| 37 |
<indexterm zone="database-roles"> |
| 38 |
<primary>user</primary> |
| 39 |
</indexterm> |
| 40 |
|
| 41 |
<indexterm> |
| 42 |
<primary>CREATE ROLE</primary> |
| 43 |
</indexterm> |
| 44 |
|
| 45 |
<indexterm> |
| 46 |
<primary>DROP ROLE</primary> |
| 47 |
</indexterm> |
| 48 |
|
| 49 |
<para> |
| 50 |
Database roles are conceptually completely separate from |
| 51 |
operating system users. In practice it might be convenient to |
| 52 |
maintain a correspondence, but this is not required. Database roles |
| 53 |
are global across a database cluster installation (and not |
| 54 |
per individual database). To create a role use the <link |
| 55 |
linkend="sql-createrole"><command>CREATE ROLE</command></link> SQL command: |
| 56 |
<synopsis> |
| 57 |
CREATE ROLE <replaceable>name</replaceable>; |
| 58 |
</synopsis> |
| 59 |
<replaceable>name</replaceable> follows the rules for SQL |
| 60 |
identifiers: either unadorned without special characters, or |
| 61 |
double-quoted. (In practice, you will usually want to add additional |
| 62 |
options, such as <literal>LOGIN</literal>, to the command. More details appear |
| 63 |
below.) To remove an existing role, use the analogous |
| 64 |
<link linkend="sql-droprole"><command>DROP ROLE</command></link> command: |
| 65 |
<synopsis> |
| 66 |
DROP ROLE <replaceable>name</replaceable>; |
| 67 |
</synopsis> |
| 68 |
</para> |
| 69 |
|
| 70 |
<indexterm> |
| 71 |
<primary>createuser</primary> |
| 72 |
</indexterm> |
| 73 |
|
| 74 |
<indexterm> |
| 75 |
<primary>dropuser</primary> |
| 76 |
</indexterm> |
| 77 |
|
| 78 |
<para> |
| 79 |
For convenience, the programs <xref linkend="app-createuser"/> |
| 80 |
and <xref linkend="app-dropuser"/> are provided as wrappers |
| 81 |
around these SQL commands that can be called from the shell command |
| 82 |
line: |
| 83 |
<synopsis> |
| 84 |
createuser <replaceable>name</replaceable> |
| 85 |
dropuser <replaceable>name</replaceable> |
| 86 |
</synopsis> |
| 87 |
</para> |
| 88 |
|
| 89 |
<para> |
| 90 |
To determine the set of existing roles, examine the <structname>pg_roles</structname> |
| 91 |
system catalog, for example: |
| 92 |
<synopsis> |
| 93 |
SELECT rolname FROM pg_roles; |
| 94 |
</synopsis> |
| 95 |
or to see just those capable of logging in: |
| 96 |
<synopsis> |
| 97 |
SELECT rolname FROM pg_roles WHERE rolcanlogin; |
| 98 |
</synopsis> |
| 99 |
The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command |
| 100 |
is also useful for listing the existing roles. |
| 101 |
</para> |
| 102 |
|
| 103 |
<para> |
| 104 |
In order to bootstrap the database system, a freshly initialized |
| 105 |
system always contains one predefined login-capable role. This role |
| 106 |
is always a <quote>superuser</quote>, and it will have |
| 107 |
the same name as the operating system user that initialized the |
| 108 |
database cluster with <command>initdb</command> unless a different name |
| 109 |
is specified. This role is often named |
| 110 |
<literal>postgres</literal>. In order to create more roles you |
| 111 |
first have to connect as this initial role. |
| 112 |
</para> |
| 113 |
|
| 114 |
<para> |
| 115 |
Every connection to the database server is made using the name of some |
| 116 |
particular role, and this role determines the initial access privileges for |
| 117 |
commands issued in that connection. |
| 118 |
The role name to use for a particular database |
| 119 |
connection is indicated by the client that is initiating the |
| 120 |
connection request in an application-specific fashion. For example, |
| 121 |
the <command>psql</command> program uses the |
| 122 |
<option>-U</option> command line option to indicate the role to |
| 123 |
connect as. Many applications assume the name of the current |
| 124 |
operating system user by default (including |
| 125 |
<command>createuser</command> and <command>psql</command>). Therefore it |
| 126 |
is often convenient to maintain a naming correspondence between |
| 127 |
roles and operating system users. |
| 128 |
</para> |
| 129 |
|
| 130 |
<para> |
| 131 |
The set of database roles a given client connection can connect as |
| 132 |
is determined by the client authentication setup, as explained in |
| 133 |
<xref linkend="client-authentication"/>. (Thus, a client is not |
| 134 |
limited to connect as the role matching |
| 135 |
its operating system user, just as a person's login name |
| 136 |
need not match his or her real name.) Since the role |
| 137 |
identity determines the set of privileges available to a connected |
| 138 |
client, it is important to carefully configure privileges when setting up |
| 139 |
a multiuser environment. |
| 140 |
</para> |
| 141 |
</sect1> |
| 142 |
|
| 143 |
<sect1 id="role-attributes"> |
| 144 |
<title>Role Attributes</title> |
| 145 |
|
| 146 |
<para> |
| 147 |
A database role can have a number of attributes that define its |
| 148 |
privileges and interact with the client authentication system. |
| 149 |
|
| 150 |
<variablelist> |
| 151 |
<varlistentry> |
| 152 |
<term>login privilege<indexterm><primary>login privilege</primary></indexterm></term> |
| 153 |
<listitem> |
| 154 |
<para> |
| 155 |
Only roles that have the <literal>LOGIN</literal> attribute can be used |
| 156 |
as the initial role name for a database connection. A role with |
| 157 |
the <literal>LOGIN</literal> attribute can be considered the same |
| 158 |
as a <quote>database user</quote>. To create a role with login privilege, |
| 159 |
use either: |
| 160 |
<programlisting> |
| 161 |
CREATE ROLE <replaceable>name</replaceable> LOGIN; |
| 162 |
CREATE USER <replaceable>name</replaceable>; |
| 163 |
</programlisting> |
| 164 |
(<command>CREATE USER</command> is equivalent to <command>CREATE ROLE</command> |
| 165 |
except that <command>CREATE USER</command> includes <literal>LOGIN</literal> by |
| 166 |
default, while <command>CREATE ROLE</command> does not.) |
| 167 |
</para> |
| 168 |
</listitem> |
| 169 |
</varlistentry> |
| 170 |
|
| 171 |
<varlistentry> |
| 172 |
<term>superuser status<indexterm><primary>superuser</primary></indexterm></term> |
| 173 |
<listitem> |
| 174 |
<para> |
| 175 |
A database superuser bypasses all permission checks, except the right |
| 176 |
to log in. This is a dangerous privilege and should not be used |
| 177 |
carelessly; it is best to do most of your work as a role that is not a |
| 178 |
superuser. To create a new database superuser, use <literal>CREATE |
| 179 |
ROLE <replaceable>name</replaceable> SUPERUSER</literal>. You must do |
| 180 |
this as a role that is already a superuser. |
| 181 |
</para> |
| 182 |
</listitem> |
| 183 |
</varlistentry> |
| 184 |
|
| 185 |
<varlistentry> |
| 186 |
<term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term> |
| 187 |
<listitem> |
| 188 |
<para> |
| 189 |
A role must be explicitly given permission to create databases |
| 190 |
(except for superusers, since those bypass all permission |
| 191 |
checks). To create such a role, use <literal>CREATE ROLE |
| 192 |
<replaceable>name</replaceable> CREATEDB</literal>. |
| 193 |
</para> |
| 194 |
</listitem> |
| 195 |
</varlistentry> |
| 196 |
|
| 197 |
<varlistentry> |
| 198 |
<term id="role-creation">role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term> |
| 199 |
<listitem> |
| 200 |
<para> |
| 201 |
A role must be explicitly given permission to create more roles |
| 202 |
(except for superusers, since those bypass all permission |
| 203 |
checks). To create such a role, use <literal>CREATE ROLE |
| 204 |
<replaceable>name</replaceable> CREATEROLE</literal>. |
| 205 |
A role with <literal>CREATEROLE</literal> privilege can alter and drop |
| 206 |
roles which have been granted to the <literal>CREATEROLE</literal> |
| 207 |
user with the <literal>ADMIN</literal> option. Such a grant occurs |
| 208 |
automatically when a <literal>CREATEROLE</literal> user that is not |
| 209 |
a superuser creates a new role, so that by default, a |
| 210 |
<literal>CREATEROLE</literal> user can alter and drop the roles |
| 211 |
which they have created. |
| 212 |
Altering a role includes most changes that can be made using |
| 213 |
<literal>ALTER ROLE</literal>, including, for example, changing |
| 214 |
passwords. It also includes modifications to a role that can |
| 215 |
be made using the <literal>COMMENT</literal> and |
| 216 |
<literal>SECURITY LABEL</literal> commands. |
| 217 |
</para> |
| 218 |
<para> |
| 219 |
However, <literal>CREATEROLE</literal> does not convey the ability to |
| 220 |
create <literal>SUPERUSER</literal> roles, nor does it convey any |
| 221 |
power over <literal>SUPERUSER</literal> roles that already exist. |
| 222 |
Furthermore, <literal>CREATEROLE</literal> does not convey the power |
| 223 |
to create <literal>REPLICATION</literal> users, nor the ability to |
| 224 |
grant or revoke the <literal>REPLICATION</literal> privilege, nor the |
| 225 |
ability to modify the role properties of such users. However, it does |
| 226 |
allow <literal>ALTER ROLE ... SET</literal> and |
| 227 |
<literal>ALTER ROLE ... RENAME</literal> to be used on |
| 228 |
<literal>REPLICATION</literal> roles, as well as the use of |
| 229 |
<literal>COMMENT ON ROLE</literal>, |
| 230 |
<literal>SECURITY LABEL ON ROLE</literal>, |
| 231 |
and <literal>DROP ROLE</literal>. |
| 232 |
Finally, <literal>CREATEROLE</literal> does not |
| 233 |
confer the ability to grant or revoke the <literal>BYPASSRLS</literal> |
| 234 |
privilege. |
| 235 |
</para> |
| 236 |
</listitem> |
| 237 |
</varlistentry> |
| 238 |
|
| 239 |
<varlistentry> |
| 240 |
<term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term> |
| 241 |
<listitem> |
| 242 |
<para> |
| 243 |
A role must explicitly be given permission to initiate streaming |
| 244 |
replication (except for superusers, since those bypass all permission |
| 245 |
checks). A role used for streaming replication must |
| 246 |
have <literal>LOGIN</literal> permission as well. To create such a role, use |
| 247 |
<literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION |
| 248 |
LOGIN</literal>. |
| 249 |
</para> |
| 250 |
</listitem> |
| 251 |
</varlistentry> |
| 252 |
|
| 253 |
<varlistentry> |
| 254 |
<term>password<indexterm><primary>password</primary></indexterm></term> |
| 255 |
<listitem> |
| 256 |
<para> |
| 257 |
A password is only significant if the client authentication |
| 258 |
method requires the user to supply a password when connecting |
| 259 |
to the database. The <option>password</option> and |
| 260 |
<option>md5</option> authentication methods |
| 261 |
make use of passwords. Database passwords are separate from |
| 262 |
operating system passwords. Specify a password upon role |
| 263 |
creation with <literal>CREATE ROLE |
| 264 |
<replaceable>name</replaceable> PASSWORD '<replaceable>string</replaceable>'</literal>. |
| 265 |
</para> |
| 266 |
</listitem> |
| 267 |
</varlistentry> |
| 268 |
|
| 269 |
<varlistentry> |
| 270 |
<term>inheritance of privileges<indexterm><primary>role</primary><secondary>privilege to inherit</secondary></indexterm></term> |
| 271 |
<listitem> |
| 272 |
<para> |
| 273 |
A role inherits the privileges of roles it is a member of, by default. |
| 274 |
However, to create a role which does not inherit privileges by |
| 275 |
default, use <literal>CREATE ROLE <replaceable>name</replaceable> |
| 276 |
NOINHERIT</literal>. Alternatively, inheritance can be overridden |
| 277 |
for individual grants by using <literal>WITH INHERIT TRUE</literal> |
| 278 |
or <literal>WITH INHERIT FALSE</literal>. |
| 279 |
</para> |
| 280 |
</listitem> |
| 281 |
</varlistentry> |
| 282 |
|
| 283 |
<varlistentry> |
| 284 |
<term>bypassing row-level security<indexterm><primary>role</primary><secondary>privilege to bypass</secondary></indexterm></term> |
| 285 |
<listitem> |
| 286 |
<para> |
| 287 |
A role must be explicitly given permission to bypass every row-level security (RLS) policy |
| 288 |
(except for superusers, since those bypass all permission checks). |
| 289 |
To create such a role, use <literal>CREATE ROLE <replaceable>name</replaceable> BYPASSRLS</literal> as a superuser. |
| 290 |
</para> |
| 291 |
</listitem> |
| 292 |
</varlistentry> |
| 293 |
|
| 294 |
<varlistentry> |
| 295 |
<term>connection limit<indexterm><primary>role</primary><secondary>privilege to limit connection</secondary></indexterm></term> |
| 296 |
<listitem> |
| 297 |
<para> |
| 298 |
Connection limit can specify how many concurrent connections a role can make. |
| 299 |
-1 (the default) means no limit. Specify connection limit upon role creation with |
| 300 |
<literal>CREATE ROLE <replaceable>name</replaceable> CONNECTION LIMIT '<replaceable>integer</replaceable>'</literal>. |
| 301 |
</para> |
| 302 |
</listitem> |
| 303 |
</varlistentry> |
| 304 |
</variablelist> |
| 305 |
|
| 306 |
A role's attributes can be modified after creation with |
| 307 |
<command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</primary></indexterm> |
| 308 |
See the reference pages for the <xref linkend="sql-createrole"/> |
| 309 |
and <xref linkend="sql-alterrole"/> commands for details. |
| 310 |
</para> |
| 311 |
|
| 312 |
<para> |
| 313 |
A role can also have role-specific defaults for many of the run-time |
| 314 |
configuration settings described in <xref |
| 315 |
linkend="runtime-config"/>. For example, if for some reason you |
| 316 |
want to disable index scans (hint: not a good idea) anytime you |
| 317 |
connect, you can use: |
| 318 |
<programlisting> |
| 319 |
ALTER ROLE myname SET enable_indexscan TO off; |
| 320 |
</programlisting> |
| 321 |
This will save the setting (but not set it immediately). In |
| 322 |
subsequent connections by this role it will appear as though |
| 323 |
<literal>SET enable_indexscan TO off</literal> had been executed |
| 324 |
just before the session started. |
| 325 |
You can still alter this setting during the session; it will only |
| 326 |
be the default. To remove a role-specific default setting, use |
| 327 |
<literal>ALTER ROLE <replaceable>rolename</replaceable> RESET <replaceable>varname</replaceable></literal>. |
| 328 |
Note that role-specific defaults attached to roles without |
| 329 |
<literal>LOGIN</literal> privilege are fairly useless, since they will never |
| 330 |
be invoked. |
| 331 |
</para> |
| 332 |
|
| 333 |
<para> |
| 334 |
When a non-superuser creates a role using the <literal>CREATEROLE</literal> |
| 335 |
privilege, the created role is automatically granted back to the creating |
| 336 |
user, just as if the bootstrap superuser had executed the command |
| 337 |
<literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, |
| 338 |
INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can |
| 339 |
only exercise special privileges with regard to an existing role if they |
| 340 |
have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient |
| 341 |
to allow a <literal>CREATEROLE</literal> user to administer the roles they |
| 342 |
created. However, because it is created with <literal>INHERIT FALSE, SET |
| 343 |
FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the |
| 344 |
privileges of the created role, nor can it access the privileges of that |
| 345 |
role using <literal>SET ROLE</literal>. However, since any user who has |
| 346 |
<literal>ADMIN OPTION</literal> on a role can grant membership in that |
| 347 |
role to any other user, the <literal>CREATEROLE</literal> user can gain |
| 348 |
access to the created role by simply granting that role back to |
| 349 |
themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal> |
| 350 |
options. Thus, the fact that privileges are not inherited by default nor |
| 351 |
is <literal>SET ROLE</literal> granted by default is a safeguard against |
| 352 |
accidents, not a security feature. Also note that, because this automatic |
| 353 |
grant is granted by the bootstrap superuser, it cannot be removed or changed by |
| 354 |
the <literal>CREATEROLE</literal> user; however, any superuser could |
| 355 |
revoke it, modify it, and/or issue additional such grants to other |
| 356 |
<literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal> |
| 357 |
users have <literal>ADMIN OPTION</literal> on a role at any given time |
| 358 |
can administer it. |
| 359 |
</para> |
| 360 |
</sect1> |
| 361 |
|
| 362 |
<sect1 id="role-membership"> |
| 363 |
<title>Role Membership</title> |
| 364 |
|
| 365 |
<indexterm zone="role-membership"> |
| 366 |
<primary>role</primary><secondary>membership in</secondary> |
| 367 |
</indexterm> |
| 368 |
|
| 369 |
<para> |
| 370 |
It is frequently convenient to group users together to ease |
| 371 |
management of privileges: that way, privileges can be granted to, or |
| 372 |
revoked from, a group as a whole. In <productname>PostgreSQL</productname> |
| 373 |
this is done by creating a role that represents the group, and then |
| 374 |
granting <firstterm>membership</firstterm> in the group role to individual user |
| 375 |
roles. |
| 376 |
</para> |
| 377 |
|
| 378 |
<para> |
| 379 |
To set up a group role, first create the role: |
| 380 |
<synopsis> |
| 381 |
CREATE ROLE <replaceable>name</replaceable>; |
| 382 |
</synopsis> |
| 383 |
Typically a role being used as a group would not have the <literal>LOGIN</literal> |
| 384 |
attribute, though you can set it if you wish. |
| 385 |
</para> |
| 386 |
|
| 387 |
<para> |
| 388 |
Once the group role exists, you can add and remove members using the |
| 389 |
<link linkend="sql-grant"><command>GRANT</command></link> and |
| 390 |
<link linkend="sql-revoke"><command>REVOKE</command></link> commands: |
| 391 |
<synopsis> |
| 392 |
GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ; |
| 393 |
REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ; |
| 394 |
</synopsis> |
| 395 |
You can grant membership to other group roles, too (since there isn't |
| 396 |
really any distinction between group roles and non-group roles). The |
| 397 |
database will not let you set up circular membership loops. Also, |
| 398 |
it is not permitted to grant membership in a role to |
| 399 |
<literal>PUBLIC</literal>. |
| 400 |
</para> |
| 401 |
|
| 402 |
<para> |
| 403 |
The members of a group role can use the privileges of the role in two |
| 404 |
ways. First, member roles that have been granted membership with the |
| 405 |
<literal>SET</literal> option can do |
| 406 |
<link linkend="sql-set-role"><command>SET ROLE</command></link> to |
| 407 |
temporarily <quote>become</quote> the group role. In this state, the |
| 408 |
database session has access to the privileges of the group role rather |
| 409 |
than the original login role, and any database objects created are |
| 410 |
considered owned by the group role, not the login role. Second, member |
| 411 |
roles that have been granted membership with the |
| 412 |
<literal>INHERIT</literal> option automatically have use of the |
| 413 |
privileges of those directly or indirectly a member of, though the |
| 414 |
chain stops at memberships lacking the inherit option. As an example, |
| 415 |
suppose we have done: |
| 416 |
<programlisting> |
| 417 |
CREATE ROLE joe LOGIN; |
| 418 |
CREATE ROLE admin; |
| 419 |
CREATE ROLE wheel; |
| 420 |
CREATE ROLE island; |
| 421 |
GRANT admin TO joe WITH INHERIT TRUE; |
| 422 |
GRANT wheel TO admin WITH INHERIT FALSE; |
| 423 |
GRANT island TO joe WITH INHERIT TRUE, SET FALSE; |
| 424 |
</programlisting> |
| 425 |
Immediately after connecting as role <literal>joe</literal>, a database |
| 426 |
session will have use of privileges granted directly to <literal>joe</literal> |
| 427 |
plus any privileges granted to <literal>admin</literal> and |
| 428 |
<literal>island</literal>, because <literal>joe</literal> |
| 429 |
<quote>inherits</quote> those privileges. However, privileges |
| 430 |
granted to <literal>wheel</literal> are not available, because even though |
| 431 |
<literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the |
| 432 |
membership is via <literal>admin</literal> which was granted using |
| 433 |
<literal>WITH INHERIT FALSE</literal>. After: |
| 434 |
<programlisting> |
| 435 |
SET ROLE admin; |
| 436 |
</programlisting> |
| 437 |
the session would have use of only those privileges granted to |
| 438 |
<literal>admin</literal>, and not those granted to <literal>joe</literal> or |
| 439 |
<literal>island</literal>. After: |
| 440 |
<programlisting> |
| 441 |
SET ROLE wheel; |
| 442 |
</programlisting> |
| 443 |
the session would have use of only those privileges granted to |
| 444 |
<literal>wheel</literal>, and not those granted to either <literal>joe</literal> |
| 445 |
or <literal>admin</literal>. The original privilege state can be restored |
| 446 |
with any of: |
| 447 |
<programlisting> |
| 448 |
SET ROLE joe; |
| 449 |
SET ROLE NONE; |
| 450 |
RESET ROLE; |
| 451 |
</programlisting> |
| 452 |
</para> |
| 453 |
|
| 454 |
<note> |
| 455 |
<para> |
| 456 |
The <command>SET ROLE</command> command always allows selecting any role |
| 457 |
that the original login role is directly or indirectly a member of, |
| 458 |
provided that there is a chain of membership grants each of which has |
| 459 |
<literal>SET TRUE</literal> (which is the default). |
| 460 |
Thus, in the above example, it is not necessary to become |
| 461 |
<literal>admin</literal> before becoming <literal>wheel</literal>. |
| 462 |
On the other hand, it is not possible to become <literal>island</literal> |
| 463 |
at all; <literal>joe</literal> can only access those privileges via |
| 464 |
inheritance. |
| 465 |
</para> |
| 466 |
</note> |
| 467 |
|
| 468 |
<note> |
| 469 |
<para> |
| 470 |
In the SQL standard, there is a clear distinction between users and roles, |
| 471 |
and users do not automatically inherit privileges while roles do. This |
| 472 |
behavior can be obtained in <productname>PostgreSQL</productname> by giving |
| 473 |
roles being used as SQL roles the <literal>INHERIT</literal> attribute, while |
| 474 |
giving roles being used as SQL users the <literal>NOINHERIT</literal> attribute. |
| 475 |
However, <productname>PostgreSQL</productname> defaults to giving all roles |
| 476 |
the <literal>INHERIT</literal> attribute, for backward compatibility with pre-8.1 |
| 477 |
releases in which users always had use of permissions granted to groups |
| 478 |
they were members of. |
| 479 |
</para> |
| 480 |
</note> |
| 481 |
|
| 482 |
<para> |
| 483 |
The role attributes <literal>LOGIN</literal>, <literal>SUPERUSER</literal>, |
| 484 |
<literal>CREATEDB</literal>, and <literal>CREATEROLE</literal> can be thought of as |
| 485 |
special privileges, but they are never inherited as ordinary privileges |
| 486 |
on database objects are. You must actually <command>SET ROLE</command> to a |
| 487 |
specific role having one of these attributes in order to make use of |
| 488 |
the attribute. Continuing the above example, we might choose to |
| 489 |
grant <literal>CREATEDB</literal> and <literal>CREATEROLE</literal> to the |
| 490 |
<literal>admin</literal> role. Then a session connecting as role <literal>joe</literal> |
| 491 |
would not have these privileges immediately, only after doing |
| 492 |
<command>SET ROLE admin</command>. |
| 493 |
</para> |
| 494 |
|
| 495 |
<para> |
| 496 |
</para> |
| 497 |
|
| 498 |
<para> |
| 499 |
To destroy a group role, use <link |
| 500 |
linkend="sql-droprole"><command>DROP ROLE</command></link>: |
| 501 |
<synopsis> |
| 502 |
DROP ROLE <replaceable>name</replaceable>; |
| 503 |
</synopsis> |
| 504 |
Any memberships in the group role are automatically revoked (but the |
| 505 |
member roles are not otherwise affected). |
| 506 |
</para> |
| 507 |
</sect1> |
| 508 |
|
| 509 |
<sect1 id="role-removal"> |
| 510 |
<title>Dropping Roles</title> |
| 511 |
|
| 512 |
<para> |
| 513 |
Because roles can own database objects and can hold privileges |
| 514 |
to access other objects, dropping a role is often not just a matter of a |
| 515 |
quick <link linkend="sql-droprole"><command>DROP ROLE</command></link>. Any objects owned by the role must |
| 516 |
first be dropped or reassigned to other owners; and any permissions |
| 517 |
granted to the role must be revoked. |
| 518 |
</para> |
| 519 |
|
| 520 |
<para> |
| 521 |
Ownership of objects can be transferred one at a time |
| 522 |
using <command>ALTER</command> commands, for example: |
| 523 |
<programlisting> |
| 524 |
ALTER TABLE bobs_table OWNER TO alice; |
| 525 |
</programlisting> |
| 526 |
Alternatively, the <link linkend="sql-reassign-owned"><command>REASSIGN OWNED</command></link> command can be |
| 527 |
used to reassign ownership of all objects owned by the role-to-be-dropped |
| 528 |
to a single other role. Because <command>REASSIGN OWNED</command> cannot access |
| 529 |
objects in other databases, it is necessary to run it in each database |
| 530 |
that contains objects owned by the role. (Note that the first |
| 531 |
such <command>REASSIGN OWNED</command> will change the ownership of any |
| 532 |
shared-across-databases objects, that is databases or tablespaces, that |
| 533 |
are owned by the role-to-be-dropped.) |
| 534 |
</para> |
| 535 |
|
| 536 |
<para> |
| 537 |
Once any valuable objects have been transferred to new owners, any |
| 538 |
remaining objects owned by the role-to-be-dropped can be dropped with |
| 539 |
the <link linkend="sql-drop-owned"><command>DROP OWNED</command></link> command. Again, this command cannot |
| 540 |
access objects in other databases, so it is necessary to run it in each |
| 541 |
database that contains objects owned by the role. Also, <command>DROP |
| 542 |
OWNED</command> will not drop entire databases or tablespaces, so it is |
| 543 |
necessary to do that manually if the role owns any databases or |
| 544 |
tablespaces that have not been transferred to new owners. |
| 545 |
</para> |
| 546 |
|
| 547 |
<para> |
| 548 |
<command>DROP OWNED</command> also takes care of removing any privileges granted |
| 549 |
to the target role for objects that do not belong to it. |
| 550 |
Because <command>REASSIGN OWNED</command> does not touch such objects, it's |
| 551 |
typically necessary to run both <command>REASSIGN OWNED</command> |
| 552 |
and <command>DROP OWNED</command> (in that order!) to fully remove the |
| 553 |
dependencies of a role to be dropped. |
| 554 |
</para> |
| 555 |
|
| 556 |
<para> |
| 557 |
In short then, the most general recipe for removing a role that has been |
| 558 |
used to own objects is: |
| 559 |
</para> |
| 560 |
<programlisting> |
| 561 |
REASSIGN OWNED BY doomed_role TO successor_role; |
| 562 |
DROP OWNED BY doomed_role; |
| 563 |
-- repeat the above commands in each database of the cluster |
| 564 |
DROP ROLE doomed_role; |
| 565 |
</programlisting> |
| 566 |
|
| 567 |
<para> |
| 568 |
When not all owned objects are to be transferred to the same successor |
| 569 |
owner, it's best to handle the exceptions manually and then perform |
| 570 |
the above steps to mop up. |
| 571 |
</para> |
| 572 |
|
| 573 |
<para> |
| 574 |
If <command>DROP ROLE</command> is attempted while dependent objects still |
| 575 |
remain, it will issue messages identifying which objects need to be |
| 576 |
reassigned or dropped. |
| 577 |
</para> |
| 578 |
</sect1> |
| 579 |
|
| 580 |
<sect1 id="predefined-roles"> |
| 581 |
<title>Predefined Roles</title> |
| 582 |
|
| 583 |
<indexterm zone="predefined-roles"> |
| 584 |
<primary>role</primary> |
| 585 |
</indexterm> |
| 586 |
|
| 587 |
<para> |
| 588 |
<productname>PostgreSQL</productname> provides a set of predefined roles |
| 589 |
that provide access to certain, commonly needed, privileged capabilities |
| 590 |
and information. Administrators (including roles that have the |
| 591 |
<literal>CREATEROLE</literal> privilege) can <command>GRANT</command> these |
| 592 |
roles to users and/or other roles in their environment, providing those |
| 593 |
users with access to the specified capabilities and information. For |
| 594 |
example: |
| 595 |
|
| 596 |
<programlisting> |
| 597 |
GRANT pg_signal_backend TO admin_user; |
| 598 |
</programlisting> |
| 599 |
</para> |
| 600 |
|
| 601 |
<warning> |
| 602 |
<para> |
| 603 |
Care should be taken when granting these roles to ensure they are only used |
| 604 |
where needed and with the understanding that these roles grant access to |
| 605 |
privileged information. |
| 606 |
</para> |
| 607 |
</warning> |
| 608 |
|
| 609 |
<para> |
| 610 |
The predefined roles are described below. |
| 611 |
Note that the specific permissions for each of the roles may change in |
| 612 |
the future as additional capabilities are added. Administrators |
| 613 |
should monitor the release notes for changes. |
| 614 |
|
| 615 |
<variablelist> |
| 616 |
<varlistentry id="predefined-role-pg-checkpoint" xreflabel="pg_checkpoint"> |
| 617 |
<term><varname>pg_checkpoint</varname></term> |
| 618 |
<listitem> |
| 619 |
<para> |
| 620 |
<literal>pg_checkpoint</literal> allows executing the |
| 621 |
<link linkend="sql-checkpoint"><command>CHECKPOINT</command></link> command. |
| 622 |
</para> |
| 623 |
</listitem> |
| 624 |
</varlistentry> |
| 625 |
|
| 626 |
<varlistentry id="predefined-role-pg-create-subscription" xreflabel="pg_create_subscription"> |
| 627 |
<term><varname>pg_create_subscription</varname></term> |
| 628 |
<listitem> |
| 629 |
<para> |
| 630 |
<literal>pg_create_subscription</literal> allows users with |
| 631 |
<literal>CREATE</literal> permission on the database to issue |
| 632 |
<link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>. |
| 633 |
</para> |
| 634 |
</listitem> |
| 635 |
</varlistentry> |
| 636 |
|
| 637 |
<varlistentry id="predefined-role-pg-database-owner" xreflabel="pg_database_owner"> |
| 638 |
<term><varname>pg_database_owner</varname></term> |
| 639 |
<listitem> |
| 640 |
<para> |
| 641 |
<literal>pg_database_owner</literal> always has exactly one implicit |
| 642 |
member: the current database owner. It cannot be granted membership in |
| 643 |
any role, and no role can be granted membership in |
| 644 |
<literal>pg_database_owner</literal>. However, like any other role, it |
| 645 |
can own objects and receive grants of access privileges. Consequently, |
| 646 |
once <literal>pg_database_owner</literal> has rights within a template |
| 647 |
database, each owner of a database instantiated from that template will |
| 648 |
possess those rights. Initially, this role owns the |
| 649 |
<literal>public</literal> schema, so each database owner governs local |
| 650 |
use of that schema. |
| 651 |
</para> |
| 652 |
</listitem> |
| 653 |
</varlistentry> |
| 654 |
|
| 655 |
<varlistentry id="predefined-role-pg-maintain" xreflabel="pg_maintain"> |
| 656 |
<term><varname>pg_maintain</varname></term> |
| 657 |
<listitem> |
| 658 |
<para> |
| 659 |
<literal>pg_maintain</literal> allows executing |
| 660 |
<link linkend="sql-vacuum"><command>VACUUM</command></link>, |
| 661 |
<link linkend="sql-analyze"><command>ANALYZE</command></link>, |
| 662 |
<link linkend="sql-cluster"><command>CLUSTER</command></link>, |
| 663 |
<link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>, |
| 664 |
<link linkend="sql-reindex"><command>REINDEX</command></link>, |
| 665 |
and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all |
| 666 |
relations, as if having <literal>MAINTAIN</literal> rights on those |
| 667 |
objects. |
| 668 |
</para> |
| 669 |
</listitem> |
| 670 |
</varlistentry> |
| 671 |
|
| 672 |
<varlistentry id="predefined-role-pg-monitor" xreflabel="pg_monitor"> |
| 673 |
<term><varname>pg_monitor</varname></term> |
| 674 |
<term><varname>pg_read_all_settings</varname></term> |
| 675 |
<term><varname>pg_read_all_stats</varname></term> |
| 676 |
<term><varname>pg_stat_scan_tables</varname></term> |
| 677 |
<listitem> |
| 678 |
<para> |
| 679 |
These roles are intended to allow administrators to easily configure a |
| 680 |
role for the purpose of monitoring the database server. They grant a |
| 681 |
set of common privileges allowing the role to read various useful |
| 682 |
configuration settings, statistics, and other system information |
| 683 |
normally restricted to superusers. |
| 684 |
</para> |
| 685 |
<para> |
| 686 |
<literal>pg_monitor</literal> allows reading/executing various |
| 687 |
monitoring views and functions. This role is a member of |
| 688 |
<literal>pg_read_all_settings</literal>, |
| 689 |
<literal>pg_read_all_stats</literal> and |
| 690 |
<literal>pg_stat_scan_tables</literal>. |
| 691 |
</para> |
| 692 |
<para> |
| 693 |
<literal>pg_read_all_settings</literal> allows reading all configuration |
| 694 |
variables, even those normally visible only to superusers. |
| 695 |
</para> |
| 696 |
<para> |
| 697 |
<literal>pg_read_all_stats</literal> allows reading all pg_stat_* views |
| 698 |
and use various statistics related extensions, even those normally |
| 699 |
visible only to superusers. |
| 700 |
</para> |
| 701 |
<para> |
| 702 |
<literal>pg_stat_scan_tables</literal> allows executing monitoring |
| 703 |
functions that may take <literal>ACCESS SHARE</literal> locks on tables, |
| 704 |
potentially for a long time (e.g., <function>pgrowlocks(text)</function> |
| 705 |
in the <xref linkend="pgrowlocks"/> extension). |
| 706 |
</para> |
| 707 |
</listitem> |
| 708 |
</varlistentry> |
| 709 |
|
| 710 |
<varlistentry id="predefined-role-pg-read-all-data" xreflabel="pg_read_all_data"> |
| 711 |
<term><varname>pg_read_all_data</varname></term> |
| 712 |
<term><varname>pg_write_all_data</varname></term> |
| 713 |
<listitem> |
| 714 |
<para> |
| 715 |
<literal>pg_read_all_data</literal> allows reading all data (tables, |
| 716 |
views, sequences), as if having <command>SELECT</command> rights on |
| 717 |
those objects and <literal>USAGE</literal> rights on all schemas. This |
| 718 |
role does not bypass row-level security (RLS) policies. If RLS is being |
| 719 |
used, an administrator may wish to set <literal>BYPASSRLS</literal> on |
| 720 |
roles which this role is granted to. |
| 721 |
</para> |
| 722 |
<para> |
| 723 |
<literal>pg_write_all_data</literal> allows writing all data (tables, |
| 724 |
views, sequences), as if having <command>INSERT</command>, |
| 725 |
<command>UPDATE</command>, and <command>DELETE</command> rights on those |
| 726 |
objects and <literal>USAGE</literal> rights on all schemas. This role |
| 727 |
does not bypass row-level security (RLS) policies. If RLS is being |
| 728 |
used, an administrator may wish to set <literal>BYPASSRLS</literal> on |
| 729 |
roles which this role is granted to. |
| 730 |
</para> |
| 731 |
</listitem> |
| 732 |
</varlistentry> |
| 733 |
|
| 734 |
<varlistentry id="predefined-role-pg-read-server-files" xreflabel="pg_read_server_files"> |
| 735 |
<term><varname>pg_read_server_files</varname></term> |
| 736 |
<term><varname>pg_write_server_files</varname></term> |
| 737 |
<term><varname>pg_execute_server_program</varname></term> |
| 738 |
<listitem> |
| 739 |
<para> |
| 740 |
These roles are intended to allow administrators to have trusted, but |
| 741 |
non-superuser, roles which are able to access files and run programs on |
| 742 |
the database server as the user the database runs as. They bypass all |
| 743 |
database-level permission checks when accessing files directly and they |
| 744 |
could be used to gain superuser-level access. Therefore, great care |
| 745 |
should be taken when granting these roles to users. |
| 746 |
</para> |
| 747 |
<para> |
| 748 |
<literal>pg_read_server_files</literal> allows reading files from any |
| 749 |
location the database can access on the server using |
| 750 |
<command>COPY</command> and other file-access functions. |
| 751 |
</para> |
| 752 |
<para> |
| 753 |
<literal>pg_write_server_files</literal> allows writing to files in any |
| 754 |
location the database can access on the server using |
| 755 |
<command>COPY</command> and other file-access functions. |
| 756 |
</para> |
| 757 |
<para> |
| 758 |
<literal>pg_execute_server_program</literal> allows executing programs |
| 759 |
on the database server as the user the database runs as using |
| 760 |
<command>COPY</command> and other functions which allow executing a |
| 761 |
server-side program. |
| 762 |
</para> |
| 763 |
</listitem> |
| 764 |
</varlistentry> |
| 765 |
|
| 766 |
<varlistentry id="predefined-role-pg-signal-autovacuum-worker" xreflabel="pg_signal_autovacuum_worker"> |
| 767 |
<term><varname>pg_signal_autovacuum_worker</varname></term> |
| 768 |
<listitem> |
| 769 |
<para> |
| 770 |
<literal>pg_signal_autovacuum_worker</literal> allows signaling |
| 771 |
autovacuum workers to cancel the current table's vacuum or terminate its |
| 772 |
session. See <xref linkend="functions-admin-signal"/>. |
| 773 |
</para> |
| 774 |
</listitem> |
| 775 |
</varlistentry> |
| 776 |
|
| 777 |
<varlistentry id="predefined-role-pg-signal-backend" xreflabel="pg_signal_backend"> |
| 778 |
<term><varname>pg_signal_backend</varname></term> |
| 779 |
<listitem> |
| 780 |
<para> |
| 781 |
<literal>pg_signal_backend</literal> allows signaling another backend to |
| 782 |
cancel a query or terminate its session. Note that this role does not |
| 783 |
permit signaling backends owned by a superuser. See |
| 784 |
<xref linkend="functions-admin-signal"/>. |
| 785 |
</para> |
| 786 |
</listitem> |
| 787 |
</varlistentry> |
| 788 |
|
| 789 |
<varlistentry id="predefined-role-pg-use-reserved-connections" xreflabel="pg_use_reserved_connections"> |
| 790 |
<term><varname>pg_use_reserved_connections</varname></term> |
| 791 |
<listitem> |
| 792 |
<para> |
| 793 |
<literal>pg_use_reserved_connections</literal> allows use of connection |
| 794 |
slots reserved via <xref linkend="guc-reserved-connections"/>. |
| 795 |
</para> |
| 796 |
</listitem> |
| 797 |
</varlistentry> |
| 798 |
</variablelist> |
| 799 |
</para> |
| 800 |
|
| 801 |
</sect1> |
| 802 |
|
| 803 |
<sect1 id="perm-functions"> |
| 804 |
<title>Function Security</title> |
| 805 |
|
| 806 |
<para> |
| 807 |
Functions, triggers and row-level security policies allow users to insert |
| 808 |
code into the backend server that other users might execute |
| 809 |
unintentionally. Hence, these mechanisms permit users to <quote>Trojan |
| 810 |
horse</quote> others with relative ease. The strongest protection is tight |
| 811 |
control over who can define objects. Where that is infeasible, write |
| 812 |
queries referring only to objects having trusted owners. Remove |
| 813 |
from <varname>search_path</varname> any schemas that permit untrusted users |
| 814 |
to create objects. |
| 815 |
</para> |
| 816 |
|
| 817 |
<para> |
| 818 |
Functions run inside the backend |
| 819 |
server process with the operating system permissions of the |
| 820 |
database server daemon. If the programming language |
| 821 |
used for the function allows unchecked memory accesses, it is |
| 822 |
possible to change the server's internal data structures. |
| 823 |
Hence, among many other things, such functions can circumvent any |
| 824 |
system access controls. Function languages that allow such access |
| 825 |
are considered <quote>untrusted</quote>, and |
| 826 |
<productname>PostgreSQL</productname> allows only superusers to |
| 827 |
create functions written in those languages. |
| 828 |
</para> |
| 829 |
</sect1> |
| 830 |
|
| 831 |
</chapter> |