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.
1 2 3 4 5 6 |
SET ROLE user1; --impersonate user1 CREATE TABLE public.example_tbl ( id INT NOT NULL, notes TEXT NULL ); SET ROLE none; |
In psql, use the “describe” meta command:
1 |
\d |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam WHERE c.relkind IN ('r','p','v','m','S','f','') AND n.nspname <> 'pg_catalog' AND n.nspname !~ '^pg_toast' AND n.nspname <> 'information_schema' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; |
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:
- Only a
superuser
or theowner
of an object (table, function, procedure, sequence, etc.) canALTER
/DROP
the object. - Only a
superuser
or theowner
of an object canALTER
the ownership of that object. - 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--NOTE: don’t execute test code like this on a cluster that --has personal information on it, especially if the cluster --can be accessed by the Internet CREATE ROLE devgrp WITH NOLOGIN; CREATE ROLE dev1 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp; CREATE ROLE dev2 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp; -- This will allow our developers to create objects -- in the public schema GRANT CREATE ON SCHEMA public TO devgrp; -- For example purposes only. You should be selective on -- privileges based on your needs GRANT ALL ON SCHEMA public TO devgrp; GRANT ALL ON ALL TABLES IN SCHEMA public TO devgrp; |
Now check with psql that the users are part of the group
1 |
\du |
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.
1 2 3 4 5 6 7 |
-- as the 'postgres' super user we can set the -- session to impersonate any role SET ROLE dev1; CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL ); |
In psql, use the “describe” meta command:
1 |
\d |
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.
1 2 |
SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL; |
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.
1 2 3 4 5 |
-- as the 'postgres' superuser OR the object owner ALTER TABLE user_social OWNER TO devgrp; SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL; SELECT * FROM user_social; |
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.
1 2 3 4 5 6 7 8 9 |
-- as dev1 or 'postgres' superuser DROP TABLE user_social; -- as dev1 or the 'postgres' superuser we can set the -- session to impersonate the devgrp role SET ROLE devgrp; CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL ); |
In psql, use the “describe” meta command:
1 |
\d |
Now you will see:
And now, as the second dev user
1 2 3 4 |
SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL; SELECT * FROM user_social; SET ROLE none; --otherwise, very easy to forget your role context |
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.
1 2 3 4 5 6 7 8 9 |
-- As superuser or a role that has CREATEROLE attribute CREATE ROLE rptusr WITH LOGIN PASSWORD 'secretpw'; -- Set the session to the new role SET ROLE rptusr; -- Count the number of users that have handles for Mastodon SELECT count(*) FROM user_social WHERE mastodon_handle IS NOT NULL; |
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.
1 2 3 4 5 6 7 8 9 10 |
-- As a superuser or owner of the required object GRANT SELECT ON TABLE user_social TO rptusr; -- Set the session to rptusr role SET ROLE rptusr; -- Count the number of users that have handles for Mastodon SELECT count(*) FROM user_social WHERE mastodon_handle IS NOT NULL; SET ROLE none; |
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:
1 |
\ddp |
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.
1 2 3 4 5 6 |
-- As the role that will create objects, create -- default privileges SET ROLE devgrp; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rptusr; SET ROLE none; |
Once again, check to see if the default privilege was created:
1 |
\ddp |
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.
1 2 3 4 5 6 7 8 9 10 11 |
-- As the devgrp role that will own the table SET ROLE devgrp; CREATE TABLE rpt_log ( id int NOT NULL, rpt_date timestamptz NOT NULL, notes TEXT null ); SET ROLE rptusr; -- select from the table to verify that privileges -- were applied correctly SELECT * FROM rpt_log; |
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
:
1 |
\ddp |
This returns:
Now we will undo the current configuration before creating the new one.
1 2 3 |
-- REVOKE the current default access privilege for the single user ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM rptusr; |
Check that the access privilege was removed:
1 |
\ddp |
This will return:
Now create the new group, and security setup:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Create a new read only group role CREATE ROLE read_only WITH NOLOGIN; -- Grant select on all current tables in public schema -- Remember: this is just for current tables, not future ones GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; -- Grant membership to the read_only role GRANT read_only TO rptusr; -- Now create the same default access privilege for -- As the role that will create objects, create -- default privileges SET ROLE devgrp; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only; |
Once again, check to see if the default privilege was created:
1 |
\ddp |
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
orDROP
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.
Load comments