Raw SQL access for users with row-level-security

Written by Craig Kerstiens
April 4, 2018

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 needs of 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

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

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;
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 now we can 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)

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, issue the same query, and you'll see the data that is returned is scoped only to that users 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.

Craig Kerstiens

Written by Craig Kerstiens

Former Head of Cloud at Citus Data. Ran product at Heroku Postgres. Countless conference talks on Postgres & Citus. Loves bbq and football.