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 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