2 $Header: /cvsroot/pgsql/doc/src/sgml/user-manag.sgml,v 1.17 2002/10/24 17:48:54 petere Exp $
5 <chapter id="user-manag">
6 <title>Database Users and Privileges</title>
9 Every database cluster contains a set of database users. Those
10 users are separate from the users managed by the operating system on
11 which the server runs. Users own database objects (for example,
12 tables) and can assign privileges on those objects to other users to
13 control who has access to which object.
17 This chapter describes how to create and manage users and introduces
18 the privilege system. More information about the various types of
19 database objects and the effects of privileges can be found in the
23 <sect1 id="database-users">
24 <title>Database Users</title>
27 Database users are conceptually completely separate from
28 operating system users. In practice it might be convenient to
29 maintain a correspondence, but this is not required. Database user
30 names are global across a database cluster installation (and not
31 per individual database). To create a user use the <command>CREATE
32 USER</command> SQL command:
34 CREATE USER <replaceable>name</replaceable>
36 <replaceable>name</replaceable> follows the rules for SQL
37 identifiers: either unadorned without special characters, or
38 double-quoted. To remove an existing user, use the analogous
39 <command>DROP USER</command> command:
41 DROP USER <replaceable>name</replaceable>
46 For convenience, the programs <application>createuser</application>
47 and <application>dropuser</application> are provided as wrappers
48 around these SQL commands that can be called from the shell command
51 createuser <replaceable>name</replaceable>
52 dropuser <replaceable>name</replaceable>
57 In order to bootstrap the database system, a freshly initialized
58 system always contains one predefined user. This user will have the
59 fixed ID 1, and by default (unless altered when running
60 <application>initdb</application>) it will have the same name as
61 the operating system user that initialized the database
62 cluster. Customarily, this user will be named
63 <systemitem>postgres</systemitem>. In order to create more users
64 you first have to connect as this initial user.
68 Exactly one user identity is active for a connection to the
69 database server. The user name to use for a particular database
70 connection is indicated by the client that is initiating the
71 connection request in an application-specific fashion. For example,
72 the <application>psql</application> program uses the
73 <option>-U</option> command line option to indicate the user to
74 connect as. Many applications assume the name of the current
75 operating system user by default (including
76 <application>createuser</> and <application>psql</>). Therefore it
77 is convenient to maintain a naming correspondence between the two
82 The set of database users a given client connection may connect as
83 is determined by the client authentication setup, as explained in
84 <xref linkend="client-authentication">. (Thus, a client is not
85 necessarily limited to connect as the user with the same name as
86 its operating system user, in the same way a person is not
87 constrained in its login name by her real name.) Since the user
88 identity determines the set of privileges available to a connected
89 client, it is important to carefully configure this when setting up
90 a multiuser environment.
94 <sect1 id="user-attributes">
95 <title>User Attributes</title>
98 A database user may have a number of attributes that define its
99 privileges and interact with the client authentication system.
103 <term>superuser</term>
106 A database superuser bypasses all permission checks. Also,
107 only a superuser can create new users. To create a database
108 superuser, use <literal>CREATE USER <replaceable>name</replaceable>
109 CREATEUSER</literal>.
115 <term>database creation</term>
118 A user must be explicitly given permission to create databases
119 (except for superusers, since those bypass all permission
120 checks). To create such a user, use <literal>CREATE USER
121 <replaceable>name</replaceable> CREATEDB</literal>.
127 <term>password</term>
130 A password is only significant if the client authentication
131 method requires the user to supply a password when connecting
132 to the database. At present, the <option>password</>,
133 <option>md5</>, and <option>crypt</> authentication methods
134 make use of passwords. Database passwords are separate from
135 operating system passwords. Specify a password upon user
136 creation with <literal>CREATE USER
137 <replaceable>name</replaceable> PASSWORD 'string'</literal>.
143 A user's attributes can be modified after creation with
144 <command>ALTER USER</command>.
145 See the reference pages for <command>CREATE USER</command> and
146 <command>ALTER USER</command> for details.
150 A user can also set personal defaults for many of the run-time
151 configuration settings described in <xref
152 linkend="runtime-config">. For example, if for some reason you
153 want to disable index scans (hint: not a good idea) anytime you
156 ALTER USER myname SET enable_indexscan TO off;
158 This will save the setting (but not set it immediately) and in
159 subsequent connections it will appear as though <literal>SET geqo
160 TO off;</literal> had been called right before the session started.
161 You can still alter this setting during the session; it will only
162 be the default. To undo any such setting, use <literal>ALTER USER
163 <replaceable>username</> RESET <replaceable>varname</>;</literal>.
168 <title>Groups</title>
171 As in Unix, groups are a way of logically grouping users to ease
172 management of privileges: privileges can be granted to, or revoked
173 from, a group as a whole. To create a group, use
175 CREATE GROUP <replaceable>name</replaceable>
177 To add users to or remove users from a group, use
179 ALTER GROUP <replaceable>name</replaceable> ADD USER <replaceable>uname1</replaceable>, ...
180 ALTER GROUP <replaceable>name</replaceable> DROP USER <replaceable>uname1</replaceable>, ...
185 <sect1 id="privileges">
186 <title>Privileges</title>
188 <comment>Being moved to User's Guide. Will eventually disappear here.</comment>
191 When a database object is created, it is assigned an owner. The
192 owner is the user that executed the creation statement. To change
193 the owner of a table, index, sequence, or view, use the
194 <command>ALTER TABLE</command> command. By default, only an owner
195 (or a superuser) can do anything with the object. In order to allow
196 other users to use it, <firstterm>privileges</firstterm> must be
201 There are several different privileges: <literal>SELECT</>,
202 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
203 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
204 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
205 <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For more
206 information on the different types of privileges support by
207 <productname>PostgreSQL</productname>, refer to the
208 <command>GRANT</command> reference manual. The right to modify or
209 destroy an object is always the privilege of the owner only. To
210 assign privileges, the <command>GRANT</command> command is
211 used. So, if <literal>joe</literal> is an existing user, and
212 <literal>accounts</literal> is an existing table, the privilege to
213 update the table can be granted with
216 GRANT UPDATE ON accounts TO joe;
218 The user executing this command must be the owner of the table. To
219 grant a privilege to a group, use
221 GRANT SELECT ON accounts TO GROUP staff;
223 The special <quote>user</quote> name <literal>PUBLIC</literal> can
224 be used to grant a privilege to every user on the system. Writing
225 <literal>ALL</literal> in place of a specific privilege specifies that all
226 privileges will be granted.
230 To revoke a privilege, use the fittingly named
231 <command>REVOKE</command> command:
233 REVOKE ALL ON accounts FROM PUBLIC;
235 The special privileges of the table owner (i.e., the right to do
236 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc)
237 are always implicit in being the owner,
238 and cannot be granted or revoked. But the table owner can choose
239 to revoke his own ordinary privileges, for example to make a
240 table read-only for himself as well as others.
244 <sect1 id="perm-functions">
245 <title>Functions and Triggers</title>
248 Functions and triggers allow users to insert code into the backend
249 server that other users may execute without knowing it. Hence, both
250 mechanisms permit users to <firstterm>Trojan horse</firstterm>
251 others with relative impunity. The only real protection is tight
252 control over who can define functions.
256 Functions written in any language except SQL run inside the backend
257 server process with the operating systems permissions of the
258 database server daemon process. It is possible to change the
259 server's internal data structures from inside of trusted functions.
260 Hence, among many other things, such functions can circumvent any
261 system access controls. This is an inherent problem with
262 user-defined C functions.