PostgreSQL with The Best Security Applications

1. Summary
This white paper provides a framework and a set of recommendations for securing and protecting the PostgreSQL database. We discuss a layered security model that covers physical security, network security, server access control, database access management, and data security. Although all of these features are equally important, this report focuses on the database and data security features specific to PostgreSQL. In our discussion of specific security issues related to the database and the data managed within it, we use a common AAA (Authentication, Authorization, and Accounting) approach for computer and network security.

Most of the recommendations in this white paper apply to PostgreSQL (community edition) and EDB Postgres™ Advanced Server (EDBTM), which is an enterprise-class, feature-rich commercial distribution of PostgreSQL. Advanced Server provides additional security enhancements not found in PostgreSQL in the same way, such as Password Profiles, Auditing, Data Redaction, and SQL Injection Protection.

This document has been updated for PostgreSQL 12 and EDB Postgres Advanced Server 12.

2. Introduction
We can think of security as layers and recommend the strategy of granting the least access necessary for any business or role by blocking unnecessary access at the first opportunity.
First, it is to secure physical access to the server
2- The next step is to generally restrict access to your corporate network
3- The next step is to restrict access to the database server.
4- The next step is to restrict access to the database application
5- The next step is to restrict access to the data contained within.
6- The next step is to secure the stored data.

We can think of security as layers and propose a strategy of granting the minimum required access.

General Recommendations

• Keep your operating system and database up to date. EDB’s support subscriptions provide timely notifications of appropriate security updates and patches for Postgres. There are many tools available that can monitor operating system updates, which can be integrated with package management systems such as yum/dnf or apt.
• Do not leave postmaster ports open to the internet unless it is truly critical for your business. Protect this port properly with a firewall; if this is not possible, use a read-only standby database instead of a read-write database on this port. Audited network port forwarding for all connections is another valid option.
• Isolate the database port from other network traffic using subnetting or other techniques.
• Grant users the minimum access privileges necessary to do their jobs, no more; strictly reserve the use of privileged user accounts for tasks or roles when absolutely necessary.
• Restrict system administrators' access to configuration files (postgresql.conf and pg_hba.conf) and log files (pg_log).
• Deny server login by database superuser roles (postgres in PostgreSQL, enterprisedb in EDB Postgres Advanced Server). Enable authorized user access only when necessary in exceptional circumstances.
• Give all users their own login credentials; shared access credentials are not a recommended practice and make auditing more complex. As an option, use the edb_audit_tag feature (available only in EDB Postgres Advanced Server) to allow applications to add more auditing information to sessions originating from application-level connections.
• To prevent unauthorized access to your database, do not rely solely on your frontend application; integrate database security with enterprise-grade authentication and authorization models such as LDAP/AD or Kerberos.
  Keep backups and have a tested recovery plan. No matter how well you secure your system, an intruder can delete or modify your data. Make sure your backups are also securely stored to prevent unauthorized access.

It can be useful to think about security in terms of the AAA model developed for network and computer security. AAA stands for Authentication, Authorization, and Auditing.

• Authentication: Verify that the user is the person they claim to be.
• Authorization: Verify that user access is granted.
• Audit (or accountability): Log all database activities, including username and time information, to log files.

Not all features fit neatly into these categories, but the AAA model provides a useful framework for this technical report.

Implementation of PostgreSQL Security Features to Authentication, Authorization, and Auditing Framework

The following sections provide detailed information on how to incorporate PostgreSQL security features into the Authentication, Authorization, and Accounting (AAA) Framework.

3.1. Authentication

— The PG_hba.conf (PostgreSQL host-based access) file restricts access based on username, database, and source IP address (if the user is connecting via TCP/IP). Authentication methods are also assigned in this file. The authentication method (or methods) you choose depends on your use case.

— Kerberos/GSSAPI — PostgreSQL supports GSSAPI, which includes Kerberos authentication according to RFC 1964. GSSAPI provides automatic authentication (single sign-on) for supporting systems. The authentication itself is secure, but the data sent over the database connection is not encrypted unless GSS or SSL encryption is used.

— SSPI — Use this feature if you are using a Windows system and want to implement Single Sign-On (SSO) authentication.

— LDAP should only be used if Kerberos—involving both SSPI and GSSAPI—is not an option. LDAP is less secure because passwords are transmitted to the LDAP server and can easily be intercepted in an insecure manner.

