Open toadjaune opened 1 year ago
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-objectGRANT
command.ALL FUNCTIONS
also affects aggregate functions, but not procedures, again just like the specific-objectGRANT
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]
}
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.
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" {
@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"]
}
@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.
@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!
Terraform Version
Affected Resource(s)
Terraform Configuration Files
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
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
withobject_type = "table"
and an emptyobjects
list, apply the privileges to views as well. 2) Add aview
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 :
permissions
? If so, do they make sense on a normal table ?