Integrating PostgreSQL With Authentication Systems

PostgreSQL is one of the most secure databases in the world. Database security plays an imperative role in the real-world mission critical environments. It is important to ensure databases and the data is always secured and is not subjected to un-authorized access thereby compromising the data security. Whilst PostgreSQL provides various mechanisms and methods for users to access the database in a secured manner, it can also be integrated with various external authentication systems to ensure enterprise standard database security requirements are met.

Apart from providing secured authentication mechanisms via SSL, MD5, pgpass and pg_ident etc., PostgreSQL can be integrated with various other popular enterprise grade external authentication systems. My focus in this blog will be on LDAP, Kerberos and RADIUS with SSL and pg_ident.

LDAP

LDAP refers to Lightweight Directory Access Protocol which is a popularly used centralized authentication system. It is a datastore which stores the user credentials and various other user related details like Names, Domains, Business Units etc. in the form of a hierarchy in a table format. The end users connecting to the target systems (E.g., a database) must first connect to LDAP server to get through a successful authentication. LDAP is one of the popular authentication systems currently used across organizations demanding high security standards.

LDAP + PostgreSQL

PostgreSQL can be integrated with LDAP. In my customer consulting experience, this is considered one of the key capabilities of PostgreSQL. As the authentication of the username and password takes place at the LDAP server, to ensure users can connect to the database via LDAP, the user account must exist in the database. In other words, this means the users when attempting to connect to PostgreSQL are routed to the LDAP server first and then to the Postgres database upon successful authentication. Configuration can be made in the pg_hba.conf file to ensure connections are routed to the LDAP server. Below is a sample pg_hba.conf entry –

host    all    pguser   0.0.0.0/0    ldap ldapserver=ldapserver.example.com ldapprefix="cn=" ldapsuffix=", dc=example, dc=com"

Below is an example of an LDAP entry in pg_hba.conf:

host    all    pguser   0.0.0.0/0    ldap ldapserver=ldapserver.example.com ldapprefix="cn=" ldapsuffix=", ou=finance, dc=example, dc=com"

When using non-default ldap port and TLS:

ldap ldapserver=ldapserver.example.com ldaptls=1 ldapport=5128 ldapprefix="uid=" ldapsuffix=",ou=finance,dc=apix,dc=com"

Understanding The Above LDAP Entry

• LDAP uses various attributes and terminologies to store / search for a user entry in its datastore. Also, as mentioned above, user entries are stored in hierarchy.
• The above pg_hba.conf ldap entries consists of attributes called CN (Common Name), OU (Organization Unit) and DC (Domain Component) which, are termed as Relative Distinguished Names (RDN), these sequence of RDN together become something called DN (Distinguished Name). DN is the LDAP object based which, the search is performed in the LDAP data store.
• LDAP attribute values like CN, DC, OU etc. are defined in LDAP’s Object Classes, which can be provided by the systems experts who built the LDAP environment.

Will That Make LDAP Secured Enough?

Maybe not. Passwords communicated over the network in an LDAP environment are not encrypted, which can be a security risk as the encrypted passwords can be hacked. There are options to make the credentials communication more secure.

1. Consider configuring LDAP on TLS (Transport Layer Security)
2. LDAP can be configured with SSL which is another option

Tips to achieve LDAP integration with PostgreSQL

(for Linux based systems)

• Install appropriate openLDAP modules based on operating system version
• Ensure PostgreSQL software is installed with LDAP libraries
• Ensure LDAP is integrated well with Active Directory
• Familiarize with any existing BUGs in the openLDAP modules being used. This can be catastrophic and can compromise security standards.
• Windows Active Directory can also be integrated with LDAP
• Consider configuring LDAP with SSL which is more secure. Install appropriate openSSL modules and be aware of BUGs like heart-bleed which can expose the credentials transmitted over the network.

Kerberos

Kerberos is an industry-standard centralized authentication system popularly used in organizations and provides encryption-based authentication mechanism. The passwords are authenticated by a third-party authentication server termed as KDC (Key Distribution Centre). The passwords can be encrypted based on various algorithms and can only be decrypted with the help of shared private keys.  This also means, passwords communicated over the network are encrypted.

PostgreSQL + Kerberos

PostgreSQL supports GSSAPI based authentication with Kerberos. The users attempting to connect to the Postgres database, will be routed to KDC server for authentication. This authentication between clients and KDC database is performed based on shared private keys and upon successful authentication, the clients would now hold Kerberos based credentials. The same credentials are subjected to validation between the Postgres server and the KDC which will be done based on the keytab file generated by Kerberos. This keytab file must exist on the database server with appropriate permissions to the user owning the Postgres process.

The Kerberos configuration and connection process –

• Kerberos based user accounts must generate a ticket ( a connection request ) using “kinit” command.

• A keytab file must be generated using “kadmin” command for a fully qualified Kerberos based user account (principal) and then Postgres would use the same keytab file to validate the credentials. Principals can be encrypted and added to existing keytab file using “ktadd” command. Kerberos encryption supports various industry standard encryption algorithms.

  The generated keytab file must be copied across to the Postgres server, it must be readable by the Postgres process. The below postgresql.conf parameter must be configured:

  krb_server_keyfile = '/database/postgres/keytab.example.com'

  If you are particular about case-sensitivity, then, use the below parameter

  krb_caseins_users which is by default “off”  (case sensitive)

