Last active
December 16, 2020 19:55
-
-
Save norberttech/71eca9b0cb514021f591a214d6d13335 to your computer and use it in GitHub Desktop.
PostgreSQL, Terraform - Grant SELECT to a ROLE for single database table in given schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## Make sure that on the host machine that executes terraform apply there is a psql client available in $PATH | |
resource "postgresql_role" "user" { | |
name = "some-user" | |
login = false | |
skip_reassign_owned = true | |
depends_on = [data.azurerm_postgresql_server.server] | |
} | |
resource "postgresql_grant" "user-grant-connect" { | |
database = azurerm_postgresql_database.db.name | |
role = postgresql_role.userr.name | |
schema = "public" | |
object_type = "database" | |
privileges = ["CONNECT"] | |
depends_on = [postgresql_role.user] | |
} | |
resource "null_resource" "user-grant-select" { | |
# Change to 0 before removing resource from the code, then run terraform apply, then remove resource from the code | |
# In other case the on destroy local-exec provision will not be executed and you wont be able to remove the role becaose of | |
# existing dependencies | |
count = 1 | |
depends_on = [postgresql_grant.user-grant-connect] | |
triggers = { | |
user = postgresql_role.user.name | |
pguser = "${var.postgresql_admin_login}@${data.azurerm_postgresql_server.server.name}" | |
pgpassword = var.postgresql_admin_password | |
pghost = data.azurerm_postgresql_server.server.fqdn | |
pgdatabase = azurerm_postgresql_database.db.name | |
} | |
provisioner "local-exec" { | |
command = "psql -c 'GRANT SELECT ON public.table_name TO \"${postgresql_role.user.name}\";'" | |
environment = { | |
PGPORT = 5432 | |
PGUSER = "${var.postgresql_admin_login}@${data.azurerm_postgresql_server.server.name}" | |
PGPASSWORD = var.postgresql_admin_password | |
PGHOST = data.azurerm_postgresql_server.server.fqdn | |
PGDATABASE = azurerm_postgresql_database.db.name | |
} | |
} | |
provisioner "local-exec" { | |
when = destroy | |
command = "psql -c 'REVOKE SELECT ON public.table_name FROM \"${self.triggers.user}\";'" | |
environment = { | |
PGPORT = 5432 | |
PGUSER = self.triggers.pguser | |
PGPASSWORD = self.triggers.pgpassword | |
PGHOST = self.triggers.pghost | |
PGDATABASE = self.triggers.pgdatabase | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment