Row-level security & zero trust

Row-level security (RLS) enables one of the most precise manifestations of the "least-privilege per-request access decisions" part of NIST's zero trust (ZT) definition, applied to data as a resource.

With row-level security, we grant access to an authorized and authenticated user (or more broadly, subject, in ZT terminology) at the row-level of a table in a database.

How to implement

Row-level security is implemented with database policies (in ZT speak, the resource gateway part of a agent/gateway-based PEP is implemented in the database, based on policies from the control plane). For example, following policy can be declared in PostgreSQL to grant users view access based on the value of a column, in this case user_id:

create policy "Users can retrieve emails that belong to them"
? ? on emails for SELECT
? ? using ( auth.uid() = user_id );        

For PostgreSQL, do note that you will need to first enable row-level security for the desired table with:

ALTER TABLE public.emails ENABLE ROW LEVEL SECURITY;        

Row-level policy definitions can be applied to all CRUD (Create, Read, Update, Delete) operations. It can be used to adopt a plethora of zero trust principles, from explicit verification of identity & data classification to least privilege limits such as risk-based adaptive policies based on the data that are available to be queried through the database.

Advantages

Row-level security policies used well are the epitome of zero trust. The table row is arguably the most atomic data resource that most users will use. By declaring policies at this level, there is no implicit trust granted to any other parties besides the user and the administrator.

Why is this more secure? Imagine a breach to the web/application layer as seen above. For conventional implementation without row-level security, the attacker need only obtain the credentials of the application to get buffet level access to all of the data that the application had been granted. This typically includes all rows with data belonging to all users. This is because the trusted party (in this case, the application) in the trusted perimeter (the application layer network) had been breached. With row-level security, the attacker needs to obtain user-level credentials for each user, which is considerably more complex, significantly reducing the ROI of mounting further attacks.

Another advantage of row-level policy declaration is the possibility of using no-code web servers such as PostgREST. PostgREST turns your PostgreSQL database directly into a RESTful API. This saves a whole layer of work coding up the application layer API and ORM components. It also means that you maintain one source of truth, the database itself.

Disadvantages

Skipping all the Controller (of Model-view-controller) coding sounds great, but there is a catch. Developers may find it easier, although significantly more verbose, to deal with coding up the checks that otherwise will be done through RLS policies in their object-oriented or imperative languages of choice.

SQL is declarative. I won't start a debate, but if you're new to declarative coding, here's how a policy declaration to only allow authenticated users to insert into a group_members table look like (to help you see if there's a learning curve):

CREATE POLICY "Enable insert for authenticated users only" ON 
public.group_members FOR INSERT TO authenticated WITH CHECK
 ((group_id IN ( SELECT invites.group_id
? ?FROM public.invites
 ? WHERE (invites.email = ( SELECT usermetas.email
? ? ? ? ? ?FROM public.usermetas
? ? ? ? ? WHERE (usermetas.id = auth.uid()))))));        

The switch

The last thing to note is that when you enable row-level security on a table, EVERYTHING is disallowed by default unless explicitly granted through a policy declaration. We had seen earlier how to allow users access to rows. Now, imagine doing that for every table where RLS is turned on. In practice, this might be most of the tables in your project.

This is not a bad thing though, depending on what you're looking at. From a security point of view, it is definitely more secure. Nevertheless, if you're working on a fast-paced proof-of-concept or want to get into 'flow-mode' and spend more time on application features, be prepared to have to do a lot more non-functional declarations that will add a bit of friction to your flow.

That's all! I hope this article had given you some new insights into row-level security and how it relates to zero trust.

If you're keen to explore further, I'll suggest reading up about Trust Algorithms and explore how it can be applied to row-level security.