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 reason the CREATE privilege was removed from the public role in PostgreSQL 15.

Second, even if you have legitimate reasons for many different roles to create objects in a schema, each of them will need to create and maintain a set of default privileges to ensure the other roles have the access they need. That’s a lot to manage, even more so because it’s challenging to easily visualize default privileges in PostgreSQL, currently.

Instead, consider this second illustration, showing one group role that owns all database objects. In this scenario, the group role cannot login which provides some level of security, and only one set of default privileges must be maintained over time to manage access of database objects and data effectively and efficiently.

Let’s look at how you can start to move towards this kind of security set up within your PostgreSQL databases.

Create High-level Group Roles

In PostgreSQL, roles can be granted membership in other roles and inherit their privileges, something we discussed in the first article. The challenge, then, is to think about the kinds of privileges you need to provide to different roles in your database and how to abstract them into higher sets of roles.

Remember that roles are created at the instance level. Therefore, if you create a ‘read_only' role, it can be used to manage read-only access ('SELECT') in each database. The same can be done with different kinds of privileges from DML access (‘INSERT', ‘DELETE', ‘UPDATE') all the way up to DDL operations (‘CREATE').

A sample “template”, then, can start by creating these three group roles in every database instance. Your needs may be more complex, so please add and adjust as you see fit.

• The following scripts are a modified version of this Stack Overflow answer to use as a template. I happened upon it last year and thought it does a good job outlining a straightforward process to setting up PostgreSQL database privileges. (It doesn’t hurt that it mentioned the best Database DevOps tool as well! 😊)

PostgreSQL Instance Group Roles

The following two scripts need to be run once on each database instance to create the appropriate roles which we will then be granted privileges within each database.

Creating these roles hasn’t done anything of substance, yet. Instead, these roles become a mechanism to manage other privileges as we create a database and schema objects. Even though you cannot connect to a database directly with these roles ('WITH NOLOGIN'), these roles can still own objects and create default privileges.

Next, we can grant membership into these group roles to other roles as necessary. In the example below, we have three types of user roles:

• Object admins: these roles are allowed to create objects in a database. Creating objects is a DDL action, so we named the role 'ddl_grp'
• Data admins: these roles are allowed to modify data and associated objects with DML statements, so we named the role ‘dml_grp'
• Read-only: these roles are allowed to select data from specific schemas or tables. We named the role ‘read_only_grp'

The examples user roles (eg. 'dev_admin1', ‘flyway', ‘dev1', ‘report_usr', etc.) need to exist prior to granting membership in the group role. The key is that any role that is granted membership to the ‘ddl_grp' role will be able to run scripts that create or modify objects in the database as ‘ddl_grp'.

With all roles in place and membership updated, we can now turn to the individual database where privileges really matter.

Individual Database Privileges

Now that we have the database roles created, we can focus on the individual database privileges. In this case you have two options.

You could run this script each time a new database is created in the cluster. Or, using a superuser role, you can modify the template database (typically called ‘template1') on your PostgreSQL instance. Any settings that are applied in the template database will automatically set these privileges for each new database that is created from that template.

The script below can be applied as one script, however, I’ve broken it apart to discuss the purpose of each section and how it applies to the database.

Revoke all privileges from PUBLIC

The first thing that should be done for any new database is to revoke all privileges that are granted by default to the PUBLIC role. We discussed in the first article why this is a recommended practice.

Notice one nuance here. We are removing ‘ALL' privileges from 'PUBLIC' at the database level, not the schema level of the database. If we removed all privileges from the schema as well, then normal PostgreSQL commands would not work for many users without resetting additional privileges.

Revoking 'ALL' at the database level prevents users from connecting to the database without specific grants applied elsewhere, as shown below.

Grant basic privileges

All roles need the ability to connect to the database and (typically) access temporary tables. However, without additional privileges like USAGE and SELECT, these roles can’t actually do anything in the database yet.

Normally every role would inherit these privileges from the PUBLIC role, but we just revoked those privileges above to give us more control over which roles we want to access each database and the public schema.

Grant usage and other privileges to each group role

Now we get to the good stuff. For each database we can decide which roles can use the database objects ('USAGE') and what they can do per schema. As noted below, this example only shows how to grant privileges on the public schema. If your application has other schemas (which it probably does!), you will need to grant the appropriate permissions on each schema.

First, our 'ddl_grp' (and all members) is allowed to use and create objects in the schema and any sequences. If there are existing objects such as TABLE, SEQUENCE, TYPE, DOMAIN, etc. you do need to run a GRANT statement for each time to set their existing privileges appropriately because default privileges only effect newly created objects. In the example below, I show granting privileges to existing sequences and tables.

Next, we move on to the group roles that will be allowed to use objects in a schema and then select data and sequence values. As before, if your database has multiple schemas, the roles will need to be granted usage for each object type in each schema.

Finally, as the user that will be used to create schema objects, set the default privileges for other roles in the database. Notice two things in the following example.

• We only set default privileges for tables and sequences. You should consider other privileges that your users will need as objects are created and add them to your script.
• The 'ddl_grp' role doesn’t have any default privileges assigned, nor to any of the roles which are members of it. This is because the group will be the owner of the object and any other member role will automatically inherit the ownership privileges. Nothing more is needed.

Default privileges are normally applied to the role that executes the statement. Therefore, your scripts must temporarily set the role to the owner that will create objects in the future. This requires that the role executing this script is a member of the other role.

Alternatively, and slightly clearer in the long run, you can set the default privileges for another role as part of the ALTER DEFAULT PRIVILEGES statement as long as the current role is a member of it.

In both examples below, the role that is executing the SQL must be a member of the other group role. The second form is a bit more verbose and easier to track at a statement level what privileges are being applied to whom.

Option 1: SET ROLE before assigning privileges

Option 2: Assign privileges for another role

Verify the privileges are set correctly

The last step is to verify that the default privileges are set and ready to do their job. The easiest way to see the result of your work is with the ‘\ddp’ command in ‘psql’.

This helps us verify that the 'ddl_grp' role set default privileges for all tables and sequences in the public schema.

Obviously, your application and role set up is probably more complex than this example template. However, the principles apply regardless of how many roles and object types you need to manage. Use this as a starting point.

Bringing It Together At Migrations Time

With everything in place, let’s look at how you would use this in practice on a production database (for example).

In the migration pipeline, we set the connection string for the migration script to use our pipeline role, shown as “flyway” above. Ideally, the password for this role would be stored in a vault and is retrieved as part of the pipeline script so that no regular user can authenticate as this role.

At the top of any migration script, then, we first set the role to the 'ddl_grp' role so that any creation DDL will cause the objects to be owned by that group and the default privileges will kick in and be applied correctly! A simple example might look something like this.

Once the migration has been applied, you can quickly check that the privileges were applied correctly using the '\dp' command in '\psql'.

Success! From this point forward, as long as the 'ddl_grp' is used to create tables and sequences (or any other objects that you set default privileges for), other roles will have the access you defined, and the overall management burden is reduced!

Conclusion

Privileges in PostgreSQL can be complex to think about and manage. Knowing how to grant privileges to roles and membership between roles can quickly improve this burden. However, learning how to setup default privileges in an effective way is where the real power of managing your database begins.