Managing a database with pgluser, on DICE

Setting up PostgreSQL and pgluser from scratch using LCFG is a relatively straightforward task, but the various layers of abstraction can make it hard to break in. This document attempts to demonstrate by a very simple worked example how to configure pgluser for use with a simple PostgreSQL server and defined set of users.

Setting the scene

Let us consider an isolated service database servicedb. A process running on the server will modify the data periodically; end-users of the service need interactive, readonly access to the database.

Division of labour

Planning for pgluser management starts by determining what classes of PostgreSQL object you wish to manage. pgluser works in an entirely lightweight, additive manner on the database, and will only manage objects which it can see by means of its SELECT rules.

In this case, we should set out the bits which will be manually defined as part of our service:

  • database creation
  • user groups
  • table creation
  • table access control

we probably don't want pgluser to create or modify databases or their contents; we can accept that if the target database is missing, pgluser won't and shouldn't work.

All we want pgluser to manage is:

  • user accounts for all entitled users
  • read-write group membership to users with the 'db/servicedb/rw' capability
  • read-only group membership to users with the 'db/servicedb/ro' capability

The key to pgluser's ability to manage this structure cleanly is in the division between access control and group membership: by distinguishing between group roles, which are manually given database, table and column-level permissions; and user roles, which are created and deleted automatically by pgluser; these represent the users or agents of the system and are given permissions by membership of the appropriate group role#p1.

Creating pgluser rules

Before you begin

This is the best time to add your new capabilities (i.e. entitlements) to any appropriate DICE roles. This way, your users will be ready and waiting when you've written your pgluser rules.

Creating Users

Step one to configuring pgluser is to define rules which cover its behaviour. Let's take the first task we selected for pgluser:

  • user accounts for all entitled users

For each such task, pgluser requires a capability name; for the purposes of this example this is just an LCFG tag. So for the above let's pick the fairly obvious user:

!pgluser.caps   mADD(user)

pgluser splits the management of a capability into a number of attributes:

  • create, drop, prune: SQL required to perform addition or removal
  • select: SQL required to determine existing capability holders in the database
  • users, ignore: list of users to be added manually, or left well alone
  • replaces, requires, implies: dependency rules

It's usually easiest to start with the creation and deletion. Creating and deleting a user can be represented with simple SQL:

pgluser.create_user CREATE ROLE "%(role)s" LOGIN INHERIT;
pgluser.drop_user   REASSIGN OWNED BY "%(role)s" TO postgres; DROP ROLE IF EXISTS "%(role)s";

A few things worth noting:

  • the use of the substitution parameter %(role)s; this is quoted above, in case usernames turn out to be SQL reserved words.
  • the use of the PostgreSQL term INHERIT to explicitly define the role as one which automatically inherits those roles it is granted. This is important both from a user point of view (though it is the PostgreSQL default) and in more complex configurations, can allow pgluser to distinguish "user" roles from "group" roles#p2.
  • The splitting of CREATE; ALTER and use of REASSIGN OWNED are optional, but can help smooth out edge cases where roles already exist or where ownership isn't as expected.

The select rule is slightly trickier as it requires a little knowledge of PostgreSQL's internal admin tables:

pgluser.select_user SELECT rolname FROM pg_roles WHERE rolcanlogin;

Note that this will let pgluser see all extant users, which means that anything present in the database without an appropriate capability will be removed according to the rule above. It is therefore imperative that you define now anything that you don't want to be managed, or risk it being dropped (you can test this first using --dry-run of course):

pgluser.users_user   serviceuser nagios
pgluser.ignore_user  testuser

Whether you use users or ignore depends on whether you feel it's pgluser's business to create the user if it is missing. The postgres role is the only special special case; pgluser always ignores objects with this name, for obvious reasons#3.

Group Membership

We've defined a user list, but on the DICE side we have no members, and in any even we don't want to have to give DICE users explicit user accounts: we just want to define whether they should have readonly or read/write access to the data. This is where the group membership rules come in.

  • read-write group membership to users with the 'db/servicedb/rw' capability
  • read-only group membership to users with the 'db/servicedb/ro' capability

