DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Raw SQL Access for Users With Row-Level Security

Raw SQL Access for Users With Row-Level Security

Row-level security offers a way to safely provide an expanded level of data access directly to customers. It also provides access in a way that can impact performance.

Craig Kerstiens user avatar by
Craig Kerstiens
·
Apr. 09, 18 · Database Zone · Tutorial
Like (2)
Save
Tweet
4.88K Views

Join the DZone community and get the full member experience.

Join For Free

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.

security Database sql Data (computing)

Published at DZone with permission of Craig Kerstiens, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Python 101: Equality vs. Identity
  • Component Testing of Frontends: Karate Netty Project
  • Pub/Sub Design Pattern in .NET Distributed Cache
  • MEAN vs MERN Stack: Which One Is Better?

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo