# Choices

While working on automating database security, I had to prioritize the security issues to tackle. Controlling user & app access was a priority, but fine-grained RBAC was not.

Yet, the databases I was working on had plenty of juicy PII and sensitive information. So I had to find a solution.

# Community effort

Terraform is a brilliant tool that I try to use whenever possible. I used it to manage the table-scoped access control system described here.

At the time though (late 2021), there was no official PostgreSQL provider for Terraform. There still isn’t. cyrilgdn’s provider was, and still is, the most widely used and maintained community provider for PostgreSQL. It’s a small community effort though, so the provider is not as fully featured as one might hope.

More specifically, it lacked the ability to grant permissions on specific table columns. In a psql console, you can run GRANT SELECT user_email ON TABLE users TO human_readonly to allow the role human_readonly to read users’ emails. The Terraform PostgreSQL provider did not have this capability.

I rolled up my sleeves and got to work.

# Pull request

I opened a pull request on the provider’s Github repository: github.com/cyrilgdn/terraform-provider-postgresql/pull/135.

It was my first contribution to a Terraform provider. HashiCorp’s SDK makes things easy to understand and build upon, dare I even say enjoyable.

However, the SQL statement that runs on each terraform refresh was a tough puzzle to crack. After some initial struggling, I produced a statement that worked for vanilla PostgreSQL databases, but failed on AWS RDS instances. That is not an edge case that can be forgotten.

Fortunately, a community member, wilsonjackson, swooped in and fixed the remaining bug. The PR was merged on March 18th 2023 :tada:.

# Result

You can now grant permissions on a per column basis using the Terraform PostgreSQL provider!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Equivalent to 'GRANT SELECT user_email ON TABLE users TO human_readonly'
resource "postgresql_grant" "grant" {
  database    = "test_database"
  role        = "human_readonly"
  schema      = "public"
  object_type = "column"
  objects     = ["users"]
  columns     = ["user_email"]
  privileges  = ["SELECT"]
}

This story proves yet again that security is a team sport. Whether it’s in the open source space, or in your own organisation, security professionals have to be involved in the engineering, or risk falling by the wayside as others move forward.