terraform migration: "atlas_schema_revisions" table created in "public" schema

[ubetvince]

Every time I run the migration in my CI, an atlas_schema_revisions table is created in the public schema. It is empty, while the table in the atlas_schema_revisions schema is actually updated. How can I get this table in the public schema to stop being created?

I am using the atlas provider:

terraform {
  required_providers {
    atlas = {
      source  = "ariga/atlas"
      version = "0.4.0"
    }

and this resource:

resource "atlas_migration" "my_postgres" {
  dir     = data.atlas_migration.my_postgres.dir
  version = data.atlas_migration.my_postgres.latest
  url     = data.atlas_migration.my_postgres.url
}

Thanks, Vince

[a8m]

Hey @ubetvince 👋

What database (provider) do you use?

cc @giautm

[ubetvince]

postgres

[a8m]

OK, I just wanted to verify it's not Neon because it does'nt supported the search_path option.

You can pass to the provider the revisions_schema option. See: https://registry.terraform.io/providers/ariga/atlas/latest/docs/resources/migration#revisions_schema

[ubetvince]

Hmm, it still created the (empty) table in the public schema like before. There were no new migrations. Any idea why it would create this empty table yet only fill out the one in the atlas_schema_revisions schema?

[giautm]

Can you please upgrade TF provider to the latest version (v0.8.0) and let me if the issue still exists?

[ubetvince]

I just updated to v0.8.0 (using v0.19.0 for the atlas CLI) and it still created the empty table in public. What other circumstances would this table be created by the provider or CLI?

[giautm]

Can you show us your URL format? it must have the search_path=public in case you work with schema connection.

[ubetvince]

url = format("postgresql://%s:%s@%s:5432/%s?sslmode=require", local.db_secrets.postgres_user, local.db_secrets.postgres_pass, local.db_secrets.postgres_host, local.db_secrets.postgres_db)

It should have search_path=public if I don't want this table created in the publics schema?

[giautm]

What kind of resource do you want to manage? The whole database instance (realm connection) with all its schema. Or single schema's resources?

• If you want to manage the realm, don't put search_path=public anywhere.
• If you want to manage the resources in the public schema add search_path=public to the connection string.

See more about the URL here: https://atlasgo.io/concepts/url

[ubetvince]

There are two schemas in particular I want to manage, so I believe I want a "realm connection". I currently don't set a search_path. If that is the case (a realm connection with no search_path specified), would you expect the provider/atlas-cli to create the atlas_schema_revisions in the public schema?

[giautm]

No. The altlas-cli shouldn't create the atlas_schema_revisions table in the public schema. There is only one case that it does is Neon database.

Can you please share with us more detail about your PostgreSQL instance? Like its version and where you deployed it?

[ubetvince]

I am deploying these changes on a instance from supabase (https://supabase.com), from a github workflow that invokes terraform. Doing a select version gives me PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit.

[giautm]

Hey, I’ll verify and update you soon

[ubetvince]

Hi guys! Any news on this? I was just reminded of this because of my DB linter complaining about that table.