In the first security article, PostgreSQL Basics: Roles and Privileges, I discussed how roles (users and groups) are created and managed in PostgreSQL Depending on your background with permissions, particularly in other database products, some of those nuances in how permissions work may have been surprising.

Understanding how roles and privileges work in Postgres is key to understanding the next, and often more confusing part of security, object ownership. Although there are many privileges that can be assigned in Postgres, object ownership comes with a specific level of privilege that cannot be transferred to other roles. Understanding the implications of this is essential to the management of your database schema, and access to the objects it contains, over time.

Who owns database objects?

In PostgreSQL, the role that creates an object (table, view, function, etc.) becomes the owner. It can be altered after the fact, but initially, the creator is the owner. We can see the owner of objects in the database using the psql interactive terminal or querying the pg_catalog tables that correspond to the object type.

In psql, use the “describe” meta command:

On a clean database, this will show you (if you have other objects in your database, you may get additional rows of output.):

As we can see, the table’s owner is set to user1 because that role created it.

The \d meta-command in psql executes the following query under the covers to show us the list of relations (including the table we created) and who owns each relation.

Tip: As you can see, the catalogs in PostgreSQL contain a lot of useful information. Learning how to query them effectively, however, takes time and experience. This is why the psql meta-commands are particularly useful and very popular among PostgreSQL developers. We’re working on a “what you need to know about psql” article, but until then, check out psql-tips.org for some great tips.

I can hear what you’re thinking already.

“You keep saying that object owners are important, but you haven’t said why that matters! Surely any other role that has appropriate privileges on the same schema can work with that table. Right?”

It depends.

There are three major points you need to understand about object ownership:

1. Only a superuser or the owner of an object (table, function, procedure, sequence, etc.) can ALTER/DROP the object.
2. Only a superuser or the owner of an object can ALTER the ownership of that object.
3. Only the owner of an object can define default privileges for the objects they create.

Let’s give each of these a look to better describe how ownership and privilege play together, and what you can do to proactively manage this in PostgreSQL.

Setting up users and groups for demonstration

For the following examples, we will assume that your Postgres instance has the normal superuser principal with the name postgres. We’ll then take a cue from the first article to set up two development users and a development group to manage privileges more easily.

Now check with psql that the users are part of the group

This will return:

After running this SQL, the database has two developers that can login to the database, and each is a member of the devgrp group role. We’ve granted privileges to the group that allows members to create objects in the public schema and they have all basic DML privileges on all tables.

Now let’s explore how to overcome a couple of common security issues in PostgreSQL by watching this team of developers begin to implement a new feature.

Problem #1: Altering an object

The first developer is ready to dig into the new project, tracking social media user handles for various networks. To get started, they create a table to store Twitter and Facebook handles.

In psql, use the “describe” meta command:

This will return:

As expected, the table was created and is owned by dev1. As the developers get to work on the feature, they quickly realize that a new social network is rising in popularity, and they need to track user handles for it. dev2 offers to add the new column to keep things moving forward.

This will cause the following error to occur:

ERROR: must be owner of table user_social

Hopefully this first, straightforward example helps to clarify why object ownership is so important in your PostgreSQL schema. There is no privilege that can be granted to the second developer which allows them to make modifications to the table. Altering the object is an inherent privilege reserved for the owner (or a superuser).

Most development teams that start using PostgreSQL with roles for each developer hit this problem during migrations and day-to-day development. Because there is no privilege that can be set which allows other roles to alter the object, a different approach needs to be taken.

The most common solution is to set the ownership of all objects to a consistent role and then grant membership in that role to users that need to modify objects. In our example setup a reasonable choice is the devgrp role because all developers are members of this role. In a more complex environment and development team structure, you’ll likely have to create a few more groups to appropriately manage ownership and access. I’ll provide a starting template of how to manage groups are the end of the article.

To provide an example using our small development team, we can change the owner of this table to a group that all developers are members of, in our case the devgrp role. Once the owner is changed, dev2 should be able to ALTER it because they are members of the group.

The output of this query is:

Alternatively, you can temporarily set the role of your session to the common owner role before creating the object (assuming you are a member of that role). Any objects that are created will be owned by the role in force at the time of creation. To demonstrate, I’ll drop the table and try the same process again, but this time setting the role before creating the table.

In psql, use the “describe” meta command:

Now you will see:

And now, as the second dev user

This returns:

The key here is that ownership is an essential component in maintaining and altering objects in a PostgreSQL database. Whether you set the roles before creating objects or alter ownership after the fact, only members of the role that own an object can alter the object.

Lesson learned, we can now move on to the second common problem many teams run into when they are working with multiple logins in PostgreSQL – default object privileges.

