1 <Chapter id="user-manag">
2 <title>Database Users and Permissions</title>
5 Managing database users and their privileges is in concept similar
6 to managing users of a Unix operating system, but the details are not
10 <sect1 id="database-users">
11 <title>Database Users</title>
14 Database users are conceptually completely separate from
15 operating system users. In practice it might be convenient to
16 maintain a correspondence, but this is not required. Database user
17 names are global across a database cluster installation (and not
18 per individual database). To create a user use the <command>CREATE
19 USER</command> SQL command:
21 CREATE USER <replaceable>name</replaceable>
23 <replaceable>name</replaceable> follows the rules for SQL
24 identifiers: either unadorned without special characters, or
25 double-quoted. To remove an existing user, use the analogous
26 <command>DROP USER</command> command.
30 For convenience, the shell scripts <filename>createuser</filename>
31 and <filename>dropuser</filename> are provided as wrappers around these SQL
36 In order to bootstrap the database system, a freshly initialized
37 system always contains one predefined user. This user will have the
38 fixed id 1, and by default (unless altered when running
39 <command>initdb</command>) it will have the same name as the
40 operating system user that initialized the area (and is presumably
41 being used as the user that runs the server). Customarily, this user
42 will be named <systemitem>postgres</systemitem>. In order to create more
43 users you first have to connect as this initial user.
47 The user name to use for a particular database connection is
48 indicated by the client that is initiating the connection request
49 in an application-specific fashion. For example, the
50 <command>psql</command> program uses the <option>-U</option>
51 command line option to indicate the user to connect as. The set of
52 database users a given client connection may connect as is
53 determined by the client authentication setup, as explained in
54 <xref linkend="client-authentication">. (Thus, a client is not
55 necessarily limited to connect as the user with the same name as
56 its operating system user, in the same way a person is not
57 constrained in its login name by her real name.)
60 <sect2 id="user-attributes">
61 <title>User attributes</title>
64 A database user may have a number of attributes that define its
65 privileges and interact with the client authentication system.
69 <term>superuser</term>
72 A database superuser bypasses all permission checks. Also,
73 only a superuser can create new users. To create a database
74 superuser, use <literal>CREATE USER name
81 <term>database creation</term>
84 A user must be explicitly given permission to create databases
85 (except for superusers, since those bypass all permission
86 checks). To create such a user, use <literal>CREATE USER name
96 A password is only significant if password authentication is
97 used for client authentication. Database passwords are separate
98 from operating system passwords. Specify a password upon
99 user creation with <literal>CREATE USER name PASSWORD
106 A user's attributes can be modified after creation with
107 <command>ALTER USER</command>.
108 See the reference pages for <command>CREATE USER</command> and
109 <command>ALTER USER</command> for details.
115 <title>Groups</title>
118 As in Unix, groups are a way of logically grouping users to ease
119 management of permissions: permissions can be granted to, or revoked
120 from, a group as a whole. To create a group, use
122 CREATE GROUP <replaceable>name</replaceable>
124 To add users to or remove users from a group, use
126 ALTER GROUP <replaceable>name</replaceable> ADD USER <replaceable>uname1</replaceable>, ...
127 ALTER GROUP <replaceable>name</replaceable> DROP USER <replaceable>uname1</replaceable>, ...
132 <sect1 id="privileges">
133 <title>Privileges</title>
136 When a database object is created, it is assigned an owner. The
137 owner is the user that executed the creation statement. There is
138 currently no polished interface for changing the owner of a database
139 object. By default, only an owner (or a superuser) can do anything
140 with the object. In order to allow other users to use it,
141 <firstterm>privileges</firstterm> must be granted.
145 There are several different privileges: <literal>SELECT</literal>
146 (read), <literal>INSERT</literal> (append), <literal>UPDATE</literal>
147 (write), <literal>DELETE</literal>, <literal>RULE</literal>,
148 <literal>REFERENCES</literal> (foreign key), and
149 <literal>TRIGGER</literal>. (See the <command>GRANT</command> manual
150 page for more detailed information.) The right to modify or destroy
151 an object is always the privilege of the owner only. To assign
152 privileges, the <command>GRANT</command> command is used. So, if
153 <literal>joe</literal> is an existing user, and
154 <literal>accounts</literal> is an existing table, write access can be
157 GRANT UPDATE ON accounts TO joe;
159 The user executing this command must be the owner of the table. To
160 grant a privilege to a group, use
162 GRANT SELECT ON accounts TO GROUP staff;
164 The special <quote>user</quote> name <literal>PUBLIC</literal> can
165 be used to grant a privilege to every user on the system. Writing
166 <literal>ALL</literal> in place of a specific privilege specifies that all
167 privileges will be granted.
171 To revoke a privilege, use the fittingly named
172 <command>REVOKE</command> command:
174 REVOKE ALL ON accounts FROM PUBLIC;
176 The special privileges of the table owner (i.e., the right to do
177 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc)
178 are always implicit in being the owner,
179 and cannot be granted or revoked. But the table owner can choose
180 to revoke his own ordinary privileges, for example to make a
181 table read-only for himself as well as others.
185 <sect1 id="perm-functions">
186 <title>Functions and Triggers</title>
189 Functions and triggers allow users to insert code into the backend
190 server that other users may execute without knowing it. Hence, both
191 mechanisms permit users to <firstterm>Trojan horse</firstterm>
192 others with relative impunity. The only real protection is tight
193 control over who can define functions (e.g., write to relations
194 with SQL fields) and triggers. Audit trails and alerters on the
195 system catalogs <literal>pg_class</literal>,
196 <literal>pg_shadow</literal> and <literal>pg_group</literal> are also
201 Functions written in any language except SQL run inside the backend
202 server process with the operating systems permissions of the
203 database server daemon process. It is possible to change the
204 server's internal data structures from inside of trusted functions.
205 Hence, among many other things, such functions can circumvent any
206 system access controls. This is an inherent problem with
207 user-defined C functions.