The catalog reader: what it reads, what it never touches

The audit suite works from your database's live schema rather than from your migration files. That means SQLens connects to a database you probably care about a great deal, and this page is the checkable version of the promise it makes about that: it reads catalog metadata, it writes nothing, it takes no locks, and it bounds itself.

What it reads

The reader issues a fixed battery of queries against system catalog relations. The list is complete — there is nothing else — and a test over a recorded run enforces it, so this page cannot drift away from the code without something going red.

On PostgreSQL it reads pg_class, pg_namespace, pg_attribute, pg_attrdef, pg_index, pg_am, pg_opclass, pg_constraint, pg_type, pg_collation, pg_inherits, pg_depend, pg_extension, pg_settings and pg_roles.

On MySQL it reads information_schema.SCHEMATA, .TABLES, .COLUMNS, .STATISTICS, .TABLE_CONSTRAINTS, .KEY_COLUMN_USAGE, .REFERENTIAL_CONSTRAINTS and .PARTITIONS.

What it never touches

• No user table is read. Not to count rows, not to sample values, not as a join in a catalog query. Your data is not part of the input.
• No write. With exactly one exception, described below.
• No lock. No LOCK TABLE, no SELECT … FOR UPDATE, no FOR SHARE. A tool sent to look for lock problems must not be the one that causes them.
• No DDL. Nothing is created, altered or dropped.

The single exception is the read-only probe. The reader opens its own connection, seals it read-only, and then attempts one write and requires the refusal. That probe runs inside a savepoint and is rolled back either way, so it leaves nothing behind. It exists because a session-wide read-only flag can report itself as set while writes go through — measured, on PostgreSQL — and a promise that rests on an unverified setting is not a promise. If the write succeeds, the reading does not happen at all.

If you build your own read seam: open the transaction through Laravel

The probe above only works because the transaction it runs in is one Laravel opened, with $connection->beginTransaction(). A raw BEGIN TRANSACTION READ ONLY sent through statement() looks equivalent and is not.

Laravel's lost-connection detector counts PostgreSQL's refusal — SQLSTATE 25006, cannot execute INSERT in a read-only transaction — among the errors that mean the connection died. When a statement fails, Connection::handleQueryException() rethrows only while the connection is inside a transaction it is itself counting. Outside one it reconnects and runs the statement again, on a fresh connection where no read-only transaction is open.

So with a raw BEGIN, the server refuses your write and the framework performs it a moment later. On a seam like this one the retried statement is the probe itself: the tool writes into the database it promised never to write to, and then reports that the session is not sealed.

The order matters too. SET TRANSACTION READ ONLY has to be issued after the transaction is open — MySQL refuses it inside one with SQLSTATE 25001, and PostgreSQL applies it to the transaction it is in.

MySQL is not retried today, because the detector matches on message text rather than on the SQLSTATE, and its needle carries PostgreSQL's wording. That is a measured fact about one version of one framework, not a property to build on: open the transaction through Laravel on both engines.

The privileges it needs

SQLens is designed to run as an ordinary, least-privileged account. Neither engine needs a superuser.

PostgreSQL

CREATE ROLE sqlens_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_database TO sqlens_reader;
GRANT USAGE ON SCHEMA public TO sqlens_reader;

That is enough for the core of an audit: pg_class, pg_attribute and pg_index are readable by any role on a stock PostgreSQL, so tables, columns, indexes, constraints and types all come back. Two things do not, and both end as named skips rather than errors:

-- Optional. Without it, no check may reason about table size or row estimates.
GRANT pg_read_all_stats TO sqlens_reader;

-- Optional. Without it, settings that require elevated access are invisible — and they
-- are ABSENT from pg_settings rather than refused, so nothing in a reading would
-- otherwise reveal that they were missing.
GRANT pg_read_all_settings TO sqlens_reader;

MySQL

CREATE USER 'sqlens_reader'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT ON your_database.* TO 'sqlens_reader'@'%';

The SELECT grant is not optional here, and the reason is worth knowing: MySQL filters information_schema by privilege, silently. An account without SELECT on a table does not see that table's row — the query succeeds and returns nothing. An under-privileged audit therefore comes back clean, empty and complete-looking, which is why SQLens probes what it can see before reading and reports an invisible database as a finding rather than as an empty result.

