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 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.conffile, wherehbastands for host-based authentication. - It is good to know the default initial authentication settings shipped with PostgreSQL distribution.
- The
pg_hba.conffile is often located in the data directory, but it also can be specified in thepostgresql.confconfiguration file. - When changing the authentication, one needs to send a
SIGHUPsignal, 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.confrecords matters. The session connection is compared withpg_hba.confrecords 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.
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.53but 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
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 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.