A Postgres table, carefully protected behind a thick undergrowth of privileges and policies. Photo by ZACHARY PEARSON on Unsplash

A Beginner’s Guide to Role Inheritance and Policies for Postgres

Every Friday evening when I sit down to build out a new database schema, I always find myself looking up the same questions so I collected them here to save myself the trouble.

I’m calling this a “beginner’s guide” because every time I build something on Postgres I learn new things. I’ve deployed many of these practices into widely-available production applications so any feedback you can provide is valuable, especially if it’s critical.

User Types, defined as roles

Creating separate database roles for each type of user allows us to enforce a deep level of control and security for the underlying data. This is important for any multi-tenant or privacy-sensitive applications. These roles will ultimately instruct our interfaces on how they can access what from the database.

Roles I commonly use include some sort of reader, writer, and administrator. Many applications may require more, but these building blocks give us a good place to start.

Readers

These users have the least level of access and may not be able to mutate the database at all. They are often the most numerous, so ensuring that we limit their scope of access is critical. Fortunately, we can often use simply, table-level privileges to restrict them.

Writers

Writers are the users who create content in our applications; in some cases, they may be human and, in others, they may be services. In multi-tenant applications, all writers will be mutating the same tables so we will need policies to isolate their access to their own data.

Administrators

I like to start out each project pretending I don’t need this role until about 3 AM in the morning when I realize I need to query or mutate something on behalf of a user and have no way to do it. A great forcing function for this role is to restrict direct database access and create policies and privileges that allow our application administrators to execute each action they may need to take.

With these basic types in mind, let’s take a look at how role inheritance can simplify what privileges and policies we need.

Role Inheritance

Postgres has a fantastic role attribute called inheritance, which works almost the same way you would expect inheritance to work. There are a few gotchas, and it can be confusing to get started, so this is how I often begin:

Base Application User

This role will be granted privileges which all users will need, and that’s it. We’ll create it with the noinherit attribute to ensure we do not leak privileges, and we’ll grant it to our other application roles, which will inherit it:

CREATE ROLE application_base_user NOINHERIT;
CREATE ROLE reader INHERIT;
CREATE ROLE writer INHERIT;
CREATE ROLE administrator INHERIT;
GRANT application_base_user TO reader, writer, administrator;

Since each of our other roles inherit the base role they will receive the same privileges. Let’s create one more role before exploring what that inheritance means.

Authentication Role

In most cases, we won’t create an individual role for each of our application users. You can do this; but, since we often want additional attributes such as email which can’t be stored in Postgres’ native role tables, we’ll need an additional table or service to manage them. To meet these common requirements, we’ll use a single point of entry which is only allowed to assume one of our application roles.

CREATE ROLE authenticator NOINHERIT LOGIN WITH PASSWORD password;
GRANT reader, writer, administrator TO authenticator;

This reveals an important security concern: all of our users are logging into the database with the same role. It will be critical to ensure that users are not allowed to switch roles in their queries and mutations. As far as I’m aware, we can’t leverage any Postgres magic to prevent this, so the burden will fall to our application-layer code. If users need the ability to execute arbitrary SQL, a role should probably be created for each user. For example:

CREATE ROLE bob INHERIT LOGIN WITH PASSWORD password;
GRANT writer TO bob;

I won’t touch on that use case much more since it is not as common, and if your users need that level of access you probably already know everything in this article anyway.

Privileges

Privileges define what actions each role can take on an entire table. Let’s take a look at how our inheritance will work by creating some tables and granting access to them:

CREATE EXTENSION "uuid-ossp";
CREATE TABLE public_things (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  label TEXT NOT NULL,
  writer_id UUID NOT NULL,
  deleted_at timestamp without time zone
);
CREATE TABLE private_things (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  label TEXT NOT NULL,
  writer_id UUID NOT NULL
);
GRANT USAGE ON SCHEMA public TO application_base_user;
GRANT SELECT ON TABLE public_things TO application_base_user;
GRANT ALL ON TABLE private_things TO writer, administrator;

Now each of our roles can query public_things, while the administrator and writer roles are also allowed to do whatever they want to private_things. We can further embrace the inheritance by allowing our more privileged roles the ability to mutate public_things as well:

GRANT ALL ON TABLE public_things TO administrator, writer;

It may seem pointless to have initially granted SELECT on public_table to our more privileged roles now, but some use cases may only want to GRANT INSERT, UPDATE which will work with our approach as well. Remember that privileges are additive so we can’t GRANT ALL ON TABLE public_things to our base role and revoke specific actions from roles that inherit from it. Our schema will also be easier to read if we avoid using REVOKE in general; by limiting ourselves toGRANT it is clearer what each role is allowed to do and everyone who has to maintain this schema after we write it will like us more.

