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
Best Answer
One option is to use
SET ROLE
command after you open a connection from your application to thedatabaserole
:In such way any object (including large objects) created within this session will be owned by
databaserole
instead oftoken-XXX
.Another option is to use
LOCAL
and make it work only within the transaction used to create the large object:Alternatively, you could just set the role directly as the user setting:
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).