wiki:pgluser

Overview

Pgluser is the tool used by some DICE PostgreSQL databases including the Theon infdb to synchronise a user or privilege list with DICE LDAP capabilities. It also maintains access control to this Trac instance through limited SQLite support. The tool can be found at tools/pgluser/.

The tool relies upon python's [initd.org/psycopg/ psycopg2] module (with an appropriately-authenticated postgresql connection) as well as a machine which will respond sensibly to getent netgroup (the mechanism by which the capabilities are retrieved). SQLite support is provided via the usual Python sqlite3 module.

More complete documentation is provided via the module documentation (not least in the supporting UserConfig class) and extensive documentation is available in the pgluser man page, and the lcfg-pgluser man page, both of which can be found on any DICE machine carrying the software.

Basic Usage

pgluser is designed to be invoked regularly from a crontab, or manually to initialise a database:

$ pgluser -h
pgluser.py -- 1.1.0 (10132)
=============================
Syncs postgres user-roles with the list of approved users on LDAP.
Does not remove stubborn stains.

Usage:  /usr/bin/pgluser [option [option...]]
  where option in:
  -h --help          - this message
  -v --verbose       - display inner thoughts and feelings
  -q --quiet         - do not display anything
  -i --init          - proceed even if database looks empty.
     --prune         - DROP absent roles, rather than disabling
     --dry-run       - just print SQL, don't execute it.
  -c --cfg <file>    - capability configuration file
                       (--help for full details)

Part of the Theon project: http://www.theon.inf.ed.ac.uk/

pgluser's operation is entirely dictated by its configuration file. Brief examples of its use can be found in the application's manual page and --help output:

$ pgluser --help

 [...]

Configuration file format:
-----
[CONFIG]
capabilities = svc/instance/type, ...
getent = shell cmd [args...] to retrieve netgroup-formatted userlist
dbconn = pydbapi-compliant database string to connect as to DB as superuser

[DEFAULT]
users =   # always empty by default
ignore =  # users to always ignore
capname = # no default; calculated from section name.
select =  # SQL SELECT required to get capability membership

create    # SQL GRANT / CREATE required to give capability
          # Note that SQL such as CREATE DATABASE requires execution outwith
          #  a transaction block.  To force this behaviour, prepend an
          #  exclamation mark (!) to the beginning of the SQL.

drop =    # SQL DROP / REOVKE required to remove capability
prune =   # as 'drop' but more permanent. falls back to 'drop'.

[svc/instance/type] # capability name
select =  # opportunity to override default
create =  # [...]
users =   a_user, ... # list of extra users which have this capability
[...]
implies =  # list of capabilities which will be added implicitly by this one
requires = # list of capabilities required for this one to be granted
replaces = # list of capabilities to be removed if this one is granted

[svc/instance/another] # capability-based sections are optional.
noinit = true
          # skip this capability if running with --init
capname = %(role)s_grp 
          # any of the fields in [DEFAULT] can be overridden.
          # note use of interpolation of 'role' value.
-----

Database users

Theon uses the pgluser tool to sync the DICE (LDAP) defined list of users with infdb accounts.

All capabilities used by the database must be listed in the appropriate configuration file under the capabilities parameter. The important elements of the configuration file for user management, are the following:

[CONFIG]
# list of all capabilities used by the server
capabilities = db/infdb/user, db/infdb/interim, db/infdb/self

[DEFAULT]
# list of users to never add, remove or otherwise alter.
ignore = postgres

Then for each capability, optionally create a named configuration section if you need to override any of the DEFAULT values:

[db/infdb/user]
# list of users to which to grant this capability as if defined in LDAP.
users = infdb, daidb

[db/infdb/self]
users = nagios

Note that on the database server, configuration is defined by lcfg-pgluser component resources which generate the pgluser.conf file. For full details, see the lcfg-pgluser and pgluser manual pages.

Regular accounts

All database users, with one or two exceptions, are defined in DICE by possession of the appropriate capability - at its most basic, db/infdb/user. The remainder are defined in the configuration file, explained below.

By default, processing a capability named db/infdb/foo will grant access to a role named foo. Creation of the group-role, and the permissions which this role confers, are the job of the schema (as processed by dev/xslt/perms.xsl; see SchemaProcessing? for more details). More advanced configuration is explained in the annotated config file and the pgluser documentation.

Non-standard accounts

postgres need never be added to any list, since it is a superuser which will not be modified by the standard configuration.

Because nagios does not have a genuine user account, it is added as an exception to the db/infdb/self capability, which creates a user with connect permission, and also creates a self-named database.

Pseudo-users such as infdb and daidb (though hopefully these can be renamed...) who do not have LDAP entries require exceptions to be added to the user configuration variable for whichever capability they require (in general probably just infdb/db/user). The user configuration variable allows capabilities to be granted to arbitrary roles in the same way as normal LDAP users.

Since pseudo-users cannot be authenticated using Kerberos, but real users will never be permitted local login, low-level (connect) access can be controlled simply by permitting local access on trust, while restricting all remote access to Kerberos. If more fine-grained control is required a manual list of local-access pseudo-users can be maintained in LCFG for that server, specifically to modify the pg_hba.conf.

In Context

The sync script is run periodically on infdb, as a/the database admin account. For safety, the script will not delete roles, but will repeatedly report disabld roles in its output. These my be purged by running the script manually with the --prune option.

Future Development / TODO:

  • allow multiple configuration files (easily done as this is supported by python's native ConfigParser).
  • allow alternative sources of group membership
  • better bootstrapping procedures
  • ...

Writing pgluser rules

See PgluserConfig for details, recipes and links.

Last modified 5 years ago Last modified on Oct 23, 2014 3:56:48 PM