Two connections, two roles — the setup that makes an injection cheaper

An application that migrates and serves requests as the same database user gives every SQL injection the power to change the schema. Splitting that into two identities is configuration, not code — and it is the one measure on this site that costs nothing at runtime.

SQLens checks both halves of it. SEC.PRIV.CONNECTIONS_UNSEPARATED reads your configuration and says whether two identities exist at all; SEC.PRIV.RUNTIME_DDL reads the server and says whether the runtime one can still perform DDL. This page is what those two findings ask you to do.

What it buys, stated honestly

An injection that reaches a runtime role holding CREATE can add a table, a function or a trigger — a foothold that outlives the request and the deploy. The same injection against a role holding only DML reads and writes rows.

That is a smaller incident, not a prevented one. Separation does not stop data from leaking through SELECT, and it does not stop rows from being changed through UPDATE or DELETE. It removes one consequence, and it removes it completely. Anyone who tells you it prevents SQL injection is selling something.

The Laravel side

Two connections against the same database, with different credentials:

// config/database.php
'connections' => [

    'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE'),
        'username' => env('DB_USERNAME'),          // the runtime identity
        'password' => env('DB_PASSWORD'),
        // …the rest of your usual settings
    ],

    'pgsql_migrations' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'database' => env('DB_DATABASE'),          // the SAME database
        'username' => env('DB_MIGRATION_USERNAME'),
        'password' => env('DB_MIGRATION_PASSWORD'),
        // …the rest of your usual settings
    ],

],

Tell SQLens which is which, so both rules have something to judge:

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

Then run migrations on the second one. In a deploy script:

php artisan migrate --force --database=pgsql_migrations

A single migration can also pin the connection itself, which is what you want for a migration that must run somewhere else entirely:

class CreateOrdersTable extends Migration
{
    protected $connection = 'pgsql_migrations';
}

note

Both connection entries name the same database. What differs is the user. A second database would be a different setup with different problems; this page is about two identities on one database.

PostgreSQL

The migration role owns the schema. The runtime role connects, reads and writes rows, and owns nothing.

CREATE ROLE app_migrations LOGIN PASSWORD 'change-me';
CREATE ROLE app_runtime    LOGIN PASSWORD 'change-me';

CREATE DATABASE your_database OWNER app_migrations;

\connect your_database

-- PostgreSQL 15 and later already do this; on older servers it is the line that stops every
-- logged-in role from creating objects in `public`.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO app_migrations;

GRANT CONNECT ON DATABASE your_database TO app_runtime;
GRANT USAGE   ON SCHEMA   public        TO app_runtime;

The step everybody forgets

The grants above cover the schema, not the tables in it. Run your migrations now and the runtime role sees nothing — permission denied for table orders on the first request after deploy.

Two statements fix it, and both are needed:

-- The tables that exist right now.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO app_runtime;
GRANT USAGE, SELECT                  ON ALL SEQUENCES IN SCHEMA public TO app_runtime;

-- And every table a future migration creates, without anyone remembering to come back here.
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrations IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrations IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;

ALTER DEFAULT PRIVILEGES is the half that gets skipped, because the setup works without it — right up until the next migration adds a table nobody granted. Note FOR ROLE app_migrations: default privileges attach to the role that creates the object, so naming the wrong one produces a statement that runs cleanly and does nothing.

Measured against PostgreSQL 18.4, in this order:

Step	Result
Migration role creates a table before the defaults are set	runtime: permission denied for table
GRANT … ON ALL TABLES	that table becomes readable
Migration role creates a table after the defaults are set	runtime reads and writes it, no further grant
Runtime tries CREATE TABLE	permission denied for schema public
Runtime tries ALTER TABLE / DROP TABLE	must be owner of table

The last two lines are what SEC.PRIV.RUNTIME_DDL looks for. It follows the right down all three roads it can arrive by — granted directly, granted to a role this role belongs to, or held through owning the tables, which no grant row mentions at all. That is why the migration role owns them and the runtime role does not.

MySQL

CREATE USER 'app_migrations'@'10.0.%' IDENTIFIED BY 'change-me';
CREATE USER 'app_runtime'@'10.0.%'    IDENTIFIED BY 'change-me';

GRANT ALL PRIVILEGES ON your_database.* TO 'app_migrations'@'10.0.%';

GRANT SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'app_runtime'@'10.0.%';

No ALTER DEFAULT PRIVILEGES equivalent is needed: a MySQL grant on your_database.* already covers tables that do not exist yet.

Bind the grantee to a host. 'app_runtime'@'%' accepts the account from anywhere, which is a separate finding of its own (SEC.PRIV.GRANT_WILDCARD_HOST_IN_MIGRATION). Use the network your application actually connects from.

Measured against MySQL 8.4:

Statement as app_runtime	Result
SELECT, INSERT	works
CREATE TABLE	ERROR 1142: CREATE command denied
DROP TABLE	ERROR 1142: DROP command denied
ALTER TABLE	denied

SHOW GRANTS for that account then reads exactly two lines — USAGE ON *.* and the four DML privileges on the one database. Anything more is what the rule reports.

Introducing this into an existing project

The order matters, and the safe one is counter-intuitive: add before you remove.

1. Create the migration identity and give it ownership. On PostgreSQL that means transferring ownership of the existing objects (ALTER TABLE … OWNER TO app_migrations, or REASSIGN OWNED BY old_role TO app_migrations for all of them at once).
2. Point migrations at it — the --database flag in your deploy script, and sqlens.security.migration_connection in the config. Deploy once and confirm migrations still run.
3. Grant the runtime role its DML, including the default privileges above. Deploy again and confirm the application still serves requests.
4. Only now revoke what the runtime role no longer needs. This is the step that can break production, and by this point everything that replaces it is already proven to work.

Doing it in the other order — revoking first — takes the application down and leaves you debugging under pressure. Every step above is reversible on its own.

What is still missing from this page

A third identity, for the read-only account SQLens itself uses. That belongs with the audit role, which documents its grants today; whether the preflight checks need their own profile beyond that is still open. When it is settled, the role table here grows a column rather than the setup changing.