Testing PostgreSQL Schemas with pgTAP and Atlas

Schema testing ensures your database behaves as expected, verifying that objects, constraints, functions, and permissions are correctly configured. Automating these checks catches regressions to prevent breaking schema changes from reaching production.

This guide covers two tools for the job: pgTAP and Atlas. pgTAP is a native PostgreSQL extension for writing schema assertions in SQL and reporting results in TAP format. Atlas is a schema management tool that runs custom tests on a copy of your schema. The sections below walk through calling pgTAP functions from Atlas schema tests.

For background on TAP, pgTAP, and Atlas, and a detailed comparison of how the two tools differ, see our blog post.

Prefer Atlas rules for schema assertions

Many pgTAP functions check the same properties as Atlas lint rules: whether tables, columns, roles, and permissions conform to a declared policy. For structural and security checks like these, Atlas's custom lint rules are the recommended approach. They evaluate your schema source directly when you run atlas schema lint and do not require the pgTAP extension or a provisioned database.

This guide shows how to call pgTAP functions from Atlas exec blocks when you need TAP-formatted diagnostics or want to verify grants on a live database after the schema is applied.

Atlas Pro Feature

Schema testing, PostgreSQL extensions, and role and permission management are available for Atlas Pro users. To use these features, run:

atlas login

Using pgTAP with Atlas

This example defines a users table, two application roles, and table-level grants declared in the schema. An Atlas test file calls pgTAP's table_privs_are() assertion in exec blocks and matches the TAP output to confirm each role holds the expected privileges.

Set up a project with the following directory structure:

.
├── atlas.hcl
├── schema.pg.hcl   # or schema.sql
└── schema.test.hcl

Schema File

Declare the users table, application roles, and their table permissions:

HCL

schema.pg.hcl

schema "public" {}

table "users" {
  schema = schema.public
  column "id" {
    null = false
    type = serial
  }
  column "email" {
    null = false
    type = text
  }
  primary_key {
    columns = [column.id]
  }
}

role "app_reader" {}

role "app_writer" {}

permission {
  to         = role.app_reader
  for        = table.users
  privileges = [SELECT]
}

permission {
  to         = role.app_writer
  for        = table.users
  privileges = [SELECT, INSERT, UPDATE, DELETE]
}

SQL

schema.sql

CREATE ROLE app_reader NOLOGIN;

CREATE ROLE app_writer NOLOGIN;

CREATE TABLE "public"."users" (
  "id" serial NOT NULL,
  "email" text NOT NULL,
  PRIMARY KEY ("id")
);

GRANT SELECT ON TABLE "public"."users" TO app_reader;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE "public"."users" TO app_writer;

Atlas Test File

Call pgTAP functions in exec blocks after Atlas applies the schema on the dev database. Start with no_plan() so pgTAP does not require a fixed test count, then use match to assert each assertion emits an ok TAP line. A cleanup block revokes the grants afterward so the test does not leave extra privileges behind:

schema.test.hcl

test "schema" "table_privileges" {
  exec {
    sql = "SELECT no_plan();"
  }
  exec {
    sql = <<-SQL
      SELECT table_privs_are(
        'public', 'users', 'app_reader',
        ARRAY['SELECT'],
        'app_reader should be granted read-only on users'
      )
    SQL
    match = "^ok"
  }
  exec {
    sql = <<-SQL
      SELECT table_privs_are(
        'public', 'users', 'app_writer',
        ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
        'app_writer should be granted read-write on users'
      )
    SQL
    match = "^ok"
  }
  cleanup {
    sql = <<-SQL
      REVOKE ALL ON TABLE public.users FROM app_reader;
      REVOKE ALL ON TABLE public.users FROM app_writer;
    SQL
  }
}

Config File

Define the dev database with the pgTAP extension as its baseline, enable role and permission management in the schema mode, and point to your test file:

atlas.hcl

docker "postgres" "dev" {
  image = "postgres:17-pgtap"
  build {
    context = "."
    dockerfile_inline = <<-EOF
      FROM postgres:17
      RUN apt-get update \
          && apt-get install -y postgresql-17-pgtap \
          && rm -rf /var/lib/apt/lists/*
    EOF
  }
  baseline = "CREATE EXTENSION IF NOT EXISTS pgtap;"
}

env "local" {
  dev = docker.postgres.dev.url
  schema {
    src = "file://schema.sql"
    mode {
      roles       = true
      permissions = true
    }
  }
  test {
    schema {
      src = ["schema.test.hcl"]
    }
  }
}

Running Tests

Run the test suite:

atlas schema test --env local

The output should be:

-- PASS: table_privileges (4ms)
PASS

Atlas applies your schema, runs each pgTAP assertion, and checks that the TAP output matches ^ok. When a privilege is missing, pgTAP emits not ok with diagnostics and Atlas marks the test case as failed.

Catching a Regression

Suppose a schema change drops the DELETE privilege from app_writer:

HCL

schema.pg.hcl

permission {
  to         = role.app_writer
  for        = table.users
  privileges = [SELECT, INSERT, UPDATE]
}

SQL

schema.sql

REVOKE DELETE ON TABLE "public"."users" FROM app_writer;

Running the tests again surfaces the mismatch:

-- FAIL: table_privileges (4ms)
    schema.test.hcl:15:
                Error:          no match for `^ok` found in "not ok 2 - app_writer should be granted read-write on users\n# Failed test 2: \"app_writer should be granted read-write on users\"\n#     Missing privileges:\n#         DELETE"
FAIL

pgTAP reports which assertion failed and why. In this case, the DELETE privilege is missing on app_writer. Atlas surfaces the TAP diagnostics in the failure output, so CI blocks the change until the schema and tests agree.

Restore the privilege in the schema:

HCL

schema.pg.hcl

permission {
  to         = role.app_writer
  for        = table.users
  privileges = [SELECT, INSERT, UPDATE, DELETE]
}

SQL

schema.sql

GRANT DELETE ON TABLE "public"."users" TO app_writer;

Re-run the tests and they should pass:

-- PASS: pgtap (5ms)
PASS

Summary

pgTAP and Atlas both help you test PostgreSQL schemas, but they solve different parts of the problem.

pgTAP excels at SQL-native assertions with rich TAP diagnostics. Functions like table_privs_are() make privilege checks concise, and TAP output shows exactly which grant is missing when a test fails.

Atlas excels at tying tests to a declared schema. It provisions isolated environments, orchestrates test cases in HCL, and fits naturally into schema-as-code and CI/CD workflows. For many structural checks, Atlas lint rules are the better fit; pgTAP in schema tests is useful when you want TAP-formatted runtime verification on a provisioned database.

Using this guide, you can verify table-level grants and other pgTAP assertions directly from Atlas schema tests without a separate test runner.

Keep reading about Atlas

Schema Testing

Write and run `.test.hcl` files with `atlas schema test` to validate schema behavior on a dev database.

GitHub Actions

Run schema tests in CI by spinning up a database and applying migrations before your test suite.

Atlas Lint Rules

Define schema policies for roles, permissions, and other objects with Atlas lint rules.