2 $PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.26 2004/12/27 22:30:10 tgl 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 <xref linkend="ddl">.
22 <sect1 id="database-users">
23 <title>Database Users</title>
25 <indexterm zone="database-users">
26 <primary>user</primary>
30 <primary>CREATE USER</primary>
34 <primary>DROP USER</primary>
38 Database users are conceptually completely separate from
39 operating system users. In practice it might be convenient to
40 maintain a correspondence, but this is not required. Database user
41 names are global across a database cluster installation (and not
42 per individual database). To create a user use the <command>CREATE
43 USER</command> SQL command:
45 CREATE USER <replaceable>name</replaceable>;
47 <replaceable>name</replaceable> follows the rules for SQL
48 identifiers: either unadorned without special characters, or
49 double-quoted. To remove an existing user, use the analogous
50 <command>DROP USER</command> command:
52 DROP USER <replaceable>name</replaceable>;
57 <primary>createuser</primary>
61 <primary>dropuser</primary>
65 For convenience, the programs <command>createuser</command>
66 and <command>dropuser</command> are provided as wrappers
67 around these SQL commands that can be called from the shell command
70 createuser <replaceable>name</replaceable>
71 dropuser <replaceable>name</replaceable>
76 In order to bootstrap the database system, a freshly initialized
77 system always contains one predefined user. This user will have the
78 fixed ID 1, and by default (unless altered when running
79 <command>initdb</command>) it will have the same name as the
80 operating system user that initialized the database
81 cluster. Customarily, this user will be named
82 <literal>postgres</literal>. In order to create more users you
83 first have to connect as this initial user.
87 Exactly one user identity is active for a connection to the
88 database server. The user name to use for a particular database
89 connection is indicated by the client that is initiating the
90 connection request in an application-specific fashion. For example,
91 the <command>psql</command> program uses the
92 <option>-U</option> command line option to indicate the user to
93 connect as. Many applications assume the name of the current
94 operating system user by default (including
95 <command>createuser</> and <command>psql</>). Therefore it
96 is convenient to maintain a naming correspondence between the two
101 The set of database users a given client connection may connect as
102 is determined by the client authentication setup, as explained in
103 <xref linkend="client-authentication">. (Thus, a client is not
104 necessarily limited to connect as the user with the same name as
105 its operating system user, in the same way a person is not
106 constrained in its login name by her real name.) Since the user
107 identity determines the set of privileges available to a connected
108 client, it is important to carefully configure this when setting up
109 a multiuser environment.
113 <sect1 id="user-attributes">
114 <title>User Attributes</title>
117 A database user may have a number of attributes that define its
118 privileges and interact with the client authentication system.
122 <term>superuser<indexterm><primary>superuser</></></term>
125 A database superuser bypasses all permission checks. Also,
126 only a superuser can create new users. To create a database
127 superuser, use <literal>CREATE USER <replaceable>name</replaceable>
128 CREATEUSER</literal>.
134 <term>database creation<indexterm><primary>database</><secondary>privilege to create</></></term>
137 A user must be explicitly given permission to create databases
138 (except for superusers, since those bypass all permission
139 checks). To create such a user, use <literal>CREATE USER
140 <replaceable>name</replaceable> CREATEDB</literal>.
146 <term>password<indexterm><primary>password</></></term>
149 A password is only significant if the client authentication
150 method requires the user to supply a password when connecting
151 to the database. The <option>password</>,
152 <option>md5</>, and <option>crypt</> authentication methods
153 make use of passwords. Database passwords are separate from
154 operating system passwords. Specify a password upon user
155 creation with <literal>CREATE USER
156 <replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>.
162 A user's attributes can be modified after creation with
163 <command>ALTER USER</command>.<indexterm><primary>ALTER USER</></>
164 See the reference pages for the <xref linkend="sql-createuser"
165 endterm="sql-createuser-title"> and <xref linkend="sql-alteruser"
166 endterm="sql-alteruser-title"> commands for details.
170 A user can also set personal defaults for many of the run-time
171 configuration settings described in <xref
172 linkend="runtime-config">. For example, if for some reason you
173 want to disable index scans (hint: not a good idea) anytime you
176 ALTER USER myname SET enable_indexscan TO off;
178 This will save the setting (but not set it immediately). In
179 subsequent connections by this user it will appear as though
180 <literal>SET enable_indexscan TO off;</literal> had been executed
181 just before the session started.
182 You can still alter this setting during the session; it will only
183 be the default. To undo any such setting, use <literal>ALTER USER
184 <replaceable>username</> RESET <replaceable>varname</>;</literal>.
189 <title>Groups</title>
191 <indexterm zone="groups">
192 <primary>group</primary>
196 As in Unix, groups are a way of logically grouping users to ease
197 management of privileges: privileges can be granted to, or revoked
198 from, a group as a whole. To create a group, use
200 CREATE GROUP <replaceable>name</replaceable>;
202 To add users to or remove users from a group, use
204 ALTER GROUP <replaceable>name</replaceable> ADD USER <replaceable>uname1</replaceable>, ... ;
205 ALTER GROUP <replaceable>name</replaceable> DROP USER <replaceable>uname1</replaceable>, ... ;
210 <sect1 id="privileges">
211 <title>Privileges</title>
213 <indexterm zone="privileges">
214 <primary>privilege</primary>
217 <indexterm zone="privileges">
218 <primary>owner</primary>
221 <indexterm zone="privileges">
222 <primary>GRANT</primary>
225 <indexterm zone="privileges">
226 <primary>REVOKE</primary>
229 <remark>Being moved to the DDL chapter. Will eventually disappear here.</remark>
232 When a database object is created, it is assigned an owner. The
233 owner is the user that executed the creation statement. To change
234 the owner of a table, index, sequence, or view, use the
235 <command>ALTER TABLE</command> command. By default, only an owner
236 (or a superuser) can do anything with the object. In order to allow
237 other users to use it, <firstterm>privileges</firstterm> must be
242 There are several different privileges: <literal>SELECT</>,
243 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
244 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
245 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
246 <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For more
247 information on the different types of privileges supported by
248 <productname>PostgreSQL</productname>, see the
249 <xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
250 The right to modify or
251 destroy an object is always the privilege of the owner only. To
252 assign privileges, the <command>GRANT</command> command is
253 used. So, if <literal>joe</literal> is an existing user, and
254 <literal>accounts</literal> is an existing table, the privilege to
255 update the table can be granted with
258 GRANT UPDATE ON accounts TO joe;
260 The user executing this command must be the owner of the table. To
261 grant a privilege to a group, use
263 GRANT SELECT ON accounts TO GROUP staff;
265 The special <quote>user</quote> name <literal>PUBLIC</literal> can
266 be used to grant a privilege to every user on the system. Writing
267 <literal>ALL</literal> in place of a specific privilege specifies that all
268 privileges will be granted.
272 To revoke a privilege, use the fittingly named
273 <command>REVOKE</command> command:
275 REVOKE ALL ON accounts FROM PUBLIC;
277 The special privileges of the table owner (i.e., the right to do
278 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc)
279 are always implicit in being the owner,
280 and cannot be granted or revoked. But the table owner can choose
281 to revoke his own ordinary privileges, for example to make a
282 table read-only for himself as well as others.
286 <sect1 id="perm-functions">
287 <title>Functions and Triggers</title>
290 Functions and triggers allow users to insert code into the backend
291 server that other users may execute without knowing it. Hence, both
292 mechanisms permit users to <quote>Trojan horse</quote>
293 others with relative ease. The only real protection is tight
294 control over who can define functions.
298 Functions run inside the backend
299 server process with the operating system permissions of the
300 database server daemon. If the programmming language
301 used for the function allows unchecked memory accesses, it is
302 possible to change the server's internal data structures.
303 Hence, among many other things, such functions can circumvent any
304 system access controls. Function languages that allow such access
305 are considered <quote>untrusted</>, and
306 <productname>PostgreSQL</productname> allows only superusers to
307 create functions written in those languages.