— LDAP and RADIUS — LDAP and RADIUS are useful when you have a large number of users and need to manage passwords from a centralized location. The advantage of this centralization is that it keeps your pg_hba.conf file small and manageable, and with centralization, your users can be provided with a “unified password experience” across your infrastructure. Since you will be relying on this service and connection when accessing your database, a robust infrastructure is required for both LDAP and RADIUS.

— RADIUS should not be used as its encryption is weak and it relies on MD5 hashing for credentials.

— Cert — TLS certificate authentication (sometimes called SSL) can be used for encrypting traffic over the wire and for authentication. Certificates are typically used in machine-to-machine communication.

— md5 — md5 stores username and password information in the database; it can be a suitable alternative if you have a very small number of users. Since passwords are securely hashed, SCRAM is preferred over md5.

— Scarm — if you have a very small number of trusted users, you can use scram-sha-256 authentication. Since passwords are securely hashed, SCRAM is preferred over md5.

— Reject (Reddet) — use this method to reject specific users, connections to specific databases, and/or specific source IP addresses.

— Trust (Güven) — since trusted authentication allows a matched client to connect to the server without further authentication, it should only be used in exceptional circumstances.

You need to know the results of all authentication methods completely. For more detailed research on the ones given above and other authentication methods, you can review the PostgreSQL documentation.

As stated in the introduction section, access to the pg_hba.conf file must be restricted to system administrators. Try to keep this file properly restricted; larger, more complex files are harder to maintain and are more likely to contain incorrect or outdated entries. Review this file regularly for unnecessary entries.

3.2. Word Profiles

Starting from version 9.5, Advanced Server supports Oracle-compatible password profiles using MD5 or SCRAM authentication. A password profile is a set of password properties that allows the DBA to easily manage a group of roles sharing similar authentication requirements. Each profile can be associated with one or more users. When a user connects to the server, the server applies the profile associated with the login role.

For more information, please refer to Section 2.3 “Profile Management” of the EDB’s Database Compatibility for Oracle® Developer’s Guide.

Profiles can be used for the following operations:
• Specifying the allowed number of failed login attempts.
Account lockout due to excessive failed login attempts.
• Marking a password to expire.
• Definition of the grace period after the parole ends.
• Defining rules for password complexity.
• Defining rules to limit password reuse.

3.3. Authorization

Once the user has authenticated properly, you must grant them permission to view data and operate on the database. As previously mentioned, grant only the privileges required for the user to perform the relevant task and do not allow shared (group) login credentials. Manage users and groups in PostgreSQL through role assignments. A role can represent an individual user or a group of users. In Postgres, roles are created at the cluster (or database server) level. This means that roles apply to all databases defined for the cluster/database server, and it is crucial to limit role permissions appropriately. Permissions can be applied to database objects (tables, views, functions, etc.), rows within tables, and data reduction policies.

3.3.1 – Access to database objects

The privileges and warnings assigned are summarized in the PostgreSQL CREATE ROLE documentation:

• • • Revoke CREATE privileges from all users and grant them back only to trusted users.
    • Disallow the use of functions or triggers written in untrusted procedural languages.
    • SECURITY DEFINER functions allow users to run functions with a controlled high privilege level, but an accidentally carelessly written function can reduce security. Check the documentation for more details (Writing Security Definer Functions Safely section of CREATE FUNCTION).
    • Database objects should be owned not by a role that any application user can connect to, but ideally by a secure role with very restricted access to the database (e.g., only from a Unix Domain socket). Thus, the chance of an attacker modifying or dropping objects is minimized. While this is preferred from a security standpoint, it can create issues with application frameworks that manage the schema themselves; such functionalities must be implemented carefully.

Note that when Log_Statement is set to ‘ddl’ or higher, changing a role's password via the ALTER ROLE command will result in the password being exposed in the logs, except in EDB Postgres Advanced Server 11 and later, where edb_filter_log.redact_password_command instructs the server to redact passwords from the log file. For more information Click.

When authentication credentials (e.g., usernames and passwords) are kept in a table, the use of statement logging can expose this information even if the table is specifically secure. Similarly, if sensitive information is used in queries (for example, any personally identifiable information as a key), these parameters can be exposed through statement logs.

3.3.2 – Views

Access to database objects, which are views, can be controlled as explained above. By creating a VIEW on a table and restricting the permissions for this VIEW, it is possible to limit data visibility to specific user groups. Since it is thought that extra precautions must be taken to avoid potential security issues as described by Robert Haas, PostgreSQL versions 9.2 and later offer the CREATE VIEW WITH (security_barrier) option.

