Permission denied for Large Object with outbox-polling strategy

Hibernate Search
1

Hi,
We wanted to start using the outbox-polling strategy in our system, we have multiple applications that have different database users and they share the same database (AWS RDS Postgres in this case). On turning on outbox-polling strategy in these applications, if suppose there is an object created by app1 and hsearch_agent in app2 tries to pick this for indexing, it encounters a permission denied for Large Object (since the object is owned by app1). Is there any workaround for this in hibernate-search, as lo_compat_privileges setting is unmodifiable in AWS RDS Postgres?

2

Hi,

I unfortunately cannot offer much advice on the database side of your problem since I know next to nothing about AWS RDS, and very little about large objects on postgres. But I would suggest to find a solution to share large objects between app1 and app2. It does not seem normal that those apps can share the same tables but not the same large objects.

From what I’ve read, there are ways to define default privileges on postgres, so you probably can execute some SQL on your database to grant select/update/delete privileges on large objects to app2 by default?

Quoting this page:

PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command.

Maybe @gsmet can offer some advice, as our postgres guru?

3

@yrodiere thanks for the input, I agree that we indeed have a peculiar scenario here of shared database among different applications, we have already tried a bunch of stuff on the RDS side, and the default privileges doesn’t seem to be applicable to Large Objects.
Is there a way for the hsearch_agents to only pick the objects that were committed by “their” application instance? I know this might take away from the load sharing principle among agents, but just looking for any kind of workaround here.

4

If your application instances work on strictly separate subsets of your data, then I would suggest having them use separate database schemas, or even separate databases; both solutions would solve the problem. But I suspect that’s not your case?

If your application instances can potentially work on the same entity instances (no strict partitioning), as I suppose they do, then no, there is no way for agents to pick only objects that were committed by “their” application. That would break the whole design and re-introduce the problems outbox-polling is supposed to solve.

If you really need a workaround right now, regardless of how brittle it is, you can set the property hibernate.search.coordination.outboxevent.entity.mapping to a Hibernate ORM XML mapping of your choice. You’ll probably want to base your mapping on the value of org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxPollingOutboxEventAdditionalJaxbMappingProducer#ENTITY_DEFINITION, and change the type of the payload property to longvarbinary, which should work fine on postgres (but not on every other database). Then you won’t be using large objects at all.

But, as you can see, this involves dealing with an SPI and internals. You should prepare for the possibility that your code breaks when moving to the next version of Hibernate Search, even a micro (e.g. 6.1.4). So fixing your postgres permission problem (somehow) would be a much better idea. You’ve been warned :slight_smile:

Maybe we should make the default mapping smarter, switching to longvarbinary [EDIT: it’s image, actually, see below] for dialects that we know support it correctly… I’ve been reluctant to do that, since it really shouldn’t be Hibernate Search’s role to adapt to each database vendor, that’s Hibernate ORM’s job. But maybe that’s the only pragmatic solution :confused:

EDIT: I created [HSEARCH-4532] - Hibernate JIRA to try to switch to a better mapping in a future version of Hibernate Search (don’t hold your breath though, that’ll probably be in Search 7.0)

1 Like
5

We will try out the custom hibernate.search.coordination.outboxevent.entity.mapping with longvarbinary and let you know. We have been using this library since the beta days, so we have always remained on the “warned” side :slight_smile:
(I knew you would have something up your sleeve :upside_down_face:)

And I agree that switching to longvarbinary should be under Hibernate ORM’s concern.

6

From a PostgreSQL point of view, if your applications are separated, I would recommend that you use separate schemas for each application.
That’s what PostgreSQL schemas are for.
You can find more information about them here: PostgreSQL: Documentation: 14: 5.9. Schemas .

By doing so, you will have several outbox polling tables (with the same name) in separate schemas and that will fix your issue (and frankly probably others as having several separate applications mixing tables in the same schema is not exactly a good practice).

7

@gsmet yes, that would be an ideal solution, but the current application ecosystem is sort of a cross between monolith and microservice architecture, and since its legacy, we are incurring quite a lot of tech debt right now on it. For newer applications, we follow strict data isolation principles per application.

8

@yrodiere did a sample run with the custom mapping, encountering this error atm:

