Create role_membership resource

[vascop]

Right now we have imported our roles into terraform but one key thing that is missing is being able to define GRANTS on roles. A way to manage permissions in postgresql is to inherit from base roles like db_superusers / db_readonly / db_readwrite and then GRANT them like:

GRANT db_readonly TO joe;
GRANT db_readwrite TO maria;

So it'd be nice to have something like other providers have (thinking about AWS iam_group / iam_user / iam_group_membership):

resource "postgresql_role" "db_readwrite" {
  name     = "db_readwrite"
  login    = "false"
}
resource "postgresql_role" "maria" {
  name     = "maria"
  login    = true
  password = "mariapass"
}
resource "postgresql_role_membership" "db_readwrite" {
  name  = "db_readwrite"
  roles = [
    "maria"
  ]
}

[apparentlymart]

Hi @vascop! Thanks for this feature request.

This seems like a great idea! Unfortunately at this time the Terraform team at HashiCorp is not doing new feature development on this provider, due to our focus being elsewhere. However, if someone in the community has the time and motivation to work on it we'd love to review a pull request.

[ghost]

I've run into the same requirement. Though, I don't so much want to have an attachment resource as I want to be able to specify the roles a new should be made a member of, the same way I can in a 'CREATE ROLE' statement in the psql client.

CREATE ROLE devops nologin createrole createdb;
CREATE ROLE dev nologin;
CREATE ROLE sam password 'khkjfs' login inherit in group dev, devops;

translates to

resource "postgresql_role" "devops" {
  name     = "devops"
  login    = "false"
  createrole = "true"
  createdb = "true"
}

resource "postgresql_role" "dev" {
  name     = "dev"
  login    = "false"
  createrole = "true"
  createdb = "true"
}

resource "postgresql_role" "sam" {
  name = "sam"
  login = "true"
  password = "khkjfs"
  inherit = "true"
  groups = ["${resource.postgresql_role.devops.name}", "${resource.postgresql_role.dev.name}"]
}

Granted, it would also be nice to be able to do an assignment after role creation, but my priority is being able to set the groups at create-time.

I'm sure I'll try to make this work in the provider eventually, but I really don't know much about Go or building providers, so it's not exactly high on my to-do list. I'm assuming it won't be too difficult to just hack it into the codebase via cut and paste/trial and error, but I still have to get Go working on my machine and have to figure out how to tell terraform to use a locally-built provider before I can even start on it.

[cknowles]

Would like to contribute this but it will be some time as new to terraform and also this provider. After several days playing around with the terraform postgres support, we ended up ditching it in favour of an imperative script. Mostly due to missing support for this and a few other things like default privileges.

We also hit cases where this provider is not particular declarative, some changes simply won't roll out or need other manual steps. The rabbitmq provider seems to work well in that sense as it appears to query rabbitmq for the existing state to work around some of these sorts of problems. Perhaps that is against the terraform best practice though?

[ghost]

Yeah, the provider is kind of useless as it is. It doesn't do any existence checks on extensions or users, even though there is perfectly legal DDL syntax for doing exactly that. When you remove a role from the config, it errors out when deleting the user. Error messaging is a disaster. There's no way to execute DDL in a transaction.

In reality, I'll use a schema migration tool for the vast majority of such things, just running it via terraform, but extensions and roles (and default privleges) are the one thing that are best to set up at db creation time, before anything else is done to the database, since default privileges are not updated on existing objects in the db, only newly created ones. I had it in mind to set up my extensions, groups, roles, and default privileges before running a schema migration tool or pg_restore in order to populate the db, but I can tell I'm going to end up doing all of that via a local exec provisioner on the RDS instance, rather than trying to use this postgresql provider, especially since the rds db is definitely fully available and ready by the time the instance creation is complete, so there's no risk of the local-exec provisioner running too early.

[ghost]

I'll admit, I'm surprised there isn't a liquibase or flyway provider which will simply run those tools from terraform. Since the tools are intended to be idempotent, a simple provider that always calls the tool doesn't seem very tough. I can do that with a provisioner, but it would be nice to be able to configure the tool with paths/repo addresses and db connection information so that it can all be dynamically generated by other resources, if necessary. Seems like the kind of thing I'd have expected to show up in terraform fairly early. I'm surprised it doesn't seem to exist.

[gbergere]

This would be so useful is there any update on it?