Ryan Booz: PostgreSQL Basics: A Template for Managing Database Privileges

In the first two articles of this series about PostgreSQL privileges, we reviewed how to create roles, grant them privileges to database objects, and how object ownership is an important aspect in managing access and control within the database. When it comes to managing what roles can access or modify an existing object, ownership is the ultimate privilege. Because PostgreSQL privileges work from a Principle of Least Privilege mindset, the owner of an object (table, trigger, function, procedure, etc.) needs to GRANT privilege to other roles. We discussed how this can be done manually with a GRANT command each time an object is created, however, that is time consuming to manage and easy to miss a detail. Instead, PostgreSQL provides a method for setting default privileges which are granted on behalf of the object owner as database objects are created. Using default privileges, a role can prepare the database ahead of time to ensure that consistent access privileges are applied while easing the management burden over time. But how do you go about creating a set of roles and default privileges that will provide the right level of control and access? Let’s dig a little deeper. Using Default Privileges to Manage Migrations Recall that default privileges are set per role (which can represent a user or a group of users). That is, each role must specify what privileges it will grant to other roles whenever a specific type of object is created. Consider the image illustration below of a database with a number of objects inside, each owned by a different role. A database that allows many roles to create and own objects introduces at least two issues. First, it means that many roles have the CREATE privilege in each schema. As we discussed in the previous articles, object creators are also the owners by default and have a superuser-like privilege for the object. More than that, however, having the privilege to create objects in a schema creates a potential security issue, the very re[...]