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. Each of the following four is enough on its own – together they also catch a mistake in any one of them.

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 and can only be reached through the Kubernetes API, with the same sign-in and the same rights as everything else.

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 unless set otherwise. The text, the database, the row count and the duration go into the audit log, the results never.

A permission of its own, separate from the database rights, decides who may use the console – reading customer data follows from no other right. The queries go through a small runner in the cluster that Clusterward reaches through the Kubernetes API; the database stays on the private network.

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