Skip to content


In a subZero based API, the authorization is performed at the database level by leveraging role-based access control capabilities from PostgreSQL in combination with row-level security policies.

subZero's role in the authorization process is to provide the database with the identity of the current user performing the request. Armed with that knowledge, the database (using the rules you define) decides if the user can access (or modify) the data (tables/columns/rows) referenced by the query generated based on the http request.

Since every applications authorization policies are unique, it's not possible to define a "reference" for how to implement the authorization, however there is a specific path that can be followed that can be used to break down the process and make it a little more easy to grasp

Define your application roles

Determine the types of users that are relevant to your application. For example you can have anonymous users that access some parts of the api. Then you can have roles like employee, manager, administrator.

Define all your application roles in db/src/authorization/roles.sql


drop role if exists employee;
create role employee;
grant employee to authenticator;

Define high level access privileges for each role

Once we have our user types defined, we can specify to which tables and columns they have access and the level of that access.

You can specify either table level or column level access privileges.

-- administrator can do everything
grant select, insert, update, delete on data.payroll to administrator

-- managers can see everything but only update the salary column
-- (but not delete or insert any rows)
grant select, update(salary) on data.payroll to manager;

-- since there are no specific grants,
-- employees have no access at all to the payroll table

Define row level access privileges for ech specific user

The grant command gives the ability to restrict access on the table/column level but in almost all cases we need a mechanism to restrict the specific rows a specific user (not just the user types) can access. To implement this type of functionality we leverage row-level security policies (RLS)

-- enable rls for the table
alter table data.payroll enable row level security;

-- administrators can access all rows
create policy administrator_payroll_access_policy on data.payroll to administrator 
using (true);

-- managers can see all rows but modify only specific rows for employees they manage
create policy administrator_payroll_access_policy on data.payroll to manager 
using (true) -- this section is for reading
with check ( -- this section is for writing
    user_id in ( select id from data.users where manager_id = request.user_id() )

Tables vs views as api endpoints

In the above examples, we defined grans and policies for our application user types that refer to the tables holding the data (data.payroll). These type of security policies are very clear and easy to understand but for them to work as is, we would have to have the tables themselves exposed as api endpoints, however by doing that, we loose a couple of advantages available to us if we chose to expose as api endpoints only views. It's up to you to decide between these two major directions

  • tables as api endpoints

    • advantage - easy to define and understand authorization policies

    • disadvantage - api coupled with data model, hard to maintain backward compatibility (though can be worked around using generated columns), bigger potential to generate migrations that affect the data

  • views as api endpoints

    • advantage - api decoupled from data model, ability to version the api and maintain backward compatibility, smaller chance of deleting the data when generating migrations

    • disadvantages - authorization policies that require an intermediate role ( usually called api ) that acts as the api views owner and as such creates a level of indirection when defining the authorization policies

Using request properties in RLS policies

You might have notices in the above example the part request.user_id(). This function when called will return id of the current user that is performing the request. Functions of this type (that return some property of the current request) can be used to implement dynamic security policies that take into account properties of the current request

Below is a list of functions that can be used for such purpose.

Function Description
request.jwt_claim(c text) returns text Returns a claim value from the JWT payload
request.jwt_claim('exp')::int > 1614596747
request.cookie(c text) returns text Returns the value of a cookie
request.cookie('SESSIONID') not null
request.header(h text) returns text Returns the value of a header
request.header('X-My-Custom-Header') = 'test'
request.user_id() returns int Returns the current user id (which comes from the jwt payload)
request.user_id() = 10
request.user_role() returns text Returns the current user role (which comes from the jwt payload)
request.user_role() = 'manager'
request.env_var(v text) returns text Returns an arbitrary env var (GUC variable)
request.env_var('request.jwt.claim.role') = 'manager'