pgluser - Postgres LDAP User sync (DOCUMENTATION CURRENT FOR VERSION 0.9.9-1)


This tool executes arbitrary SQL on a PostgreSQL server, based on the content of an LDAP directory (or equivalent).

It is designed specifically to synchronise database server accounts (role and database) with selected host system accounts.


pgluser can be set to perform virtually any SQL for every user account which is given certain privileges, or which has those privileges removed.

pgluser's operation is defined almost completely by its configuration file, and it is important that this configuration file is completely correct to avoid data loss or denial of service, before putting the tool into regular, unattended operation.

It is suggestd that pgluser is operated as follows:

Trial Run

Configuration file to be generated in "etc/pgluser.conf", and tested using pgluser --dry-run --verbose and then lpgluser --verbose on a trial database.

Initial Charge

Load the first set of database accounts with pgluser --init.

Scheduled Operation

Regular operation by a cron or similar daemon with pgluser --quiet; this will flag pending deletions for later user intervention.

Manual Cleanup

As a privileged user, runing pgluser --prune --dry-run and pgluser --prune where appropriate.


pgluser is designed to be run regularly on the postgresql server, as the postgresql superuser.

Alternatively, pgluser can be run on any other host / account combination which has appropriately powerful access to the server, but in general this access must be provided canonically (usually only applicable to the postgres user via local access) or manually added to the server before the first run of pgluser, ALSO being described in the pgluser configuration in such a way that it will not automatically be removed the first time pgluser is run!

pgluser is controlled almost completely by its configuration file. However the utility provides a number of options to control how this file is processed:

  -h --help          - this message

The longer option --help also prints a brief, annotated, sample configuration file.

  -v --verbose       - display inner thoughts and feelings
  -q --quiet         - do not display anything

These two options provide a variable number of levels of output which will help control what cron or a similar daemon reports back. -q and -v are additive, meaning that:

  (no options)      prints only modification activity 
  -q                prints nothing but fatal errors
  -v                prints more detail
  -v -v [...]       prints even more detail

Combining -q and -v results in no net change to verbosity(!)

  -i --init          - proceed even if database looks empty.

pgluser performs some safety checks on empty databases which should warn you of potential problems and prevent mass denial of service. Unfortunately this means that on its first run, pgluser should be encouraged to write onto an empty database using this option.

     --prune         - DROP absent roles, rather than disabling

pgluser has two behaviours when system accounts are no longer detected and the corresponding postgresql accounts are selected for deletion. By default it is suggested that in any pgluser configuration, no irreversible changes are made by the DROP SQL configuration option, so that pgluser will never destroy data automatically (see CAPABILITY CONFIGURATION, below).

By passing the --prune flag, pgluser will execute the SQL specifed in the PRUNE config- uration option against each account which is to be dropped, rather than the default DROP.

It is advised that this be run manually in conjunction with the --dry-run option before using "for real", to prevent unexpected data loss.

     --dry-run       - just print SQL, don't execute it.

This option instructs pgluser to print all the SQL which would have been executed, rather than actually sending it to the server. Note that this does not prevent the SQL configured for each capability's SELECT option, as this SQL must be run to determine postgresql account membership. As a result it is essential that the SELECT configuration option does not modify the database in any way.

  -c --cfg <file>    - capability configuration file

This option allows an alternative configuration file to be used. The default is /etc/pgluser.conf.


A pgluser configuration file takes the form:

 # comments
 # general configuration options such as
 capabilities = list, of, capabilities

 # then the default capability template, if required:
 # with its default SQL, if required:
 select="Some SQL"
 create="Some more SQL"
 drop="Yet more SQL"
 prune="Yes; even more SQL"

 # and each capability which has different requirements to the default defined
 # as a section below, e.g.
 # where individual SQL commands can be overridden:
 create="Some overriding SQL"
 drop="" # ensuring nothing is done for this action on this capability 
 prune="!Some SQL which occurs outwith the normal transaction"

and a full list of options is defined below.


Simply listing a capability in the capabilities variable under [CONFIG] is enough to define a new capability which will inherit all its configuration from the [DEFAULT] section.

