PostgreSQL Basics: Object Ownership and Default Privileges

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.

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:

\d

On a clean server, this will show you (if you have other objects on your server, 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.

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:

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.

--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

\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.

-- 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:

\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.

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 Postgres 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.

-- 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 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.

-- 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:

\d

Now you will see:

And now, as the second dev user

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.

-- 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.

-- 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:

\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.

-- 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:

\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.

-- 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:

\ddp

This returns:

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

-- 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:

\ddp

This will return:

Now create the new group, and security setup:

-- 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:

\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 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.

The post PostgreSQL Basics: Object Ownership and Default Privileges appeared first on Simple Talk.

from Simple Talk https://ift.tt/T34mtMo
via