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
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>PGINTRO(UNIX) manual page</TITLE>
</HEAD>
<BODY>
<A HREF="pgsql.html">PostgreSQL Contents</A>
<P>
<H2><A NAME="sect0" HREF="#toc0">OVERVIEW </A></H2>
This section outlines the interaction between Postgres and
the operating system. In particular, this section describes the Postgres
support programs that are executable as Unix commands.
<H2><A NAME="sect1" HREF="#toc1">TERMINOLOGY </A></H2>
In
the following documentation, the term <I>site</I> may be interpreted as the host
machine on which Postgres is installed. Since it is possible to install
more than one set of Postgres databases on a single host, this term more
precisely denotes any particular set of installed Postgres binaries and
databases. <P>
The <I>Postgres super-user</I> is the user named `postgres' who owns
the Postgres binaries and database files. As the database super-user, all
protection mechanisms may be bypassed and any data accessed arbitrarily.
In addition, the Postgres super-user is allowed to execute some support
programs which are generally not available to all users. Note that the
Postgres super-user is <I>not</I> the same as the Unix super-user, <I>root</I>, and should
have a non-zero userid for security reasons. <P>
The <I>database base administrator</I>
or DBA, is the person who is responsible for installing Postgres to enforce
a security policy for a site. The DBA can add new users by the method
described below and maintain a set of template databases for use by <I><A HREF="createdb.1.html">createdb</I>(1)</A>
.
<P>
The <I>postmaster</I> is the process that acts as a clearing-house for requests
to the Postgres system. Frontend applications connect to the <I>postmaster</I>,
which keeps tracks of any system errors and communication between the
backend processes. The <I>postmaster</I> can take several command-line arguments
to tune its behavior. However, supplying arguments is necessary only if
you intend to run multiple sites or a non-default site. See <I><A HREF="postmaster.1.html">postmaster</I>(1)</A>
for details. <P>
The <I>Postgres backend</I> (the actual executable program called
"postgres") may be executed directly from the user shell by the Postgres
super-user (with the database name as an argument). However, doing this
bypasses the shared buffer pool and lock table associated with a postmaster/site,
therefore this is not recommended in a multiuser site.
<H2><A NAME="sect2" HREF="#toc2">NOTATION </A></H2>
`.../' at the
front of a file name is used to represent the path to the Postgres super-user's
home directory. Anything in brackets (`[' and `]') is optional. Anything in
braces (`{' and `}') can be repeated 0 or more times. Parentheses (`(' and `)'
) are used to group boolean expressions. `|' is the boolean operator <FONT SIZE=-1>OR .</FONT>
<H2><A NAME="sect3" HREF="#toc3">USING Postgres FROM Unix </A></H2>
All Postgres commands that are executed directly
from a Unix shell are found in the directory `.../bin'. Including this directory
in your search path will make executing the commands easier. <P>
A collection
of system catalogs exist at each site. These include a class (`pg_user')
that contains an instance for each valid Postgres user. The instance specifies
a set of Postgres privileges, such as the ability to act as Postgres super-user,
the ability to create/destroy databases, and the ability to update the
system catalogs. A Unix user cannot do anything with Postgres until an
appropriate instance is installed in this class. Further information on
the system catalogs is available by running queries on the appropriate
classes.
<H2><A NAME="sect4" HREF="#toc4">Security </A></H2>
<P>
<H2><A NAME="sect5" HREF="#toc5">USER AUTHENTICATION </A></H2>
<I>Authentication</I> is the process
by which the backend server and <I>postmaster</I> ensure that the user requesting
access to data is in fact who he/she claims to be. All users who invoke
Postgres are checked against the contents of the `pg_user' class to ensure
that they are authorized to do so. However, verification of the user's
actual identity is performed in a variety of ways.
<H3><A NAME="sect6" HREF="#toc6">From the user shell
</A></H3>
A backend server started from a user shell notes the user's (effective)
user-id before performing a <I><A HREF="setuid.3.html">setuid</I>(3)</A>
to the user-id of user `postgres'.
The effective user-id is used as the basis for access control checks. No
other authentication is conducted.
<H3><A NAME="sect7" HREF="#toc7">From the network </A></H3>
If the Postgres system
is built as distributed, access to the Internet TCP port of the <I>postmaster</I>
process is available to anyone. The DBA configures the pg_hba.conf file
in the PGDATA directory to specify what authentication system is to be
used according to the host making the connection and which database it
is connecting to. See <A HREF="pg_hba.conf.5.html">pg_hba.conf(5)</A>
for a description of the authentication
systems available. Of course, host-based authentication is not fool-proof
in Unix, either. It is possible for determined intruders to also masquerade
the origination host. Those security issues are beyond the scope of Postgres.
<P>
<H2><A NAME="sect8" HREF="#toc8">ACCESS CONTROL </A></H2>
Postgres provides mechanisms to allow users to limit the
access to their data that is provided to other users.
<H3><A NAME="sect9" HREF="#toc9">Database superusers
</A></H3>
Database super-users (i.e., users who have `pg_user.usesuper' set) silently
bypass all of the access controls described below with two exceptions:
manual system catalog updates are not permitted if the user does not have
`pg_user.usecatupd' set, and destruction of system catalogs (or modification
of their schemas) is never allowed.
<H3><A NAME="sect10" HREF="#toc10">Access Privilege </A></H3>
The use of access
privilege to limit reading, writing and setting of rules on classes is
covered in <I>grant/<A HREF="revoke.l.html">revoke</I>(l)</A>
.
<H3><A NAME="sect11" HREF="#toc11">Class removal and schema modification </A></H3>
Commands
that destroy or modify the structure of an existing class, such as <I>alter</I>,
<I>drop table</I>, and <I>drop index</I>, only operate for the owner of the class. As
mentioned above, these operations are <B>never</B> permitted on system catalogs.
<H2><A NAME="sect12" HREF="#toc12">FUNCTIONS AND RULES </A></H2>
Functions and rules allow users to insert code into
the backend server that other users may execute without knowing it. Hence,
both mechanisms permit users to <B>trojan horse</B> 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 rules. Audit trails and alerters
on `pg_class', `pg_user' and `pg_group' are also recommended.
<H3><A NAME="sect13" HREF="#toc13">Functions </A></H3>
Functions
written in any language except SQL run inside the backend server process
with the permissions of the user `postgres' (the backend server runs with
its real and effective user-id set to `postgres'). It is possible for users
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.
<H3><A NAME="sect14" HREF="#toc14">Rules </A></H3>
Like SQL functions, rules always run with the identity
and permissions of the user who invoked the backend server.
<H2><A NAME="sect15" HREF="#toc15">SEE ALSO </A></H2>
<A HREF="postmaster.1.html">postmaster(1)</A>
,
<A HREF="alter.l.html">alter(l)</A>
, <A HREF="insert.l.html">insert(l)</A>
, grant/<A HREF="revoke.l.html">revoke(l)</A>
, <A HREF="copy.l.html">copy(l)</A>
, <A HREF="create.l.html">create(l)</A>
, <A HREF="delete.l.html">delete(l)</A>
, drop
<A HREF="table.l.html">table(l)</A>
, drop <A HREF="index.l.html">index(l)</A>
, drop <A HREF="rule.l.html">rule(l)</A>
, <A HREF="update.l.html">update(l)</A>
, <A HREF="select.l.html">select(l)</A>
, <A HREF="kerberos.1.html">kerberos(1)</A>
,
<A HREF="kinit.1.html">kinit(1)</A>
, <A HREF="kerberos.3.html">kerberos(3)</A>
<H2><A NAME="sect16" HREF="#toc16">CAVEATS </A></H2>
<P>
There are no plans to explicitly support
encrypted data inside of Postgres (though there is nothing to prevent
users from encrypting data within user-defined functions). There are no
plans to explicitly support encrypted network connections, either, pending
a total rewrite of the frontend/backend protocol. <P>
User names, group names
and associated system identifiers (e.g., the contents of `pg_user.usesysid')
are assumed to be unique throughout a database. Unpredictable results
may occur if they are not.
<H2><A NAME="sect17" HREF="#toc17">APPENDIX: USING KERBEROS </A></H2>
<H3><A NAME="sect18" HREF="#toc18">Availability </A></H3>
The
<I>Kerberos</I> authentication system is not distributed with Postgres, nor is
it available from the University of California at Berkeley. Versions of
<I>Kerberos</I> are typically available as optional software from operating system
vendors. In addition, a source code distribution may be obtained through
MIT Project Athena by anonymous FTP from ATHENA-DIST.MIT.EDU (18.71.0.38). (You
may wish to obtain the MIT version even if your vendor provides a version,
since some vendor ports have been deliberately crippled or rendered non-interoperable
with the MIT version.) Users located outside the United States of America
and Canada are warned that distribution of the actual encryption code
in <I>Kerberos</I> is restricted by U. S. government export regulations. <P>
Any additional
inquiries should be directed to your vendor or MIT Project Athena (`info-kerberos@ATHENA.MIT.EDU').
Note that FAQLs (Frequently-Asked Questions Lists) are periodically posted
to the <I>Kerberos</I> mailing list, `kerberos@ATHENA.MIT.EDU' (send mail to `kerberos-request@ATHENA.MIT.EDU'
to subscribe), and USENET news group, `comp.protocols.kerberos'.
<H3><A NAME="sect19" HREF="#toc19">Installation
</A></H3>
Installation of <I>Kerberos</I> itself is covered in detail in the <I>Kerberos
Installation Notes</I>. Make sure that the server key file (the <I>srvtab</I> or <I>keytab</I>)
is somehow readable by user `postgres'. <P>
Postgres and its clients can be compiled
to use either Version 4 or Version 5 of the MIT <I>Kerberos</I> protocols by
setting the <FONT SIZE=-1>KRBVERS</FONT>
variable in the file `.../src/Makefile.global' to the appropriate
value. You can also change the location where Postgres expects to find
the associated libraries, header files and its own server key file. <P>
After
compilation is complete, Postgres must be registered as a <I>Kerberos</I> service.
See the <I>Kerberos Operations Notes</I> and related manual pages for more details
on registering services.
<H3><A NAME="sect20" HREF="#toc20">Operation </A></H3>
After initial installation, Postgres
should operate in all ways as a normal <I>Kerberos</I> service. For details on
the use of authentication, see the manual pages for <I><A HREF="postmaster.1.html">postmaster</I>(1)</A>
and
<I><A HREF="psql.1.html">psql</I>(1)</A>
. <P>
In the <I>Kerberos</I> Version 5 hooks, the following assumptions are
made about user and service naming: (1) user principal names (anames)
are assumed to contain the actual Unix/Postgres user name in the first
component; (2) the Postgres service is assumed to be have two components,
the service name and a hostname, canonicalized as in Version 4 (i.e., all
domain suffixes removed). <P>
user example: frew@S2K.ORG <BR>
user example: aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.ORG
<BR>
host example: postgres_dbms/ucbvax@S2K.ORG <BR>
<P>
Support for Version 4 will
disappear sometime after the production release of Version 5 by MIT. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">OVERVIEW</A></LI>
<LI><A NAME="toc1" HREF="#sect1">TERMINOLOGY</A></LI>
<LI><A NAME="toc2" HREF="#sect2">NOTATION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">USING Postgres FROM Unix</A></LI>
<LI><A NAME="toc4" HREF="#sect4">Security</A></LI>
<LI><A NAME="toc5" HREF="#sect5">USER AUTHENTICATION</A></LI>
<UL>
<LI><A NAME="toc6" HREF="#sect6">From the user shell</A></LI>
<LI><A NAME="toc7" HREF="#sect7">From the network</A></LI>
</UL>
<LI><A NAME="toc8" HREF="#sect8">ACCESS CONTROL</A></LI>
<UL>
<LI><A NAME="toc9" HREF="#sect9">Database superusers</A></LI>
<LI><A NAME="toc10" HREF="#sect10">Access Privilege</A></LI>
<LI><A NAME="toc11" HREF="#sect11">Class removal and schema modification</A></LI>
</UL>
<LI><A NAME="toc12" HREF="#sect12">FUNCTIONS AND RULES</A></LI>
<UL>
<LI><A NAME="toc13" HREF="#sect13">Functions</A></LI>
<LI><A NAME="toc14" HREF="#sect14">Rules</A></LI>
</UL>
<LI><A NAME="toc15" HREF="#sect15">SEE ALSO</A></LI>
<LI><A NAME="toc16" HREF="#sect16">CAVEATS</A></LI>
<LI><A NAME="toc17" HREF="#sect17">APPENDIX: USING KERBEROS</A></LI>
<UL>
<LI><A NAME="toc18" HREF="#sect18">Availability</A></LI>
<LI><A NAME="toc19" HREF="#sect19">Installation</A></LI>
<LI><A NAME="toc20" HREF="#sect20">Operation</A></LI>
</UL></UL>
</BODY></HTML>
|
