Hypatia Database User Account Management

Hypatia database account management is integrated into DICE. Fine-grained permissions are not exposed; instead the database defines a number of account classes which provide differing levels of access.

Overall Structure

PostgreSQL account management involves the following sources:

  • LDAP holds database connection and role membership information -- normal editing routes apply.
  • Theon Model for Hypatia holds user classes and their privilege information -- see [UserManagement#CreatinganewGroupRole Creating a New Group Role] for an outline.
  • LCFG contains rules which control =pgluser.conf='s user / group / privilege mapping configuration. LCFG also contains manual user additions and exclusions (for users not controlled by LDAP) for each group role. LCFG also holds the host-level access control rules for the postgresql service, for alternative access methods.
  • pgluser.conf configuration file for the ["pgluser"] tool, managed by LCFG. This tool provides the interface between LDAP and PostgreSQL and has its own [/wiki/pgluser documentation page].
  • PostgreSQL Holds the effective user accounts, user groups and their privileges as SQL. Automatically generated by pgluser.

User and Group information

Effective database accounts (i.e. those used directly by the server) are held entirely in the database, but master information is held in both the schema and DICE user accounts. Account management consists of handling three components: the user role, the group role, and the privileges awarded to a group role.

User roles exist only as analogues to DICE (or pseudo-) user accounts and are created (and destroyed) on demand by the pgluser tool. There must be a user role for (and named after) each DICE account with access to the database. Each user role is granted zero or more group roles. The assignment of a group role is covered by the single DICE LDAP capability, db/infdb/user.

The existence of a user role is not sufficient to grant access to data on the database; it only grants the ability to connect. Therefore the DICE capability should not generally be granted directly; instead it should be granted as part of a DICE role which will assign a user account alongside other privileges. For those cases where a DICE user needs only the ability to connect, or where all other privileges will be handled manually, DICE users should be given the infdb role, which will grant them a user account.

A note about pseudo-users: at present, each configured local pseudo-user must be given specific permission to connect to the database using the standard pg_hba configuration, as specified using postgresql.hosts and related LCFG resources. This is because these users do not authenticate using Kerberos, and therefore are governed by separate pg_hba rules. For pseudo-users which require detailed access to the system, an alternative method of access is to grant Kerberos credentials to the connecting service and use use the pg_ident mapping configuration to map a specific postgres role to a given Kerberos principal, available as of lcfg-postgresql component version 2.4.0. Please see the lcfg-postgresql man page for full details of both of these methods.

Group roles exist independently of user roles. A group role contains a number of privileges, as assigned in the master schema. Each group role corresponds to a DICE LDAP "capability", in the form db/infdb/capability. Group roles define a class of access, for example "admin" for database administrators or "ito" for members of the Teaching Organisation.

Like user roles, these capabilities and their mapping to group roles are defined in the configuration for the pgluser tool, the configuration for which is described separately. Again, users should not have the db/infdb/* capabilities assigned directly to them; nor should they, generally, have roles created specifically for such a purpose. Of course, exceptions apply where there is no existing role which encompasses the only necessary group of users.

A note about existing (pre-PostgreSQL) database users: For the purposes of transitioning to the new Theon schema-driven configuration, each existing user role has a db/infdb/interim capability which contains all of the user's previous privileges. This cannot be applied to any other user, and will be replaced by the privileges granted automatically by the user's functional role (for example sysman or itoadmin).

Some Examples

Legacy users account should not need modification, but each such user has the interim infdb role which grants the following capabilities:

db/infdb/user           /* grants connection */
db/infdb/interim        /* grants access to the appropriately-named <user>_grp group */
As an existing "legacy" database user, the account gdutton has had an interim group role automatically generated for it, assigned using the db/infdb/interim capability. This role, gdutton_grp, contains all the privileges previously assigned to gdutton's user account).

If a new user newperson were join the ITO, their user account would already be granted the ito role. The ITO role already contains the following capabilities:

db/infdb/user           /* grants connection */
db/infdb/ito            /* grants read access to many sourcs and write access to ITO-specific tables. */
so no further action should be necessary.

The nagios pseudo-user requires an infdb account to check the infdb database exists and is accessible, but it need not read any of the data therein. It is therefore granted a basic user role using the db/infdb/user LDAP role. Lacking the appropriate LDAP presence, nagios cannot have the role assigned in this way so it is instead added manually to ["pgluser"] using the following LCFG resources:

!pgluser.users_user             mADD(nagios)

This gives the db/infdb/user role, which gives a basic user role and connection privilege, to nagios. However, nagios will be unable to authenticate using Kerberos and the postgresql hba configuration will deny other forms of access. The access it requires is of a fairly low-risk nature, and access will always be from the monitoring server, so in this case specific connect access is granted to nagios on the connect server:

!postgresql.hostssls            mADD(nagios)

[... see infdb-server headers for full details]

The "ordersync" pseudo-user requires more comprehensive access to the PostgreSQL server, so requires specific privileges which invoke some of the newer features of the postgresql component.

Ensuring that pg_hba configuration for the required connecting hosts includes the orders host:

!postgresql.sslhosts       mADD(ord)
postgresql.sslauthtype_ord krb5

add the ident configuration to map a PostgreSQL account to the Kerberos principal:

!postgresql.ident          mADD(ord)
postgresql.idmap_ord       ordersync /* map name */
postgresql.pgname_ord      order2db /* postgresql role */
postgresql.sysname_ord     ordersync/ordershost.inf.ed.ac.uk /* principal name */

Then, given that ordersync is not given LDAP capabilities, allow pgluser to create that account with appropriate permissions:

!pgluser.users_interim     mADD(<%postgresql.pgname_ord%>)
!pgluser.users_user        mADD(<%postgresql.pgname_ord%>)

Common Account Operations

(including some less-common, but important, operations...)

Adding a user to the database

In principle this should never be necessary. Individual users should gain their database privileges via their functional role, for example igs or sysman.

However, in the case where no such functional role exists, consider the type of access required. If it matches the access granted to an existing group, it might be appropriate to:

  • Create a new role
  • Add the db/infdb/user capability, along with the appropriate db/infdb/* capability, to the role
  • Assign the role to the user(s)

If no existing group has the privileges required, then follow the instructions for creating a new group role.

Removing a user from the database

Conversely, to remove a user account from the database, all that is required is the removal of the role which granted the database access. For example, removing the ito role from an ITO user should be sufficient to remove their access.

You can search for all roles which grant infdb access, using the current rfe tools.

$ rfe -xf roles/infdb

There are two likely exceptions to this procedure:

  • legacy users: if the user previously held an account on the database, they may still have the interim infdb role. Simply remove this role from their account, in addition to any others which grant the capability.

  • users who gain their database privileges some other way, or whose granting role cannot be removed for other reasons: it is possible to use the LCFG pgluser configuration to manually block a user from accessing the system. See manual overrides below.

Modifying permissions on a user

It should not in general be necessary to give unique privileges to one specific user; the user should either be assigned an existing group role which meets their needs, or an existing group role's privileges should be modified where appropriate.

However, where circumstances are such that one user does require unique privileges, the following steps are required:

  • Create a new group role (described below)
  • Label the group role with a capability. These steps procedure is described below in the [UserManagement#CreatinganewGroupRole Creating a New Group Role] section.
  • Assign the DICE capability to a DICE role
  • Assign the role to the new user(s). These steps should be performed like any normal DICE role operations.

Creating a new Group Role

This is done using Theon by making changes to the Hypatia model. Current groups roles are held in the roles portion of the schema tree. Use the Theon Toolkit and TheonUI to add a role and permissions, then export and apply as an upgrade to the live database. Follow the examples in the TheonBook.

Once the schema changes have taken place, and the group role and its privileges exist on the database (you can test this with the interactive psql command \z TABLE) you may begin to add the user to the pgluser configuration. The ["pgluser" complete pgluser configuration] is described elsewhere, but the minimum requirement for adding a new capability is as follows:

!pgluser.caps           mADD(newgroup)
pgluser.title_newgroup  db/infdb/newgroup
additionally, most groups will either require that the user already exist in the database (i.e. have the db/infdb/user role) or will wish to include it implicitly. This can be performed using the requires and implies resources, respectively:
pgluser.requires_newgroup  user
pgluser.implies_newgroup   readwrite
where user and readwrite are the LCFG tags for other capabilities. See the complete pgluser documentation for details of this and other inter-role dependencies. These changes should be made in the dice/options/infdb-server.h and live/infdb-server.h headers as appropriate for release purposes.

Manual Overrides

Manual overrides can take multiple forms. The simplest is the addition of users not present in the LDAP capability listings. For each user who requires a given pgluser capability, simply add them to the capability's _users resource:

!pgluser.users_capname    mADD(newuser)

Removal of a user from a group where that user is already present could be done in a number of ways. The most useful change structurally might be to define a new capability which replaces the old group for some user or number of users and add the user manually to that new group:

!pgluser.capabilities    mADD(newcap)
pgluser.replaces_newcap  oldcap
[... pgluser.{select,create,...} defined as required ...]
!pgluser.users_newcap    mADD(theuser)

Alternatively, manual changes can be made where required by use of the ignore list:

!pgluser.ignore_capname  mADD(ignoreduser)

on all subsequent pgluser runs, the presence or absence of this user from this capability will not trigger additions or removals (though note that dependent capabilities will still be processed, so the user should be added to the ignore configuration for these capabilities, also. LCFG can help here:

!pgluser.ignore_dependendcap  mEXTRA(<%pgluser.ignore_capname%>)

once ignored, the user or object can be removed from the database by any manual method such as DROP ROLE....

Propagation and Manual Updates

Adding, removing or altering user privileges is subject to the following propagation delays:

  • Assigning different database privilege is a role operation, so incurs the regular LDAP-update propagation delay.
  • Generating a new capability incurs an additional delay until the next buildcaps run.
  • Altering the schema requires that the new schema DDL is generated and applied to infdb.
  • Once all LDAP changes have reached the pginfdb server, privileges will be altered at the next pgluser run.

At present, pgluser runs hourly. It can be triggered at any time by executing pgluser as postgres. To see the result of any changes it is possible to run pgluser --dry-run.

-- TimColles - 30 Nov 2018

Topic revision: r1 - 30 Nov 2018 - 13:54:43 - TimColles
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies