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