Picture an organisation with many database servers hosting many databases (not hard to imagine especially for organisations that develop their own applications and require a variety of database testing environments). Once you've got that visualisation; imagine trying to administer appropriate access to those multiple databases for a number of new developers joining the team. Bear in mind that auditing down to individual users is required (so no generic role access allowed).
Not so straight forward when object ownership and privileges are only granted to a 'database role'; and the only implementation of a 'database role' exists at the database cluster level. Yes, while we can have generic users with predefined privileges to our database objects that we can simply inherit from, it still requires creating those roles on each separate cluster.
This results in many login names and passwords to remember, and if passwords are forgotten, they require time by the Database Administrators to reset them, as no client self-service password reset mechanism is currently available like those found in most modern web applications.
In addition to taking up a lot of the Database Administrator's time, security can also be compromised due to roles and privileges not being removed once they are no longer required.
This is the first of a series of blogs that looks at how to centralise authentication and authorisation in PostgreSQL in a similar way to that which has become accepted practice in SQL Server.
By centralising database user management we are aiming to:
- Have a single place where users are defined
- Simplify password management using a common store like Active Directory and allowing self-service of password management
- Enable the allocation of privileges to users to be performed in one place
- Allow user management to be performed by Help Desk staff rather than Database Administrators
- Provide consistency across platforms in how privileges are defined and allocated
- Increase security by ensuring that once a user is removed from the common store they can no longer access databases.
Centralised authentication is relatively straight forward to achieve in PostgreSQL with the ability to be able to specify one of a number of authentication methods that include LDAP, PAM along with support for single sign on with GSSAPI and SSPI.
The next few blogs will cover each of these authentication types in detail.
Centralised authorisation becomes somewhat trickier in PostgreSQL and is currently limited to synchronising database roles within each PostgreSQL cluster with the LDAP compliant directory.
Once authentication has been covered, a number of blogs will examine synchronisation and the different approaches for managing privileges from the central directory including mapping directory groups to PostgreSQL no login roles:
Part 1 (this one) – Centralising Authentication and Authorisation in PostgreSQL
Part 2 – LDAP and PAM authentication
Part 3 – Single sign-on authentication
Part 4 – Synchronising with LDAP: considerations and custom scripts
Part 5 – Synchronising with LDAP: pg-ldap-sync and possible improvements
I also encourage you to register for PGDay Australia 2017 in Melbourne in November, and look forward to meeting you there.