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
=&gt; \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.

=&gt; \lo_list
            Large objects
  ID   |     Owner       | Description
-------+-----------------+-------------
 17286 | token-1234-5678 |


As the Java application on instance 2, has a different login to instance 1 eg. token-abcd-efgh, it means it cant execute select * from entries; as it is not the owner of the Large Object.

I am aware that I can change the owner of the object after it has been created, but this is not feasible due to the number of insertions.



Question

How can I set it so that any Large Object has the owner of databaserole rather than the login of the application that created it?



TLDR

As I have an multiple instances of an app, with unique and dynamically created logins. How can I setup Postgres so that the Large Objects always have the ownership of the role's member parent, so that any login that is a member of this parent role can view the object?



SOLVED

I added another transaction to run the follow sql everytime hibernate tries to insert on that table.

SET ROLE databaserole
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).