The audit role: what SQLens needs to read, and what it deliberately does not

sqlens:audit reads catalogs. It writes nothing, takes no locks of its own, and never asks for a privilege it does not use. This page is the account to give it — and, just as importantly, what that account cannot see, because a check that cannot run reports undetermined rather than passing.

Every privilege claim below was measured against a running server, not read off a manual. Where the two disagreed, the measurement won.

The short answer

Substitute your own password and database name; everything else runs as printed.

-- PostgreSQL 18
CREATE ROLE sqlens_audit LOGIN PASSWORD 'change-me';
GRANT pg_monitor TO sqlens_audit;
GRANT CONNECT ON DATABASE your_database TO sqlens_audit;

-- MySQL 8.4
CREATE USER 'sqlens_audit'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT ON your_database.* TO 'sqlens_audit'@'%';
GRANT SELECT ON mysql.* TO 'sqlens_audit'@'%';
GRANT SHOW_ROUTINE, PROCESS ON *.* TO 'sqlens_audit'@'%';

Neither account can change anything. Both are enough for the whole audit suite except the host-based authentication rules on PostgreSQL — see below, because that exception is the interesting part.

Which schemas it reads

With sqlens.catalog.schemas left empty, the audit reads the schemas the account's own session resolves. On PostgreSQL that is the search_path as current_schemas(false) answers it, and that answer leaves out every schema the role has no USAGE on. Measured on 18.4: the role above, with its path set to app, resolves no schema at all. So if your tables live outside public, either name the schemas, or give the role USAGE on them:

GRANT USAGE ON SCHEMA app TO sqlens_audit;

A path that names "$user", PostgreSQL's default, is the exception. "$user" stands for the role of each session, so for this account it names a schema called sqlens_audit, not the one your application's role reaches through it, and USAGE does not change that. Measured on 18.0 with "$user", public: the application's role resolved its own schema and public, this account with USAGE on that schema resolved public alone. Name such a schema in sqlens.catalog.schemas. When the account has no schema of its own, the audit reports each schema named after another role that the default left out this way, as excluded_by_config, so the gap shows in the report instead of passing for a clean one.

On MySQL the default is the connection's current database, and a connection configured with an empty database opens without one. In both cases a default that resolves to no schema stops the audit with an error naming what the session resolved and what exists, because a reading of no schema would otherwise report a clean run.

What each PostgreSQL profile can read

Measured on PostgreSQL 18.4:

Catalog	pg_monitor	pg_read_all_data + pg_read_all_settings	superuser
pg_roles — accounts and attributes	✅	✅	✅
pg_authid — stored password verifiers	❌ denied	✅	✅
pg_class.relacl and friends — grants	✅	✅	✅
pg_policy — row-level security	✅	✅	✅
pg_proc — stored routines	✅	✅	✅
restricted server settings	✅	✅	✅
pg_hba_file_rules — who may connect	❌ denied	❌ denied	✅

Two rows are worth reading twice.

pg_authid needs pg_read_all_data. pg_monitor is refused it. That matters because pg_authid is where a password's hash type lives — pg_roles masks it as ******** for every role, superuser included. Without it, SQLens reports the hash type as withheld rather than guessing.

pg_hba_file_rules is refused to both profiles. It is not a view over a table; it is a view over a superuser-only function, and pg_read_all_settings does not open it — has_function_privilege answers f for both profiles.

The six checks a least-privileged account cannot make

Because of that last row, these go dark under either profile:

• SEC.AUTH.HBA_TRUST and SEC.AUTH.HBA_TRUST_LOCAL
• SEC.AUTH.HBA_CLEARTEXT
• SEC.AUTH.HBA_MD5
• SEC.AUTH.HBA_OPEN_CIDR
• SEC.AUTH.HBA_PARSE_ERROR

They do not silently pass. The reading reports SEC.SKIPPED.PG.HBA with the reason, and the run's overall_status is not pass. That is the design: a check that could not run is not a check that found nothing.

If you want those six, grant the function explicitly:

GRANT EXECUTE ON FUNCTION pg_hba_file_rules() TO sqlens_audit;

Measured: the reading returns the rules immediately afterwards. Decide that deliberately — it is a narrow grant, but it is a grant.

