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¶
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.
|request.jwt_claim(c text) returns text||Returns a claim value from the JWT payload
|request.cookie(c text) returns text||Returns the value of a cookie
|request.header(h text) returns text||Returns the value of a header
|request.user_id() returns int||Returns the current user id (which comes from the jwt payload)
|request.user_role() returns text||Returns the current user role (which comes from the jwt payload)
|request.env_var(v text) returns text||Returns an arbitrary env var (GUC variable)