Table of Contents
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.
In
the following documentation, the term site 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.
The Postgres super-user 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 not the same as the Unix super-user, root, and should
have a non-zero userid for security reasons.
The database base administrator
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 createdb(1)
.
The postmaster is the process that acts as a clearing-house for requests
to the Postgres system. Frontend applications connect to the postmaster,
which keeps tracks of any system errors and communication between the
backend processes. The postmaster 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 postmaster(1)
for details.
The Postgres backend (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.
`.../' 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 OR .
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.
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.
Authentication is the process
by which the backend server and postmaster 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.
A backend server started from a user shell notes the user's (effective)
user-id before performing a setuid(3)
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.
If the Postgres system
is built as distributed, access to the Internet TCP port of the postmaster
process is available to anyone. However, Postgres offers optional host-based
authentication where only access from certain hosts are allowed. 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.
If greater security is
desired, Postgres and its clients may be modified to use a network authentication
system. For example, the postmaster, psql and the libpq library have already
been configured to use either Version 4 or Version 5 of the Kerberos authentication
system from the Massachusetts Institute of Technology. For more information
on using Kerberos with Postgres, see the appendix below.
Postgres provides mechanisms to allow users to limit the access to their
data that is provided to other users.
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.
The use of access privilege to limit reading,
writing and setting of rules on classes is covered in grant/revoke(l)
.
Commands that destroy or modify
the structure of an existing class, such as alter, drop table, and drop
index, only operate for the owner of the class. As mentioned above, these
operations are never permitted on system catalogs.
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 trojan horse 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.
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.
Like SQL functions, rules always run with the identity and permissions
of the user who invoked the backend server.
postmaster(1)
, alter(l)
,
insert(l)
, grant/revoke(l)
, copy(l)
, create(l)
, delete(l)
, drop table(l)
,
drop index(l)
, drop rule(l)
, update(l)
, select(l)
, kerberos(1)
, kinit(1)
,
kerberos(3)
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.
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.
The Kerberos authentication
system is not distributed with Postgres, nor is it available from the
University of California at Berkeley. Versions of Kerberos 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 Kerberos
is restricted by U. S. government export regulations.
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 Kerberos mailing list, `kerberos@ATHENA.MIT.EDU' (send mail to `kerberos-request@ATHENA.MIT.EDU'
to subscribe), and USENET news group, `comp.protocols.kerberos'.
Installation of Kerberos itself is covered in detail in the Kerberos
Installation Notes. Make sure that the server key file (the srvtab or keytab)
is somehow readable by user `postgres'.
Postgres and its clients can be compiled
to use either Version 4 or Version 5 of the MIT Kerberos protocols by
setting the KRBVERS
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.
After
compilation is complete, Postgres must be registered as a Kerberos service.
See the Kerberos Operations Notes and related manual pages for more details
on registering services.
After initial installation, Postgres
should operate in all ways as a normal Kerberos service. For details on
the use of authentication, see the manual pages for postmaster(1)
and
psql(1)
.
In the Kerberos 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).
user example: frew@S2K.ORG
user example: aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.ORG
host example: postgres_dbms/ucbvax@S2K.ORG
Support for Version 4 will
disappear sometime after the production release of Version 5 by MIT.
Table of Contents