Database-SQL-RDBMS HOW-TO document for Linux (PostgreSQL Object Relational Database System): Security of Database

11. Security of Database

Database security is addressed at several levels:

Database file protection. All files stored within the database are protected from reading by any account other than the postgres superuser account
Connections from a client to the database server are, by default, allowed only via an local UNIX socket, not via TCP/IP sockets. The back-end must be started with the -i option to allow nonlocal clients to connect.
Client connections can be restricted by IP address and/or username via the pg_hba.conf file in $PG_DATA.
Client connections may be authenticated via other external packages.
Each user in Postgres is assigned an username and (optionally) a password. By default, users do not have write access to databases they did not create.
Users may be assigned to groups, and table access may be restricted based on group priveleges.

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 an user shell notes the user's (effective) user-id before performing a setuid 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 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 pg_hba.conf(5) (man 5 pg_hba.conf) 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.

11.2 Host-Based Access Control

Host-based access control is the name for the basic controls PostgreSQL exercises on what clients are allowed to access a database and how the users on those clients must authenticate themselves. Each database system contains a file named pg_hba.conf, in its $PGDATA directory, which controls who can connect to each database. Every client accessing a database must be covered by one of the entries in pg_hba.conf. Otherwise all attempted connections from that client will be rejected with a "User authentication failed" error message.

See online man page of pg_hba.conf(5) (man 5 pg_hba.conf).

The general format of the pg_hba.conf file is of a set of records, one per line. Blank lines and lines beginning with a hash character ("#") are ignored. A record is made up of a number of fields which are separated by spaces and/or tabs.

Connections from clients can be made using Unix domain sockets or Internet domain sockets (ie. TCP/IP). Connections made using Unix domain sockets are controlled using records of the following format:

local database authentication method

where

database specifies the database that this record applies to. The value all specifies that it applies to all databases.

authentication method specifies the method an user must use to authenticate themselves when connecting to that database using Unix domain sockets. The different methods are described below.

Connections made using Internet domain sockets are controlled using records of the following format.

host database TCP/IP-address TCP/IP-mask authentication method

The TCP/IP address is logically and'ed to both the specified TCP/IP mask and the TCP/IP address of the connecting client. If the two resulting values are equal then the record is used for this connection. If a connection matches more than one record then the earliest one in the file is used. Both the TCP/IP address and the TCP/IP mask are specified in dotted decimal notation. If a connection fails to match any record then the reject authentication method is applied (see Authentication Methods).

11.3 Authentication Methods

The following authentication methods are supported for both Unix and TCP/IP domain sockets:

trust The connection is allowed unconditionally.
reject The connection is rejected unconditionally.
crypt The client is asked for a password for the user. This is sent encrypted (using crypt(3)) and compared against the password held in the pg_shadow table. If the passwords match, the connection is allowed.
password The client is asked for a password for the user. This is sent in clear and compared against the password held in the pg_shadow table. If the passwords match, the connection is allowed. An optional password file may be specified after the password keyword which is used to match the supplied password rather than the pg_shadow table. See pg_passwd.

The following authentication methods are supported for TCP/IP domain sockets only:

krb4 Kerberos V4 is used to authenticate the user.
krb5 Kerberos V5 is used to authenticate the user.
ident The ident server on the client is used to authenticate the user (RFC 1413). An optional map name may be specified after the ident keyword which allows ident user names to be mapped onto Postgres user names. Maps are held in the file $PGDATA/pg_ident.conf.

Here are some examples:

# Trust any connection via Unix domain sockets.
local   trust
# Trust any connection via TCP/IP from this machine.
host    all 127.0.0.1   255.255.255.255     trust
# We don't like this machine.
host    all 192.168.0.10    255.255.255.0       reject
# This machine can't encrypt so we ask for passwords in clear.
host    all 192.168.0.3 255.255.255.0       password
# The rest of this group of machines should provide encrypted passwords.
host    all 192.168.0.0 255.255.255.0       crypt

11.4 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.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.
Access Privilege The use of access privilege to limit reading, writing and setting of rules on classes is covered in SQL grant/revoke(l).
Class removal and schema modification 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.

11.5 Secure TCP/IP Connection via SSH

You can use ssh to encrypt the network connection between clients and a Postgres server. Done properly, this should lead to an adequately secure network connection.

The documentation for ssh provides most of the information to get started. Please refer to http://www.heimhardt.de/htdocs/ssh.html for better insight. A step-by-step explanation can be done in just two steps.

Running a secure tunnel via ssh: A step-by-step explanation can be done in just two steps.

Establish a tunnel to the back-end machine, like this:

ssh -L 3333:wit.mcs.anl.gov:5432 postgres@wit.mcs.anl.gov

The first number in the -L argument, 3333, is the port number of your end of the tunnel. The second number, 5432, is the remote end of the tunnel -- the port number your backend is using. The name or the address in between the port numbers belongs to the server machine, as does the last argument to ssh that also includes the optional user name. Without the user name, ssh will try the name you are currently logged on as on the client machine. You can use any user name the server machine will accept, not necessarily those related to postgres.
Now that you have a running ssh session, you can connect a postgres client to your local host at the port number you specified in the previous step. If it's psql, you will need another shell because the shell session you used in step 1 is now occupied with ssh.
```
psql -h localhost -p 3333 -d mpw
```
Note that you have to specify the -h argument to cause your client to use the TCP socket instead of the Unix socket. You can omit the port argument if you chose 5432 as your end of the tunnel.

11.6 Kerberos Authentication

Kerberos is an industry-standard secure authentication system suitable for distributed computing over a public network.

Availability: The Kerberos authentication system is not distributed with Postgres. 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.

Note: 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.

Inquiries regarding your Kerberos should be directed to your vendor or MIT Project Athena. Note that FAQLs (Frequently-Asked Questions Lists) are periodically posted to the Kerberos mailing list (send mail to subscribe), and USENET news group.

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 the Postgres account. 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.

Operation: After initial installation, Postgres should operate in all ways as a normal Kerberos service. For details on the use of authentication, see the PostgreSQL User's Guide reference sections for postmaster and psql.

In the Kerberos Version 5 hooks, the following assumptions are made about user and service naming(also, see Table below):

User principal names (anames) are assumed to contain the actual Unix/Postgres user name in the first component.
The Postgres service is assumed to be have two components, the service name and a hostname, canonicalized as in Version 4 (i.e., with all domain suffixes removed).

                Table: Kerberos Parameter Examples
 ------------------------------------------------------
 Parameter      Example
 ------------------------------------------------------
 user           frew@S2K.ORG
 user           aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.ORG
 host           postgres_dbms/ucbvax@S2K.ORG 
 ------------------------------------------------------

Next Previous Contents