atlas-postgres
PostgreSQL specifics for Atlas: dev database and URL scope (search_path), extensions, roles and grants, default privileges (ALTER DEFAULT PRIVILEGES, default_permission), ownership, row-level security, sequences and serial or identity columns, enums and column type changes, partitions, triggers, concurrent indexes, lock-safe migrations, schema and migration tests, custom lint rules, managed services (RDS, Cloud SQL, Supabase, Neon, Azure), and connection poolers. Use with the atlas skill whenever Atlas manages a PostgreSQL database.
How do I install this agent skill?
npx skills add https://github.com/ariga/atlas --skill atlas-postgresIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill provides comprehensive instructions for managing PostgreSQL databases using Atlas. It covers configuration, security, migrations, and testing. All external references are to the official documentation and trusted service providers.
- Socketpass
No alerts
- Snykwarn
Risk: MEDIUM · 1 issue
What does this agent skill do?
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, default privileges (ALTER DEFAULT PRIVILEGES, default_permission), 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
- Check the login first with
atlas whoami. If it shows no login, ask the user to runatlas 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 containingare available to logged-in users only. A user without an account can start a free trial withatlas login. - 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, nosearch_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.
- Schema scope, a URL with
- 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 adockerblockbaseline. Never pointdevat a real environment's database. - Who creates the objects decides how their privileges are managed (
references/security.md, Default Privileges):- Atlas migrates them, in the declarative or the versioned workflow: declare their grants, and keep
default_permissionsoff.ALTER DEFAULT PRIVILEGESin 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, withoutFOR ROLE. - Another tool creates them, such as the application's migration tool: manage access from an env
that excludes those objects and sets
mode "postgres" { default_permissions = true }. Never enable it in an env that migrates the schema: default privileges only decide what objects created later get.
- Atlas migrates them, in the declarative or the versioned workflow: declare their grants, and keep
- Managing roles needs database scope and
mode { roles = true }. Mark every role Atlas must not change withexternal = true, and create the external roles that grants name in the devbaseline. On a managed service, exclude the provider's admin role (references/security.md, Managed Providers). - Build and drop indexes on populated tables concurrently: the
concurrent_indexdiff policy, or a hand-written file that starts with-- atlas:txmode none. Never writeBEGINorCOMMITin a migration file: Atlas runs each file in a transaction unless it setstxmode none. - Add
CHECKconstraints and foreign keysNOT VALID, then validate them in a separate transaction: atxmode nonefile, or a later file. Use the lock-safeNOT NULLpolicy on large tables. - Apply migrations over a direct connection, never through a connection pooler in transaction mode,
such as Supabase's transaction pooler (port 6543), Neon's
-poolerendpoint, 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. - Some changes need a hand edit to the generated migration (
references/objects.md):- A conversion PostgreSQL cannot make implicitly, such as
texttointegeroruuid, needs aUSINGclause. - A populated column converted to
serialneeds asetval, or inserts fail on duplicate keys. serialtobigserialneedsALTER 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.
- A conversion PostgreSQL cannot make implicitly, such as
- Test every change before reporting it done, as a habit. Run
atlas schema validate, add atest "schema"case for each function, trigger, view, domain, check, or policy you add or change, and atest "migrate"case with seeded rows for each migration that moves or converts data. Run them withatlas schema testandatlas migrate test(references/testing.md). - 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) |
permission denied to change default privileges | The user Atlas connects as needs the privileges of the owner role: GRANT <owner> TO <atlas user> WITH INHERIT TRUE (references/security.md, When another tool creates the objects) |
postgres: managing default permissions requires enabling "default_permissions" in the schema mode | default_permission blocks need mode "postgres" { default_permissions = true }, in an env that manages access only (Rule 4) |
postgres: default_permission blocks are not supported in schema scope, remove search_path from the URLs | Default privileges belong to the database: remove search_path from both url and dev |
A default_permission change is planned again on every run | The block grants to a superuser, a predefined pg_* role, or the owner itself. Atlas does not manage these, so delete the block |
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
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/ariga/atlas/atlas-postgres">View atlas-postgres on skillZs</a>