White dot for spacing only
The Dice Project

accounts.html,v 1.25 2021/11/11 18:47:24 gdutton Exp

This page is intended to assist computing staff in account administration, and is unlikely to be of use to users requiring an account on our database service. Please see our PostgreSQL Computing.Help page for user-facing documentation.

PostgreSQL User Account Administration

For a vast majority of accounts, no action should need to be taken. Students should have their access determined by possession of the appropriate course role (see entitlement), which will create and drop a database named after their user account, and their access privileges, as appropriate. Research staff requiring accounts will form the bulk of requests requiring intervention.


Teaching Server (pgteach)

Any student taking part in a course which requires use of a database should automatically be given access to the teaching service via their course roles. Students with specific requirements can be given access by explicit addition of the pgtdb role, or in special circumstances by defining a database and role in the live/postgresql-teaching-server header (covered in special cases, below).

Any staff requiring access to the teaching server for course preparation or other teaching purposes should automatically have access as they should possess an appropriate teaching support role for the course; for example: Students taking the fictitious 'XDB' course should have the module-xdb role, and this role should grant access automatically. Accordingly, tutors should have the tutor-xdb role which should grant either @module-xdb in its entirety, or @pgtdb or some or all of its entitlements. It's important to check this first, as users' roles — and the list of courses whihc entitle users to access — must always be kept correct in the first instance.

If these automatic entitlements don't apply (or RAT have otherwise confirmed), an individual may be given the pgtdb role or a header-based exception (the latter, for example, to create a template database for students to copy).

Research Server (pgresearch)

Any research staff requiring a database account can be offered a choice. Which is appropriate depends on what they intend to do with their data. Where the data does not need to be shared explicitly, and can be erased on departure of the requester, a role-based exception is adequate. This will create a user and named database automatically, and remove it on expiry. A header-based exception should be defined:

Research staff databases are not restricted by quota, but as both disk and backup capacity are somewhat limited it's important to get some estimation of size from the user. Support staff would be best to check with the RAT unit for estimates over about 5% of the data partition of the database server (typically /disk/data/; locate it with qxprof postgresql.pgdata).

Adding by Role

This should cover the vast number of students and staff and is either done automatically, or can be manually added by the usual role modification mechanism.

In addition to the course-based roles (rfe -xf roles/pgteach or rfe -xf roles/pgresearch will reveal the full list of roles granting the all-important db/pgxxxxx/user capability), the roles pgrdb and pgtdb have been defined for the sole purpose of granting database access. These are appropriate if a student or, more likely, staff member has no more relevant role (check with the RAT unit if you are unsure).

Research / special cases: adding to the Header

Having selected a server to use, simply follow the instructions in the relevant live header:


These generally refer to a macro which can be used to perform the following:

All of these tasks are performed in the same way, at present using the same macro. For example:

PGLUSER_SHARED_DBUSER(shared_db, owning_user, "Requested by RT#XXXXX")
PGLUSER_SHARED_DBUSER(shared_db, another_user, "Additional access, RT#XXXXX")

PGLUSER_SHARED_DBUSER(username, username, "Persistent database for username, RT#XXXXX")

See the relevant live header for full details.

Host-based GSSAPI access

There exists another macro which allows a given server to accept Kerberised (GSSAPI) connections from a specific host and username.

