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!
|
|
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.