Postgres Large Objects & Multiple Users

Problem

Dynamically created users are not able to query a table that contains Large Objects as it did not originally create it.

Background

I have an Java Atom Hopper application deployed to AWS across 2 instances, both using a Postgres 9.5.2 database hosted in RDS.
Vault Credentials

The database credentials are stored in Vault, which generates a new login and password for the databases in Postgres. This user is created with the following permissions.

CREATE ROLE "{{name}}" WITH INHERIT LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';GRANT ALL PRIVILEGES on DATABASE "DATABASE_NAME" to "{{name}}";GRANT databaserole to "{{name}}";

This means each time the Java application starts, it sends a request to Vault, to get a new username and password.

eg. username: token-1234-5678
=> \du

       Role name     |                  Attributes                   |   Member of
    -----------------+-----------------------------------------------+----------------
    token-1234-5678  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 1)
    token-abcd-efgh  |  Password valid until 2016-09-11 09:57:14+00  | {databaserole} (java instance 2)

The tables inside of the database are all owned by databaserole

Schema |          Name           | Type  |     Owner
-------+-------------------------+-------+----------------
public | table1                  | table | databaserole
public | table2                  | table | databaserole
public | entries                 | table | databaserole

Inserting Data

When I insert a new row into table entries, one of the fields is too large and therefore converted into a Large Object, but its owner is set to the login of the app that inserted it, rather than database role.

=> \lo_list
            Large objects
  ID   |     Owner       | Description
-------+-----------------+-------------
 17286 | token-1234-5678 |

As the Java application on instance 2, has a different login

One option is to use SET ROLE command after you open a connection from your application to the databaserole:

SET ROLE databaserole;

In such way any object (including large objects) created within this session will be owned by databaserole instead of token-XXX.

Another option is to use LOCAL and make it work only within the transaction used to create the large object:

BEGIN;
SET LOCAL ROLE databaserole;
-- create and insert the large object
COMMIT;

Alternatively, you could just set the role directly as the user setting:

ALTER ROLE "{{name}}" SET role TO 'databaserole';

I kind of dislike that option as it can become a bit obscure to others how it is working, and if you manage other roles to your user it won't inherit them (although that doesn't seem like a problem to you, as with dynamically created user you should have a single role that it directs inherit from).

StackExchange Database Administrators Q#147607, answer score: 7