{"id":96105,"date":"2023-02-21T21:37:45","date_gmt":"2023-02-21T21:37:45","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96105"},"modified":"2026-05-21T15:47:51","modified_gmt":"2026-05-21T15:47:51","slug":"postgresql-basics-object-ownership-and-default-privileges","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/featured\/postgresql-basics-object-ownership-and-default-privileges\/","title":{"rendered":"PostgreSQL Object Ownership and Default Privileges: A Practical Guide"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

When different PostgreSQL users create objects in the same database, ownership and permission problems quickly emerge: one developer creates a table and another gets ‘permission denied’ when they try to query it. The root cause is PostgreSQL’s ownership model – the role that creates an object is its owner, and object owners control access. The solution involves two concepts: setting object ownership to a shared role (so all team members inherit access), and using ALTER DEFAULT PRIVILEGES to automatically grant permissions on future objects. This article covers both with worked examples for a multi-developer database scenario.<\/strong><\/p>\n\n\n\n

In the first security article, PostgreSQL Basics: Roles and Privileges<\/a>, 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.<\/p>\n\n\n\n

Understanding how roles and privileges work in Postgres is key to understanding the next, and often more confusing part of security, object ownership<\/em>. 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.<\/p>\n\n\n\n

Who owns database objects?<\/h2>\n\n\n\n

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<\/code> interactive terminal<\/a> or querying the pg_catalog<\/code> tables that correspond to the object type.<\/p>\n\n\n\n

SET ROLE user1; --impersonate user1\nCREATE TABLE public.example_tbl (\n  id INT NOT NULL,\n  notes TEXT NULL\n);\nSET ROLE none;<\/pre>\n\n\n\n
In psql, use the \u201cdescribe\u201d meta command:<\/p>\n\n\n\n
\\d<\/code><\/pre>\n\n\n\n
On a clean database, this will show you (if you have other objects in your database, you may get additional rows of output.):<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As we can see, the table\u2019s owner is set to user1<\/code> because that role created it.<\/p>\n\n\n\n
The \\d<\/code> meta-command in psql <\/code>executes the following query under the covers to show us the list of relations (including the table we created) and who owns each relation.<\/p>\n\n\n\n
SELECT n.nspname as \"Schema\",\n  c.relname as \"Name\",\n  CASE c.relkind \n  \tWHEN 'r' THEN 'table' \n  \tWHEN 'v' THEN 'view' \n  \tWHEN 'm' THEN 'materialized view' \n  \tWHEN 'i' THEN 'index' \n  \tWHEN 'S' THEN 'sequence' \n  \tWHEN 't' THEN 'TOAST table' \n  \tWHEN 'f' THEN 'foreign table' \n  \tWHEN 'p' THEN 'partitioned table' \n  \tWHEN 'I' THEN 'partitioned index' END as \"Type\",\n  pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\"\nFROM pg_catalog.pg_class c\n     LEFT JOIN pg_catalog.pg_namespace n \n         ON n.oid = c.relnamespace\n     LEFT JOIN pg_catalog.pg_am am \n         ON am.oid = c.relam\nWHERE c.relkind IN ('r','p','v','m','S','f','')\n      AND n.nspname <> 'pg_catalog'\n      AND n.nspname !~ '^pg_toast'\n      AND n.nspname <> 'information_schema'\n  AND pg_catalog.pg_table_is_visible(c.oid)\nORDER BY 1,2;<\/pre>\n\n\n\n
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<\/code> meta-commands are particularly useful and very popular among PostgreSQL developers. We’re working on a “what you need to know about psql<\/code>” article, but until then, check out psql-tips.org<\/a> for some great tips.<\/p>\n\n\n\n
I can hear what you\u2019re thinking already.<\/p>\n\n\n\n
\u201cYou keep saying that object owners are important, but you haven\u2019t said why that matters! Surely any other role that has appropriate privileges on the same schema can work with that table. Right?<\/em><\/strong>\u201d<\/p>\n\n\n\n
It depends.<\/p>\n\n\n\n
There are three major points you need to understand about object ownership:<\/p>\n\n\n
\n
    \n
  1. Only a superuser<\/code> or the owner<\/code> of an object (table, function, procedure, sequence, etc.) can ALTER<\/code>\/DROP<\/code> the object.<\/li>\n\n\n\n
  2. Only a superuser<\/code> or the owner<\/code> of an object can ALTER<\/code> the ownership of that object.<\/li>\n\n\n\n
  3. Only the owner<\/code> of an object can define default privileges for the objects they create.<\/li>\n<\/ol>\n<\/div>\n\n\n
    Let\u2019s 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.<\/p>\n\n\n\n
    Setting up users and groups for demonstration<\/h2>\n\n\n\n
    For the following examples, we will assume that your Postgres instance has the normal superuser principal with the name postgres<\/code>. We\u2019ll then take a cue from the first article to set up two development users and a development group to manage privileges more easily.<\/p>\n\n\n\n
    --NOTE: don\u2019t execute test code like this on a cluster that \n--has personal information on it, especially if the cluster \n--can be accessed by the Internet\nCREATE ROLE devgrp WITH NOLOGIN;\nCREATE ROLE dev1 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp;\nCREATE ROLE dev2 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp;\n-- This will allow our developers to create objects\n-- in the public schema\nGRANT CREATE ON SCHEMA public TO devgrp;\n-- For example purposes only. You should be selective on\n-- privileges based on your needs\nGRANT ALL ON SCHEMA public TO devgrp;\nGRANT ALL ON ALL TABLES IN SCHEMA public TO devgrp;<\/pre>\n\n\n\n
    Now check with psql that the users are part of the group<\/p>\n\n\n\n
    \\du<\/pre>\n\n\n\n
    This will return:<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    After running this SQL, the database has two developers that can login to the database, and each is a member of the devgrp<\/code> group role. We\u2019ve 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.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    Problem #1: Altering an object<\/h2>\n\n\n\n
    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.<\/p>\n\n\n\n
    -- as the 'postgres' super user we can set the\n-- session to impersonate any role\nSET ROLE dev1;\nCREATE TABLE user_social (\n   user_id INT NOT NULL,\n   twitter_handle TEXT NULL,\n   facebook_handle TEXT NULL );<\/pre>\n\n\n\n
    In psql, use the “describe” meta command:<\/p>\n\n\n\n
    \\d<\/pre>\n\n\n\n
    This will return:<\/p>\n\n\n\n
    \"A<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    SET ROLE dev2;\nALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;<\/pre>\n\n\n\n
    This will cause the following error to occur:<\/p>\n\n\n\n
    ERROR: must be owner of table user_social<\/code><\/p>\n\n\n\n
    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).<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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<\/code> 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.<\/p>\n\n\n\n
    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<\/code> role. Once the owner is changed, dev2<\/code> should be able to ALTER it because they are members of the group.<\/p>\n\n\n\n
    -- as the 'postgres' superuser OR the object owner\nALTER TABLE user_social OWNER TO devgrp;\nSET ROLE dev2;\nALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;\nSELECT * FROM user_social;<\/pre>\n\n\n\n
    The output of this query is:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Alternatively, you can temporarily set the role of your session to the common owner role before<\/strong> 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.<\/p>\n\n\n\n
    -- as dev1 or 'postgres' superuser\nDROP TABLE user_social;\n-- as dev1 or the 'postgres' superuser we can set the\n-- session to impersonate the devgrp role\nSET ROLE devgrp;\nCREATE TABLE user_social (\n   user_id INT NOT NULL,\n   twitter_handle TEXT NULL,\n   facebook_handle TEXT NULL );<\/pre>\n\n\n\n
    In psql, use the “describe” meta command:<\/p>\n\n\n\n
    \\d<\/pre>\n\n\n\n
    Now you will see:<\/p>\n\n\n\n
    \"Chart,<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    And now, as the second dev user<\/p>\n\n\n\n
    SET ROLE dev2;\nALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;\nSELECT * FROM user_social;\nSET ROLE none; --otherwise, very easy to forget your role context<\/pre>\n\n\n\n
    This returns:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    Problem #2: Default Object Privileges<\/h2>\n\n\n\n
    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.<\/p>\n\n\n\n
    But what happens when we add a new role to the database that will be used for reporting or read-only purposes?<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    -- As superuser or a role that has CREATEROLE attribute\nCREATE ROLE rptusr WITH LOGIN PASSWORD 'secretpw';\n\n-- Set the session to the new role\nSET ROLE rptusr;\n\n-- Count the number of users that have handles for Mastodon\nSELECT count(*) FROM user_social \nWHERE mastodon_handle IS NOT NULL;<\/pre>\n\n\n\n
    This causes the error:<\/p>\n\n\n\n
    ERROR:  permission denied for table user_social<\/code><\/p>\n\n\n\n
    This shouldn’t be much of a surprise given what we’ve learned so far. The new rptusr<\/code> 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.<\/p>\n\n\n\n
    -- As a superuser or owner of the required object\nGRANT SELECT ON TABLE user_social TO rptusr;\n\n-- Set the session to rptusr role\nSET ROLE rptusr;\n\n-- Count the number of users that have handles for Mastodon\nSELECT count(*) FROM user_social \nWHERE mastodon_handle IS NOT NULL;\nSET ROLE none;<\/pre>\n\n\n\n
    This returns:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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<\/code>. Stated another way, GRANT<\/code> is not a forward-looking action. Instead, we need a way to have PostgreSQL apply privileges every time an object is created.<\/p>\n\n\n\n
    Enter default privileges<\/a>.<\/p>\n\n\n\n
    Each role can create a set of default access privileges that are applied whenever they create an object in a specific database<\/em>. This gives complete control to each role, ensuring that objects are created with the correct privileges each time.<\/p>\n\n\n\n
    To illustrate, let’s create new default access privileges before<\/em> creating another new table that the rptuser<\/code> should be able to query.<\/p>\n\n\n\n
    First, check that there are no default access privileges using psql:<\/p>\n\n\n\n
    \\ddp<\/pre>\n\n\n\n
    On my demo server, this shows no default access privileges.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    -- As the role that will create objects, create \n-- default privileges\nSET ROLE devgrp;\nALTER DEFAULT PRIVILEGES IN SCHEMA public\n\tGRANT SELECT ON TABLES TO rptusr;\nSET ROLE none;<\/pre>\n\n\n\n
    Once again, check to see if the default privilege was created:<\/p>\n\n\n\n
    \\ddp<\/pre>\n\n\n\n
    This returns:<\/p>\n\n\n\n
    \"Diagram\n\nDescription<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    We can see that the default access privilege was created which will grant SELECT<\/code> (read) privilege to the rptusr<\/code> role for any tables that are created in the public<\/code> schema. To verify that it works, we can now create a new table and attempt to select from it as rptusr<\/code> without additional intervention using GRANT<\/code> statements.<\/p>\n\n\n\n
    -- As the devgrp role that will own the table\nSET ROLE devgrp;\nCREATE TABLE rpt_log (\nid int NOT NULL,\nrpt_date timestamptz NOT NULL,\nnotes TEXT null\n);\nSET ROLE rptusr;\n-- select from the table to verify that privileges \n-- were applied correctly\nSELECT * FROM rpt_log;<\/pre>\n\n\n\n
    This returns:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Success! The devgrp<\/code> was able to create the table and the new rptusr<\/code> was able to select from it without error. Moving forward, as long as the devgrp<\/code> is the one to create and own tables (our example object), the rptusr<\/code> will be able to select from them.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    First, check the current default access privileges using psql<\/code>:<\/p>\n\n\n\n
    \\ddp<\/pre>\n\n\n\n
    This returns:<\/p>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Now we will undo the current configuration before creating the new one.<\/p>\n\n\n\n
    -- REVOKE the current default access privilege for the single user\nALTER DEFAULT PRIVILEGES IN SCHEMA public\n\tREVOKE SELECT ON TABLES FROM rptusr;<\/pre>\n\n\n\n
    Check that the access privilege was removed:<\/p>\n\n\n\n
    \\ddp<\/pre>\n\n\n\n
    This will return:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Now create the new group, and security setup:<\/p>\n\n\n\n
    -- Create a new read only group role\nCREATE ROLE read_only WITH NOLOGIN;\n\n-- Grant select on all current tables in public schema\n-- Remember: this is just for current tables, not future ones\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;\n\n-- Grant membership to the read_only role\nGRANT read_only TO rptusr;\n\n-- Now create the same default access privilege for \n-- As the role that will create objects, create \n-- default privileges\nSET ROLE devgrp;\nALTER DEFAULT PRIVILEGES IN SCHEMA public\n\tGRANT SELECT ON TABLES TO read_only;<\/pre>\n\n\n\n
    Once again, check to see if the default privilege was created:<\/p>\n\n\n\n
    \\ddp<\/pre>\n\n\n\n
    This will return the following:<\/p>\n\n\n\n
    \"Diagram\n\nDescription<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    At this point, any tables that the devgrp<\/code> user creates will have this default access privilege applied and all members of the read_only<\/code> role will be able to select data.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n    
    \n\n
    You may also be interested in:<\/strong>
    Tracking SQL Server view changes and stale views – a parallel problem across database platforms<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
    Managing ownership and privileges at scale<\/h2>\n\n\n\n
    Between these first two articles we’ve covered a lot of ground on PostgreSQL roles and security.<\/p>\n\n\n
    \n