Answer a question

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.

Answers

I don't believe the tf provider for postgres does this. You need to manually revoke those as thats the default behavior for postgres.

https://github.com/terraform-providers/terraform-provider-postgresql/issues/85

https://www.postgresql.org/docs/11/ddl-schemas.html#DDL-SCHEMAS-PRIV

A user can also be allowed to create objects in someone else's schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. Some usage patterns call for revoking that privilege: