Looking at production data without sharing the password

Tuesday lunchtime, a customer writes that her order from yesterday never arrived. Support wants to know whether it is in the database at all. The quickest way is almost always the same: DATABASE_URL copied from the environment variables, kubectl port-forward, psql. Five minutes later the question is answered – and three new problems have appeared.

Why the application’s password is the wrong key

The application usually connects as the owner of its database. Whoever has its password has everything:

• Write access to every table, plus DROP and TRUNCATE – a forgotten WHERE is enough.
• Afterwards the password sits in the chat history, the shell history and perhaps a note. Changing it means rolling out the application again.
• The port-forward opens a path from the laptop into the private network for as long as it runs.
• The database log shows only the application’s login. Later nobody knows who saw what.

None of this is ill intent. It is simply the path that happens to be open. The answer is therefore not a ban but a better path that is just as quick.

Four layers for real read access

Read access to a production database only deserves the name when several layers prevent writes independently of each other. The first two each prevent writes on their own and catch each other's mistakes; the other two close what can still go wrong beyond that: commands on the client's machine and queries that bring the database down.

1. A login of its own with read rights. In PostgreSQL a role with CONNECT, USAGE on the schema and SELECT on the tables – plus ALTER DEFAULT PRIVILEGES for the owner role, otherwise the login sees no table the application creates later. In MySQL, GRANT SELECT ON db.* is enough.
2. A read-only transaction. BEGIN READ ONLY in PostgreSQL, START TRANSACTION READ ONLY in MySQL, and always ROLLBACK at the end. Even if the login had too many rights, the database refuses writes to tables.
3. One statement, no command-line client. psql knows meta-commands such as \!, which runs a shell command on the client’s machine. Anything that passes text on to a client inherits that. Better: a database driver and exactly one statement per query.
4. Limits for time and rows. A SELECT * without a condition on a table with 40 million rows loads the database and the browser. PostgreSQL stops with statement_timeout, MySQL with max_execution_time for SELECT statements; rows are capped while reading, not only in the display.

The permissions side of this – why a read user without default privileges sees nothing after the next migration – is covered in the article One database per service: roles, privileges and the read-only user who sees nothing.

The evidence: who asked what

Read access to customer data needs evidence – for your own security, for ISO 27001 and for NIS2. The audit log should hold:

• who asked and when,
• in which database,
• the query text,
• how many rows came back and how long it took.

What does not belong there: the results. Otherwise the audit log becomes a second copy of the customer data, with its own retention periods and its own risk.

The network: why no port-forward

A well-run database has no public endpoint; it sits on the private network next to the cluster. A port-forward or a bastion host breaks exactly that, usually from a laptop. The better way reverses the direction: a small service inside the cluster runs the query; from outside it can only be reached through the Kubernetes API, with the same sign-in and the same rights as everything else, and it only answers requests carrying its own key.

A typical case

Back to the customer with the missing order:

1. Support opens the shop’s database and searches for the order by email and date. It is there, status “paid”.
2. A second query shows that the shipping job never picked it up. The fault is in the worker, not in the order.
3. Both queries are in the audit log, with name and time. Nobody had write access, no password was passed on.

How Clusterward does it

In the cockpit under Operations → SQL console: pick a database, write one statement, read the result. The console connects with its own read-only login per database, every query runs in a read-only transaction that is rolled back, and it stops after 60 seconds or 1,000 rows. Both can be raised per query, up to the limits you set for the workspace (at most 600 seconds and 50,000 rows); “Load next” fetches further rows, each load with its own audit entry. The text, the database, the row count and the duration go into the audit log, the results never. Clusterward deletes the entries automatically after 12 months.

The console brings more than an input box: an SQL editor that completes tables and columns, tabs and a schema browser with tables, columns and indexes. Queries can be saved for yourself or the team, with parameters such as :email whose values the database binds separately from the text; a history shows your own queries of the last 12 months from the audit log, and a link opens a query for a colleague without running it. Results open full screen, can be sorted and downloaded as CSV, CSV for Excel or JSON. A running query can be cancelled, “Explain” shows its execution plan, and ready-made diagnostics answer the usual questions about size, indexes and sessions.

A permission of its own, Operations, decides who may use the console – reading customer data follows from no database right alone. On top it takes at least view access to databases; users limited to some applications see only their databases, and API tokens cannot use it at all. The queries go through a small runner in the cluster that Clusterward reaches through the Kubernetes API; the database stays on the private network. Since October, the console can also change data if you allow it – every change runs as a preview first, which is rolled back.

Conclusion

Read access to a production database is quick to build if you take the application’s password – and then it is not read access. Its own login, a read-only transaction, one statement without a client and limits for time and rows make it real; the audit log makes it traceable.

Sorting out access to your production data? Tell us who looks into your databases today and how. We’ll show you how that works without a shared password. Ask a question →

Sources and further reading

• PostgreSQL docs: SET TRANSACTION
• PostgreSQL docs: ALTER DEFAULT PRIVILEGES
• PostgreSQL docs: psql meta-commands
• MySQL docs: START TRANSACTION