PostgreSQL Security Missteps and Tips

Security, when done right, not only protects your data but improves performance, system stability, and enhances the development life-cycle. Because PostgreSQL security can easily become an all-encompassing activity, we’ll deal with the most common mechanisms.

Three files control security in the postgres data cluster but for our purposes, we’ll deal only with postgresql.conf and pg_hba.conf, leaving the third file, pg_ident.conf, alone.

About postgresql.conf

Secure Socket Layer

Enabling the use of the secure socket layer, SSL, makes it possible to protect client sessions from sniffing sensitive information such as passwords and data such as credit card social security numbers across the network. Typical use-case includes superuser administrative sessions, monitoring services such as Percona Monitoring and Management (PMM), and maintenance activities used for logical backups and cron jobs. As well, all master-slave replication processes must use SSL in order to protect sensitive data.

ssl: This parameter allows the use of a secure socket layer certificate allowing for encrypted sessions. Although enabled by default on Debian derived distributions, such as Ubuntu, it uses a self-signed ssl-snakeoil certificate. Note that this parameter, however, is not activated on Redhat/Centos distributions and additionally requires either a self-signed certificate or one signed by a Certificate Authority, such as Let’s Encrypt.

ssl_cert_file: The name of the file containing the SSL server certificate.

ssl_key_file: The file containing the SSL server private key.

UNIX Domain Sockets

These parameters handle unix user and group permissions on the file system. Reasons justifying use-case include those situations where many users and processes are permitted login privileges onto the postgres host but are not supposed to be able to login to the server. For example, a connection pooler owned by a non-postgres process accesses postgres but a unix account that logs in for the purpose of using this host to jump to another one should not be permitted. An interesting, and frankly bizarre, example is to interdict the postgres superuser from accessing the very service it runs by setting the directory or socket permissions after service startup. If you want to cause havoc, just set these variables the wrong way and I guarantee you some people will not think to look here to troubleshoot.

unix_socket_directories: This parameter specifies the directory of the Unix-domain socket(s) on which the server is to listen for connections from client applications.

unix_socket_group: This parameter sets the owning group of the Unix-domain socket(s).

unix_socket_permissions: This parameter sets the access permissions of the Unix-domain socket(s). Setting the permissions to 0777, which is the default, allows anyone to connect. Please note that only write permission matter. Setting read and executable permissions have no effect on securing the socket.

About pg_hba.conf

Improperly configuring the host-based authentication file, pg_hba.conf, is arguably the single most common reason people lose control of their data.

Mastering the configuration is simple: much like a firewall, always remember that postgres host authentication is a rules-based system. The first line that matches the connection rule is the one that it uses. Beware adding newer, more strict, rules at the bottom of the file without removing the old rules located higher up the file first. Otherwise, the rules located at the bottom of the file won’t work since they will never be reached.

The Golden Rules

In regards to localhost connections:

• All local connections should use Domain Sockets i.e. TYPE “local”
• All local connections, used for administrative activities, should be limited to ROLE “postgres” and use METHOD “peer”.
• If you want one or people to administrate your system then escalate your Unix accounts using “sudo su – postgres” thus leveraging user account history to track login activities.

In regards to remote connections:

• Eliminate remote connections using METHOD “trust” and “password”.
• Use METHOD “md5” but, if you can go one step further, use “scram” authentication (some clients can’t handle this).
• All administrative connections should be executed via SSL sessions i.e. TYPE “hostssl”.
• Never, NEVER, NEVER, use the superuser for regular client application processes.

In regards to replication:

• Use a ROLE exclusively dedicated to replication.
• Enforce SSL sessions.
• Limit your CIDR to between master and slave.

And remember; always add a rule METHOD “reject” for each user and database connection at the bottom of your file in order to account for unforeseen network conditions (never underestimate Murphy’s Law).

Summary

There is a lot more you can do within the confines of postgres. For example, the pg_ident.conf file leverages the ident service. This much under-appreciated piece of technology that, despite its age having been first created at the dawn of the networking computers, when properly configured can be used to authenticate localhost user accounts preempting the use of passwords.

Leveraging the various third-party technologies and services used in the industry, including LDAP, Kerberos, Active Directory, etc., opens even more vistas streamlining the entire security and authentication paradigm.

by Robert Bernier via Percona Database Performance Blog