The least-privilege audit role

SQLens reads. It never writes, never takes a lock of its own, and never needs a superuser. This page is the role to give it — and, at the same length, the list of checks that go quiet when you give it the smaller version.

Every statement here was run against a real PostgreSQL 18.4 and a real MySQL 8.4.10 before it was written down. Where a grant turned out not to be enough, that is said rather than smoothed over.

What SQLens never requires

• No superuser. Not on either engine, not for any suite.
• No write access. The debt ledger is a file in your repository, not a table. Nothing this package runs issues INSERT, UPDATE, DELETE or DDL against your database.
• No locks of its own. Readings are catalog and state views. A run bounds its own session timeouts so it cannot become the thing that blocks a deploy.

The one exception is explicit and opt-in: the shadow-database modes create a throwaway database and tear it down again. Those are named modes behind a production guard, and the audit role below does not need the rights for them.

PostgreSQL

CREATE ROLE sqlens_audit LOGIN PASSWORD 'change-me';

GRANT pg_monitor TO sqlens_audit;
GRANT CONNECT ON DATABASE your_database TO sqlens_audit;
GRANT USAGE ON SCHEMA public TO sqlens_audit;

pg_monitor is a predefined role. It carries pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, which is what the server settings, activity and index-usage readings need.

Three areas stay closed with that alone, measured under exactly this role:

Catalog	What goes quiet	How the run says so
pg_authid	password verifiers — the deprecated-hash and no-password rules	SEC.SKIPPED.PG.AUTHID
pg_hba_file_rules	every host-based authentication rule	SEC.SKIPPED.PG.HBA
pg_statistic	table size and row estimates a deploy check reasons about	AUDIT.CATALOG.UNREAD.INSUFFICIENT_PRIVILEGE

Each arrives as a named skip with SQLSTATE 42501 in the message. A skip is not a pass — the run tells you it did not look, which is the whole reason those ids exist.

Opening the three, one at a time

-- password verifiers: SEC.AUTH.ROLE_DEPRECATED_PASSWORD_HASH and its siblings
GRANT SELECT ON pg_authid TO sqlens_audit;

-- host-based authentication: the SEC.AUTH.HBA_* family.
-- BOTH lines are needed. The view is a wrapper around a function, and granting the view alone
-- fails with `permission denied for function pg_hba_file_rules` — measured, not guessed.
GRANT SELECT ON pg_hba_file_rules TO sqlens_audit;
GRANT EXECUTE ON FUNCTION pg_hba_file_rules() TO sqlens_audit;

-- table statistics, for the deploy checks that reason about size
GRANT SELECT ON pg_statistic TO sqlens_audit;

With all four blocks applied, a security run over this database produces no privilege-related skip at all — verified by running it and counting.

pg_read_all_data is the shortcut, and it is the wrong one. It does open pg_authid and pg_statistic in one line — and it also grants SELECT on every table in every schema. An auditing role that can read all your rows is not a least-privilege role; it is the thing this page exists to avoid. It does not open pg_hba_file_rules either, so it is not even a complete shortcut.

On a managed database

Amazon RDS, Cloud SQL, Neon and their relatives withhold pg_authid and pg_hba_file_rules from every role, including the one they call your superuser. That is their design and there is nothing to configure. The run reports it as the two named skips above, and the rest of the suite is unaffected — which is exactly what the three-valued model is for.

MySQL

CREATE USER 'sqlens_audit'@'%' IDENTIFIED BY 'change-me';

GRANT SELECT, SHOW VIEW ON your_database.* TO 'sqlens_audit'@'%';
GRANT PROCESS ON *.* TO 'sqlens_audit'@'%';
GRANT SHOW_ROUTINE ON *.* TO 'sqlens_audit'@'%';
GRANT SELECT ON mysql.* TO 'sqlens_audit'@'%';

PROCESS is what performance_schema and the server-variable readings need. SHOW_ROUTINE is what lets information_schema.ROUTINES report a routine body rather than blanking it.

The last line is the one people leave out, and it costs the most. Without SELECT on mysql.*, every account and grant catalog answers ERROR 1142 and the run reports:

Skip	Catalog
SEC.SKIPPED.MY.USER	mysql.user
SEC.SKIPPED.MY.GRANTS	mysql.db
SEC.SKIPPED.MY.ROLE_EDGES	mysql.role_edges

…plus SEC.AUTH.ROLE_NO_PASSWORD, SEC.AUTH.ROLE_DEPRECATED_PASSWORD_HASH, their locked variants and SEC.CFG.LOCAL_INFILE_FILE_GRANT, each as undetermined with missing_privilege. That is the whole account half of the security suite.

With the four lines above, a run produces no privilege-related skip — verified the same way.

Pointing SQLens at the role

// config/sqlens.php
'security' => [
    'audit_connection' => 'sqlens_audit',
],

The name must exist in config/database.php. A name nothing defines is a misconfiguration, not a fall back to the default connection — falling back would examine the wrong instance and report it as clean. --connection on the command line wins over the config, because a run somebody typed is a decision about that run.

The two-connection split

The audit role above is about what SQLens may read. This is about what your application may do, and it is the single most effective measure the suite can check for.

When the connection that serves requests is the same role that runs migrations, every SQL injection that reaches the database reaches it holding DDL rights. A leaked row and a dropped table are very different incidents.

// config/database.php
'connections' => [
    'pgsql' => [
        // The runtime connection: SELECT, INSERT, UPDATE, DELETE. No CREATE, no ALTER, no DROP.
        'username' => env('DB_USERNAME', 'app_runtime'),
        // …
    ],

    'pgsql_migrations' => [
        // Same server, same database, a role that owns the schema.
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE'),
        'username' => env('DB_MIGRATION_USERNAME', 'app_migrations'),
        'password' => env('DB_MIGRATION_PASSWORD'),
    ],
],

// config/sqlens.php
'security' => [
    'runtime_connection' => 'pgsql',
    'migration_connection' => 'pgsql_migrations',
],

Leaving both null is a real answer rather than a missing one: the suite then reports the state as a finding instead of guessing which of your connections was meant to be the safe one. What it will not do is stay silent — SEC.PRIV.CONNECTIONS_UNSEPARATED says it could not tell, with the reason.

What a skip means, and why it is not an error

Every reading in this package answers with one of three values: it looked and found nothing wrong, it looked and found something, or it could not look. The third is a SEC.SKIPPED.* finding carrying the catalog it could not cover and the SQLSTATE the server answered with.

A skip does not fail a run by default and it is not a defect in your setup. It is the package refusing to report an unexamined area as a clean one. Give the role more, or accept the gap knowingly — both are decisions. Not being told is not.