2 $PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.27 2005/01/08 22:13:36 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 <xref
43 linkend="sql-createuser" endterm="sql-createuser-title"> 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 <xref linkend="sql-dropuser" endterm="sql-dropuser-title"> command:
52 DROP USER <replaceable>name</replaceable>;
57 <primary>createuser</primary>
61 <primary>dropuser</primary>
65 For convenience, the programs <xref linkend="app-createuser">
66 and <xref linkend="app-dropuser"> 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 To determine the set of existing users, examine the <structname>pg_user</>
77 system catalog, for example
79 SELECT usename FROM pg_user;
81 The <xref linkend="app-psql"> program's <literal>\du</> meta-command
82 is also useful for listing the existing users.
86 In order to bootstrap the database system, a freshly initialized
87 system always contains one predefined user. This user will have the
88 fixed ID 1, and by default (unless altered when running
89 <command>initdb</command>) it will have the same name as the
90 operating system user that initialized the database
91 cluster. Customarily, this user will be named
92 <literal>postgres</literal>. In order to create more users you
93 first have to connect as this initial user.
97 Exactly one user identity is active for a connection to the
98 database server. The user name to use for a particular database
99 connection is indicated by the client that is initiating the
100 connection request in an application-specific fashion. For example,
101 the <command>psql</command> program uses the
102 <option>-U</option> command line option to indicate the user to
103 connect as. Many applications assume the name of the current
104 operating system user by default (including
105 <command>createuser</> and <command>psql</>). Therefore it
106 is convenient to maintain a naming correspondence between the two
111 The set of database users a given client connection may connect as
112 is determined by the client authentication setup, as explained in
113 <xref linkend="client-authentication">. (Thus, a client is not
114 necessarily limited to connect as the user with the same name as
115 its operating system user, just as a person's login name
116 need not match her real name.) Since the user
117 identity determines the set of privileges available to a connected
118 client, it is important to carefully configure this when setting up
119 a multiuser environment.
123 <sect1 id="user-attributes">
124 <title>User Attributes</title>
127 A database user may have a number of attributes that define its
128 privileges and interact with the client authentication system.
132 <term>superuser<indexterm><primary>superuser</></></term>
135 A database superuser bypasses all permission checks. Also,
136 only a superuser can create new users. To create a database
137 superuser, use <literal>CREATE USER <replaceable>name</replaceable>
138 CREATEUSER</literal>.
144 <term>database creation<indexterm><primary>database</><secondary>privilege to create</></></term>
147 A user must be explicitly given permission to create databases
148 (except for superusers, since those bypass all permission
149 checks). To create such a user, use <literal>CREATE USER
150 <replaceable>name</replaceable> CREATEDB</literal>.
156 <term>password<indexterm><primary>password</></></term>
159 A password is only significant if the client authentication
160 method requires the user to supply a password when connecting
161 to the database. The <option>password</>,
162 <option>md5</>, and <option>crypt</> authentication methods
163 make use of passwords. Database passwords are separate from
164 operating system passwords. Specify a password upon user
165 creation with <literal>CREATE USER
166 <replaceable>name</replaceable> PASSWORD '<replaceable>string</>'</literal>.
172 A user's attributes can be modified after creation with
173 <command>ALTER USER</command>.<indexterm><primary>ALTER USER</></>
174 See the reference pages for the <xref linkend="sql-createuser"
175 endterm="sql-createuser-title"> and <xref linkend="sql-alteruser"
176 endterm="sql-alteruser-title"> commands for details.
180 A user can also set personal defaults for many of the run-time
181 configuration settings described in <xref
182 linkend="runtime-config">. For example, if for some reason you
183 want to disable index scans (hint: not a good idea) anytime you
186 ALTER USER myname SET enable_indexscan TO off;
188 This will save the setting (but not set it immediately). In
189 subsequent connections by this user it will appear as though
190 <literal>SET enable_indexscan TO off;</literal> had been executed
191 just before the session started.
192 You can still alter this setting during the session; it will only
193 be the default. To undo any such setting, use <literal>ALTER USER
194 <replaceable>username</> RESET <replaceable>varname</>;</literal>.
199 <title>Groups</title>
201 <indexterm zone="groups">
202 <primary>group</primary>
206 As in Unix, groups are a way of logically grouping users to ease
207 management of privileges: privileges can be granted to, or revoked
208 from, a group as a whole. To create a group, use the <xref
209 linkend="sql-creategroup" endterm="sql-creategroup-title"> SQL command:
211 CREATE GROUP <replaceable>name</replaceable>;
214 To add users to or remove users from an existing group, use <xref
215 linkend="sql-altergroup" endterm="sql-altergroup-title">:
217 ALTER GROUP <replaceable>name</replaceable> ADD USER <replaceable>uname1</replaceable>, ... ;
218 ALTER GROUP <replaceable>name</replaceable> DROP USER <replaceable>uname1</replaceable>, ... ;
221 To destroy a group, use <xref
222 linkend="sql-dropgroup" endterm="sql-dropgroup-title">:
224 DROP GROUP <replaceable>name</replaceable>;
226 This only drops the group, not its member users.
230 To determine the set of existing groups, examine the <structname>pg_group</>
231 system catalog, for example
233 SELECT groname FROM pg_group;
235 The <xref linkend="app-psql"> program's <literal>\dg</> meta-command
236 is also useful for listing the existing groups.
240 <sect1 id="privileges">
241 <title>Privileges</title>
243 <indexterm zone="privileges">
244 <primary>privilege</primary>
247 <indexterm zone="privileges">
248 <primary>owner</primary>
251 <indexterm zone="privileges">
252 <primary>GRANT</primary>
255 <indexterm zone="privileges">
256 <primary>REVOKE</primary>
259 <remark>Being moved to the DDL chapter. Will eventually disappear here.</remark>
262 When a database object is created, it is assigned an owner. The
263 owner is the user that executed the creation statement. To change
264 the owner of a table, index, sequence, or view, use the
265 <command>ALTER TABLE</command> command. By default, only an owner
266 (or a superuser) can do anything with the object. In order to allow
267 other users to use it, <firstterm>privileges</firstterm> must be
272 There are several different privileges: <literal>SELECT</>,
273 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
274 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
275 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
276 <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For more
277 information on the different types of privileges supported by
278 <productname>PostgreSQL</productname>, see the
279 <xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
280 The right to modify or
281 destroy an object is always the privilege of the owner only. To
282 assign privileges, the <command>GRANT</command> command is
283 used. So, if <literal>joe</literal> is an existing user, and
284 <literal>accounts</literal> is an existing table, the privilege to
285 update the table can be granted with
288 GRANT UPDATE ON accounts TO joe;
290 The user executing this command must be the owner of the table. To
291 grant a privilege to a group, use
293 GRANT SELECT ON accounts TO GROUP staff;
295 The special <quote>user</quote> name <literal>PUBLIC</literal> can
296 be used to grant a privilege to every user on the system. Writing
297 <literal>ALL</literal> in place of a specific privilege specifies that all
298 privileges will be granted.
302 To revoke a privilege, use the fittingly named
303 <command>REVOKE</command> command:
305 REVOKE ALL ON accounts FROM PUBLIC;
307 The special privileges of the table owner (i.e., the right to do
308 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc)
309 are always implicit in being the owner,
310 and cannot be granted or revoked. But the table owner can choose
311 to revoke his own ordinary privileges, for example to make a
312 table read-only for himself as well as others.
316 <sect1 id="perm-functions">
317 <title>Functions and Triggers</title>
320 Functions and triggers allow users to insert code into the backend
321 server that other users may execute without knowing it. Hence, both
322 mechanisms permit users to <quote>Trojan horse</quote>
323 others with relative ease. The only real protection is tight
324 control over who can define functions.
328 Functions run inside the backend
329 server process with the operating system permissions of the
330 database server daemon. If the programmming language
331 used for the function allows unchecked memory accesses, it is
332 possible to change the server's internal data structures.
333 Hence, among many other things, such functions can circumvent any
334 system access controls. Function languages that allow such access
335 are considered <quote>untrusted</>, and
336 <productname>PostgreSQL</productname> allows only superusers to
337 create functions written in those languages.