Release 9.5 of PostgreSQL delivers many new features like upsert, new JSONB functions, new GROUPING functions, and more. While some of these like upsert or JSONB may be useful to many people, a number of these new features really only service edge cases. If you have the particular edge case a feature solves though then that new feature can be invaluable. RLS (Row Level Security) is one of these edge case features.

RLS does just what it says: it secures a row in a table. But, you do have to enable it for each table plus you need to commit to using database roles as a main security mechanism. That last part is the barrier but also the reason to use such a feature.

With RLS, you use the database tier to secure the data (at least for the enabled tables). Both multi-tenant tables and analytics schemas where users have general access to the database via a query tool are solid examples of when RLS makes sense.

Functionally RLS uses table level policies to enforce data protection. Often role names are embedded in a row's data. If they aren't then some key eventually maps to a role because the current_user for the connection usually drives the authorization to access the data. Basically, the policy constrains access and limits updates by constraining DML statements automatically. This is not unlike adding a WHERE clause to any statement regarding a particular table.

Change an Example to RLS

We have an example schema which has been referenced in a number of articles:

The differences in securing data with RLS versus the example are instructive. The above schema does not rely on RLS. It relies on the application layer to enforce security. It provides the application layer with a Users entity which it can use to authenticate and authorize. The application usually connects with an application level role that has elevated privileges and then sends authentication requests and constrained DML statements when necessary. With RLS, the Users entity becomes obsolete:

The pg_catalog.pg_roles in some logical way replaces the Users table and the application level role disappears. Then the application connects with each particular user to the database. Here each user is the constraint via table policies. And with this the DML is simpler since these policies already constrain the data.

Create Table, Alter Table, Create Policy: Enabling RLS

CREATE TABLE ratings2 (  
  user_role_name NAME,
  rating_type_name TEXT,
  artist_name TEXT,
  rating INTEGER
);

ALTER TABLE ratings2 ENABLE ROW LEVEL SECURITY;

CREATE POLICY ratings2_user ON ratings2  
  USING(user_role_name = current_user);

The above encapsulates the simplest example of RLS. The policy returns a boolean for each row. When true then row accessible.

This can be defined with more precision by declaring each type of statement and how it can be authorized. The above defaults to ALL. SELECT, INSERT, UPDATE, and DELETE are individually available too. If desired, SELECT and INSERT could be declared to create a readable append only table scoped to an individual user:

CREATE POLICY ratings2_user_select ON ratings2  
  FOR SELECT
  USING(user_role_name = current_user);

CREATE POLICY ratings2_user_insert ON ratings2  
  FOR INSERT 
  WITH CHECK(user_role_name = current_user);

Replacing the ratings2_user policy with the above two policies enables the append only read.

Even more specifically, hierarchies and groups can be accommodated with the pg_has_role(current_user, user_role_name, 'member') function. Drop the two policies above and replace with the following:

CREATE POLICY ratings2_user ON ratings2  
  USING(pg_has_role(current_user, user_role_name, 'member'));

If a user_role_name of a row is a group that the current_user is a member of then it will work. If the user_role_name has been granted to the current_user then that will work too.

CREATE ROLE group1;  
GRANT ALL ON ratings2 TO group1;

CREATE ROLE music1 LOGIN PASSWORD 'change';  
GRANT group1 to music1;

CREATE ROLE music2 LOGIN PASSWORD 'change';  
GRANT group1 TO music2;

With the above, music1 and music2 can insert into ratings2 each privately by setting user_role_name for any rows they insert to their respective role names. Plus, by setting the user_role_name to group1 on an insert they can both access the row.

This can get complicated but in certain situations can be invaluable. Also, when doing this don't forget that PostgreSQL abstracts users and groups into roles. The user role merely categorizes roles that have the LOGIN privilege. They are still just roles.

Not Standard SQL but Widely Implemented

Many database systems implement some form of RLS. It will not be the most used feature of PostgreSQL 9.5+ but in some situations Row Level Security is invaluable.