Outline

Postgres is traditionally deployed as an application’s backend RDBMS solution serving the OLTP workloads, and also it is not uncommon to find it used as a reporting/analytical database, serving the OLAP workloads.

The aim here is to illustrate the most common tasks performed when administering such a database server (or cluster in postgres jargon) with good practices in mind.

We are not concerned with the users authentication process, that is a separate topic in itself, the assumption held here is that users are not connecting to a publicly exposed database hostname, but still are required to submit their password, as an extra security feature.

Contents:

• 1. User on-boarding
  • 1.2. Valid Until
  • 1.3. Renaming users
• 2. User off-boarding
• 3. Activity Logging

1. User on-boarding

To create a user run:

# 1.
CREATE USER <name> WITH ENCRYPTED PASSWORD '*******random*ascii*chars*****';

DBA’s should not store any passwords, in case of a loss in order to reset run:

ALTER USER <name> WITH ENCRYPTED PASSWORD '*******random*ascii*chars*****';

Since a cluster may contain multiple databases, an explicit GRANT of the CONNECT ON privilege is needed in order to connect (see Cluster Access Layers diagram).

# 2.
GRANT CONNECT ON DATABASE <db-name> TO USER <name>;

For a user to have an access to schema:

# 3.
GRANT USAGE ON <schema> TO USER <name>;

See the GRANT command documentation for full. And lastly so that our user is able to access an object

# 4.
GRANT [SELECT, INSERT, ...] ON [TABLE, ...] <schema>.<object-name> TO <name>;

Since the particular use case here is a reporting database, we are going stop here, for a more manageable, fine grained and yet flexible approach for accessing schema objects head over to this article.

1.2. Valid until

Should the need to restrict a user’s access until a particular time (probation period/known last day at the organisation), come, then DBAs may choose use the valid until role attribute. It applies to password expiration only, and may be used either upon user creation or via the ALTER command.

ALTER USER <name> VALID UNTIL 'timestamp';

To introspect users where password expiration was applied execute: SELECT * FROM pg_user WHERE valuntil IS NOT NULL;.

1.3. Renaming users

This doesn’t really occur, unless some marital status changes or hr automation/audit refactoring take place, ideally the naming convention should be on par with employee’s local part of email address such as john.doe@example.com <-> john_doe.

ALTER USER <old-name> RENAME TO <name>;
ALTER USER <name> WITH ENCRYPTED PASSWORD '*******random*ascii*chars*****';

The RENAME TO clears the password in case ENCRYPTED clause was used to issue it, so it has be reset again.

2. User off-boarding

Since some users (such as Data Analysts, Managers etc.,) may have created tables, views, indexes and other objects, then they are OWNERS of these objects, with the ability to grant privileges on these objects.

In this case DBAs wish to introspect schemas and reassign objects to new owners (or generic roles) as part of the handover/(off-boarding) process, then they can run:

SELECT nsp.nspname AS schemaname,
       cls.relname AS objectname,
       CASE cls.relkind
           WHEN 'r' THEN 'TABLE'
           WHEN 'm' THEN 'MATVIEW'
           WHEN 'i' THEN 'INDEX'
           WHEN 'S' THEN 'SEQUENCE'
           WHEN 'v' THEN 'VIEW'
           WHEN 'c' THEN 'TYPE'
           ELSE cls.relkind::TEXT
       END AS ObjectType
FROM pg_class cls
JOIN pg_roles rol ON rol.oid = cls.relowner
JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE nsp.nspname NOT IN ('information_schema', 'pg_catalog')
  AND nsp.nspname NOT LIKE 'pg_toast%'
-- filter the user here
  AND rol.rolname = '<name>'
ORDER BY nsp.nspname, cls.relname;

The last part of DROPing a user is not as straightforward as a single command. Since the paradigm of an Object oriented RDBMS, GRANT’s are actually treated as objects too, so running a DROP USER <name>; will likely result in a ERROR: role <name> cannot be dropped because some objects depend on it. This will pertain until all user’s GRANTS are REVOKED, luckily the following sequel spares us time writing more procedural utilities for the object ownership reassignment.

REASSIGN OWNED BY <name> TO postgres;
DROP OWNED BY <name>;
DROP USER <name>;

3. Activity Logging

Lastly, slightly unrelated, since we ruled out topics like authentication at the beginning, however also of crucial importance, is the user’s activity for the entire time he possesses the access to the cluster.

See the Postgres logging for a full reference, in order to achieve the minimum setup proceed with the following:

• Locate the postgresql.conf file postgres=# show config_file;
• Edit/uncomment these options:

    log_destination = 'csvlog'
    logging_collector = on
    log_directory = 'log'  
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' 
    log_connections = on     
    log_disconnections = on
    log_min_duration_statement = 0 # logs all, !NO OLTP workload here

• Restart the service $ service postgresql restart
• Verify the log files were created $ ls -alh $PGDATA/log/
• Use some kind of a utility like logrotate to ship them off to a new home.

To sum it up, depending on the size of an organization its operations, different degrees of automation might be desired for the on and off -boarding process, they can be as simple as events triggered by an HR system, resulting in few other API calls, the more common scenario for smaller organisations is one where these requests are handled on individual add/remove basis. The three topics above summarise the most common atoms of user management in Postgres. If you’re interested in design of access policies, then head over to this article.