Allow grant to specific tables

[jmks]

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]

@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]

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

[cyrilgdn]

@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]

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]

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.