3
<chapter id="user-manag">
4
<title>Database Roles and Privileges</title>
7
<productname>PostgreSQL</productname> manages database access permissions
8
using the concept of <firstterm>roles</>. 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,
11
tables) 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</> in a role to another role, thus
14
allowing the member role use of privileges assigned to the role it is
19
The concept of roles subsumes the concepts of <quote>users</> and
20
<quote>groups</>. In <productname>PostgreSQL</productname> versions
21
before 8.1, users and groups were distinct kinds of entities, but now
22
there are only roles. Any role can act as a user, a group, or both.
26
This chapter describes how to create and manage roles and introduces
27
the privilege system. More information about the various types of
28
database objects and the effects of privileges can be found in
32
<sect1 id="database-roles">
33
<title>Database Roles</title>
35
<indexterm zone="database-roles">
36
<primary>role</primary>
39
<indexterm zone="database-roles">
40
<primary>user</primary>
44
<primary>CREATE ROLE</primary>
48
<primary>DROP ROLE</primary>
52
Database roles are conceptually completely separate from
53
operating system users. In practice it might be convenient to
54
maintain a correspondence, but this is not required. Database roles
55
are global across a database cluster installation (and not
56
per individual database). To create a role use the <xref
57
linkend="sql-createrole" endterm="sql-createrole-title"> SQL command:
59
CREATE ROLE <replaceable>name</replaceable>;
61
<replaceable>name</replaceable> follows the rules for SQL
62
identifiers: either unadorned without special characters, or
63
double-quoted. (In practice, you will usually want to add additional
64
options, such as <literal>LOGIN</>, to the command. More details appear
65
below.) To remove an existing role, use the analogous
66
<xref linkend="sql-droprole" endterm="sql-droprole-title"> command:
68
DROP ROLE <replaceable>name</replaceable>;
73
<primary>createuser</primary>
77
<primary>dropuser</primary>
81
For convenience, the programs <xref linkend="app-createuser">
82
and <xref linkend="app-dropuser"> are provided as wrappers
83
around these SQL commands that can be called from the shell command
86
createuser <replaceable>name</replaceable>
87
dropuser <replaceable>name</replaceable>
92
To determine the set of existing roles, examine the <structname>pg_roles</>
93
system catalog, for example
95
SELECT rolname FROM pg_roles;
97
The <xref linkend="app-psql"> program's <literal>\du</> meta-command
98
is also useful for listing the existing roles.
102
In order to bootstrap the database system, a freshly initialized
103
system always contains one predefined role. This role is always
104
a <quote>superuser</>, and by default (unless altered when running
105
<command>initdb</command>) it will have the same name as the
106
operating system user that initialized the database
107
cluster. Customarily, this role will be named
108
<literal>postgres</literal>. In order to create more roles you
109
first have to connect as this initial role.
113
Every connection to the database server is made in the name of some
114
particular role, and this role determines the initial access privileges for
115
commands issued on that connection.
116
The role name to use for a particular database
117
connection is indicated by the client that is initiating the
118
connection request in an application-specific fashion. For example,
119
the <command>psql</command> program uses the
120
<option>-U</option> command line option to indicate the role to
121
connect as. Many applications assume the name of the current
122
operating system user by default (including
123
<command>createuser</> and <command>psql</>). Therefore it
124
is often convenient to maintain a naming correspondence between
125
roles and operating system users.
129
The set of database roles a given client connection can connect as
130
is determined by the client authentication setup, as explained in
131
<xref linkend="client-authentication">. (Thus, a client is not
132
necessarily limited to connect as the role with the same name as
133
its operating system user, just as a person's login name
134
need not match her real name.) Since the role
135
identity determines the set of privileges available to a connected
136
client, it is important to carefully configure this when setting up
137
a multiuser environment.
141
<sect1 id="role-attributes">
142
<title>Role Attributes</title>
145
A database role can have a number of attributes that define its
146
privileges and interact with the client authentication system.
150
<term>login privilege<indexterm><primary>login privilege</></></term>
153
Only roles that have the <literal>LOGIN</> attribute can be used
154
as the initial role name for a database connection. A role with
155
the <literal>LOGIN</> attribute can be considered the same thing
156
as a <quote>database user</>. To create a role with login privilege,
159
CREATE ROLE <replaceable>name</replaceable> LOGIN;
160
CREATE USER <replaceable>name</replaceable>;
162
(<command>CREATE USER</> is equivalent to <command>CREATE ROLE</>
163
except that <command>CREATE USER</> assumes <literal>LOGIN</> by
164
default, while <command>CREATE ROLE</> does not.)
170
<term>superuser status<indexterm><primary>superuser</></></term>
173
A database superuser bypasses all permission checks. This is a
174
dangerous privilege and should not be used carelessly; it is best
175
to do most of your work as a role that is not a superuser.
176
To create a new database superuser, use <literal>CREATE ROLE
177
<replaceable>name</replaceable> SUPERUSER</literal>. You must do
178
this as a role that is already a superuser.
184
<term>database creation<indexterm><primary>database</><secondary>privilege to create</></></term>
187
A role must be explicitly given permission to create databases
188
(except for superusers, since those bypass all permission
189
checks). To create such a role, use <literal>CREATE ROLE
190
<replaceable>name</replaceable> CREATEDB</literal>.
196
<term>role creation<indexterm><primary>role</><secondary>privilege to create</></></term>
199
A role must be explicitly given permission to create more roles
200
(except for superusers, since those bypass all permission
201
checks). To create such a role, use <literal>CREATE ROLE
202
<replaceable>name</replaceable> CREATEROLE</literal>.
203
A role with <literal>CREATEROLE</> privilege can alter and drop
204
other roles, too, as well as grant or revoke membership in them.
205
However, to create, alter, drop, or change membership of a
206
superuser role, superuser status is required;
207
<literal>CREATEROLE</> is not sufficient for that.
213
<term>password<indexterm><primary>password</></></term>
216
A password is only significant if the client authentication
217
method requires the user to supply a password when connecting
218
to the database. The <option>password</> and
219
<option>md5</> authentication methods
220
make use of passwords. Database passwords are separate from
221
operating system passwords. Specify a password upon role
222
creation with <literal>CREATE ROLE
223
<replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>.
229
A role's attributes can be modified after creation with
230
<command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</></>
231
See the reference pages for the <xref linkend="sql-createrole"
232
endterm="sql-createrole-title"> and <xref linkend="sql-alterrole"
233
endterm="sql-alterrole-title"> commands for details.
238
It is good practice to create a role that has the <literal>CREATEDB</>
239
and <literal>CREATEROLE</> privileges, but is not a superuser, and then
240
use this role for all routine management of databases and roles. This
241
approach avoids the dangers of operating as a superuser for tasks that
242
do not really require it.
247
A role can also have role-specific defaults for many of the run-time
248
configuration settings described in <xref
249
linkend="runtime-config">. For example, if for some reason you
250
want to disable index scans (hint: not a good idea) anytime you
251
connect, you can use:
253
ALTER ROLE myname SET enable_indexscan TO off;
255
This will save the setting (but not set it immediately). In
256
subsequent connections by this role it will appear as though
257
<literal>SET enable_indexscan TO off;</literal> had been executed
258
just before the session started.
259
You can still alter this setting during the session; it will only
260
be the default. To remove a role-specific default setting, use
261
<literal>ALTER ROLE <replaceable>rolename</> RESET <replaceable>varname</>;</literal>.
262
Note that role-specific defaults attached to roles without
263
<literal>LOGIN</> privilege are fairly useless, since they will never
268
<sect1 id="privileges">
269
<title>Privileges</title>
271
<indexterm zone="privileges">
272
<primary>privilege</primary>
275
<indexterm zone="privileges">
276
<primary>owner</primary>
279
<indexterm zone="privileges">
280
<primary>GRANT</primary>
283
<indexterm zone="privileges">
284
<primary>REVOKE</primary>
288
When an object is created, it is assigned an owner. The
289
owner is normally the role that executed the creation statement.
290
For most kinds of objects, the initial state is that only the owner
291
(or a superuser) can do anything with the object. To allow
292
other roles to use it, <firstterm>privileges</firstterm> must be
294
There are several different kinds of privilege: <literal>SELECT</>,
295
<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
296
<literal>TRUNCATE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
297
<literal>CREATE</>, <literal>CONNECT</>, <literal>TEMPORARY</>,
298
<literal>EXECUTE</>, and <literal>USAGE</>.
299
For more information on the different types of privileges supported by
300
<productname>PostgreSQL</productname>, see the
301
<xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
305
To assign privileges, the <command>GRANT</command> command is
306
used. So, if <literal>joe</literal> is an existing role, and
307
<literal>accounts</literal> is an existing table, the privilege to
308
update the table can be granted with:
310
GRANT UPDATE ON accounts TO joe;
312
The special name <literal>PUBLIC</literal> can
313
be used to grant a privilege to every role on the system. Writing
314
<literal>ALL</literal> in place of a specific privilege specifies that all
315
privileges that apply to the object will be granted.
319
To revoke a privilege, use the fittingly named
320
<xref linkend="sql-revoke" endterm="sql-revoke-title"> command:
322
REVOKE ALL ON accounts FROM PUBLIC;
327
The special privileges of an object's owner (i.e., the right to modify
328
or destroy the object) are always implicit in being the owner,
329
and cannot be granted or revoked. But the owner can choose
330
to revoke his own ordinary privileges, for example to make a
331
table read-only for himself as well as others.
335
An object can be assigned to a new owner with an <command>ALTER</command>
336
command of the appropriate kind for the object. Superusers can always do
337
this; ordinary roles can only do it if they are both the current owner
338
of the object (or a member of the owning role) and a member of the new
343
<sect1 id="role-membership">
344
<title>Role Membership</title>
346
<indexterm zone="role-membership">
347
<primary>role</><secondary>membership in</>
351
It is frequently convenient to group users together to ease
352
management of privileges: that way, privileges can be granted to, or
353
revoked from, a group as a whole. In <productname>PostgreSQL</productname>
354
this is done by creating a role that represents the group, and then
355
granting <firstterm>membership</> in the group role to individual user
360
To set up a group role, first create the role:
362
CREATE ROLE <replaceable>name</replaceable>;
364
Typically a role being used as a group would not have the <literal>LOGIN</>
365
attribute, though you can set it if you wish.
369
Once the group role exists, you can add and remove members using the
370
<xref linkend="sql-grant" endterm="sql-grant-title"> and
371
<xref linkend="sql-revoke" endterm="sql-revoke-title"> commands:
373
GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
374
REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
376
You can grant membership to other group roles, too (since there isn't
377
really any distinction between group roles and non-group roles). The
378
database will not let you set up circular membership loops. Also,
379
it is not permitted to grant membership in a role to
380
<literal>PUBLIC</literal>.
384
The members of a role can use the privileges of the group role in two
385
ways. First, every member of a group can explicitly do
386
<xref linkend="sql-set-role" endterm="sql-set-role-title"> to
387
temporarily <quote>become</> the group role. In this state, the
388
database session has access to the privileges of the group role rather
389
than the original login role, and any database objects created are
390
considered owned by the group role not the login role. Second, member
391
roles that have the <literal>INHERIT</> attribute automatically have use of
392
privileges of roles they are members of. As an example, suppose we have
395
CREATE ROLE joe LOGIN INHERIT;
396
CREATE ROLE admin NOINHERIT;
397
CREATE ROLE wheel NOINHERIT;
399
GRANT wheel TO admin;
401
Immediately after connecting as role <literal>joe</>, a database
402
session will have use of privileges granted directly to <literal>joe</>
403
plus any privileges granted to <literal>admin</>, because <literal>joe</>
404
<quote>inherits</> <literal>admin</>'s privileges. However, privileges
405
granted to <literal>wheel</> are not available, because even though
406
<literal>joe</> is indirectly a member of <literal>wheel</>, the
407
membership is via <literal>admin</> which has the <literal>NOINHERIT</>
412
the session would have use of only those privileges granted to
413
<literal>admin</>, and not those granted to <literal>joe</>. After:
417
the session would have use of only those privileges granted to
418
<literal>wheel</>, and not those granted to either <literal>joe</>
419
or <literal>admin</>. The original privilege state can be restored
430
The <command>SET ROLE</> command always allows selecting any role
431
that the original login role is directly or indirectly a member of.
432
Thus, in the above example, it is not necessary to become
433
<literal>admin</> before becoming <literal>wheel</>.
439
In the SQL standard, there is a clear distinction between users and roles,
440
and users do not automatically inherit privileges while roles do. This
441
behavior can be obtained in <productname>PostgreSQL</productname> by giving
442
roles being used as SQL roles the <literal>INHERIT</> attribute, while
443
giving roles being used as SQL users the <literal>NOINHERIT</> attribute.
444
However, <productname>PostgreSQL</productname> defaults to giving all roles
445
the <literal>INHERIT</> attribute, for backwards compatibility with pre-8.1
446
releases in which users always had use of permissions granted to groups
447
they were members of.
452
The role attributes <literal>LOGIN</>, <literal>SUPERUSER</>,
453
<literal>CREATEDB</>, and <literal>CREATEROLE</> can be thought of as
454
special privileges, but they are never inherited as ordinary privileges
455
on database objects are. You must actually <command>SET ROLE</> to a
456
specific role having one of these attributes in order to make use of
457
the attribute. Continuing the above example, we might well choose to
458
grant <literal>CREATEDB</> and <literal>CREATEROLE</> to the
459
<literal>admin</> role. Then a session connecting as role <literal>joe</>
460
would not have these privileges immediately, only after doing
461
<command>SET ROLE admin</>.
468
To destroy a group role, use <xref
469
linkend="sql-droprole" endterm="sql-droprole-title">:
471
DROP ROLE <replaceable>name</replaceable>;
473
Any memberships in the group role are automatically revoked (but the
474
member roles are not otherwise affected). Note however that any objects
475
owned by the group role must first be dropped or reassigned to other
476
owners; and any permissions granted to the group role must be revoked.
480
<sect1 id="perm-functions">
481
<title>Functions and Triggers</title>
484
Functions and triggers allow users to insert code into the backend
485
server that other users might execute unintentionally. Hence, both
486
mechanisms permit users to <quote>Trojan horse</quote>
487
others with relative ease. The only real protection is tight
488
control over who can define functions.
492
Functions run inside the backend
493
server process with the operating system permissions of the
494
database server daemon. If the programming language
495
used for the function allows unchecked memory accesses, it is
496
possible to change the server's internal data structures.
497
Hence, among many other things, such functions can circumvent any
498
system access controls. Function languages that allow such access
499
are considered <quote>untrusted</>, and
500
<productname>PostgreSQL</productname> allows only superusers to
501
create functions written in those languages.
added
removed
doc/src/sgml/user-manag.sgml
