Restrict access to 1 user = 1 database

[ggarson]

Hello community,

I tried lot of things with that provider but impossible to achieve what I want. I have an AWS RDS shared database between several applications. I created 1 user per database and I want to restrict access to only one database for each user.
I tried to :

• set allow_connections = false during base creation and after add CONNECT grant per user per database but it doesn't work. AWS give a normal user (not superuser) to manage the database so I cannot connect to the DB and change grants
• revoke CONNECT to all pairs of users and databases not wanted without success

resource "postgresql_grant" "revoke_connect_to_db" {
  for_each = local.revoke_users

  database    = each.value["dbname"]
  role        = each.value["username"]
  object_type = "database"
  privileges  = []

  depends_on = [
    postgresql_database.db,
    postgresql_role.role
  ]
}

• revoke CONNECT from template1 from public role. It doesn't work

resource "postgresql_grant" "revoke_connect_to_db" {
  count = var.enabled ? 1 : 0

  database    = "template1"
  role        = "public"
  object_type = "database"
  privileges  = []
}

Thanks for your help

[ggarson]

I found a way to do it, even if it is not perfect. The drawback is that it leads to a big state...

resource "postgresql_database" "db" {
  for_each = var.enabled ? toset([for db in var.db_names : db if db != var.master_db]) : []
  name     = each.key
  template = "template1"
}

resource "postgresql_role" "role" {
  for_each = local.credentials

  name     = each.value["username"]
  password = each.value["password"]
  login    = true

  lifecycle {
    ignore_changes = [
      password
    ]
  }

  depends_on = [
    postgresql_grant.revoke_connect_to_all_db,
    postgresql_grant.revoke_all_to_all_db
  ]
}

# Shared Database Hosting https://wiki.postgresql.org/wiki/Shared_Database_Hosting
# REVOKE DB CONNEXION FROM ALL NEW USERS
resource "postgresql_grant" "revoke_connect_to_all_db" {
  for_each = var.enabled ? toset(var.db_names) : []

  database    = each.value
  role        = "public"
  object_type = "database"
  privileges  = []

  depends_on = [
    postgresql_database.db
  ]
}

# REVOKE ALL FROM ALL NEW USERS
resource "postgresql_grant" "revoke_all_to_all_db" {
  for_each = var.enabled ? toset(var.db_names) : []

  database    = each.value
  role        = "public"
  schema      = "public"
  object_type = "table"
  privileges  = []

  depends_on = [
    postgresql_database.db
  ]
}

# GRANT DB CONNEXION ONLY TO THE USER DB
resource "postgresql_grant" "connect_to_db" {
  for_each = local.credentials

  database    = each.value["dbname"]
  role        = each.value["username"]
  object_type = "database"
  privileges  = ["CONNECT", "TEMPORARY"]

  depends_on = [
    postgresql_database.db,
    postgresql_role.role
  ]
}

# GRANT ALL WITHOUT TRUNCATE IN SCHEMA public ON ALL TABLES
resource "postgresql_grant" "all_on_public" {
  for_each = local.credentials

  database    = each.value["dbname"]
  role        = each.value["username"]
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE", "REFERENCES", "TRIGGER"]

  depends_on = [
    postgresql_database.db,
    postgresql_role.role
  ]
}

# ALTER DEFAULT PRIVILEGES ALL WITHOUT TRUNCATE IN SCHEMA public ON ALL TABLES
resource "postgresql_default_privileges" "default_all_on_public" {
  for_each = local.credentials

  database    = each.value["dbname"]
  role        = each.value["username"]
  schema      = "public"
  owner       = var.master_username
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE", "REFERENCES", "TRIGGER"]

  depends_on = [
    postgresql_database.db,
    postgresql_role.role
  ]
}

# GRANT USAGE ON SCHEMA pg_catalog
resource "postgresql_grant" "usage_on_pg_catalog" {
  for_each = local.credentials

  database    = each.value["dbname"]
  role        = each.value["username"]
  schema      = "public"
  object_type = "schema"
  privileges  = ["USAGE"]

  depends_on = [
    postgresql_database.db,
    postgresql_role.role
  ]
}

[ggarson]

If you have another way to do it, you're welcome because it is not scalable at all.