Security and Access Restrictions

PostgreSQL's security systems can be divided into two components . One part is access restriction for certain users and certain objects. These settings are valid for objects inside a database.

The second part has to do with global access restriction and user authentification.

With the help of the operating system, it is possible to achieve even higher levels of security by using commonly known network security methods , such as Netfilter or SSH.

In this section, we try to give you an overview of what can be done with PostgreSQL's security features, and how you can make your database as secure as possible.

User Authentification

When working with more than just one user, authentification is an essential component of every application. PostgreSQL provides lots of methods for dealing with users efficiently . This section will guide you through the basics of user authentification.

Overview

When a user tries to connect to a database, PostgreSQL checks whether the user is allowed to connect and which objects he or she is allowed to access. This process is called authentification.

PostgreSQL offers two types of client authentification. Authentification can be done by client or by database. Both methods can be used in combination with various authentification methods.

Authentification by client means that the database checks the host that wants to establish the connection. This is done with the help of the IP address.

Authentification by database means that the database checks whether a user has the right to access a certain database.

To configure the client authentification system, we have to use a file called pg_hba.conf that can be found in the $PGDATA directory on your machine.

pg_hba.confpg_hba.conf contains the information about what hosts can connect to which databases. Every time a user tries to connect to the database, the pg_hba.conf is read. This is very convenient because you don't have to restart your PostgreSQL server when making simple changes in your configuration. This is also very important on high-availability systems.

Three types of lines can be found in pg_hba.conf :

• Comments ” Comments are lines that start with # .

• Empty ” Empty lines are ignored.

• Record ” Contains "real" configuration data.

Records consist of fields that can be separated by tabs or spaces. All spaces at the beginning or the end of a line are ignored. One record can only be one line; there is no way to continue records across multiple lines.

PostgreSQL supports three types of records:

• host A host record defines a list of hosts that are allowed to connect to the database. This works only when the server is started with the -i flag enabled; otherwise , the database won't accept connections via a TCP/IP network.

• hostssl This works if you have compiled your server with SSL enabled. You also have to enable SSL when starting the server. This can be done with the -l option. hostssl is also used to define a list of hosts allowed to connect to your database.

• local These records tell the server which configuration to use when connecting to a local database.

The following two lines of code should give you an impression of how pg_hba.conf records look.

 host/hostssl DBNAME IP_ADDRESS ADDRESS_MASK AUTHTYPE [AUTH_ARGUMENT] local DBNAME AUTHTYPE [AUTH_ARGUMENT] 

Before we get to a sample configuration, let's take a look at PostgreSQL's authentification methods:

• trust PostgreSQL establishes a connection, no matter who wants to connect to the server.

• reject reject is the opposite of trust . In the case of reject , nobody is allowed to connect to the database. In many cases, reject can be a good method for debugging purposes when you want to reject connections during your work.

• password Every user who wants to connect to the database has to use his or her password. This is no secure way of connecting to your server because using password does not make the database transfer the password in an encrypted way. This is especially dangerous over Internet connections because hackers can discover the passwords easily by using a packet sniffer.

• crypt This method transfers the password in an encrypted way over the network. The password is encrypted by using a simple challenge/response protocol.

• krb4 Kerberos V4 is another method for user authentification and is only available over TCP/IP connections. To use Kerberos with PostgreSQL, you have to enable it at compile time. This can be done with ./configure --with-krb4 --with-krb5 .

• krb5 Kerberos V5 is the successor of Kerberos V4 and is also only available over TCP/IP connections.

• ident ident is a very useful feature of PostgreSQL. The database server asks the client system who wants to connect to the server. PostgreSQL decides whether the user is allowed to establish a connection. ident authentification can only be done over TCP/IP networks. ident authentification is based on the "Identification Protocol" (RFC 1413). On Unix systems, an ident server is usually listening on TCP port 113. Because it is fairly easy for a hacker to fake the behavior of an ident server, we recommend that you use it only in highly-protected LAN networks. In a public network, a hacker could pretend to be any user. In RFC 1413, it says, "The identification Protocol is not intended as an authorization or access control protocol." Please keep that in mind when working with public PostgreSQL systems.

To help you better you understand what we have just described theoretically, we have included a pg_hba.conf file that contains some examples of typical entries:

 # TYPE       DATABASE    IP_ADDRESS    MASK                AUTHTYPE  MAP host         template1   192.168.1.0 255.255.255.0       ident     sameuser host         template1   192.168.12.10 255.255.255.255    crypt host         all        192.168.2.1  255.255.255.255     reject host         all        0.0.0.0       0.0.0.0             trust 

The first line allows every user that connects from network 192.168.1.x because the user was identified by the ident server. The access is restricted to the database called template1 .

The second line allows users on machine 192.168.12.10 to connect to the PostgreSQL server by using their passwords. The password is transmitted over the network in an encrypted way.

The third line rejects all users from host 192.168.2.1. The restriction is defined for all databases. Users from that machine won't have access to the database server at all.

The fourth line allows anyone from any machine to connect to the database, no matter who the user is. 0.0.0.0 means that any IP address is considered to be valid.