DEV Community

ひとし 田畑
ひとし 田畑

Posted on

I shipped a database console with no login — on purpose

#security #postgres #webdev

"You built a web UI that runs SQL against a database, and there's no login?"

Yes. On purpose. And the reaction is worth unpacking, because "add authentication" is a reflex, and reflexes skip the question that actually matters: what's the threat you're defending against? For a tool that runs on your own machine, next to your own database, a login turns out to be the wrong tool for the real danger — and it drags in a liability you don't want.

What auth would actually cost

The moment you add accounts, you're holding credentials on a server. Now you own: password hashing, session management, reset flows, lockouts, and — because this thing also stores database connection passwords — an encryption-at-rest story for those too. You've signed up for a multi-tenant security posture to protect a tool that one person runs on localhost.

And here's the thing: none of that defends against the attack that can actually reach a local tool. If anything, being "logged in" makes it worse.

The real threat: the tab you forgot about

A web server on localhost:8000 isn't invisible just because it's local. Every other website open in your browser can send requests to it. So the realistic attack on a no-auth local console isn't "a stranger logs in as you" — it's a drive-by: some page you have open in another tab quietly fires a cross-origin POST at http://localhost:8000/... and your database executes a DROP.

Notice what a login does against that attack: nothing. You'd be logged in. The malicious form submits as you, with your cookies, to your authenticated session. Auth defends against the wrong actor — a remote impostor — when the actual actor is a webpage abusing the trust your browser already extends to you.

The defense that does work is the one for exactly this shape of attack: CSRF protection and clickjacking protection. So those are the two things cli2ui actually spends its security budget on:

# settings.py — even though the tool is local-only and has no sessions:
MIDDLEWARE = [
    "django.middleware.security.SecurityMiddleware",
    ...
    "django.middleware.csrf.CsrfViewMiddleware",
    # Sending X-Frame-Options: DENY so the UI can't be framed stops a
    # clickjacking page from tricking you into clicking its buttons.
    "django.middleware.clickjacking.XFrameOptionsMiddleware",
]

# Cookie-based CSRF works standalone here (no SessionMiddleware needed),
# and the trusted origins are pinned to localhost only.
CSRF_TRUSTED_ORIGINS = ["http://localhost:8000", "http://127.0.0.1:8000"]
Enter fullscreen mode Exit fullscreen mode

Without CSRF, any website you visit could fire that cross-origin form POST at localhost and mutate — or drop — your database. That is the threat a local SQL console has to take seriously, and it's a per-request token, not a login. (The htmx UI sends the token via an hx-headers attribute on <body>, so there's no {% csrf_token %} sprinkled through every form.)

The rest of the hardening that still matters

Dropping auth doesn't mean dropping care. The destructive surface is real, so:

  • DEBUG is off by default. A Django error page leaks tracebacks, settings, and SQL — and this tool sits right next to a database. You opt into DJANGO_DEBUG=1 only when you're actually debugging.
  • Identifiers are bound, not formatted. Schema / table / column names go through psycopg2.sql.Identifier; the few raw-SQL spots (like an index access method) use a fixed allow-list. The ad-hoc SQL runner executes what you typed — that's the feature — but everything around it doesn't concatenate your table name into a string.
  • The runner defaults to read-only at the server. SET TRANSACTION READ ONLY means Postgres itself refuses writes; write mode is opt-in and snapshots the database first.

The honest part: this is a position, not a law

I'm not arguing "auth is bad." I'm arguing auth is the wrong first move for a single-user, local tool, and that the reflex to add it skips the threat model. The README says this plainly and loudly: do not expose cli2ui to an untrusted network. The moment any of these change —

  • more than one person uses it,
  • it's reachable from outside your machine or trusted network,
  • it's deployed as a shared service —

— the threat model flips, "a remote impostor" becomes real, and you do need accounts, encrypted credential storage, and the whole apparatus. At that point this is the wrong design. That boundary is the actual decision, stated up front, instead of bolting on a login so the architecture looks secure while the drive-by POST sails through.

(For full honesty: cli2ui stores your database connection passwords in plaintext in a local SQLite file — which is fine for a single-user local tool and indefensible the instant it isn't. Same boundary, same rule.)

"No login" isn't the absence of a security decision. It's a security decision — scoped to a threat model I wrote down, defended where the attack actually lands.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What command do you reach for that should be a button?

Top comments (0)