We define these capabilities as implying the user capability:

!pgluser.caps   mADD(readonly)
pgluser.implies_readonly   user

!pgluser.caps   mADD(readwrite)
pgluser.implies_readwrite  user

This defines our two new capabilities, makes the link between the pgluser capability and the DICE entitlement and states that being given either implies that pgluser should also create a capability of type user, too.

These capabilities need to be assigned to holders of a specific DICE entitlement, so let's establish this link now:

pgluser.title_readonly     db/servicedb/ro
pgluser.title_readwrite    db/servicedb/rw

Like user, the above need to have creation, deletion and selection criteria. This, again, requires just a little knowledge of SQL, and furthermore of PostgreSQL's internal tables:

pgluser.create_readonly   GRANT "%(cap)s" TO "%(role)s";
pgluser.drop_readonly     REVOKE "%(cap)s" FROM "%(role)s";
pgluser.select_readonly   SELECT u.rolname AS user FROM pg_auth_members m, pg_roles u, pg_roles g WHERE g.oid = m.roleid AND u.oid = m.member AND g.rolname = '%(cap)';

The select_ SQL above uses a PostgreSQL internal view to lists all users who have been granted the specified role. Create and drop perform the obvious. Note the use of %(cap) above: this is optional; you could just as easily have typed readonly, as this is the name of the capability. However, the user of '%(cap)' allows us to reuse the scripts verbatim:

pgluser.create_readwrite  <%pgluser.create_readonly%>
pgluser.drop_readwrite    <%pgluser.drop_readonly%>
pgluser.select_readwrite  <%pgluser.select_readonly%>

in practice, if you are setting many different groups in this manner, you would do well to investigate the pgluser DEFAULT capability, which would preclude the need to define these at all -- but this is beyond the scope of this tutorial#p4.

Back to the database

Finally, remember that the named group roles are not managed by pgluser, and should be created manually:

servicedb# CREATE ROLE "readonly" NOLOGIN NOINHERIT;
servicedb# CREATE ROLE "readwrite" NOLOGIN NOINHERIT;
servicedb# GRANT SELECT ON mytable TO "readonly";
servicedb# GRANT SELECT,UPDATE,INSERT,DELETE ON mytable TO "readwrite";

noting the explicit (but redundant) use of NOLOGIN, the flag used by our rules above to tell user and group roles apart.


Putting this into practice requires little more than the following:

  • Put the dice/options/pgluser.h header into your profile
  • Add the rules defined above

Then, on the machine itself:

  • Run updaterpms to install pgluser and the pgluser component
  • Configure pgluser to create /etc/pgluser.conf
  • Test with the --dry-run option to ensure your changes are safe and sane
  • Perform your first run. Depending on your configuration and the state of your database you may need to pass the --init parameter.

Further Reading

pgluser is highly generic and flexible, and any aspect of the database which can be captured and manipulated with SQL can be defined in LCFG and reused without significant effort. The best place to learn about this is in the manual pages for pgluser and lcfg-pgluser, which define their behaviour more completely. Additional information can also be found in the DICE headers which make extensive use of the system: infdb-server, theon-demodb, and postgresql-RAT-server are three diverse examples.


p1 Note that while it would be possible for pgluser to manage the contents of ACLs on tables, in typical managed database environments this is unlikely to be desirable or practical. Also, as of version 1.0, pgluser operates by connecting to a single database, so management could not extend beyond this without multiple configuration files.

p2 There is no real difference in PostgreSQL between a user role and a group role; in practical terms, user roles are ones with LOGIN set. The advantage of having pgluser use INHERIT instead to distinguish these types is that it allows pgluser to disable roles by setting NOLOGIN without them becoming invisible to the select call. Feel free to ignore this convention if you will never have a need to disable login from a user account.

p3 Though you could write a rule which managed it explicitly...

p4 The pgluser Trac page, and existing DICE headers, are the best starting points to investigate this.

Last modified 8 years ago Last modified on Sep 23, 2013, 9:02:32 AM