pg-roleman

RoleMan: Functions for Managing Roles in PostgreSQL

One of the substantial difficulties with role management in PostgreSQL from programming perspectives is that utility statements such as CREATE ROLE do not have plans and therefore do not take parameters. This means that programs which want to create roles must issue the statements and guard against SQL injection with very little help from the standard tools.

The roleman extension is tested in PostgreSQL 9.4 and higher. It makes extensive use of built-in excaping functions and registered entity types in PostgreSQL to ensure that inputs are handled and escaped properly regardless of inputs.

Permissions are whitelisted.

Compatibility

As of 0.1.2, installcheck works properly against 9.4 through 9.6. There are no known or expected issues with PostgreSQL 10, but this will be further tested. Test case failures may need to be manually reviewed in that case.

There are no changes to the extension between 0.1.0 and 0.1.2. All changes are in documentation and test case compatibility.

Conventions

The basic form of the argument list is:

grantee_role, granted_object, permissions_granted

In the case of granting to all objects in a schema, we have this divided a little more:

grantee_role, granted_schema, granted_object_type, permissions_tranted

permissions_granted is always a whitelisted text array. The other fields are always singe values.

Where the granted object has a registered entity type associated with it in all supported versions (like regclass and regprocedure) we use that registered type. This ensures that the object granted is valid, and is properly escaped during the dynamic sql generation.

API Reference

Major Uses

   CREATE FUNCTION change_my_password(in_password text) RETURNS VOID
   LANGUAGE SQL SECURITY DEFINER SET search_path=roleman
   as
   $$
   select role_change_password(session_user, in_password, ('today'::date + 90)::timestamp);
   $$;

Future Features

Here are some features we’d like to add to this module:

  1. Revoke rights rather than reset and rebuild
  2. Parse acl lists.
  3. Query WITH attributes of roles
  4. ALTER ROLE …. SET x TO Y