Make pg_hba.conf Redundant by Using pg_hba.conf

Friday May 10, 2013

Let’s face it, the pg_hba.conf file is a pain in the ass to use regularly. Sure, reloading the database will cause it to re-read this file, but with a lot of active users and frequent changes, this isn’t really tenable.

Luckily lurking deep within its bowels, PostgreSQL has a little-known feature that can easily be overlooked because it’s so humbly stated. Here’s the manual entry for pg_hba.conf for the user section:

Specifies which database user name(s) this record matches. The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by +. (Recall that there is no real distinction between users and groups in PostgreSQL; a + mark really means “match any of the roles that are directly or indirectly members of this role”, while a name without a + mark matches only that specific role.) Multiple user names can be supplied by separating them with commas. A separate file containing user names can be specified by preceding the file name with @.

The implications of this are staggering and should be shouted from the rooftops frequently and with much fanfare. But what part of that paragraph is the feature that has me raving about its awesomeness? The + decorator for a specified role.

Initially, it might occur to a DBA to simply take advantage of this ability to use existing roles and segregate access by implementing a few well-placed group lines into the file. Say we wanted to allow all DB developers to connect, and our local subnet had a range for desktop systems. We could do this:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +developer    10.10.0.0/16        md5

And viola! Instead of granting access to each individual person, anyone in the developer group could connect provided they had a password. Neat, eh?

Ah, but it goes much deeper than that.

What happens when we apply this to the entire file, and completely purge all individual user entries entirely? Even for automated or batch systems? We get the opportunity to build a connection policy enforceable by in-database methods. Instead of modifying the access file and reloading the database, GRANT and REVOKE become the only commands we’ll ever need.

Imagine we have our production environment and we’ve locked down the entire pg_hba.conf file from external access with this single line for our internal VPN:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +prod_env     10.0.0.0/8          md5

Now, being quite this permissive is probably not a good idea. In a real setup, the production system should only accessible from a very limited range of addresses. However, for the purposes of this discussion, it’s fine. Next, let’s create the prod_env group, and a user to grant it to:

CREATE ROLE prod_env WITH NOLOGIN;
CREATE USER foobar WITH PASSWORD 'testing';
GRANT prod_env TO foobar;

Now our foobar user can connect as often as he likes, and we didn’t have to touch anything external to the database after the initial configuration. Here’s where it gets fun. The foobar user has been naughty, and we’re kicking him out of production. Our prod environment is regularly copied into stage in redacted form, so it’s still OK for him to connect there. Let’s save ourselves some effort and add a stage_env group.

REVOKE prod_env FROM foobar;
CREATE ROLE stage_env WITH NOLOGIN;
GRANT stage_env TO foobar;

And in our stage environment, it would have a pg_hba.conf similar to what we have in production:

# TYPE  DATABASE    USER          CIDR-ADDRESS        METHOD
host    all         +stage_env    10.0.0.0/8          md5

Now the same user can exist in both environments, but only be able to connect to one. This kind of interleaving is easy to accomplish and the controls can be as fine or coarse as your imagination demands.

But it actually gets better!

Suppose our organization has a support team, who we clearly don’t want to give superuser access, but they want to regularly modify user rights. Well, we could grant them every group WITH GRANT OPTION for later distribution, but that’s not really ideal. How about a function they can use instead?

CREATE OR REPLACE FUNCTION grant_conn_role(
  username VARCHAR, rolename VARCHAR
)
RETURNS BOOLEAN AS
$BODY$
BEGIN

  -- Only allow 'env' roles to be granted this way. That extension is
  -- reserved for connection restrictions.
      
  IF rolename !~ E'\_env' THEN
    RETURN False;
  END IF;

  -- Don't allow the use of this function to grant superuser access!

  PERFORM (WITH RECURSIVE rolecheck AS (
    SELECT rolname
      FROM pg_authid
     WHERE rolsuper
    UNION 
    SELECT r.rolname
      FROM pg_authid a
      JOIN rolecheck c ON (c.rolname = a.rolname)
      JOIN pg_auth_members m ON (m.roleid = a.oid)
      JOIN pg_authid r on (m.member = r.oid)
  )
  SELECT 1
    FROM rolecheck
   WHERE rolname = rolename);

  IF FOUND THEN
    RETURN False;
  END IF;

  -- It's now safe to do the grant.

  EXECUTE 'GRANT ' || quote_ident(rolename) || ' TO ' ||
          quote_ident(username);

  RETURN (SELECT pg_has_role(username, rolename, 'MEMBER'));
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE EXECUTE ON FUNCTION grant_conn_role(VARCHAR, VARCHAR) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION grant_conn_role(VARCHAR, VARCHAR) TO support;

Now anyone in the support group can modify user rights as if they were a superuser. Of course, we plugged the obvious hole so support users can’t grant themselves a superuser capable role. But we also want this to only work with roles that fit a certain naming scheme. In this case, anything ending in _env is set aside for connection wrangling. You could just as easily use conn_ as a prefix instead, or any preferable nomenclature. Just modify the function to reflect the standard.

As DBAs, we want to do as little work as possible while simultaneously providing a secure and reliable system. Reloading database configs unnecessarily and doing all user management personally doesn’t really reflect that goal. We might as well use the tools the database provides to be lazy but still protect the environment.

With PostgreSQL, this is both easy and surprisingly powerful, all thanks to pg_hba.conf making itself redundant.

See Also