问题:使用 terraform 创建只读 postgres 用户

我正在尝试找到正确的 terraform 模块组合,以在 postgres RDS 实例中创建只读用户。

https://www.terraform.io/docs/providers/postgresql/

我有一个有两个模式的数据库 -public和www。

我开始了:

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]                                                                                                                                                    
}

目的是:

1. 创建一个名为readonly的角色,该角色仅对db_name数据库中的两个模式具有SELECT访问权限。

2)创建一个可以登录的用户readonly_user,并赋予他们角色readonly。

当我这样做时,创建的用户仍然可以(例如)创建一个表。但是,他们_do_ 对表本身具有只读访问权限。

\du的轻微编辑结果:

db_name=> \du

     Role name  |   Attributes                   |  Member of
----------------+--------------------------------+---------------------------
 readonly_user  | Password valid until infinity  | {readonly}
 readonly       | Cannot login                  +| {}
                | Password valid until infinity  | 

如果有办法创建一个可以_only_通过terraform读取信息的用户,我将不胜感激。

解答

我不相信 postgres 的 tf 提供者会这样做。您需要手动撤销这些作为 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: