grant privileges and permissions to a role via terrafrom is not working-CodePudding

according to terraform doc, I created a user and role as follows:

resource "postgresql_role" "ro_role" {
  name     = "ro_role"
}

resource "postgresql_role" "ro_user" {
  name     = "ro_user"
  login    = true
  password = "some sensitive password"
}

which is successfully created. next, when trying to grand permissions to a role, such as for a readonly role adding SELECT only, it does not add any privileges at all. Also when login to Postgres and trying SELECT query, it gives me a permission denied error.

resource "postgresql_grant" "readonly_tables" {
  database    = var.database
  role        = "ro_role"
  schema      = "public"
  object_type = "table"
  objects     = []
  privileges  = ["SELECT"]
}

terraform doc for grant permission: https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs/resources/postgresql_grant

CodePudding user response：

You likely didn't grant yourself USAGE on the public schema. Based on the docs you provided, you'll need to define a resource like:

resource "postgresql_grant" "readonly_tables_schema_usage_public" {
  database    = var.database
  role        = "ro_role"
  schema      = "public"
  object_type = "schema"
  objects     = ["public]
  privileges  = ["USAGE"]
}

the postgresql_role.ro_user resources is also not assinged the ro_role. You will need this for ro_user to actually have the permissions assigned to ro_role:

resource "postgresql_role" "ro_user" {
  name     = "ro_user"
  login    = true
  password = "some sensitive password",
  roles=[postgresql_role.ro_role.role]
}