Add views support on postgresql_grant

[toadjaune]

Terraform Version

Terraform v1.2.6
on darwin_arm64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.17.1

Affected Resource(s)

• postgresql_grant

Terraform Configuration Files

# This resource ignores views
resource "postgresql_grant" "all" {
  database    = "mydb"
  role        = "myrole"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

# This one grants permissions on the listed views
resource "postgresql_grant" "view" {
  database    = "mydb"
  role        = "myrole"
  schema      = "public"
  object_type = "table"
  objects     = ["my_view"]
  privileges  = ["SELECT"]
}

Debug Output

Not really useful, I can provide it if wanted

Panic Output

None

Expected Behavior

There should be a way to affect all views as well.

Actual Behavior

Granting permissions on views requires explicitly enumerating them.

Steps to Reproduce

• create a database with at least a table and a view
• try to grant a permission on "all views" with postgresql_grant

Important Factoids

Nothing specific as far as I can tell.

References

None

Design evolution ideas

The provider kind of works as expected, we're on an edge case here.

I can think of several ways to fix this :

1. When using postgresql_grant with object_type = "table" and an empty objects list, apply the privileges to views as well.
2. Add a view object_type, to be able to specify all tables and all views with 2 resources.

Option 1 has my preference, but it's a breaking change (as existing configurations would suddenly grant more privileges), therefore likely requiring a major version release.

I'm also not familiar enough with postgresql's permission system to know if this design is enough. My current concerns are :

• What happens if you try to give write permissions on a view ? Is it even legal ?
• What permissions are required to refresh materialized views ? Do we need new valid values in permissions ? If so, do they make sense on a normal table ?

[tspearconquest]

According to the postgres 11 docs:

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, functions, and procedures. ALL TABLES also affects views and foreign tables, just like the specific-object GRANT command. ALL FUNCTIONS also affects aggregate functions, but not procedures, again just like the specific-object GRANT command.

Checking the provider code, I can see on resource_postgresql_grant.go#L437 that the code does use ALL TABLES if objects in the terraform code is a blank list, so the code in your first example should be working, however I also was unable to get it to work in my dev postgres instance.

# This resource ignores views as the OP mentioned
resource "postgresql_grant" "all" {
  database    = "mydb"
  role        = "myrole"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

My code:

# This was originally used to deploy a grant to the db for a certain role on a certain schema.table before any views existed in the schema.
# However, we have 2 roles with access to the schema.table in question, one for migrations and one for the service.
# We added the view with the migration role and want to grant privileges to the service role
# Unfortunately, terraform shows no changes are required, and does not update the permissions to grant 
# privileges for the view to the service role.
resource "postgresql_grant" "table_service_permission" {
  role        = var.service
  database    = var.database
  schema      = var.schema
  object_type = "table"
  privileges = [
    "DELETE",
    "INSERT",
    "REFERENCES",
    "SELECT",
    "TRIGGER",
    "TRUNCATE",
    "UPDATE",
  ]
  depends_on = [var.grants_depends_on, postgresql_schema.service_schema]
}

[tspearconquest]

I wonder if this is a bug in how the provider is retrieving the table and views when refreshing the state.

In my case, it seems like if we update a grant which was already created by terraform, the provider is refreshing the table privileges but not seeing the new view, so terraform reflects no changes in the plan.

If the db and view already existed before terraform was ever run, such as OP's description, this should still work though. I'm not clear on why it doesn't.

[NRay7882]

I'm having a similar issue trying to configure Datadog agent integrations for Postgres which requires running grant SELECT ON pg_stat_database to datadog; for the agent DB user.

Currently we have to do this manually with PSQL and we're looking into ways to pass a .SQL script for execution during deployment. While they're not the cleanest approaches, I tried proposing the change first with postgresql_grant but it didn't seem to like any value I provided for
object_type:

resource "postgresql_grant" "ddagent_view" {
  database    = "testdb"
  role        = postgresql_role.ddagent.name
  object_type = "database"
  privileges  = ["SELECT"]
}

Error: SELECT is not an allowed privilege for object type database
with postgresql_grant.ddagent_view
on db.tf line 121, in resource "postgresql_grant" "ddagent_view":
resource "postgresql_grant" "ddagent_view" {

I thought maybe I could add the privs from postgresql_role but couldn't get anywhere with that approach either.

resource "postgresql_role" "ddagent" {
  provider = postgresql

  login     = true
  name      = local.ddagent_db_username
  password  = local.ddagent_db_password
  superuser = false

  roles = [
    "pg_monitor",
    "SELECT ON pg_stat_database"
  ]
}

Error: could not grant role SELECT ON pg_stat_database to datadog: pq: role "SELECT ON pg_stat_database" does not exist
with postgresql_role.ddagent
on db.tf line 92, in resource "postgresql_role" "ddagent":
resource "postgresql_role" "ddagent" {

[tspearconquest]

@NRay7882 pg_stat_database is a built-in view so it probably exists on the postgres database, though a check of the postgres docs does not indicate for certain where it actually exists.

That being said, I've made Terraform update views on a table in an application's database before by just simply updating the table, so it may possible to set your integration configured with Terraform by doing the below. No guarantees this will work though.

resource "postgresql_grant" "ddagent_view" {
  database    = "postgres"
  role        = postgresql_role.ddagent.name
  object_type = "table"
  objects     = "pg_stat_database"
  privileges  = ["SELECT"]
}

[ekeric13]

@NRay7882 I use this module to create the datadog user like you. I am able to access pg_stat_database simply by creating the role:

resource "postgresql_role" "datadog" {
  name     = "datadog"
  login    = true
  password = local.password
  roles    = ["pg_monitor"]
}

If I wanted to be more explicit (haven't tested), I think something like this should work:

resource "postgresql_grant" "debezium_select_pg_stat_database" {
  database    = local.database
  role        = "datadog"
  schema      = "pg_catalog"
  object_type = "table"
  privileges  = ["SELECT"]
  objects     = ["pg_stat_database"]
  depends_on  = [postgresql_role.datadog]
}

You can see information on where the view is within your db:

select * from information_schema.tables where table_name = 'pg_stat_database';

For me pg_stat_database is not in the postgres db nor public schema.

[NRay7882]

@tspearconquest @ekeric13 I appreciate it, as you can see from my examples I lack some experience in working with privileges & roles in Postgres. Targeting the table object_type was something I tried at one point but I don't believe I ever had the objects defined.

This worked for me as ekeric explained, Terraform apply produced a clean output and I verified that the database gets created and the role & table are configured as expected. I can get back to our agent testing now, thank you both for the assist!

[vmahilevskyi]

Faced the same issue while setting up Datadog agent integrations for Postgres version 17.4 and I would like to share the learnings:

1. Granting pg_monitor role to datadog indeed provides access to pg_stat_database view
2. The following Terraform resource configuration explicitly provides a proper access to pg_stat_database view when pg_monitor is not granted to datadog role.

resource "postgresql_grant" "datadog_pg_stat_database" {
  database    = "postgres"
  role        = postgresql_role.datadog[0].name
  object_type = "table"
  objects     = ["pg_stat_database"]
  schema      = "pg_catalog"
  privileges  = ["SELECT"]
}

So my presumption is that it's possible to provide access to Postgresql views using object_type = "table" at least in Postgres 17.4.