Caused by: org.hibernate.MappingException: Could not determine type for: longvarbinary, at table: hsearch_outbox_event, for columns: [org.hibernate.mapping.Column(payload)]
	at org.hibernate.mapping.SimpleValue.getType(SimpleValue.java:499)
	at org.hibernate.mapping.SimpleValue.isValid(SimpleValue.java:466)
	at org.hibernate.mapping.Property.isValid(Property.java:227)
	at org.hibernate.mapping.PersistentClass.validate(PersistentClass.java:624)
	at org.hibernate.mapping.RootClass.validate(RootClass.java:267)
	at org.hibernate.boot.internal.MetadataImpl.validate(MetadataImpl.java:354)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:298)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409)
	... 108 more

This is the XML that I am using:

<?xml version="1.0" encoding="UTF-8"?>
<hibernate-mapping>
   <class name="org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxEvent" entity-name="org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxEvent" table="HSEARCH_OUTBOX_EVENT">
      <id name="id" type="long">
         <generator class="org.hibernate.id.enhanced.SequenceStyleGenerator">
            <param name="sequence_name">HSEARCH_OUTBOX_EVENT_GENERATOR</param>
            <param name="table_name">HSEARCH_OUTBOX_EVENT_GENERATOR</param>
            <param name="initial_value">1</param>
            <param name="increment_size">1</param>
         </generator>
      </id>
      <property name="entityName" type="string" length="256" nullable="false" />
      <property name="entityId" type="string" length="256" nullable="false" />
      <property name="entityIdHash" type="integer" index="entityIdHash" nullable="false" />
      <property name="payload" type="longvarbinary" nullable="false" />
      <property name="retries" type="integer" nullable="false" />
      <property name="processAfter" type="Instant" index="processAfter" nullable="true" />
      <property name="status" index="status" nullable="false">
         <type name="org.hibernate.type.EnumType">
            <param name="enumClass"><?xml version="1.0" encoding="UTF-8"?>
<hibernate-mapping>
   <class name="org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxEvent" entity-name="org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxEvent" table="HSEARCH_OUTBOX_EVENT">
      <id name="id" type="long">
         <generator class="org.hibernate.id.enhanced.SequenceStyleGenerator">
            <param name="sequence_name">HSEARCH_OUTBOX_EVENT_GENERATOR</param>
            <param name="table_name">HSEARCH_OUTBOX_EVENT_GENERATOR</param>
            <param name="initial_value">1</param>
            <param name="increment_size">1</param>
         </generator>
      </id>
      <property name="entityName" type="string" length="256" nullable="false" />
      <property name="entityId" type="string" length="256" nullable="false" />
      <property name="entityIdHash" type="integer" index="entityIdHash" nullable="false" />
      <property name="payload" type="longvarbinary" nullable="false" />
      <property name="retries" type="integer" nullable="false" />
      <property name="processAfter" type="Instant" index="processAfter" nullable="true" />
      <property name="status" index="status" nullable="false">
         <type name="org.hibernate.type.EnumType">
            <param name="enumClass">org.hibernate.search.mapper.orm.coordination.outboxpolling.event.impl.OutboxEvent$Status</param>
         </type>
      </property>
   </class>
</hibernate-mapping></param>
         </type>
      </property>
   </class>
</hibernate-mapping>
9

Right, sorry, you need to use image. Hibernate ORM doesn’t use the name longvarbinary for some reason.

But wait, are your two applications working on strictly separate sets of entities/tables that just happen to be in the same schema? If so, my solution above won’t work, since app2 will not be able to load entities defined in app1. In this case you would need to make sure your apps use different table names for Hibernate Search’s technical tables (agents and outbox events). The easiest and safest way to do that would probably be to set up a custom PhysicalNamingStrategy in your app, and to override the name of those two tables in one of your apps: HSEARCH_OUTBOX_EVENT, HSEARCH_AGENT. Just make sure app1 and app2 use

WARNING: only do this if your two apps work on strictly separate sets of entities/tables, i.e. if they could theoretically use distinct database schemas. If even just one entity/table involved in indexing is shared between your two apps, using two different event/agent tables will lead to indexing problems.

10

Thanks @yrodiere , image type worked. You saved me a lot of Google time.

For your question, no our apps are not strictly working on separate entities/tables. They are deployed on different servers with different database users, but the code base is shared, i.e. all @Entity types are known to all servers.

11

We have managed to get it working with the proposed workaround on our staging environment. Thanks @yrodiere .

12

Glad this helped. Thanks for keeping us updated, @Ansuman_Pattanayak!

1 Like