Scoping Test Database Roles for PostGIS Suites

8 min read

Most PostGIS test suites connect as a superuser, and most of them do it for one reason: somewhere in the setup path, something needed CREATE EXTENSION postgis. That single requirement propagates outward until the whole suite runs with unlimited authority, and from then on nothing the suite does can be distinguished from anything else it could have done. This guide sits beneath security boundaries in spatial QA and covers giving the suite the narrowest role that still lets it run.

The goal is not to make the test database secure in the sense a production database is secure — a throwaway container holding synthetic geometry does not need that. The goal is that the role the suite runs under is a statement of what the suite is allowed to touch, so that a test which starts touching something else fails loudly instead of silently succeeding.

Root cause: extension creation is conflated with test execution

CREATE EXTENSION postgis requires elevated privileges because it loads a shared library and registers C functions, types, and operators. It is a one-time act of provisioning. Running the test suite is a repeated act of reading and writing rows. These two things have nothing in common except that the first must happen before the second, and the standard fixture pattern welds them together:

# The pattern that forces superuser on the whole suite
@pytest.fixture(scope="session")
def db():
    conn = psycopg.connect("postgresql://postgres@localhost/test")  # superuser
    conn.execute("CREATE EXTENSION IF NOT EXISTS postgis")
    ...

Because the fixture is session-scoped and returns the connection, every test in the suite inherits the superuser connection that only the first line needed. The fix is to move extension creation into image build or container entrypoint — where it happens once, at provisioning time — and let the suite connect as an ordinary role that finds PostGIS already present.

Conflated versus separated database privilege for a PostGIS suite Two arrangements are compared. In the first, a single superuser connection carries out three distinct jobs — creating the PostGIS extension, applying schema migrations, and executing every test — so the authority required by the first job is inherited by all the others and every test runs unlimited. In the second, the three jobs are separated: extension creation happens once when the container image is built, under a provisioning role that the suite never uses; schema migration runs under an owner role invoked by the migration step alone; and the test suite itself connects as a narrow role holding only select, insert, update and delete on the test schema, with no privilege to create extensions or drop schemas. CONFLATED one superuser connection unlimited authority CREATE EXTENSION postgis apply schema migrations run every test in the suite a test that drops the wrong schema succeeds a test that reads another database succeeds nothing distinguishes intent from capability SEPARATED provisioning role image build, once owner role migration step only test role SELECT / INSERT UPDATE / DELETE on the test schema only extension creation is refused — and never attempted a stray DROP SCHEMA raises instead of destroying the grant list is the statement of what tests may touch

The three roles

A PostGIS test environment needs exactly three roles, and the discipline is that no step ever uses a broader one than its job requires.

Role Used by Needs Must not have
provisioner Container image build / entrypoint CREATE EXTENSION, CREATE SCHEMA Any role the suite can authenticate as
gis_owner The migration step in CI CREATE/ALTER/DROP on the test schema SUPERUSER, access to other databases
gis_test The pytest suite itself SELECT, INSERT, UPDATE, DELETE, USAGE on the test schema CREATE, DROP, extension privileges

The separation that matters most is the last one. gis_owner and gis_test differ by exactly the privileges that let a test permanently change the shape of the database, and that difference is what turns an accidental DROP TABLE in a fixture teardown from a silent data loss into a raised exception.

Step-by-step implementation

Step 1 — Provision the extension outside the suite

Put the extension into the image, not the fixture. For a container-based test database, the official PostGIS images already do this, but any custom image should be explicit:

-- init/01-extensions.sql, executed by the entrypoint as the provisioning role
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE SCHEMA IF NOT EXISTS gis_test;

Because this runs at container start, every test run finds PostGIS present and never needs the privilege to install it. This also makes the pinning of GEOS, PROJ, and GDAL versions a property of the image rather than of whatever the fixture happened to install.

Step 2 — Create the two application roles with explicit grants

-- init/02-roles.sql
CREATE ROLE gis_owner LOGIN PASSWORD :'owner_pw';
CREATE ROLE gis_test  LOGIN PASSWORD :'test_pw';

-- The owner may reshape the schema.
GRANT ALL ON SCHEMA gis_test TO gis_owner;

-- The test role may use it and touch rows, nothing more.
GRANT USAGE ON SCHEMA gis_test TO gis_test;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA gis_test TO gis_test;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA gis_test TO gis_test;

-- Grants must also apply to tables the migration creates later.
ALTER DEFAULT PRIVILEGES FOR ROLE gis_owner IN SCHEMA gis_test
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO gis_test;
ALTER DEFAULT PRIVILEGES FOR ROLE gis_owner IN SCHEMA gis_test
  GRANT USAGE, SELECT ON SEQUENCES TO gis_test;

-- PostGIS keeps spatial_ref_sys in public; the test role only reads it.
GRANT USAGE ON SCHEMA public TO gis_test;
GRANT SELECT ON public.spatial_ref_sys TO gis_test;

The ALTER DEFAULT PRIVILEGES pair is the line most setups omit, and its absence produces the most confusing symptom in this whole area: the suite passes on a freshly seeded database and fails with permission denied after the next migration adds a table. Default privileges are scoped to the role that creates the object, which is why the FOR ROLE gis_owner clause is not optional.