To override variables from [DEFAULT], simply define a new section whose name matches the entry in capabilities, e.g. [root/mydb/mycapability].

It is normal not to define all of the actions in a section, but instead rely on the DEFAULT entry for all unspecified actions.

To block the action of the DEFAULT entry, simply define an empty action or value, "", for that line. The [DEFAULT] section should be configured like any other capability. Options valid in capability sections:


This allows an individual capability to override how it is represented when referenced within the SQL commands below. This effectively overrides the "%(cap)s" variable interpolation.

Defaults to the shortened name of the capability (i.e. a/b/c -> 'c').


A comma-separated list of users to predefine as holders of this capability, regardless of what LDAP says about them. These users will always be (re)created if absent from the database.


Users in this category to ignore. Whether this user holds this capability or not, and regardless of their status in the database, the user will never be processed.

Note that this does not guarantee the user will be free from side-effects of other users' changes (though this would be bad practice).

Note also that it does not mean the user will be ignored in any other capability configuration (unless specified in DEFAULT).


The SQL required to show all current users with the given capability in the database.

This SQL should not routinely modify any data in the database -- note that it cannot be suppressed with the --dry-run option.


The SQL required to give the current capability in the database. See the note about SQL TRANSACTIONS, below.

The role in question can be accessed with the standard "%(role)s" interpolation.


The SQL required to remove the current capability from the database.

This should ideally not result in data loss; see prune.

It should ideally also be reversible using create.


The SQL required to irrevocably remove the current capability from the database, causing data loss where necessary. In most suggested configurations this SQL will not be run automatically; instead the pending removal will be signalled to an operator for attended removal using the --prune flag.


A comma-separated list of existing capabilities from which a user will be removed, if granted this capability. The user need not have all or any of the capabilities listed to be granted the capability (except if, perversely, the capabilities are also requirements, but this is guaranteed to cause trouble!)


A comma-separated list of existing capabilities which a user will gain if granted this capability. The user need not have all or any of the capabilities listed to be granted the capability.


A comma-separated list of names of capabilities which a user must have for this capability to be granted. The user must have all listed capabilities, or no changes will be made.


Currently unimplemented, but a valid resource.

(This will describe the template (other than DEFAULT) which this capability follows.)


Whether this capability should be applied during a manual "--init" run of pgluser.

This is boolean and defaults to false unless specified in the configuration. Normal python ConfigParser rules apply for boolean values such as this: most obvious 'true' values will set this variable to true; obvious 'false' values will force it to false.



capabilities defines a comma-separated list of capabilities. For each capability one can define any or all of the list given in the CAPABILITY CONFIGURATION section.


This can be any pydbapi-compliant database string to connect to the database. Note that a canonical superuser is usually recommended.

The default is :template1::::, designed to be run as `postgres` on the local machine.


Overrides the default command from which to receive netgroup triples of users matching each capability.

The default is getent netgroup [capability].


One of the main uses of pgluser will typically be to generate postgresql databases on demand.

In current versions of postgresql, database creation cannot be performed within a transaction, which presents a problem to pgluser in its standard behaviour since, for safety, pgluser tends to group all its activities into a single transaction.

By prepending a single exclamation mark "!" to an SQL statement option such as CREATE or PRUNE pgluser can be forced to complete its current transaction before executing the named statement. This has the disadvantage of making failures anywhere from this point onwards non-atomic, with respect to the pgluser run (overall integrity is still of course retained).

As a side effect, PostgreSQL will also, typically, warn about the tendency of pgluser to place redundant "COMMIT; BEGIN;" statements where a "!" non-transactional command occurs at the beginning or end of a pgluser run. These statements can be safely ignored, with the proviso mentioned above.


Though built to be fairly generic, this tool was built to satisfy the demands of account management on Informatics DICE servers, and some changes would be required to ensure compatibility in wider environments.

However, in theory the tool will operate on any system which provides Python 2.4--2.9 and an executable which will return a list of netgroup triples corresponding to any configured capabilities.

Last modified 10 years ago Last modified on Mar 29, 2010, 6:40:54 PM