I'm trying to find the correct combination of terraform modules to create a read-only user in a postgres RDS instance.
https://www.terraform.io/docs/providers/postgresql/
I have a database with two schemas - public and www.
I've started with:
resource "postgresql_role" "readonly" {
name = "readonly"
}
resource postgresql_grant "readonly_public" {
database = "db_name"
role = postgresql_role.readonly.name
schema = "public"
object_type = "table"
privileges = ["SELECT"]
}
resource postgresql_grant "readonly_www" {
database = "db_name"
role = postgresql_role.readonly.name
schema = "www"
object_type = "table"
privileges = ["SELECT"]
}
resource "postgresql_role" "readonly_user" {
name = "readonly_user"
password = "some_password_123"
login = true
roles = [postgresql_role.readonly.name]
}
with the intent being:
1) Create a role called readonly that will only have SELECT access to the two schemas in db_name database.
2) Create a user that can login called readonly_user, and give them the role readonly.
When I do this, the created user can still (for example) create a table. However, they do have read-only access to the tables themselves.
Lightly edited result of \du:
db_name=> \du
Role name | Attributes | Member of
----------------+--------------------------------+---------------------------
readonly_user | Password valid until infinity | {readonly}
readonly | Cannot login +| {}
| Password valid until infinity |
If there is a way to create a user that can only read information via terraform, I'd be greatly appreciative.