Managed databases

On RDS, Aurora, Cloud SQL and Neon, part of the catalog is unavailable without access nobody hands out. That is the ordinary case, not a fault, and SQLens is built for it: it finishes the run and names what it could not read.

Platform	Expect to see
Amazon RDS / Aurora	pg_read_all_settings unavailable → restricted server settings skipped
Google Cloud SQL	statistics and some settings restricted; performance_schema may be off on MySQL
Neon	superuser-only catalogs unavailable; the core catalog reads normally
Any MySQL host	performance_schema disabled or not granted → no live statement instrumentation

A skip is a finding, not noise. It appears in the report as an undetermined result with a named reason, it is counted in the summary, and under strict_undetermined it fails the run. What it never does is disappear: a check that could not run is never reported as a check that passed.

One kind of skip is not undetermined. An index read completely and left out of a comparison on purpose — a b-tree under a non-default operator class, a partial index beside another condition — reports as not_applicable under AUDIT.CATALOG.NOT_COMPARED. Nothing about it went unread, so strict_undetermined does not fail a run over it.

Configuration

Every key below lives under sqlens.catalog.

Key	Default	What it does
session.statement_timeout	5000	Milliseconds a single catalog query may take. Must be positive; zero would mean "wait forever", which is the harm the bound exists to prevent.
session.lock_timeout	1000	Milliseconds the reader waits for a lock it never intends to take. A bound against waiting, not against locking.
session.idle_in_transaction_timeout	5000	Milliseconds a stalled read transaction may sit open. This is the shape that holds a snapshot open and blocks VACUUM on PostgreSQL.
session.application_name	sqlens	The name the reader appears under in the server's activity view. An unidentified session holding a connection on production is one somebody eventually kills blind.
budget_ms	5000	Milliseconds a whole reading may take before it reports budget_exceeded. Not a second statement timeout: a hundred fast queries can be inside every per-statement bound and still hold a connection for half a minute. Exceeding it is a named undetermined, never an abort.
schemas	[]	The schemas to audit. Empty means the session's own resolved scope — the real search_path on PostgreSQL, the current database on MySQL — never an assumption that everything lives in public. A schema that does not exist is a configuration error naming it, not an empty audit.
table_prefix	null	Overrides the connection's own Laravel prefix. null means "not overridden"; '' is a real answer meaning "this project has no prefix".
prefix_scope	loose	loose reads everything and marks what is not the project's; strict reads only prefixed objects and names what it left out. Loose is the default because omitting objects is the worse way to be wrong: strict would silently drop a project's own unprefixed legacy tables.
report_partitions_individually	false	Whether each partition is its own object. Off by default: a partitioned table is one object to a rule, and reporting it as n multiplies every finding by the partition count.
include_extension_objects	false	Whether objects a database extension owns are audited. Off by default — PostGIS alone installs tables whose design nobody in your project chose. Ownership is read from pg_depend, never from a name list.
extensions.allow	[]	Extensions whose objects are your business, named one by one. Per-extension rather than one switch: owning your citext domains should not mean taking PostGIS's tables with them.

include_extension_objects and extensions.allow have no effect on MySQL, and that is a fact about the engine rather than a gap: a MySQL plugin is server code and owns no catalog objects, so there is no ownership edge to filter on.

Reading the result

A reading reports its own completeness. complete means nothing in scope went unread; partial means something did, and the skips say what. Two things deliberately do not make a reading partial:

• A deliberate exclusion — an extension's objects, a schema you scoped out. Nothing went unread; a scope was chosen.

• A comprehension limit — an index SQLens read completely but cannot compare, such as a partial index or one over an expression. The object is in the snapshot; what it cannot support is a rule reasoning about it, which is a question about the rule. Those are ordinary enough that counting them would make partial mean nothing.

  The skip for a partial index says what kind of condition went uncompared where it can read one — an inequality, a boolean test, a fixed set, a NULL test — and prints the predicate verbatim where it cannot. The second form is not a lesser answer by accident: it tells you the predicate was read and its shape was not recognized, which is a different fact from a shape that was.