Policies

Now that each role has access to the tables they need we can have some fun writing policies that further limit each user to the rows that they own.

ALTER TABLE public_things ENABLE ROW LEVEL SECURITY;
CREATE POLICY write_public_things
ON public_things
AS PERMISSIVE
FOR ALL
TO writer
USING (
  writer_id = current_setting('our_application.user_id')
)
WITH CHECK (
  writer_id = current_setting('our_application.user_id')
);

In short, this policy will allow a user to do whatever they want to any row where the current_setting('our_application.user_id') matches the ID in the row. Let’s break that down:

1. ENABLE ROW LEVEL SECURITY can also be attributed to the table when it’s created; if we forget this the policy won’t run at all
2. AS PERMISSIVE means that rows matching this policy are granted to the user. We can add more permissive policies to expand this access, they stack like a logical OR operator. Policies are permissive by default so we could technically omit this
3. FOR ALL means this policy will run for INSERT, SELECT, UPDATE, and DELETE. We can alternatively specify a single one of those actions if we don’t want them all granted, but you’ll need to write the policy over again for each action you want to target
4. USING will be executed for anything that queries the table, such as SELECT or INSERT/UPDATE … RETURNING
5. WITH CHECK is executed for new data in INSERT and UPDATE, which is how we prevent someone from adding rows with someone else’s ID
6. current_setting('our_application.user_id') is just a bit of magic that enables many users to use the same writer role; our application layer will need to set this local variable before the user’s query is run. If you are using a separate role for each user you can replace it with something like writer_role = current_role. We must namespace our custom variable to avoid conflicting with reserved variables which is why we’re using our_application.*

A Useful Trigger

To further enhance this pattern, we can use a trigger to automatically set the writer_id attribute when the row is written:

CREATE FUNCTION set_writer_id() RETURNS TRIGGER AS $set_writer_id$
BEGIN
  NEW.writer_id = current_setting('our_application.user_id');
  RETURN NEW;
END;
$set_writer_id$ LANGUAGE plpgsql;
CREATE TRIGGER insert_public_thing
BEFORE INSERT ON public_things
FOR EACH ROW
EXECUTE PROCEDURE set_writer_id();

This, like everything else in this article, helps to simplify what our application layer needs to worry about; so long as the local variable for the user’s ID is set, the database is now handling the security aspect of ensuring that a user can only query and mutate their own data.

Restrictive Policies

You are probably now imaging lots of useful policies you can apply for your specific use case, so I’ll just mention one last useful feature:

ALTER TABLE public_things ENABLE ROW LEVEL SECURITY;
CREATE POLICY write_public_things
ON public_things
AS RESTRICTIVE
FOR SELECT
TO writer
USING (
  deleted_at is null
);

This policy will be run in addition to our first one, but only for SELECT statements. RESTRICTIVE policies are a logical AND which results in the following statement for writers selecting from the table of public things:

The writer_id is the same as the current user ID, AND the row does not have a deleted_at value.

Administrator Special Case

Sure, you can now go stack a bunch of awesome policies and triggers onto this role too. You can even go down the rabbit hole of adding some fancy triggers to set some additional columns you cook up, such asadmin_created, but that may be overkill for most use cases. For most, one additional role attribute may be all that you need for now:

ALTER ROLE administrator WITH BYPASSRLS;

As the name suggests, this attribute will effectively allow administrators to use all tables as though row level security was never enabled.

Application Layer Interface

The final and most important consideration is how to actually run your queries from your favorite application layer framework. The steps are simple:

1. Connect to the database using the authenticator role
2. Start a transaction for the user
3. Set the users role and user ID
4. Run their query
5. Commit the transaction

It should look something like this, once you’re connected:

BEGIN;
SET ROLE writer;
SET LOCAL our_application.user_id = ${userId};
${queryOrMutation};
COMMIT;

I like to pack the role and user ID into a JWT for the user when they authenticate and ship it in a header along with each request. When your application layer unpacks that JWT, the values can be trusted assuming you created your JWT securely.

In Summary

Postgres privileges and policies are a great way to bake our access control logic directly into the database. Though it is still advisable to implement security policies all the way up the stack, we can rely on these underlying definitions to serve as a strong line of defense against highly compromised applications. If needed, we can also give our users a much deeper level of access, such as an actual database role, without much additional effort.

For further reading, check out how I’ve used these strategies with Supabase, a fully managed, (and real-time), Postgres service.