Default privileges approach is incorrect

[sworisbreathing]

The redshift_privilege resource currently creates grants but also runs ALTER DEFAULT PRIVILEGES. This appears to be due to a misunderstanding of how default privileges work in Redshift.

Per https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DEFAULT_PRIVILEGES.html:

Defines the default set of access privileges to be applied to objects that are created in the future by the specified user.
...
FOR USER target_user
Optional. The name of the user for which default privileges are defined... The default value is the current user.

Default privileges are essentially grants that are automatically applied when a particular user creates an object. In other words, if you do:

resource "redshift_privilege" "readonly_tables" {
  group       = "test_group"
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT"]
}

What actually runs is this:

ALTER DEFAULT PRIVILEGES
IN SCHEMA "public"
GRANT SELECT ON TABLES
TO GROUP "test_group"

which is equivalent to this:

ALTER DEFAULT PRIVILEGES
FOR USER CURRENT_USER
IN SCHEMA "public"
GRANT SELECT ON TABLES
TO GROUP "test_group"

which actually means the select permissions will only be given on new tables created by whoever ran terraform.

If what we're really trying to achieve here is for test_group to have select permissions on newly created tables in public then what we actually have to do is run something akin to:

-- redshift doesn't actually support a for loop like this,
-- but you get the idea.
FOR db_user IN (SELECT usename FROM pg_user) DO
  ALTER DEFAULT PRIVILEGES
  FOR USER db_user
  IN SCHEMA "public"
  GRANT SELECT ON TABLES
  TO GROUP "test_group"
END FOR

[sworisbreathing]

Reproducible test case.

Run the following terraform to create users, groups, schema, and permissions

resource "redshift_user" "writer" {
  name = "writer"
  password = "writer"
}

resource "redshift_user" "reader" {
  name = "reader"
  password = "reader"
}

resource "redshift_group" "readers" {
  name = "readers"
  users = [
    redshift_user.reader.name
  ]
}

resource "redshift_schema" "my_schema" {
  name = "my_schema"
  owner = redshift_user.writer.name
}

resource "redshift_privilege" "readers_my_schema" {
  group = redshift_group.readers.name
  schema = redshift_schema.my_schema.name
  object_type = "schema"
  privileges = ["USAGE"]
}

resource "redshift_privilege" "readers_can_read" {
  group = redshift_group.readers.name
  schema = redshift_schema.my_schema.name
  object_type = "table"
  privileges = ["SELECT"]
}

Now login as writer and run

CREATE TABLE my_schema.default_permissions_test(message)
AS
SELECT 'Hello, World!' AS message;

Now login as reader and run

SELECT message FROM my_schema.default_permissions_test

Instead of returning Hello, World! you'll get a permission denied error.

[sworisbreathing]

To make this work I think you'd need to maintain a separate terraform resource for default permissions. something like this (in addition to the above resources):

resource "redshift_default_privilege" "readers_can_read_tables_created_by_writer" {
  grantor = redshift_user.writer.name
  group = redshift_group.readers.name
  schema = redshift_schema.my_schema.name
  object_type = "table"
  privileges = ["SELECT"]
}

[winglot]

I've checked your examples and you're right. I'm not sure how such a bug went through when developing and testing the provider.

The reason why I merged grants and default privileges into one resource is because of two things:

1. it's easy to read what default privileges are set for a group (reading what grants a group has is a nightmare)
2. assuming multiple automated systems push data to a single redshift, I needed an easy want to give access to current and future data for a specific group without worrying who pushed that data

Now I can see that it won't be that easy and will require what you said, a separate resource for default privileges and separate for grants. I will try to schedule implementation for this week.

[sworisbreathing]

Cheers. I've also started working on a fix, but it's been slow going due to other commitments, as well as difficulties in dealing with redshift arrays.

Since the "subject" of the default privilege is the grantor and not the grantee, I'm wondering if it would make more sense to support multiple grantees in a single resource block. Something like this:

resource "redshift_default_privilege" "readers_can_read_tables_created_by_writer" {
  grantor = redshift_user.writer.name
  groups = [
    redshift_group.readers.name,
    redshift_group.some_other_group.name,
  ]
  users = [
    redshift_user.foo.name,
    redshift_user.bar.name,
  ]
  schema = redshift_schema.my_schema.name # optional. if not specified then privileges are applied to the database as a whole
  object_type = "table"
  privileges = ["SELECT"]
}

The challenge I'm facing with this approach is parsing the ACLs in SQL during the read. I've pretty much decided my next attempt will be to parse the ACLs in the provider code instead.

[sworisbreathing]

Actually on second thought I think I'd rather put some effort into managing data shares on the producer side before tackling this so I probably won't get to default privileges this week.

[winglot]

Cool. No worries. I will take care of fixing the grants and defaults privileges.

In case of designing how a resource should look, I would suggest looking at PostgreSQL provider (https://github.com/cyrilgdn/terraform-provider-postgresql). It can serve as a source of inspiration.
We've been using it in production for quite some time and the resources design is easy to use and fits Terraform flow nicely.

[winglot]

First part is done, redshift_default_privileges resource is ready.

I will now start implementing redshift_grant. This will allow to deprecate and remove the faulty redshift_privilege resource.