Bug: Postgres Schema is not Being Used for ID Generator

[rellerreller]

The Terraform Postgres workspace plugin requires access to the public schema even when a different schema is provided. The tables for managing state are in one schema while the ID generator for the ID column is created in the public schema. The ID generator should be generated in the schema specified by the Postgres workspace plugin.

This can be seen when creating a new database. Initialize a new empty database and then run terraform init to initialize the database. The terraform_remote_state schema will contain the table but the ID generator for the ID column will be in the public schema. In my environment the Terraform users do not have access to the public schema and the terraform init command fails for lack of privileges.

Terraform Version

Terraform v1.1.7
on darwin_amd64
+ provider registry.terraform.io/hashicorp/aws v4.3.0

Terraform Configuration Files

terraform {
  backend "pg" {
    schema_name = "terraform_remote_state"
  }
}

Debug Output

Output when Terraform does not have privileges to modify the public schema in the database and schema_name is set to "terraform_remote_state".

Initializing the backend...                                                                                                                                                                                                                 
                                                                                                                                                                                                                                           
│ Error: pq: permission denied for schema public                                                                                                                                                                                            
│ 

Otherwise if public schema is allowed then no relevant output is generated.

Expected Behavior

I expect the PG backend to initialize all database objects within the same schema that is specified in Terraform pg configuration item.

Actual Behavior

The ID generator for the ID column is created in the public schema while the table is created in the "terraform_remote_state" schema.

Steps to Reproduce

1. Create a new Postgres database for the PG backend
2. Run terraform init to initialize the schema, tables, and ID generator
3. Login to postgres database and verify that ID generator is in the public schema while the tables are in the terraform_remote_state schema4.

If the database is configured to disallow access to public schema by issuing the command REVOKE CREATE ON SCHEMA public FROM PUBLIC and all users/roles are given privileges only within terraform_remote_state schema then step 2 will fail with permission denied for schema public.

Additional Context

N/A

References

I searched but found none

[crw]

Thanks for the report! (cc: codeowner @remilapeyre).

[remilapeyre]

Hi @rellerreller everything you reported is correct and that behavior was set a very long time ago. The issue now is that fixing it would break backward compatibility.

That ID is used for locking so changing were it is stored would break locks between different Terraform versions.

Because old Terraform versions will look there to lock the workspace moving it in the user provided schema would risk to have someone using a new version of Terraform overwriting changes being simultaneously made by another person using an older version of Terraform.

If I find some way to make this change in a backward compatible fashion I would like to do it but I've not found one yet.

I will try to look for an inventive solution and at least better document this behavior next week.

[nussera]

Hey,

As postgresql is moving away from the public schema (https://www.dbi-services.com/blog/be-careful-with-public-in-postgresql/) a solution to this problem would be great.

Would it be possible to add another configuration parameter to break the backward compatibilty? For example:

terraform {
  backend "pg" {
    schema_name = "terraform_remote_state"
    dont_use_public_schema = true
  }
}

Best regards, Andreas