User mapping creation fails with "_pg_user_mappings" permission error on Google Cloud SQL

[apolienko]

User mapping creation fails with "_pg_user_mappings" permission error on Google Cloud SQL

Bug Description

The postgresql_user_mapping resource fails to read user mappings after creation on Google Cloud SQL with the error:

Error: Error reading user mapping: pq: permission denied for view _pg_user_mappings

This occurs even when the user has sufficient privileges and can manually access the view.

Environment

• Provider Version: cyrilgdn/postgresql v1.22.0
• PostgreSQL Version: PostgreSQL 17 (Google Cloud SQL)
• Platform: Google Cloud SQL for PostgreSQL (managed service)

Configuration

# Provider configuration
provider "postgresql" {
  alias     = "app_db"
  host      = var.db_host
  port      = 5432
  username  = "app_admin"  # Database admin user
  password  = var.db_password
  database  = "app_database"
  sslmode   = "disable"
}

# User mapping resource
resource "postgresql_user_mapping" "user_mappings" {
  provider = postgresql.app_db
  
  for_each = var.user_server_mappings

  server_name = each.value.server_name
  user_name   = each.value.pg_username
  
  options = {
    user     = each.value.pg_username
    password = each.value.password
  }
}

Steps to Reproduce

1. Set up Google Cloud SQL PostgreSQL instance
2. Create database admin user with cloudsqlsuperuser role
3. Create foreign data wrapper and foreign servers
4. Configure terraform with postgresql_user_mapping resource
5. Run terraform apply

Expected Behavior

User mappings should be created successfully and terraform should be able to read them for state management.

Actual Behavior

User mappings are created successfully (visible in database), but terraform fails during the read operation with:

Error: Error reading user mapping: pq: permission denied for view _pg_user_mappings

Additional Context

Database Investigation

1. View exists and is accessible:

-- This works when executed manually
SELECT * FROM _pg_user_mappings;

2. View exists in multiple schemas:

SELECT schemaname, viewname, viewowner 
FROM pg_views 
WHERE viewname = '_pg_user_mappings';

-- Results:
-- information_schema | _pg_user_mappings | cloudsqladmin
-- public             | _pg_user_mappings | app_admin

3. User has necessary permissions:

-- User can access system tables
SELECT * FROM pg_user_mappings;  -- Works
SELECT * FROM pg_foreign_server; -- Works

-- User can access the view manually
SELECT * FROM _pg_user_mappings; -- Works in psql

Workaround Attempted

Created view manually with explicit permissions:

CREATE OR REPLACE VIEW public._pg_user_mappings AS 
SELECT * FROM pg_user_mappings;

GRANT SELECT ON public._pg_user_mappings TO app_admin;

The view is accessible manually, but terraform provider still reports permission denied.

Terraform Debug Output

postgresql_user_mapping.user_mappings["user1_server1"]: Creating...
postgresql_user_mapping.user_mappings["user1_server1"]: Creation complete
postgresql_user_mapping.user_mappings["user1_server1"]: Refreshing state...

Error: Error reading user mapping: pq: permission denied for view _pg_user_mappings

Request

Could you please investigate:

1. Whether this is a known issue with Google Cloud SQL compatibility
2. If there are additional permission requirements for the _pg_user_mappings view access
3. Whether the provider could implement fallback mechanisms for managed PostgreSQL services

[smelchior]

We are running into the same issue on our AWS RDS instance recently updated to PSQL 17. In the PSQL logs this looks like this:

ERROR: permission denied for view _pg_user_mappings
STATEMENT: SELECT umoptions FROM information_schema._pg_user_mappings WHERE authorization_identifier = $1 and foreign_server_name = $2
ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: SELECT umoptions FROM pg_user_mappings WHERE usename = $1 and srvname = $2

so it would like to execute the backup query for fetching the users, but as the transaction is closed, this does not work.
A workaround is to assign the rds_superuser role to the user performing the operations in the DB, but that is not ideal.