• An entry must be made in the pg_hba.conf to ensure connections are routed to KDC server

  Example pg_hba.conf entry

  # TYPE DATABASE       USER    CIDR-ADDRESS            METHOD
  host     all                     all         192.168.1.6/32            gss include_realm=1 krb_realm=EXAMPLE.COM

  Example pg_hba.conf entry with map entry

  # TYPE DATABASE       USER    CIDR-ADDRESS            METHOD
  host     all                     all         192.168.1.6/32            gss include_realm=1 krb_realm=EXAMPLE.COM map=krb

• A user account attempting to connect must be added to the KDC database which is termed as principal and the same user account or a mapping user account must exist in the database as well

  Below is an example of a Kerberos principal

  [email protected]

  pguser is the username and the “example.com” is the realm name configured in the Kerberos config (/etc/krb5.conf) in the KDC server.

  In the kerberos world, principals are in an email like format (username@realmname) and the database users cannot be created in the same format. This makes DBAs think of creating a mapping of database user names instead and ensure principals connect with mapped names using pg_ident.conf.

  Below is an example of a map name entry in pg_ident.conf

  # MAPNAME           SYSTEM-USERNAME               GP-USERNAME
     mapuser               /^(.*)EXAMPLE.DOMAIN$      admin

Will That Make Kerberos Secured Enough ?

Maybe not. User credentials communicated over the network can be exposed, hacked. Though Kerberos encrypts the principals, they can be stolen, hacked. This brings in the need for implementing network layer security. Yes, SSL or TLS is the way to go. Kerberos authentication system can be integrated with SSL or TLS. TLS is the successor of SSL. It is recommended to have Kerberos configured with SSL or TLS so that the communication over the network is secured.

TIPS

• Ensure krb* libraries are installed
• OpenSSL libraries must be installed to configure SSL
• Ensure Postgres is installed with the following options

  ./configure --with-gssapi --with-krb-srvnam --with-openssl

RADIUS

RADIUS is a remote authentication service network protocol which provides centralized

Authentication, Authorization and Accounting (AAA). Username / password pairs are authenticated at the RADIUS server. This way of centralized authentication is much straight orward and simpler compared to other authentication systems like LDAP and Kerberos which involves a bit of complexity.

RADIUS + PostgreSQL

PostgreSQL can be integrated with RADIUS authentication mechanism. Accounting is not supported in Postgres yet. This requires database user accounts to exist in the database. Connections to the database are authorized based on the shared secret termed as “radiussecret”.

An entry in the pg_hba.conf config is essential to route the connections to radius server for authentication.

Example pg_hba.conf entry

hostssl             all        all        0.0.0.0/0         radius  radiusserver=127.0.0.1 radiussecret=secretr radiusport=3128

To understand the above entry –

“radiusserver” is the host IP address of the RADIUS server where users are routed for authentication. This parameter is configured in the /etc/radiusd.conf in the RADIUS server.

“radiussecret” value is extracted from clients.conf. This is the secret code which uniquely identifies the radius client connection.

“radiusport” can be found in /etc/radiusd.conf file. This is port on which radius connections will be listening.

Importance of SSL

SSL (Secure Socket Layer) plays an imperative role with external authentication systems in place. It is highly recommended to configure SSL with an external authentication system as there will be communication of sensitive information between clients and the servers over the network and SSL can further tighten the security.

Performance Impact of Using External Authentication Systems

An effective and efficient security system comes at the expense of performance. As the clients/users attempting to connect to the database are routed to authentication systems to establish connection, there can be performance degradation. There are ways to overcome performance hurdles.

• With external authentication mechanism in place, there could be a delay when establishing a connection to the database. This could be a real concern when there are huge number of connections being established to the database.
• Developerss need to ensure that anunnecessary high number of connections are not made to the database. Multiple application requests being served via one connection would be advantageous.
• Also, how long each request is taking at the database end plays an important role. If the request takes longer to complete, then subsequent requests would queue up. Performance tuning of the processes and meticulously architecting the infrastructure will be key!
• Database and infrastructure must be efficiently architected and adequately capacitated to ensure good performance.
• When doing performance benchmarking, ensure SSL is enabled and the average connection establishment time must then be evaluated.

Integrating External Authentication Systems With ClusterControl – PostgreSQL

PostgreSQL instances can be built and configured automatically via ClusterControl GUI. Integrating external authentication systems with PostgreSQL Instances deployed via ClusterControl is pretty much similar compared to integration with traditional PostgreSQL instances and in-fact is a bit simpler. Below is an overview of the same –

• ClusterControl installs PostgreSQL libraries enabled with LDAP, KRB, GSSAPI and OpenSSL capabilities
• Integration with external authentication systems requires various parameter configuration changes on the postgresql database server which can be done using ClusterControl GUI.