Problem #2: Default Object Privileges

We solved our first problem by setting the table owner to a role that all developers are members of. Essentially, the owner of an object is analogous to a superuser of that object.

But what happens when we add a new role to the database that will be used for reporting or read-only purposes?

The development team has decided that a new role is needed to facilitate reporting functionality for some of the data that will be generated by this new feature.

This causes the error:

ERROR: permission denied for table user_social

This shouldn’t be much of a surprise given what we’ve learned so far. The new rptusr role was created after the table existed and hasn’t been granted any privileges to the table. The superuser or owner of an object must specifically grant the necessary privilege.

This returns:

In the first article in this series, we referred to the process of ensuring users have only the minimum right necessary as the Principle of Least Privilege. Setting permissions, object by object, will quickly become a tiresome task.

Adding a group role doesn’t help either because the same problem will exist. Privileges are only granted for objects in existence at the time of the GRANT. Stated another way, GRANT is not a forward-looking action. Instead, we need a way to have PostgreSQL apply privileges every time an object is created.

Enter default privileges.

Each role can create a set of default access privileges that are applied whenever they create an object in a specific database. This gives complete control to each role, ensuring that objects are created with the correct privileges each time.

To illustrate, let’s create new default access privileges before creating another new table that the rptuser should be able to query.

First, check that there are no default access privileges using psql:

On my demo server, this shows no default access privileges.

Next, we will set security context to the group that you want to set default privileges for, which will be applied when they create new objects in certain schemas.

Once again, check to see if the default privilege was created:

This returns:

We can see that the default access privilege was created which will grant SELECT (read) privilege to the rptusr role for any tables that are created in the public schema. To verify that it works, we can now create a new table and attempt to select from it as rptusr without additional intervention using GRANT statements.

This returns:

Success! The devgrp was able to create the table and the new rptusr was able to select from it without error. Moving forward, as long as the devgrp is the one to create and own tables (our example object), the rptusr will be able to select from them.

Unfortunately, we’ve only solved our problem for this one “read-only” role named rptusr. As soon as another read-only user needs access to database objects, we’ll have to grant privileges to existing tables and then create another default access privilege for future actions. That’s not very sustainable and simply highlights what we discussed in the first article.

One common approach to deal with is to create a read-only group role and set default access privileges for it. Then, as new read-only users are created in the database, they can be granted membership into the read-only group role, inheriting the same privileges.

First, check the current default access privileges using psql:

This returns:

Now we will undo the current configuration before creating the new one.

Check that the access privilege was removed:

This will return:

Now create the new group, and security setup:

Once again, check to see if the default privilege was created:

This will return the following:

At this point, any tables that the devgrp user creates will have this default access privilege applied and all members of the read_only role will be able to select data.

With regards to read-only users, PostgreSQL 14+ does provide new default roles to more easily manage access to table data. There are enough nuances to setting it up correctly in a multi-tenant environment that it warrants a separate article in the near future.

Managing ownership and privileges at scale

Between these first two articles we’ve covered a lot of ground on PostgreSQL roles and security.

• PostgreSQL has roles. Users and groups are synonyms.
• By convention, user roles can login while group roles cannot.
• Superusers bypass all privilege checks and can access or modify any object and cluster setting.
• Access to data and objects must be specifically granted (or revoked) for every role, except the object owner or a superuser.
• Roles can be granted the permissions of other roles through inheritance.
• All roles have automatic, non-revokable membership in the PUBLIC role
• Owners of an object (or superusers) are the only role that can ALTER or DROP the object.
• Each role can create default access privileges for any objects they create (like tables) so that other roles have access automatically, rather than applying privileges each time an object is created.

That’s a lot of information to think about and manage if PostgreSQL security is new to you, making it challenging to manage PostgreSQL privileges in a larger team. Having a plan or starting template in place for managing PostgreSQL privileges and object ownership as early as possible is a helpful first step. Many times, I’ve seen teams begin a project without understanding the long-term implications of not having a security plan, often because they didn’t fully understand the things we’ve been discussing.

That said, here’s a sample role and security setup to consider as you get started. Use your knowledge of the things we’ve discussed so far (and the official documentation), mixed with the requirements of your organization, to modify and craft a scheme that’s right for you.

In the next article, we’ll look at how you can put all of this together to create a set of privilege templates each time you create a new database. This will ensure that you have consistent, reproducible access privileges across databases and roles.

Conclusion

PostgreSQL has a security and ownership model that can be confusing to new users. Understanding how roles are created, privileges are assigned, and how to prepare your database for consistent access as time marches on will help you use PostgreSQL effectively and keep your teams working without friction.