2 $PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.31 2005/10/13 23:26:00 tgl Exp $
5 <chapter id="user-manag">
6 <title>Database Roles and Privileges</title>
9 <productname>PostgreSQL</productname> manages database access permissions
10 using the concept of <firstterm>roles</>. A role can be thought of as
11 either a database user, or a group of database users, depending on how
12 the role is set up. Roles can own database objects (for example,
13 tables) and can assign privileges on those objects to other roles to
14 control who has access to which objects. Furthermore, it is possible
15 to grant <firstterm>membership</> in a role to another role, thus
16 allowing the member role use of privileges assigned to the role it is
21 The concept of roles subsumes the concepts of <quote>users</> and
22 <quote>groups</>. In <productname>PostgreSQL</productname> versions
23 before 8.1, users and groups were distinct kinds of entities, but now
24 there are only roles. Any role can act as a user, a group, or both.
28 This chapter describes how to create and manage roles and introduces
29 the privilege system. More information about the various types of
30 database objects and the effects of privileges can be found in
34 <sect1 id="database-roles">
35 <title>Database Roles</title>
37 <indexterm zone="database-roles">
38 <primary>role</primary>
41 <indexterm zone="database-roles">
42 <primary>user</primary>
46 <primary>CREATE ROLE</primary>
50 <primary>DROP ROLE</primary>
54 Database roles are conceptually completely separate from
55 operating system users. In practice it might be convenient to
56 maintain a correspondence, but this is not required. Database roles
57 are global across a database cluster installation (and not
58 per individual database). To create a role use the <xref
59 linkend="sql-createrole" endterm="sql-createrole-title"> SQL command:
61 CREATE ROLE <replaceable>name</replaceable>;
63 <replaceable>name</replaceable> follows the rules for SQL
64 identifiers: either unadorned without special characters, or
65 double-quoted. (In practice, you will usually want to add additional
66 options, such as <literal>LOGIN</>, to the command. More details appear
67 below.) To remove an existing role, use the analogous
68 <xref linkend="sql-droprole" endterm="sql-droprole-title"> command:
70 DROP ROLE <replaceable>name</replaceable>;
75 <primary>createuser</primary>
79 <primary>dropuser</primary>
83 For convenience, the programs <xref linkend="app-createuser">
84 and <xref linkend="app-dropuser"> are provided as wrappers
85 around these SQL commands that can be called from the shell command
88 createuser <replaceable>name</replaceable>
89 dropuser <replaceable>name</replaceable>
94 To determine the set of existing roles, examine the <structname>pg_roles</>
95 system catalog, for example
97 SELECT rolname FROM pg_roles;
99 The <xref linkend="app-psql"> program's <literal>\du</> meta-command
100 is also useful for listing the existing roles.
104 In order to bootstrap the database system, a freshly initialized
105 system always contains one predefined role. This role is always
106 a <quote>superuser</>, and by default (unless altered when running
107 <command>initdb</command>) it will have the same name as the
108 operating system user that initialized the database
109 cluster. Customarily, this role will be named
110 <literal>postgres</literal>. In order to create more roles you
111 first have to connect as this initial role.
115 Every connection to the database server is made in the name of some
116 particular role, and this role determines the initial access privileges for
117 commands issued on 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</> and <command>psql</>). Therefore it
126 is often convenient to maintain a naming correspondence between
127 roles and operating system users.
131 The set of database roles a given client connection may connect as
132 is determined by the client authentication setup, as explained in
133 <xref linkend="client-authentication">. (Thus, a client is not
134 necessarily limited to connect as the role with the same name as
135 its operating system user, just as a person's login name
136 need not match 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 this when setting up
139 a multiuser environment.
143 <sect1 id="role-attributes">
144 <title>Role Attributes</title>
147 A database role may have a number of attributes that define its
148 privileges and interact with the client authentication system.
152 <term>login privilege<indexterm><primary>login privilege</></></term>
155 Only roles that have the <literal>LOGIN</> attribute can be used
156 as the initial role name for a database connection. A role with
157 the <literal>LOGIN</> attribute can be considered the same thing
158 as a <quote>database user</>. To create a role with login privilege,
161 CREATE ROLE <replaceable>name</replaceable> LOGIN;
162 CREATE USER <replaceable>name</replaceable>;
164 (<command>CREATE USER</> is equivalent to <command>CREATE ROLE</>
165 except that <command>CREATE USER</> assumes <literal>LOGIN</> by
166 default, while <command>CREATE ROLE</> does not.)
172 <term>superuser status<indexterm><primary>superuser</></></term>
175 A database superuser bypasses all permission checks. This is a
176 dangerous privilege and should not be used carelessly; it is best
177 to do most of your work as a role that is not a superuser.
178 To create a new database superuser, use <literal>CREATE ROLE
179 <replaceable>name</replaceable> SUPERUSER</literal>. You must do
180 this as a role that is already a superuser.
186 <term>database creation<indexterm><primary>database</><secondary>privilege to create</></></term>
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>.
198 <term>role creation<indexterm><primary>role</><secondary>privilege to create</></></term>
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</> privilege can alter and drop
206 other roles, too, as well as grant or revoke membership in them.
207 However, to create, alter, drop, or change membership of a
208 superuser role, superuser status is required;
209 <literal>CREATEROLE</> is not sufficient for that.
215 <term>password<indexterm><primary>password</></></term>
218 A password is only significant if the client authentication
219 method requires the user to supply a password when connecting
220 to the database. The <option>password</>,
221 <option>md5</>, and <option>crypt</> authentication methods
222 make use of passwords. Database passwords are separate from
223 operating system passwords. Specify a password upon role
224 creation with <literal>CREATE ROLE
225 <replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>.
231 A role's attributes can be modified after creation with
232 <command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</></>
233 See the reference pages for the <xref linkend="sql-createrole"
234 endterm="sql-createrole-title"> and <xref linkend="sql-alterrole"
235 endterm="sql-alterrole-title"> commands for details.
240 It is good practice to create a role that has the <literal>CREATEDB</>
241 and <literal>CREATEROLE</> privileges, but is not a superuser, and then
242 use this role for all routine management of databases and roles. This
243 approach avoids the dangers of operating as a superuser for tasks that
244 do not really require it.
249 A role can also have role-specific defaults for many of the run-time
250 configuration settings described in <xref
251 linkend="runtime-config">. For example, if for some reason you
252 want to disable index scans (hint: not a good idea) anytime you
255 ALTER ROLE myname SET enable_indexscan TO off;
257 This will save the setting (but not set it immediately). In
258 subsequent connections by this role it will appear as though
259 <literal>SET enable_indexscan TO off;</literal> had been executed
260 just before the session started.
261 You can still alter this setting during the session; it will only
262 be the default. To remove a role-specific default setting, use
263 <literal>ALTER ROLE <replaceable>rolename</> RESET <replaceable>varname</>;</literal>.
264 Note that role-specific defaults attached to roles without
265 <literal>LOGIN</> privilege are fairly useless, since they will never
270 <sect1 id="role-membership">
271 <title>Role Membership</title>
273 <indexterm zone="role-membership">
274 <primary>role</><secondary>membership in</>
278 It is frequently convenient to group users together to ease
279 management of privileges: that way, privileges can be granted to, or
280 revoked from, a group as a whole. In <productname>PostgreSQL</productname>
281 this is done by creating a role that represents the group, and then
282 granting <firstterm>membership</> in the group role to individual user
287 To set up a group role, first create the role:
289 CREATE ROLE <replaceable>name</replaceable>;
291 Typically a role being used as a group would not have the <literal>LOGIN</>
292 attribute, though you can set it if you wish.
296 Once the group role exists, you can add and remove members using the
297 <xref linkend="sql-grant" endterm="sql-grant-title"> and
298 <xref linkend="sql-revoke" endterm="sql-revoke-title"> commands:
300 GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
301 REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
303 You can grant membership to other group roles, too (since there isn't
304 really any distinction between group roles and non-group roles). The
305 only restriction is that you can't set up circular membership loops.
309 The members of a role can use the privileges of the group role in two
310 ways. First, every member of a group can explicitly do
311 <xref linkend="sql-set-role" endterm="sql-set-role-title"> to
312 temporarily <quote>become</> the group role. In this state, the
313 database session has access to the privileges of the group role rather
314 than the original login role, and any database objects created are
315 considered owned by the group role not the login role. Second, member
316 roles that have the <literal>INHERIT</> attribute automatically have use of
317 privileges of roles they are members of. As an example, suppose we have
320 CREATE ROLE joe LOGIN INHERIT;
321 CREATE ROLE admin NOINHERIT;
322 CREATE ROLE wheel NOINHERIT;
324 GRANT wheel TO admin;
326 Immediately after connecting as role <literal>joe</>, a database
327 session will have use of privileges granted directly to <literal>joe</>
328 plus any privileges granted to <literal>admin</>, because <literal>joe</>
329 <quote>inherits</> <literal>admin</>'s privileges. However, privileges
330 granted to <literal>wheel</> are not available, because even though
331 <literal>joe</> is indirectly a member of <literal>wheel</>, the
332 membership is via <literal>admin</> which has the <literal>NOINHERIT</>
337 the session would have use of only those privileges granted to
338 <literal>admin</>, and not those granted to <literal>joe</>. After
342 the session would have use of only those privileges granted to
343 <literal>wheel</>, and not those granted to either <literal>joe</>
344 or <literal>admin</>. The original privilege state can be restored
355 The <command>SET ROLE</> command always allows selecting any role
356 that the original login role is directly or indirectly a member of.
357 Thus, in the above example, it is not necessary to become
358 <literal>admin</> before becoming <literal>wheel</>.
364 In the SQL standard, there is a clear distinction between users and roles,
365 and users do not automatically inherit privileges while roles do. This
366 behavior can be obtained in <productname>PostgreSQL</productname> by giving
367 roles being used as SQL roles the <literal>INHERIT</> attribute, while
368 giving roles being used as SQL users the <literal>NOINHERIT</> attribute.
369 However, <productname>PostgreSQL</productname> defaults to giving all roles
370 the <literal>INHERIT</> attribute, for backwards compatibility with pre-8.1
371 releases in which users always had use of permissions granted to groups
372 they were members of.
377 To destroy a group role, use <xref
378 linkend="sql-droprole" endterm="sql-droprole-title">:
380 DROP ROLE <replaceable>name</replaceable>;
382 Any memberships in the group role are automatically revoked (but the
383 member roles are not otherwise affected). Note however that any objects
384 owned by the group role must first be dropped or reassigned to other
385 owners; and any permissions granted to the group role must be revoked.
389 <sect1 id="privileges">
390 <title>Privileges</title>
392 <indexterm zone="privileges">
393 <primary>privilege</primary>
396 <indexterm zone="privileges">
397 <primary>owner</primary>
400 <indexterm zone="privileges">
401 <primary>GRANT</primary>
404 <indexterm zone="privileges">
405 <primary>REVOKE</primary>
409 When an object is created, it is assigned an owner. The
410 owner is normally the role that executed the creation statement.
411 For most kinds of objects, the initial state is that only the owner
412 (or a superuser) can do anything with the object. To allow
413 other roles to use it, <firstterm>privileges</firstterm> must be
415 There are several different kinds of privilege: <literal>SELECT</>,
416 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
417 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
418 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
419 and <literal>USAGE</>. For more
420 information on the different types of privileges supported by
421 <productname>PostgreSQL</productname>, see the
422 <xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
426 To assign privileges, the <command>GRANT</command> command is
427 used. So, if <literal>joe</literal> is an existing role, and
428 <literal>accounts</literal> is an existing table, the privilege to
429 update the table can be granted with
431 GRANT UPDATE ON accounts TO joe;
433 The special name <literal>PUBLIC</literal> can
434 be used to grant a privilege to every role on the system. Writing
435 <literal>ALL</literal> in place of a specific privilege specifies that all
436 privileges that apply to the object will be granted.
440 To revoke a privilege, use the fittingly named
441 <xref linkend="sql-revoke" endterm="sql-revoke-title"> command:
443 REVOKE ALL ON accounts FROM PUBLIC;
448 The special privileges of an object's owner (i.e., the right to modify
449 or destroy the object) are always implicit in being the owner,
450 and cannot be granted or revoked. But the owner can choose
451 to revoke his own ordinary privileges, for example to make a
452 table read-only for himself as well as others.
456 An object can be assigned to a new owner with an <command>ALTER</command>
457 command of the appropriate kind for the object. Superusers can always do
458 this; ordinary roles can only do it if they are both the current owner
459 of the object (or a member of the owning role) and a member of the new
464 <sect1 id="perm-functions">
465 <title>Functions and Triggers</title>
468 Functions and triggers allow users to insert code into the backend
469 server that other users may execute without knowing it. Hence, both
470 mechanisms permit users to <quote>Trojan horse</quote>
471 others with relative ease. The only real protection is tight
472 control over who can define functions.
476 Functions run inside the backend
477 server process with the operating system permissions of the
478 database server daemon. If the programmming language
479 used for the function allows unchecked memory accesses, it is
480 possible to change the server's internal data structures.
481 Hence, among many other things, such functions can circumvent any
482 system access controls. Function languages that allow such access
483 are considered <quote>untrusted</>, and
484 <productname>PostgreSQL</productname> allows only superusers to
485 create functions written in those languages.