3.3.3 – Row-Level Security

PostgreSQL introduced Row-Level Security (RLS) in version 9.5. RLS provides highly granular access to table rows depending on the user role. This includes SELECT, UPDATE, DELETE, and INSERT operations. For more information You can find it here.

EDB Postgres Advanced Server includes Oracle-compatible implementations of ADD_POLICY, DROP_POLICY, and UPDATE_POLICY in the DBMS_RLS package. For more information Click.

3.3.4 – Data Minimization

Data reduction – the ability to mask certain data elements or selectively hide data for specific user groups – is another technique used to manage data access. EDB Postgres Advanced Server introduced the data reduction feature in version 11.

Data masking is a policy-based tool that works with PostgreSQL roles to grant or revoke read access to specific data elements. For example, while a group of users sees social security numbers as XXX-XX-1235, members with the data manager role see all the details. Additional information about data masking can be found here.

3.4 Audit

Advanced Server provides the ability to generate audit reports. Database auditing allows database administrators, auditors, and operators to track and analyze database activities to support complex auditing requirements. These audited activities consist of database access and usage, along with data creation, modification, or deletion. The auditing system relies on configuration parameters defined in the configuration file.

We recommend increasing the audit level to audit the following:
• User connections
• DDL changes
• Data changes
• Data views

Very detailed auditing levels can result in a large number of logs; only collect the level of logs you need. With Postgres, you can set log levels for each user and each database. Frequently review your audit logs for abnormal activity. Establish a chain of custody for your logs.

Please note that a high logging level can cause passwords to appear in the logs along with their storage in the database. EDB Postgres Advanced Server introduced the edb_filter_log.redact_password_commands extension in version 11 to instruct the server to redact stored passwords from the audit log file.

Advanced Server enables database and security administrators, auditors, and operators to monitor and analyze database activities using the EDB Audit Logging feature.

3.5 Data Encryption

PostgreSQL offers various levels of encryption, providing flexibility in preventing data exposure due to database server theft, unethical administrators, and insecure networks:

• • User connections
  • DDL changes
  • Data changes
  • Data visualizations

More information about these options can be found in the PostgreSQL documentation.

If you are concerned about data interception during transfer between a client and a database, enable SSL in the postgresql.conf file unless you are sure that data interception does not pose a risk. SSL encryption can add overhead and certificate management can be difficult, but in general, this is a best practice.

You can also encrypt data either within the database or at the file system level (using one or the other). You can find more information about Transparent Data Encryption on EDB's blog. With this encryption option, data is decrypted as soon as it is read from the file system, allowing database administrators to view the data; therefore, roles and privileges must be locked down. Other options include the use of Thales Vormetric Transparent Encryption (VTE).

Use the pgcrypto contrib module to encrypt data per column. This method has several drawbacks:

• • There is a potential performance bottleneck depending on the size of the table.
  • Encrypted fields cannot be searched or indexed.
  • Encryption must be applied during table creation and also requires advance planning.
  • Encryption key management can also be complex.

In addition to this, your application needs to perform the encryption/decryption process, so that every change in the database remains encrypted to prevent an unethical database administrator from viewing the data.

3.6 SQL Injection Attacks

An SQL injection attack is an attempt to compromise a database by executing SQL commands that provide the attacker with clues about the database's content, structure, or security. Preventing an SQL injection attack is normally the responsibility of the application developer. Database administrators usually have very little or no control over the potential threat.

PostgreSQL uses parameterized queries as the standard method to prevent SQL injection attacks. If you are using EDB Postgres Advanced Server, we recommend using the SQL/Protect module to protect against SQL injection attacks. SQL/Protect provides an additional layer of security to normal database security policies by inspecting incoming queries against general SQL profiles. SQL/Protect returns control to the database administrator by alerting them to potentially dangerous queries and blocking those queries. For more information Click.

Source: Security Best Practices for PostgreSQL
© Copyright EnterpriseDB Corporation 2020 EnterpriseDB Corporation
34 Crosby Drive Suite 201 Bedford, MA 01730
EnterpriseDB and Postgres Enterprise Manager are registered trademarks of EnterpriseDB Corporation. EDB and EDB Postgres are trademarks of EnterpriseDB Corporation. Oracle is a registered trademark of Oracle, Inc. Other trademarks may be trademarks of their respective owners.