search

hashicorp / terraform-provider-postgresql

As part of our introduction to self-service publishing in the Terraform Registry, this copy of the provider has been archived, and ownership has been transferred to active maintainers in the community. Please see the new location on the Terraform Registry: https://registry.terraform.io/providers/cyrilgdn/postgresql
https://github.com/cyrilgdn/terraform-provider-postgresql
Mozilla Public License 2.0
103 stars 79 forks source link

Allow grant to specific tables #156

Closed jmks closed 6 months ago

jmks commented 4 years ago

Related to #85

I have the use-case of granting specific privileges to specific tables (and others seemed interested as well, so took a shot at doing that.

resource "postgresql_grant" "test" {
  role        = "some_role"
  object_type = "table"
  tables      = ["table1", "table2"]
  privileges  = ["SELECT"]
}

should produce the SQL GRANT SELECT ON TABLE table1,table2 TO "some_role".

I was interested in writing an acceptance test but could not get them running. I tried this:

➜ TF_ACC=1 TF_LOG=INFO go test -v ./postgresql -run ^TestAccPostgresqlGrant$
=== RUN   TestAccPostgresqlGrant
2020/07/19 18:43:54 [INFO] PostgreSQL DSN: `host=localhost port=5432 dbname=postgres user='' password=<redacted> sslmode='' connect_timeout=0`
    TestAccPostgresqlGrant: utils_test.go:77: could not execute query CREATE ROLE tf_tests_role_1595198634363977000 LOGIN ENCRYPTED PASSWORD 'testpwd': pq: SSL is not enabled on the server
--- FAIL: TestAccPostgresqlGrant (0.01s)
FAIL
FAIL    github.com/terraform-providers/terraform-provider-postgresql/postgresql 0.037s
FAIL

I could try harder 😄

cyrilgdn commented 4 years ago

@jmks Thanks a lot for working on this! It's indeed a high demanded feature.

I'll try to take, a look as soon as I can (can take a little while though).

Regarding the tests, if you running them against a Postgresql server started in a Docker container, you need to set the sslmode setting to disable (export PGSSLMODE=disable for example)

You can see here how it's configured for Travis and I use the same script manually with the docker-compose.yml to start Postgres:

cd tests/
docker-compose up -d
source switch_superuser.sh
# run the tests
jmks commented 4 years ago

@cyrilgdn Ahh. The README mentioned a tests/env.sh but I see its contents were moved around now. Thanks!

cyrilgdn commented 4 years ago

@cyrilgdn Ahh. The README mentioned a tests/env.sh but I see its contents were moved around now. Thanks!

Yes, recently but indeed I forgot to update the README :facepalm: , I'll fix it.

jmks commented 4 years ago

OK, I got the acceptance tests running now.

I'm realizing pulling the tables / table-level privileges information from the database is trickier than I thought. I may need a couple days to go through that.

jmks commented 4 years ago

Hey @cyrilgdn

I finally got an Acceptance test working. It turns out when they fail, it may indicate something is wrong 😄

A couple design issues that came up:

  1. To read the privileges for particular tables, I needed a lot of metadata from the resource Id.

    • I've observed other providers jamming a bunch of structured data in the Id, so I went with that approach.

  2. A lot of changes to this resource for tables.

    • This resource seems pretty complex and I made it worse 😆
    • I don't know when the resource is too complex to be broken up as you suggested here

It seems to do what I expect locally (revoke all then grant, in all cases), so think it can be reviewed now.