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 AuthentificationWhen 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. OverviewWhen 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 :
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:
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:
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. |