postgresql - 使用 terraform 创建只读 postgres 用户

Question

我正在尝试找到正确的 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。

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

\du 的轻微编辑结果:

db_name=> \du

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

如果有一种方法可以创建只能通过 terraform 读取信息的用户，我将不胜感激。

Answer 1

我不相信 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: