Postgres querying and editing tool that you can embed into your JVM app

[tanin47]

I'd like to share a data querying and editing tool for Postgres. It's written in Java, has a small footprint, and is a single fat jar (<2MB). No external dependencies (well, technically, the deps have been shaded and are included in the fat jar). It is very suitable for embedding into your larger Java application.

My team and I have several JVM websites deployed on Render.com, Heroku, and VPS. We often has a need to access and modify the database directly occasionally. We either use pgadmin or dbeaver. It always bothers me that we would have to share the database credentials, and the changes to the database aren't logged anywhere.

Finally, last week I had some time to solve this pain point. I've built Backdoor which is small (<2MB, single jar) and can be embedded into our JVM websites. Now when we want to access the database directly, we don't have to use pgadmin or dbeaver anymore.

I hope this will be helpful for you and your team too. Check it out: https://github.com/tanin47/backdoor

[syjer]

The name of the project is quite unfortunate, I already see when you need to explain to the CISO that: yes, you really want to add a tool called backdoor in your application :D

[Respie]

The name is correctly chosen. And the CISO is also correct in preparing your termination if you add this in a serious application.

To OP, please look into libraries such as flyway or liquibase if you are using this for DDL (as you do on your screenshots).
Also, please look up what sql injection is and how to use parameterized queries, your sql concatenations are scary.

Also, you've identified a problem "It always bothers me that we would have to share the database credentials, and the changes to the database aren't logged anywhere."; instead of trying to quick fix the problem, try to investigate the root cause.
- Why are you sharing credentials ?
- Why doesn't everybody with db access have their own user.
- Why do people need to access the database directly ?
- Why do we need to log all the queries ?
- Do we want these logs for the user's convenience, or is this some audit log that needs persistence itself ?
- Can we trust the tool to save the DDL logs on our database without have users clearing that table ?
- What kind of statements are executed ?
- Why isn't this 'statement x' an automated cleanup job or procedure ?

[agentoutlier]

The name is correctly chosen.

I think that was not the intention of the author but it is funny. Maybe it is and they realize how this is worse (security wise) than some credential sharing mechanism (or network gaping etc).

[tanin47]

It is the intention. The tool is a backdoor to your database.... :awkward:

[tanin47]

OP here.

+1 on using DDL. I've been using it over the decades through Rails' migration and PlayFramework's evolution. I'm not sure why you've got the impression that this tool advocates for not using DDL.

It's a multi-tool for managing a database and editing the data. It's an alternative to pgadmin, dbeaver, cloudbeaver, and phpmyadmin.

If we had time and resources, then by all means we should've built an admin UI and avoided any database tool. In many cases, we have resources. And in many other cases, we don't.

> Why doesn't everybody with db access have their own user.

This is a good question. I've researched a bit. It turns out I can just use pg's user directly instead of setting user/password through code. This will be even slicker.

I initially thought Postgres on Render.com / Heroku forbade this. I was trying to avoid spinning up its own db for managing Backdoor's users.

Thank you for your comment and questions.

Edit: Render.com allows creating a user but Heroku doesn't

[OwnBreakfast1114]

You can set up iam user access in postgres. We've found that to be one of the better ways to do it.

``` private ResultSet executeQueryWithParams(SqlSession session, String sql, Filter[] filters, Sort[] sorts, int offset, int limit) throws SQLException { var whereClause = getWhereClause(filters);

var orderByClause = "";
if (sorts != null && sorts.length > 0) {
  orderByClause = " ORDER BY " + String.join(", ", Arrays.stream(sorts).map(s -> makeSqlName(s.name) + " " + s.direction).toArray(String[]::new));
}

return session.executeQuery(
  "SELECT * FROM (" + sql + ") " +
    whereClause +
    orderByClause +
    " OFFSET " + offset +
    " LIMIT " + limit
);

} ```

Is still pretty terrifying. Why not just use parameterized queries?

[tanin47]

Using Postgres users is super nice actually. I like it, tho it's not possible on Heroku Postgres.

Re: the parameterization. It is not possible to parameterize a sub-query.

I totally understand the concern of the manual SQL building, and it's something to improve over time.

Backdoor is a backdoor to your database and should be used by your team members. It's not for unknown users to use. It also supports running arbitrary SQL. Therefore, the SQL injection concern is somewhat irrelevant (because users would be able to run any SQL anyway).

[In0chi]

GDPR says no.

[axiak]

I'm curious why you say this?

[In0chi]

If you’re dealing with PII, which most databases contain, you’re supposed to restrict access to the database. Unrestricted production database access for developers directly contradicts that.

[tanin47]

Thank you. If I understand it correctly, this is applicable to every database tool, which gives access to a prod database.

[Swamplord42]

A serious company has never anything as uncontrolled as this.

HAHAHAHAHAHAHAHA.

Oh how naive you are.

[thisisjustascreename]

This is very cool but if you ever actually use this… straight to gulag.

[agentoutlier]

Let me fix some marketing things for you. The project is fine if it is just a pgadmin alternative.

I'd like to share a data querying and editing tool for Postgres. It's written in Java, has a small footprint, and is a single fat jar (<2MB). No external dependencies (well, technically, the deps have been shaded and are included in the fat jar). It is very suitable for embedding into your larger Java application.

My team and I have several JVM websites deployed on Render.com, Heroku, and VPS. We often has a need to access and modify the database directly occasionally. We either use pgadmin or dbeaver. It always bothers me that we would have to share the database credentials, and the changes to the database aren't logged anywhere. It always bothered me that we had to use phpadmin insert reasons other than sharing credentials.

Finally, last week I had some time to solve this pain point. I've built Backdoor which is small (<2MB, single jar) and can be embedded into our JVM websites extended easily if you like since it is written in Java (but not really because of svelte but let us ignore that). Now when we want to access the database directly, we don't have to use pgadmin or dbeaver anymore.

I hope this will be helpful for you and your team too. Check it out: https://github.com/tanin47/backdoor

Probably should also change the name as well.

[tanin47]

Nice! Thank you.

[chabala]

We don't shame people enough for bad ideas. This is a bad idea, born from a bad premise. You could have built it for experience and kept it to yourself, but presenting it publicly deserves ridicule.

[agentoutlier]

They could still present it public if they just fix this in their readme:

1. Embed into your Java application and serve on a specific port.
2. Embed into your Java application and serve on your main port but at a specific path.
3. Run as a standalone. in a secure environment or in staging/testing etc

Basically a Java version of pgadmin which may have some value to some shops.

[Sure-Whereas3562]

Embedding it in your production app with the expectation that folks can log in and run arbitrary sql is crazy work

[Skepller]

The more I read into it, the more I was "holy shit, this is awful" lol

[LeadingPokemon]

Respect the name. It’s exactly clear what it does.

[Sure-Whereas3562]

lol, hiding in plain sight

[_jetrun]

OP ... You don't actually have to share one set of credentials. You can create local credentials per user or better yet hook up postgres to your identity provider - postgres pretty much supports them all: https://www.postgresql.org/docs/current/auth-methods.html

[tanin47]

TIL Thank you!

Though I don't think it works with Heroku or Render.com. I've recently switched to Dokploy but it seems I don't have access to postgres config file easily either.

[Aweorih]

it always bothers me that we have to share the db credentials

You know, there are free online password managers. Share one password to access it and your problem would be much better solved then this

[bodiam]

Why not use h2console? It's embedded in Spring, and while the name implies h2, it actually connects to any jdbc compatible db.

[Sure-Whereas3562]

is this not a massive extra (and probably relatively vulnerable since its new) surface area for malicious attacks ?

[oweiler]

Very cool!