Raw SQL Access for Users With Row-Level Security

We talk with a lot of SaaS companies that are encountering issues with their database. The most common issue we discuss relates to performance: either a need to keep scaling or, at times, just dealing with really intensive data needed by only a few customers and how to handle that.

And then, as you continue to scale and capture more data, you want to provide more value back to your customers.

At times, you might even consider giving raw SQL access to your largest and most important customers. Typically, controlling what data you give them via dashboards and canned reports is ideal — this way, you can control performance impact and other risks. But if you have extra large/important customers that require you to give them raw access to the data... then PostgreSQL (and thus Citus) has your answer.

Pro-tip: Don't grant access to all of your customers.

Enter: row-level security. Row-level security provides an extra layer of security as of Postgres 9.5, which allows you to restrict data access at the row level. Because Citus is a pure extension to Postgres, we're able to leverage all the great features of Postgres. In this case, row-level security allows you to create Postgres users and roles that only have access to a limited set of data.

Setting Up Our Events Schema

Let's first start with setting up our table. For this example, we're going to use an overly simplified events table:

CREATE TABLE events(
tenant_id int,
id int,
type text
);

SELECT create_distributed_table('events','tenant_id');

INSERT INTO events VALUES (1,1,'push');
INSERT INTO events VALUES (2,2,'push');

You'll notice that we have tenant_id on our table, and then we sharded our table based on that tenant_id. In the future, if we wanted to add more tables to our multi-tenant app, we would shard those tables on tenant_id, as well, and could then easily join on them and also enforce our row level security when we enable it.

Enabling Row-Level Security on Your Cluster

Now, we're going to run a few commands that will enable row-level security on both our coordinator and on all the Citus data nodes:

SET citus.enable_ddl_propagation to off;
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
SET citus.enable_ddl_propagation to on;
SELECT run_command_on_shards('events','ALTER TABLE %s ENABLE ROW LEVEL SECURITY;');

We'll also go ahead and grant access to our primary database user to have access to all records within the table:

CREATE POLICY admin_all ON events TO citus USING (true) WITH CHECK (true);
SELECT run_command_on_shards('events',$cmd$CREATE POLICY admin_all ON %s TO citus USING (true) WITH CHECK (true);$cmd$);

Generating Access Control for Users

With the foundation in place, we cam now take to granting access to the specific database users you've created.

CREATE POLICY user_mod ON events 
  USING (current_user = 'tenant_' ||tenant_id::text)
 ;
SELECT run_command_on_shards('events',$cmd$CREATE POLICY user_mod ON %s
  USING (current_user = 'tenant_' ||tenant_id::text)
 $cmd$);

And now you're all set. If you connect with your main database user, you still have access to all the data:

SELECT * from events;
 tenant_id | id | type
-----------+----+------
         1 |  1 | push
         2 |  2 | push
(2 rows)

Disconnect and reconnect with tenant_1 role and issue the same query, and you'll see the data that is returned is scoped only to that user's data:

SELECT * from events; 
 tenant_id | id | type
-----------+----+------
         1 |  1 | push

With Great Power Comes Great Responsibility

Row-level security offers a means to safely provide an expanded level of data access directly to your customers. It also provides access in a way that can impact performance, so it is encouraged to proceed with some caution. But the next time you need to grant raw access to the data within Citus, you now have the answer, thanks to the underlying mechanisms of Postgres.