Chapter 8. PostgreSQL Security

Data protection and security are essential for the continuity of business. Data protection is not nice to have, but it is required by the legal system. Sensitive data, such as user information, email addresses, geographical addresses, and payment information, should be protected against any data breach. There are several other topics related to data security, such as data privacy, retention, and loss prevention.

There are several levels of data protection, often defined in the data protection policy and by the country's legal system. Data protection policy often defines data dissemination to other parties, users authorized to access the data, and so on. Data should be protected on different levels, including transferring and encrypting data on storage devices. Data security is a huge topic and often there are data security managers dedicated only to these tasks.

Authentication in PostgreSQL

Authentication answers the question, who is the user? PostgreSQL supports several authentication methods, including trust, ident, password, GSSAPI, SSPI, LDAP, PAM, and so on. To understand authentication, one needs to have the following information:

  • Authentication is controlled via a pg_hba.conf file, where hba stands for host-based authentication.
  • It is good to know the default initial authentication settings shipped with PostgreSQL distribution.
  • The pg_hba.conf file is often located in the data directory, but it also can be specified in the postgresql.conf configuration file.
  • When changing the authentication, one needs to send a SIGHUP signal, and this is done via several methods based on the PostgreSQL platform. Also note that the user who sends the signal should be a superuser or the postgres or a root system user on the Linux distribution; again, this depends on the platform.
    psql -U postgres -c "SELECT pg_reload_conf();"
    sudo service postgresql reload
    sudo /etc/init.d/postgresql reload
    sudo Kill -HUP <postgres process id>
    
  • The order of pg_hba.conf records matters. The session connection is compared with pg_hba.conf records one by one until it is rejected or the end of the configuration file is reached.
  • Finally, it is important to check the PostgreSQL log files to determine whether there are errors after configuration reload.

PostgreSQL pg_hba.conf

As in postgresql.conf, the pg_hba.conf file is composed of a set of records, lines can be commented using the hash sign, and spaces are ignored. The structure of the pg_hba.conf file record is as follows:

host_type database user [IP-address| address] [IP-mask] auth-method  [auth-options]

The host_type part of this query can be:

  • Local: This is used in Linux systems to allow users to access PostgreSQL using socket connections
  • Host: This is to allow connections from other hosts, either based on the address or IP address, using TCP/IP with and without SSL encryption
  • Hostssl: This is similar to host, but the connection should be encrypted using SSL in this case
  • Hostnossl: This is also similar to host, but the connection should not be encrypted in this case

The database part of the query is the name of the database that the user would like to connect to. For flexibility, one could also use a comma-separated list to specify several databases, or one could use all to indicate that the user can access all the databases in the database cluster. Also, the sameuser and samerole values can be used to indicate that the database name is the same as the username or the user is a member of a role with the same name as the database.

The user part of the query specifies the database user's name; again, the all value matches all users. The IP address, address, and IP subnet mask are used to identify the host where the user tries to connect from. The IP address can be specified using a CIDR or dot decimal notation. Finally, the password authentication methods can be trust md5, reject, and so on.

The following are some typical examples of configuring a PostgreSQL authentication:

  • Any user on the PostgreSQL cluster can access any database using the UNIX domain socket, as shown in the following database table:
    # TYPE    DATABASE    USER  ADDRESS  METHOD
    Local    all    all      trust
    
  • Any user on the PostgreSQL cluster can access any database using the local loopback IP address, as in this table:
    # TYPE    DATABASE  USER  ADDRESS     METHOD
    Host    all    all  127.0.0.1/32    trust
    host    all    all  ::1/128    trust
    
  • The following database table rejects all connections that come from 92.168.0.53 but accepts all the connections that come from the range, 192.168.0.1/24:
    # TYPE    DATABASE  USER  ADDRESS    METHOD
    Host    all    all  92.168.0.53/32  reject
    Host    all    all  92.168.0.1/24  trust
    

Listen addresses

The listen_addresses is defined in postgresql-conf. The PostgreSQL listen_addresses connection setting is used to identify the list of IP addresses that the server should listen to from client applications. The listen_ addresses are comma-separated lists of host names or IP addresses. Changing this value requires a server restart. In addition to the preceding, one should note the following:

  • The default value is localhost
  • Giving an empty list means that the server should accept only Unix socket connection
  • The value * indicates all

Let's test the listen address Unix socket connection by changing the listen_addresses to '', as follows:

#Change the listen address
psql –c "ALTER SYSTEM SET listen_addresses ='';"
# Restart the server
/etc/init.d/postgresql restart
# connect using host
psql -h localhost -c 'SELECT 1';
psql: could not connect to server: Connection refused
  Is the server running on host "localhost" (127.0.0.1) and accepting
  TCP/IP connections on port 5432?
# connect using UNIX domain socket
psql -c 'SELECT 1';
 ?column?
----------
        1
(1 row)

Authentication best practices

Authentication best practices depend on the whole infrastructure setup, the application's nature, the user's characteristics, data sensitivity, and so on. For example, the following setup is common for start-up companies: the database application, including the database server, is hosted on the same machine and only used from one physical location by intracompany users.

Often, database servers are isolated from the world using firewalls; in this case, one can use the md5 authentication method and limit the IP addresses so that the database server accepts connections within a certain range or set. Note that it is important not to use a superuser or database owner account to connect to the database because if this account were hacked, the whole database cluster would be exposed.

If the application server—business logic—and database server are not on the same machine, one can use a strong authentication method, such as LDAP and Kerberos. However, for small applications where the database server and application are on the same machine, the md5 authentication method and limiting the listen address to the localhost might be sufficient.

To authenticate an application, it is recommended to use only one user and try to reduce the maximum number of allowed connections using a connection pooling software to better tune PostgreSQL resources. Another level of security might be needed in the application business logic to distinguish different login users. For real world users, LDAP or Kerberos authentication is more desirable.

Furthermore, if the database server is accessed from the outer world, it is useful to encrypt sessions using SSL certificates to avoid packet sniffing.

Also, one should remember to secure database servers, which trust all localhost connections as anyone who accesses the localhost can access the database server. Finally, when using MD5, it is important also to secure it in the business logic level and add salt as there are dictionaries to crack MD5 hashes.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.4.191