Making Access Control Management Manageable: Introducing pg_acm
October 20–23
PostgreSQL does not have any prescribed guidelines for the use of schemas, also called namespaces. A namespace is just one of many attributes of an object, whether an object is a table, a view, a function, or any other object type available in PostgreSQL. However, one of the common reasons for using schemas is to simplify the access control management. The idea is straightforward: all objects within one schema should have identical access privileges, isolating projects or applications from one another. As simple as it sounds, the practical implementation of this approach can quickly turn into an operational nightmare. The problem many PostgreSQL users face is that there is currently no simple way to ensure that all objects created in a schema have identical ownership and permissions. Even when we define default schema privileges, they apply only to the specific user(s) creating objects. Since any role, including the schema owner role, can be granted to multiple users, there is no easy way to ensure that default permissions will work for all of them. Nowadays, rapid development demands a smooth feature rollout, while keeping sensitive data secure is more important than ever. Faced with an unexpected “permission denied” error during the production rollout, teams rush to grant excessive permissions, which can be difficult to revoke later when no one can untangle the cascading grants. Observing situations like that happening again and again, we thought about ways to make permissions management a little simpler and, more importantly, to autocorrect inevitable user errors.
The pg_acm tool, which we introduce in this talk, addresses these problems using a set of security-definer functions and event triggers. When you use pg_acm, all you need to do is define an access management policy for your database. Once it is defined, the CREATE SCHEMA command creates a set of event triggers and roles. All of them, working together, will ensure your access policy enforcement. We hope that someday we will see an option to create “schemas with permissions” as part of the PostgreSQL core.
