Row Level Security (RLS) without RLS. Intrigued? Yes? Then keep reading

From a Security or Multi-Tenant perspective, having the ability to limit access on a row-by-row basis is a must, What happens though if your database of choice does not provide this feature out of the box? At Cockroach Labs we partner with a security provider called Satori to enable this feature when using our database. Still, sometimes there is a desire for our customers to try and do it themselves without the need for another vendor's product to be added to their solutions architecture stack.

This blog provides us with another way of achieving RLS using permissions and RBAC controls

Open a client and connect to a CockroachDB instance.

We will create a table and views for each user and show how only users that need to see their data are the only ones that can!

CREATE TABLE public.customers (
     id UUID NOT NULL DEFAULT gen_random_uuid(),
     forename STRING NULL,
     surname STRING NULL,
     “tenant” STRING NOT NULL,
     CONSTRAINT customers_pkey PRIMARY KEY (id ASC),
INDEX customers_forename_surname_idx (forename ASC, surname ASC) WHERE “tenant” = ‘adam’:::STRING,
INDEX customers_forename_surname_idx1 (forename ASC, surname ASC) WHERE “tenant” = ‘dan’:::STRING,
INDEX customers_forename_surname_idx2 (forename ASC, surname ASC) WHERE “tenant” = ‘gareth’:::STRING
 );

The above table gives us some basic customer data but also allocates a tenant column which is what we base our permissions on to give us the ability to exclude data from the people who don't need to see it.

 CREATE VIEW public.dan_customers (
     id,
     forename,
     surname,
     “tenant”
 ) AS SELECT
         customers.id, customers.forename, customers.surname, customers.tenant
     FROM
         p_test.public.customers
     WHERE
         “tenant” = ‘dan’;

 CREATE VIEW public.gareth_customers (
     id,
     forename,
     surname,
     “tenant”
 ) AS SELECT
         customers.id, customers.forename, customers.surname, customers.tenant
     FROM
         p_test.public.customers
     WHERE
         “tenant” = ‘gareth’;

What we have done is created views that we grant access to based on who needs to see that data and then we will secure access to the views.

select * from customers;
                  id                  | forename | surname  | tenant
---------------------------------------+----------+----------+---------
 02cc3f89-73b0-47e2-b098-d30562ab8d4b | Adam     | Spindler | dan
 31fd05cc-ce39-4229-97a8-c3b8a883c79e | Max      | Bashlawi | gareth
 90cc0fdc-cabe-4dd1-8f6b-2cd906d36850 | Dan      | Holt     | dan
 be72b4f1-17b2-4f4f-954b-7f6e39e8581c | Chris    | Dixon    | gareth
 d5f95021-aaa7-4fca-9336-414407f910bd | Paul     | Lovett   | dan

We add Grants to Select from dan_customers for our user account called dan. We also only allow user dan to INSERT into the customer's table too. We can see this implemented below.

show grants for dan;

 database_name | schema_name | relation_name | grantee | privilege_type | is_grantable
----------------+-------------+---------------+---------+----------------+---------------
 p_test        | public      | customers     | dan     | INSERT         |      f
 p_test        | public      | dan_customers | dan     | SELECT         |      f

show grants for gareth;
 database_name | schema_name |  relation_name   | grantee | privilege_type | is_grantable
----------------+-------------+------------------+---------+----------------+---------------
 p_test        | public      | customers        | gareth  | INSERT         |      f
 p_test        | public      | gareth_customers | gareth  | SELECT         |      f

Now let us run an explain analyze of the query to see what is happening beneath.

EXPLAIN ANALYZE Select * from dan_customer s;

info
-------------------------------------------------------------------------------------------------
 planning time: 706µs
 execution time: 3ms
 distribution: full
 vectorized: true
 rows read from KV: 3 (185 B, 1 gRPC calls)
 cumulative time spent in KV: 350µs
 maximum memory usage: 50 KiB
 network usage: 800 B (3 messages)
 sql cpu time: 119µs
 regions: eu-west-2  • render
 │
 └── • scan
       nodes: n10
       regions: eu-west-2
       actual row count: 3
       KV time: 350µs
       KV contention time: 0µs
       KV rows read: 3
       KV bytes read: 185 B
       KV gRPC calls: 1
       estimated max memory allocated: 20 KiB
       sql cpu time: 30µs
       estimated row count: 3 (60% of the table; stats collected 17 minutes               ago; using stats forecast for 16 minutes ago)
       table: customers@customers_forename_surname_idx1 (partial index)
       spans: FULL SCAN

There you have it. It even utilises the indexes we created too!

Give it a whirl..........