How to grant permissions on all tables with Terraform?

[velatorio]

Let's say I have a database called db and an user called app that has full permissions for all the tables in such database (profiles, city, etc). Now, using this provider, I create a new user (e.g. app2) and grant this user permissions so that it can use the db database:

resource "postgresql_grant" {
  database = "db"
  object_type = "table"
  schema = "public"
  role = "app2"
  privileges = ["ALL"]  # not recommended but just for this example
}

Unfortunately, this won't give permissions for the tables in db and therefore the new user won't be able to work with that.

Do you have any suggestion? Could this be related to #85?

[cyrilgdn]

Hi @velatorio ,

Thanks for opening this issue and really sorry for response delay :(

I'm not sure to get your problem, why this won't give permissions for the tables in db ? Normally this is the goal of this resource but maybe I miss something.

[TamasNeumer]

Hi @cyrilgdn

I have a similar problem. Let's say I have users: admin, app, and dev.
I have also defined a role crud_role. Now the admin and app users both belong to this crud_role, meaning that both will have read/write access to each others tables.

However I want the dev only to be able to read (i.e. SELECT) on the tables on which admin and app operate.

In order to achieve this I have something like the following:

resource "postgresql_role" "crud_stage_role" {
  name = "crud_stage_role"
}

resource "postgresql_role" "admin_user" {
  name     = var.admin_user
  login    = true
  password = "adminuser"
  roles    = [postgresql_role.crud_stage_role.name]
}

resource "postgresql_role" "dev_user" {
  name     = var.dev_user
  login    = true
  password = "devuser"
}

resource "postgresql_role" "app_user" {
  name     = var.app_user
  login    = true
  password = "appuser"
  roles    = [postgresql_role.crud_stage_role.name]
}

resource "postgresql_schema" "new_schema" {
  name  = var.schema_name
  owner = "postgres"

  policy {
    create = true
    usage  = true
    role   = postgresql_role.crud_stage_role.name
  }

  policy {
    usage = true
    role  = postgresql_role.dev_user.name
  }
}

resource postgresql_grant "readwrite_tables" {
  database    = "postgres"
  role        = postgresql_role.crud_stage_role.name
  schema      = postgresql_schema.new_schema.name
  object_type = "table"
  privileges  = ["ALL"]
}

resource postgresql_grant "read_tables" {
  database    = "postgres"
  role        = postgresql_role.dev_user.name
  schema      = postgresql_schema.new_schema.name
  object_type = "table"
  privileges  = ["SELECT"]
}

Now I log in to psql using the admin account. I set the role to crud_role. (e.g. SET ROLE crud_role) Now I can create a table, insert a row, and the other user (app) can SELECT/INSERT/DELETE whatever he wants with it. All fine.

However the dev user/role has abolutely no priviledges on the table. (And that's what I would have expected, given that I specified the last read_tables block.

Also now if I run the following command:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='xxx';

I get the following:

     grantee      | privilege_type
------------------+----------------
 crud_stage_role  | INSERT
 crud_stage_role  | SELECT
 crud_stage_role  | UPDATE
 crud_stage_role  | DELETE
 crud_stage_role  | TRUNCATE
 crud_stage_role  | REFERENCES
 crud_stage_role  | TRIGGER

So the dev role is not there, while I believe it should have READ priviledges.

[cyrilgdn]

@TamasNeumer postgresql_grant apply privileges only on existing objects, because this is how GRANT works in Postgres.

I think what you want to set is default privileges which allow to define privileges that will be applied on newly created objects.

For that you can use the postgresql_default_privileges resource

[cyrilgdn]

@velatorio I close this issue as I think you are in the same case than @TamasNeumer , but feel free to open it back if it's not the case.