Postgres Large Objects & Multiple Users

Question

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

Answer 1

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

Answer 2

How can I setup Postgres so that the Large Objects always have the ownership of the role's member parent

There's no way to do that. But if you have control over the program's code, it could issue

GRANT SELECT ON LARGE OBJECT :objid TO databaserole;

immediately after each object is created.

---

Otherwise, a drastic solution would be switching the database to pre-9.0 large object permissions:

ALTER DATABASE dbname SET lo_compat_privileges TO on;

Documentation:

lo_compat_privileges (boolean)

In PostgreSQL releases prior to 9.0, large objects did not have access privileges and were, therefore, always readable and writable by all users. Setting this variable to on disables the new privilege checks, for compatibility with prior releases. The default is off. Only superusers can change this setting.

Setting this variable does not disable all security checks related to large objects — only those for which the default behavior has changed in PostgreSQL 9.0. For example, lo_import() and lo_export() need superuser privileges regardless of this setting.

This might make sense because the logic of "extending rows" by large objects is at odds with the post-9.0 large object permissions logic. The difference is that future rows in a table have access rights predetermined by GRANTS to the table, whereas the concept of access rights applying to not-yet-created large objects does not exist.

Lastly, if there is a way to reconfigure your application so that it uses bytea fields instead of large objects, that would be an ideal solution.