MySQL, and the one privilege that is easy to miss

information_schema.ROUTINES narrows silently: it shows a caller only the routines they hold a privilege on, and it returns success either way. A short list and a successful query look identical to a complete one, which is exactly the shape SQLens refuses to report as fine.

So the audit role needs one of three global privileges for that reading to be complete, and SHOW_ROUTINE is the right one — MySQL 8.0 added it for this question:

GRANT SHOW_ROUTINE ON *.* TO 'sqlens_audit'@'%';

Without it, the reading is marked partial and reports SEC.SKIPPED.MY.ROUTINES. SELECT ON *.* also works and is far wider; prefer the narrow one.

SELECT ON mysql.* covers the account and grant tables (mysql.user, mysql.db, mysql.role_edges). Without it you get SEC.SKIPPED.MY.USER, MY.GRANTS and MY.ROLE_EDGES — again named, never silent.

Which connection SQLens reads through

The audit role above needs rights your application role should not have. Point the security readers at it once, instead of passing --connection on every run:

'security' => [
    'audit_connection' => 'sqlens_audit',
],

Null — the shipped state — means the run's own connection.

Three things follow from that setting, and each is deliberate:

• --connection on the command line wins. A run somebody typed is a decision about that run.
• The report names whichever won. On a read/write split a replica answers with different server variables, so which instance was examined is a statement about what the findings mean, not a detail. The SEC.SKIPPED.NOTHING_CHECKED finding names it too — what was not examined needs a subject as much as what was.
• A name config/database.php does not define is refused, not fallen back from, and the run exits 2 — a misconfiguration, not a finding. Falling back would examine your application connection with your application's rights and report the result as though the audit role had been used: real findings, wrong instance.

A connection this package does not support is a different case, and it does not exit 2. Point audit_connection at a sqlite connection and the run reports what it could not examine — a SEC.SKIPPED finding naming the driver — and still exits 0. That is deliberate rather than an oversight: sqlite is the connection a fresh Laravel application ships with, and a hard failure there would stop every installation that tried this package once before configuring it.

Under --profile=ci it already fails, because that profile sets strict_undetermined, and an undetermined finding then decides the exit code. So a pipeline is protected without the default being hostile to a first run. If you want the same strictness locally, set it yourself rather than reading the 0 as a clean audit — a run that examined nothing says so in its findings, and the SEC.SKIPPED.NOTHING_CHECKED line is the one to read.

Two connections, not one

audit_connection above is the connection SQLens reads THROUGH. The two below are connections it reads ABOUT — the separation between the role your application serves requests with and the one that runs migrations is itself something SQLens checks. Tell it which is which:

'security' => [
    'runtime_connection' => 'mysql',
    'migration_connection' => 'mysql_migrations',
],

When the application's runtime role also holds DDL rights, SEC.PRIV.RUNTIME_DDL reports it: a SQL injection that reaches that connection reaches it with the power to change the schema, and a dropped table is a very different incident from a leaked row. It follows the right down all three roads it can arrive by — granted to the role, granted to a role that role is a member of, or held through owning the tables, which no grant row mentions at all.

SEC.PRIV.CONNECTIONS_UNSEPARATED answers the configuration side. It says so when the two names are the same, when only one is set, and when two different names authenticate as the same user against the same host and database — a separation that exists in config/database.php and nowhere else.

Neither rule guesses. A project that has configured neither name gets one undetermined naming the keys, once, rather than a verdict about a question it was never asked; and where the credentials cannot be resolved to compare, the answer is undetermined rather than a pass.

What the audit role is never asked for

• No write privilege of any kind. The reader seals its session read-only and proves it by attempting one write and requiring the refusal. When a missing privilege refuses that write before the seal is asked, the reader reads the account's grants and the session's read-only flag instead, and stops when neither proves the seal.
• No SELECT on your application's tables on PostgreSQL. pg_read_all_data does grant it, which is why the alternative is offered above: on a database where that matters, pg_monitor plus the explicit function grant reads everything except the password hash types.
• No credential value, ever. Password verifiers, HBA options and routine bodies are never selected — not redacted afterwards, but never fetched. There is nothing to leak because there is nothing in memory.