IntegrateDB - Database sharing toolkit for Postgres

This seems really good. The only comment I have on reading is that when you are able to move more toward security, it might be worth looking at what Hashicorp Vault does regarding temporary credentials for access so that the stakeholder bootstrapping process might be able to not have setup configurations that are wholly manual.

@halostatue yup, that’s a great suggestion, thank you.

I have been thinking about some kind of more automatic bootstrapping, for example accepting an env var with a pub key for the the root user. Maybe a config file for stakeholders. Would be very interesting to think about pushing stakeholder creds to a secure store automatically. Could be vault, or perhaps into a k8s secret?

Example - integrate a reporting application

Imagine that you have a primary web application with a Postgres database and that you want to integrate a reporting system. Assume you’ve installed IntegrateDB and bootstrapped a root user. You then start by creating a Stakeholder representing your secondary reporting application:

curl -X POST -H "..." -d '{"name": "reporter"}' /api/v1/stakeholders

This creates a Postgres database user (reporter) with access scoped to its own private DDL schema (reporter.*) and returns the database user credentials in the response data:

{
  "data": {
    "credentials": {
      "username": "reporter", 
      "password": "... randomly generated ...",
    }
  }
}

Save the credentials (somewhere safe!) and provide them to your reporting application as the database user and password it should use to connect directly to the Postgres database.

Unless I misunderstood you want to give the user and password to a client in the web to access the database directly?

Also, the cURL request can be done from a client in the internet?

Unless I misunderstood you want to give the user and password to a client in the web to access the database directly?

Yup, I think it’s a slight misunderstanding in that the user and password are meant to be given to an application, not to a client. I.e.: think a Phoenix app which you would typically provide a DATABASE_URL to, not a React client that might typically talk to an API endpoint.

Thanks for flagging up as I should make that clear. You definitely shouldn’t expose the db user credentials publicly

Also, the cURL request can be done from a client in the internet?

IntegrateDB is an application you would deploy on your infrastructure (server / cloud / cluster) fairly close to / alongside your database. So you can lock down access as you prefer – network firewall, RBAC, reverse proxy, etc.

The API is also envisaged as something more for an operator to talk to (a CLI is on the roadmap) or to use as part of an provisioning workflow. It would be unusual for your application to talk to it.

Hope that makes sense

It makes more sense now, but from a security point of view I am always suspicious when databases are being exposed directly as an API.

You should strive to make clear that the API is internal and should be on a private network, but even so I would require authentication to the internal API.

Yup, maybe a “security considerations” section in the docs.

I’m conscious that there’s more that could be done to make access to the IntegrateDB app more fine grained – right now any user can do anything to any stakeholder including claiming access to any arbitrary data. That’s definitely an area for improvement.

It’s also interesting to consider whether it needs to be a running application service at all. Perhaps it could be spun up and down, or just run locally or even just be reimagined as a CLI tool?!

Why just not a library that other applications can use programmatically?

It’s an often overlooked and under appreciated need. I once had a project where I needed to connect two systems and my options were to either build an API so one application could read from the other…or connect the databases so I could just query both at the same time.

Went with door #2 and the entire urgent project was done in 2 weeks. Not a single hiccup was experienced.

Exactly

There are some nuances. Particularly the replication consumer and notifications system as envisaged need to be a running service. However, it may well be possible to refactor out so these are different tools in the box – a library / local ops client and a notification service. The latter could run with just replication permission and wouldn’t expose the db in the same way.

Thanks for the suggestion, much to ruminate on!

@brightball could I use this as a quote if I get round to posting some?!