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_read_all_data, pg_read_all_settings 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.

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. 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.

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.
• 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.