atlas-postgres

# Atlas for PostgreSQL What is specific to PostgreSQL when Atlas manages the schema. The `atlas` skill covers the workflows and commands; this skill covers the rules, edge cases, and answers that differ on PostgreSQL. PostgreSQL-compatible engines, such as CockroachDB, YugabyteDB, and Aurora DSQL, behave differently in places: follow their guides (https://atlasgo.io/databases). ## Files This skill is `SKILL.md` plus seven reference files under `references/`, each served at `https://atlasgo.io/skills/atlas-postgres/references/<name>`. Read a reference when the task needs it: | File | Read it for | |------|-------------| | `references/dev-database.md` | The dev URL and its scope, `atlas tool docker`, the `docker` block and `baseline`, extensions, RDS, Cloud SQL, Supabase, Neon, Azure, connection URLs | | `references/security.md` | Roles and users, GRANT and REVOKE, `ALTER DEFAULT PRIVILEGES`, ownership, row-level security, provider admin roles | | `references/objects.md` | Sequences, serial and identity columns, enums, domains, column type changes, functions, triggers, views, partitions, indexes, what Atlas does not manage | | `references/hcl.md` | Writing the schema in HCL: the `NOT NULL` default, column types and `sql()`, defaults, references, keys, indexes, table settings, function bodies, seed data, input variables | | `references/migrations.md` | Locks and concurrent indexes, `PG*` lint findings, transactions, connection poolers, lock-safe `NOT NULL`, seed data | | `references/custom-rules.md` | Writing a custom lint rule in the HCL rule language when no analyzer covers a check: predicates, operators, what each PostgreSQL object exposes, examples | | `references/testing.md` | Testing every change: schema tests for functions, triggers, views, domains, and policies, migration tests with seeded rows, row-level security tests, template databases, pgTAP | ## Requires The `atlas` skill, installed next to this one (`../atlas/SKILL.md`). If it is missing, download https://atlasgo.io/skills/atlas/SKILL.md to `../atlas/SKILL.md` and each reference file listed in its Files section to `../atlas/references/<name>`. ## Rules 1. Check the login first with `atlas whoami`. If it shows no login, ask the user to run `atlas login`: logged out, Atlas misses most of a PostgreSQL schema. Inspection silently skips views, functions, triggers, sequences, extensions, partitions, and policies, and a desired state that defines them fails with an error containing `are available to logged-in users only`. A user without an account can start a free trial with `atlas login`. 2. Pick the URL scope from the objects the schema holds: - Schema scope, a URL with `search_path`: Atlas manages that one schema and writes unqualified statements (`users`). Database scope, no `search_path`: Atlas covers every schema and qualifies each statement (`public.users`). - Database-level objects belong to no schema: extensions, event triggers, and foreign servers. A schema-scoped URL never sees them and leaves them out of plans without an error, so a schema that declares them must use database scope. - Schema-level objects work at either scope: tables with their indexes, constraints, triggers, and policies, plus views, functions, procedures, sequences, and types. - Roles and users are cluster-level, shared by every database on the instance. Managing them needs database scope (Rule 5). - The dev URL takes the target's scope. At schema scope, a Docker dev URL uses `search_path=public`, which exists in every new database, whatever schema the target names; a dev database you provide can use any empty schema. 3. The dev database runs the target's major version and extensions: `docker://postgres/<version>/dev`. Create what the schema uses but does not manage, such as extensions, provider roles, or other teams' schemas, in a `docker` block `baseline`. Never point `dev` at a real environment's database. 4. `ALTER DEFAULT PRIVILEGES` in the desired state never appears in a migration: Atlas runs it on the dev database and plans one GRANT per object it covers. Put it before those objects, without `FOR ROLE`. 5. Managing roles needs database scope and `mode { roles = true }`. Mark every role Atlas must not change with `external = true`, and create the external roles that grants name in the dev `baseline`. On a managed service, exclude the provider's admin role (`references/security.md`, Managed Providers). 6. Build and drop indexes on populated tables concurrently: the `concurrent_index` diff policy, or a hand-written file that starts with `-- atlas:txmode none`. Never write `BEGIN` or `COMMIT` in a migration file: Atlas runs each file in a transaction unless it sets `txmode none`. 7. Add `CHECK` constraints and foreign keys `NOT VALID`, then validate them in a separate transaction: a `txmode none` file, or a later file. Use the lock-safe `NOT NULL` policy on large tables. 8. Apply migrations over a direct connection, never through a connection pooler in transaction mode, such as Supabase's transaction pooler (port 6543), Neon's `-pooler` endpoint, or PgBouncer in transaction mode. The advisory lock Atlas holds for the run is per session, and these poolers drop it; Supabase and Neon both document the direct connection for migrations. 9. Some changes need a hand edit to the generated migration (`references/objects.md`): - A conversion PostgreSQL cannot make implicitly, such as `text` to `integer` or `uuid`, needs a `USING` clause. - A populated column converted to `serial` needs a `setval`, or inserts fail on duplicate keys. - `serial` to `bigserial` needs `ALTER SEQUENCE ... AS bigint`, or the sequence still stops at 2147483647. - A new enum value goes in one file and its first use in a later one. Removing or reordering values needs a hand-written migration, and its plan can report no changes. 10. Test every change before reporting it done, as a habit. Run `atlas schema validate`, add a `test "schema"` case for each function, trigger, view, domain, check, or policy you add or change, and a `test "migrate"` case with seeded rows for each migration that moves or converts data. Run them with `atlas schema test` and `atlas migrate test` (`references/testing.md`). 11. When no built-in analyzer covers a check, write a custom lint rule in the HCL rule language, and do the same for a convention the user states, such as row-level security on every table or an index on every foreign key. A rule can check any attribute of any object Atlas inspects, and lint enforces it on every change (`references/custom-rules.md`). ## Quick Answers | Question | Answer | |----------|--------| | A local PostgreSQL database to apply migrations to | `atlas tool docker --url "docker://postgres/17/myapp?search_path=public" --name my-db` prints its URL; `atlas tool docker kill --name my-db` stops it (`references/dev-database.md`) | | `cannot drop type ... because other objects depend on it`, or views and functions missing from a plan | Run `atlas whoami`: logged out, Atlas skips views and functions, including the ones that depend on the type (Rule 1) | | Which URL to give Atlas on Supabase, Neon, or behind PgBouncer | The direct connection, never the transaction-mode pooler (`references/migrations.md`, Connection Poolers) | | `pq: unsupported startup parameter: search_path` | The URL goes through PgBouncer; use the direct connection (`references/migrations.md`, Connection Poolers) | | `CREATE EXTENSION` is missing from the migration | The URLs are schema-scoped; extensions need database scope (`references/dev-database.md`) | | A plan drops the `public` schema | At database scope, the desired state covers every schema: declare `schema "public" {}`, or use schema-scoped URLs (`references/dev-database.md`) | | `cannot diff a schema with a database connection` | The URL and the dev URL have different scopes (`references/dev-database.md`) | | `connected database is not clean` | `dev` points at a database that is not empty (`references/dev-database.md`) | | The same change shows up in every plan | Set a dev URL with the target's scope: Atlas normalizes defaults and expressions on it (`references/dev-database.md`) | | A column became `NOT NULL` without asking for it | HCL columns are `NOT NULL` unless they set `null = true` (`references/hcl.md`) | | `ALTER COLUMN ... TYPE` fails for `text` to `integer` or `uuid` | Add a `USING` clause to the migration file (`references/objects.md`, Column Changes) | | Inserts fail with a duplicate key after a column became `serial` | Move the sequence with `setval` (`references/objects.md`) | | An enum value was removed, but the diff reports no changes | Removing enum values is not planned: write the change by hand (`references/objects.md`, Enums) | | `unsafe use of new value` | Add the enum value and its first use in separate migration files (`references/objects.md`, Enums) | | `pq: type "geometry" does not exist` | Create the extension in the schema or the dev `baseline`; the PostGIS image alone does not install it (`references/dev-database.md`) | | Lint reports `PG101` or `PG103` on an index | Use the `concurrent_index` policy or `atlas:txmode none` (`references/migrations.md`) | | A convention must hold on every table, such as row-level security or foreign-key indexes | Write a custom lint rule (`references/custom-rules.md`) | | A row-level security test passes whatever the policy says | The dev database connects as a superuser, which bypasses policies; test as a non-privileged role (`references/testing.md`) | | `pq: unexpected transaction status idle` | Remove `BEGIN`/`COMMIT` from the file: Atlas already runs it in a transaction (`references/migrations.md`) | | `ambiguous revision table` after changing the URL's scope | Set `--revisions-schema` to the schema that holds the existing table (`references/migrations.md`) | | `DROP and CREATE is required` on a partitioned table | Changing a partition key, or partitioning an existing table, needs a hand-written migration (`references/objects.md`) | | `ERROR: out of shared memory` on the dev database | Raise `max_locks_per_transaction` above 128 in the `docker` block (`references/dev-database.md`) | | An event trigger is missing from the migration | Use a database-scoped dev URL (`references/objects.md`) | | Every plan revokes from `postgres` (Cloud SQL), the RDS master user, or another provider admin | Plan through a dev database where the admin is an ordinary role (`references/security.md`, Managed Providers) | | A new role needs `SELECT` on thousands of tables | Make it a member of `pg_read_all_data` instead of granting per table (`references/security.md`) | | Objects on the dev database belong to `postgres`, but production has other owners | Owners are not part of the desired state (`references/security.md`, Ownership) | | The migration has no `ALTER DEFAULT PRIVILEGES` | Expected: Atlas plans the GRANTs it implies, per object (`references/security.md`) | | A grant on a sequence turned a `bigserial` column into `bigint` | Expected: the sequence is now managed explicitly (`references/objects.md`) | ## Documentation - PostgreSQL HCL reference: https://atlasgo.io/hcl/postgres - Dev database: https://atlasgo.io/concepts/dev-database - Security: https://atlasgo.io/guides/postgres/security-declarative, https://atlasgo.io/guides/postgres/security-default-privileges - Lock-safe changes: https://atlasgo.io/guides/lock-safe-not-null, https://atlasgo.io/lint/analyzers - Guides for each database: https://atlasgo.io/databases