Default privileges and tables created after the grant Two timelines are compared. In the first, a GRANT ON ALL TABLES statement is issued at setup time and covers only the tables that exist at that instant; a later migration creates an additional table, which carries no grant, so the test role is denied access to it and the suite fails after having passed on a freshly seeded database. In the second timeline, ALTER DEFAULT PRIVILEGES FOR ROLE gis_owner is issued alongside the initial grant, so every table the owner subsequently creates carries the same grant automatically and later migrations leave the suite working. GRANT ON ALL TABLES ONLY grant issued 2 tables covered migration adds a table 3rd table has no grant suite runs permission denied breaks PLUS ALTER DEFAULT PRIVILEGES FOR ROLE gis_owner grant + default rule 2 tables covered migration adds a table grant applied on creation suite runs all tables readable holds

Step 3 — Point the suite at the narrow role

The fixture now does nothing privileged. It connects, and that is all:

import os
import pytest
import psycopg

@pytest.fixture(scope="session")
def dsn() -> str:
    # Fails loudly if CI forgot to supply the narrow role.
    return os.environ["GIS_TEST_DSN"]      # postgresql://gis_test@db/testdb

@pytest.fixture
def conn(dsn):
    with psycopg.connect(dsn) as c:
        yield c
        c.rollback()                        # tests never commit

Reading the DSN with os.environ[...] rather than os.environ.get(...) matters more than it looks: a default value is how a suite silently falls back to a superuser DSN on a developer machine and then behaves differently in CI. The narrow role should be the only way the suite can run anywhere.

Step 4 — Assert the boundary with a permission test

A privilege boundary that is never exercised drifts. One test, run in the same suite, proves the role is still narrow:

import pytest
import psycopg

FORBIDDEN = [
    "CREATE EXTENSION IF NOT EXISTS hstore",
    "CREATE TABLE gis_test.should_not_exist (id int)",
    "DROP TABLE IF EXISTS gis_test.parcels",
]

@pytest.mark.parametrize("stmt", FORBIDDEN)
def test_test_role_cannot_reshape_the_database(conn, stmt):
    with pytest.raises(psycopg.errors.InsufficientPrivilege):
        conn.execute(stmt)
    conn.rollback()


def test_test_role_is_not_superuser(conn):
    row = conn.execute(
        "SELECT rolsuper FROM pg_roles WHERE rolname = current_user"
    ).fetchone()
    assert row is not None and row[0] is False

The DROP TABLE IF EXISTS case is worth including even though it looks like it would be harmless: IF EXISTS suppresses the missing table error, not the insufficient privilege one, so the assertion still holds and the test documents that teardown code cannot destroy the schema by accident.

Run these first in the session. If the role has been widened — someone swapped the DSN while debugging, or a new CI job reused the migration credentials — the suite reports it as a failure with a clear name rather than passing while running with more authority than intended. That is the same principle applied to the CRS validation gates: a property you rely on should be asserted, not assumed.

Where the migration role fits in CI

The migration step and the test step are separate jobs with separate credentials. In a GitHub Actions workflow the shape is:

    steps:
      - uses: actions/checkout@v4

      - name: Apply migrations
        env:
          PGURI: postgresql://gis_owner:$@localhost/testdb
        run: alembic upgrade head

      - name: Run spatial suite
        env:
          GIS_TEST_DSN: postgresql://gis_test:$@localhost/testdb
        run: pytest -q

Two environment variables, two roles, and no step that has both. The owner password is never present in the process that runs test code, which means a dependency with a compromised release cannot read it out of the environment during a test run — a small boundary, but a real one, and free once the roles exist.

One credential per CI step A CI workflow is drawn as two sequential steps drawing from separate secrets. The migration step is given only the owner connection string and uses it to apply schema changes. The test step is given only the narrow test connection string and uses it to run pytest. A dashed boundary between them shows that neither step holds both credentials, so the owner password is never present in the environment of the process that executes test code, and a compromised dependency running inside the suite cannot read it. secret: OWNER_PW migration credential secret: TEST_PW narrow credential step: apply migrations alembic upgrade head step: run spatial suite pytest -q holds DDL authority for the length of one step runs no project code beyond the migration tool runs all project and dependency code owner password absent from its environment

Failure modes and edge cases

Templates carry privileges, and CREATE DATABASE ... TEMPLATE copies them. Suites that create a fresh database per worker — the usual arrangement when parallelising with pytest-xdist — need the template’s grants to be correct, because each copy inherits them. Fix the template once; do not re-grant per worker.

Creating databases is itself a privilege. If workers create their own databases, gis_test needs CREATEDB, which widens it. The narrower arrangement is a fixed pool of pre-created databases named testdb_gw0 through testdb_gwN, provisioned at image build, with the worker selecting one by index. The suite then never creates anything.

spatial_ref_sys is writable by default in some setups. A test that inserts a custom projection into it is mutating shared state that other workers read. Grant only SELECT on it and keep custom CRS definitions in fixtures instead — the CRS round-trip guidance assumes the table is the same for every worker.

Row-level security is not tested by a narrow role. Scoping the role limits what the suite can do; it says nothing about whether the policies your application relies on work. Those need their own tests running as the roles the application uses, which is a separate concern from this one and should not be folded into it.

A superuser fallback in local development defeats the whole arrangement. If developers run against a permissive local database while CI runs narrow, the permission test in Step 4 will fail locally and get marked skip, at which point the boundary exists only in CI and drifts silently. Run the same roles everywhere; the container init scripts make that nearly free.

Conclusion

The narrow test role costs three SQL files and one test, and what it buys is that the suite’s capabilities are written down somewhere a reviewer can read them. Provision the extension at image build, keep migrations under an owner role invoked by a separate CI step, run tests under a role that can only touch rows, and assert that role’s limits inside the suite so the boundary cannot quietly widen.