/* the deprecated IPv4-only version of this macro was 
 * POSTGRES_KRB5_MAP(tag,principal,hostname,postgres_user) 

Full usage information can be found in the relevant postgresql-X-server.h live header, just below the PGLUSER_SHARED_DBUSER declarations.

Scripting Users

It's good practice for users with dedicated databases to request a (one or more) separate dedicated user(s) for their app to run as, separating privilege for better security. The macro system supports this implicitly:

PGLUSER_SHARED_DBUSER(shareddb, uun, "shared database access - RTxxxxxx")
PGLUSER_SHARED_DBUSER(shareddb, shareddb_user, "additional scripting user - RTxxxxxx")
PGLUSER_SHARED_DBUSER(shareddb, shareddb_ro, "additional readonly scripting user - RTxxxxxx")

A manual step follows: we haven't yet automated role ownership, so support will still need to set an initial password for these accounts. This is one of the few operations for which you'll still need to drop to a psql prompt: sorry about that.

 # (You should assume the 'postgres' user on the appropriate server:)
 postgres$ psql
 superuser=# BEGIN;
 superuser=# ALTER ROLE username WITH password 'new password here';
 superuser=# COMMIT;

This new user will have only CONNECT privilege to the named database -- it's up to the requestor to GRANT access to any tables or objects within.

Having created the account and set a password, send this initial password (by any means; the account has little value until it's given privileges by the requestor) and instruct the requestor to change it immediately before granting access to appropriate tables. They can change the password with the standard ALTER statement, also documented on the Computing.Help page.

Speeding up user creation

Typically, user updates take place hourly, but in urgent cases (or where you are unsure of the result) you can kick the changes on the relevant postgresql server:

 # (if added via role): check the capability has reached LDAP (there can be a short local sssd cache delay)
 netgroup db/<hostname>/user | grep -o <uun>
 # (if added via header): check the capability has reached the config
 qxprof pgluser | grep <uun>
 # to preview the result of any changes
   <you>$ om pgluser run -- --dry-run --verbose
   <you>$ tail /var/lcfg/log/pgluser
 # and, if everything is OK, to push the changes:
   <you>$ om pgluser run

If the result of dry-run looks very bad for some reason, you can suspend the regular processing by stopping the pgluser component with om pgluser stop before contacting rat-unit.

Special Cases

In some cases a teaching user will have entitlement to a database account both through their roles and through a manual override (for access to a shared database, for instance). In this case the manual override will almost always win, denying that user an automatically created database. It's worth noting that this should be an extremely rare case and, when it occurs, one should consider whether the shared database should be hosted on another server such as pgresearch.

The only sensible way to fix this, unfortunately, is to add a second override (preferably beneath the existing one) as noted above:

PGLUSER_SHARED_DBUSER(shareddb, sMATRIC, "shared database access - RTxxxxxx")
PGLUSER_SHARED_DBUSER(sMATRIC,  sMATRIC, "TEACHING access (due to shareddb conflict) - RTxxxxx")

Note that this gives the user a persistent database entry, which is unavoidable in the current configuration.

Regular Maintenance

When staff or students leave, or lose their database roles, their accounts will be disabled and their databases disowned, ready for deletion. However, databases will not be removed from the server until manual intervention is taken. If pgluser is logging that there are many accounts pending deletion:

- Pending deletions for db/pgxxxx/db

You can force the removal of any affected databases, after ensuring such changes are sane, by following the same procedure as above but with the addition of a timely backup and the --prune argument:

# just-in-case, ensure today's live data is preserved
 om postgresql run backup pre-pgluser-prune
# Check that the backup is sane by comparing to previous backups
 postgres$ ls -lart $(qxprof postgresql.pgbackupdir)
# preview the result of any changes
 om pgluser run --dry-run --verbose --prune
 less /var/lcfg/log/pgluser
# and, if everything is OK, push the changes:
 om pgluser run --verbose --prune

As a time-saver to remove a handful of user databases, you can be confident that a database contains no actual data with a bit of automated checking:

  # a bash function for your environment or path
  function psql_db_contents() {
      for cmd in d dD dT di dd dl dp; do
          psql <$1> -c "\\${cmd}" | grep '^([1-9][0-9]* rows)$'
  # to be run with a database name as your first argument:
  postgres$ psql_db_contents <username>

this function will return visible output only if some sort of data has been stored in the database.

 : Units : Research_and_teaching : Documentation : Postgresql 

Mini Informatics Logo - Link to Main Informatics Page
Please contact us with any comments or corrections.
Unless explicitly stated otherwise, all material is copyright The University of Edinburgh
Spacing Line