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
|
<Chapter id="user-manag">
<title>Database Users and Permissions</title>
<para>
Managing database users and their privileges is in concept similar
to that of Unix operating systems, but then again not identical
enough to not warrant explanation.
</para>
<sect1 id="database-users">
<title>Database Users</title>
<para>
Database users are conceptually completely separate from any
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 analog
<command>DROP USER</command> command.
</para>
<para>
For convenience, the shell scripts <filename>createuser</filename>
and <filename>dropuser</filename> are 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 same name as the operating system user that initialized the
area (and is presumably being used as the user that runs the
server). Thus, often an initial user <quote>postgres</quote>
exists. In order to create more users you have to first 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 a separate
from any operating system passwords. Specify a password upon
user creating as in <literal>CREATE USER name WITH PASSWORD
'string'</literal>.
</para>
</listitem>
</varlistentry>
</variablelist>
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 create
a group, use
<synopsis>
CREATE GROUP <replaceable>name</replaceable>
</synopsis>
To add users to or remove users from a group, respectively, user
<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
currenty 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>
Currently, there are four different privileges: select (read),
insert (append), and update/delete (write), as well as
<literal>RULE</literal>, the permission to create a rewrite rule on
a table. 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. Using
<literal>ALL</literal> in place of a 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 set of privileges held by the table owner is always implicit
and is never revokable.
</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_user</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>
|
