IntegrateDB - Database sharing toolkit for Postgres

@radiospiel you can totally use Integrate as is for this and the default behaviour with no claims is simply to provide each stakeholder application a db user that’s scoped to its own schema. However, you could also do this with a couple of auth related SQL statements.

I’m a bit snowed under at the moment but I’ll post here when I manage to get the time to restructure as planned

@thruflo I am curious to see the results Initially I was asking because what I need(ed) to do is to share a single postgres database between multiple services, where each service gets its own schema, but in general these services do trust each other to not wrack havoc on other services’ data (i.e. they somehow trust each other.) I think integratedb is not what I needed, but it is somewhat in that üroblem space

@radiospiel that’s a pretty clear sign that the docs / description aren’t clear enough!

I’m hopeful I can simplify and clarify the docs as part of the next phase. Thanks for the feedback

Hey James,

In fact I did. But I am still lost

(I am probably not the target audience though)

/eno

Hey @radiospiel did you see the docs, e.g.: Intro — IntegrateDB v0.1.0

thruflo I am really totally lost on what it really does. Simplify your architecture/ Increase your development velocity/ Without shooting yourself in the foot all sounds really great, but a pointer to an easy to follow scenario, maybe in the form of a blog post (ideally with a link to some code examples) would probably be really helpful to explain what you are achieving.

@eteeselink thanks for the feedback and for digging into the code

You are right that at the moment it’s consuming every replication update and that this isn’t necessary. Let me try and explain!

What I’d conceived of initially was to combine the data claims with the ability to use the same machinery to configure notifications, as per usage.html#notifications. So the intention is to consume the logical replication feed and then “forward on” updates that a secondary app has registered for over web hook / socket / queue / etc. Now, I haven’t got round to implementing the event handling part of this yet and it’s on my todo list when implementing to restrict the publication feed to stream updates to just the registered tables.

What I have implemented atm is using the logical replication machinery as a way to tell IntegrateDB to re-sync the data access claims after a migration. User specification data like fields: ["*"] is expanded to snapshot the actual columns that match the spec when saving data access claims. This needs to be re-done whenever the DDL changes, which means somehow triggering the process. Right now, I have an integrated_sync() function in the db that inserts a new record to the integratedb.sync table and then the only thing the replication consumer actually does atm (as per replication.ex#L56-L66) is watch for these changes.

This could and should be locked down – the publication feed only needs the sync table at the moment. (In fact, I just pushed a temporary fix: replication: limit publication to just the sync table for now. · integratedb/integrate@0b4a7a3 · GitHub).

However, following some of the previous feedback in this thread, I’m thinking a more comprehensive solution is to separate out the “ops client” (which as you say can be passive and just needs to create db accounts which can only access those fields they claim access to, and then verify that DB migrations don’t violate those claims) from the notification service, which needs to be a running application to consume and forward on the events. This would mean that you wouldn’t need to enable replication at all unless you want to run the notification service.

The wrinkle is that I still need to expand spec data into claims somehow, which maybe means moving some logic into the database. That’s maybe a better solution anyway as it then could be done transactionally as part of the migration.

Hope that makes sense and thanks again for pointing this out!

This looks really nice! I also had a look at the code base but there’s one fundamental thing I don’t understand.

What I understand IntegrateDB does is create db accounts which can only access those fields they claim access to, and then verify that DB migrations don’t violate those claims. So far so good.

But in the code I see a whole lot of stuff going on to fully consume all logical replication messages being sent. Doesn’t that mean that every change to the DB data gets sent to IntegrateDB? Every insert, update and delete? What’s that for? My (likely incorrect) understanding is that IntegrateDB is a pretty passive program until migrations or new db users happen. What am I missing?

Either way, very cool, this looks like an excellent way to turn a bad practice good!

@brightball awesome, thanks so much for taking the trouble to write this

Here you go. I was in the car when I responded earlier.

“It’s not right for every situation, but several years ago I worked on a project with two separate code bases with recurring subscription systems. Due to a dispute with one of them and some limitations in services, we had to quickly request for every single user to manually update their subscriptions from one platform to the other. In order to make it as easy as possible, the new system needed to be able to know everything about the subscription on the old system so that users would only need to update their payment information and click a button. Our choices were to build an entire API on the old system with a legacy code base that nobody understood or to let the new database connect to the legacy database directly to perform joins. We ended up going with the second option and had a working version in production under two weeks with no issues. Everyone migrated successfully and the crisis was averted. Sometimes SQL is the best API.”