PGINTRO(UNIX)
OVERVIEW
This section outlines the interaction between Postgres and
the operating system. In particular, this section
describes the Postgres support programs that are exe-
cutable as Unix commands.
TERMINOLOGY
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 secu-
rity 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 argu-
ments 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.
NOTATION
".../" 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 .
USING Postgres FROM Unix
All Postgres commands that are executed directly from a
Unix shell are found in the directory ".../bin". Includ-
ing 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 Post-
gres 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.
Security
USER AUTHENTICATION
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.
From the user shell
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.
From the network
If the Postgres system is built as distributed, access to
the Internet TCP port of the postmaster process is avail-
able 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 con-
nection and which database it is connecting to. See
pg_hba.conf(5) 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 Post-
gres.
ACCESS CONTROL
Postgres provides mechanisms to allow users to limit the
access to their data that is provided to other users.
Database superusers
Database super-users (i.e., users who have "pg_user.usesu-
per" 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 cata-
logs (or modification of their schemas) is never allowed.
Access Privilege
The use of access privilege to limit reading, writing and
setting of rules on classes is covered in grant/revoke(l).
Class removal and schema modification
Commands that destroy or modify the structure of an exist-
ing 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
Functions and rules allow users to insert code into the
backend server that other users may execute without know-
ing it. Hence, both mechanisms permit users to trojan
horse others with relative impunity. The only real pro-
tection 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
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.
Rules
Like SQL functions, rules always run with the identity and
permissions of the user who invoked the backend server.
SEE ALSO
abort(l) declare(l) large_objects(3)
alter_table(l) delete(l) libpq(3)
alter_user(l) destroydb(1) listen(l)
begin(l) destroyuser(1) load(l)
bki(5) drop(l) lock(l)
catalogs(3) drop_aggregate(l) move(l)
cleardbdir(1) drop_database(l) notify(l)
close(l) drop_function(l) oracle_compat(3)
cluster(l) drop_index(l) page(5)
commit(l) drop_language(l) pg_dump(1)
copy(l) drop_operator(l) pg_dumpall(1)
create_aggregate(l) drop_rule(l) pg_hba(conf(5)
create_database(l) drop_sequence(l) pg_passwd(1)
create_function(l) drop_table(l) pgbuiltin(3)
create_index(l) drop_trigger(l) pgintro(1)
create_language(l) drop_type(l) postgres(1)
create_operator(l) drop_user(l) postmaster(1)
create_rule(l) drop_view(l) psql(1)
create_sequence(l) ecpg(1) reset(l)
create_table(l) end(l) revoke(l)
create_trigger(l) explain(l) rollback(l)
create_type(l) fetch(l) select(l)
create_user(l) grant(l) set(l)
create_version(l) initdb(1) show(l)
create_view(l) initlocation(1) sql(l)
createdb(1) insert(l) update(l)
createuser(1) ipcclean(1) vacuum(l)
CAVEATS
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 fron-
tend/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.
APPENDIX: USING KERBEROS
Availability
The Kerberos authentication system is not distributed with
Postgres, nor is it available from the University of Cali-
fornia at Berkeley. Versions of Kerberos are typically
available as optional software from operating system ven-
dors. 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 regu-
lations.
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, "ker-
beros@ATHENA.MIT.EDU" (send mail to "kerberos-
request@ATHENA.MIT.EDU" to subscribe), and USENET news
group, "comp.protocols.kerberos".
Installation
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/Make-
file.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.
Operation
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.