How to safely allow arbitrary SQL queries in multi-tenant web applications

rls-security-multi-tennant.md

Can we use PostrgreSQL's row-level-security to enable arbitrary query execution security in secure multi-tenant web applications?

Can we break out and access another tenants information?

No, this is handled using schemas, ownership and roles (users). Using RLS does not impact leaking data between tenants. This allows us to give out SQL accounts that can run arbitrary queries without leaking data between tenants.

How can we protect against role or privileges escalation within a tenant?

Consider an application that has 3 user types (roles):

1. Student
2. Teacher
3. Administrator

Coarse permissions

We can enforce coarse permissions by creating a 1:1 mapping of application roles to database roles.

• Check constraints
• GRANT
• Row Level Security (RLS)

Role escalation

If a user was somehow able to execute arbitrary queries and maliciously used SET the worst that they can do is to impersonate another user with the same role (or lower).

Who can impersonate who?

	Student	Teacher	Admin	Developer
Student	✓	✗	✗	✗
Teacher	✓	✓	✗	✗
Admin	✓	✓	✓	✗
Developer	✓	✓	✓	✓

Ideas to prevent impersonation of another user of the same role:

1. Instead of abusing the GUC to pass the user_id from the webapp, we'll pass the session_id.
2. The session table would not be accessible to any roles used by the webapp.
3. Using a SECURITY DESCRIPTOR function that would extract the session_id from a GUC.
4. We cannot trust the GUC and should avoid using it directly in RLS rules
5. RLS rules should only use the fully qualified path to the get_application_userid() to prevent search_path from compromising security.
6. get_application_userid() should explicitly set the search_path for the sessions table and/or use the fully qualified name for the sessions table

Psuedo code

BEGIN;
    CREATE OR REPLACE FUNCTION get_application_userid()
        RETURNS integer AS $$
            SELECT user_id FROM fdw_sessions WHERE session_id = current_setting('session_id')
        $$ LANGUAGE SQL
        SECURITY DEFINER
        STABLE
        SET search_path = 'sandbox-school';

    REVOKE ALL ON FUNCTION get_application_userid() FROM PUBLIC;
    GRANT EXECUTE ON FUNCTION get_application_userid() TO spark;
COMMIT;

If properly implemented, in order to inpersonate another user an attacker would need to know the target user's session ID. Should an attacker have the session ID they can simply access the webapp using that session ID. This means your database is as secure as your session system. The attacker would need to first "own" your database, in which case all bets are off.

Questions/Commments?

Please join the discussion on this Gist in the comments section below.

CREATE TABLE IF NOT EXISTS sessions (session_id uuid PRIMARY KEY, user_id integer);

TRUNCATE sessions;

-- Populate 1,000,000 sessions
INSERT INTO sessions SELECT uuid_generate_v4() AS session_id, row_number() OVER () AS user_id  FROM generate_series(1, 1000*1000);

VACUUM sessions;

CREATE OR REPLACE FUNCTION get_application_userid()
        RETURNS integer AS $$
            SELECT user_id FROM sessions WHERE session_id = current_setting('app_name.session_id')::uuid LIMIT 1
        $$ LANGUAGE SQL
        SECURITY DEFINER
        STABLE;

CREATE TABLE IF NOT EXISTS secret_sauce (id serial primary key, user_id integer, secret uuid);

TRUNCATE secret_sauce;

-- Populate 10,000,000 secret sauces, not-clustered by user_id to try to imitate real conditions
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);

CREATE INDEX IF NOT EXISTS secret_sauce_user_id_idx ON secret_sauce (user_id);

VACUUM secret_sauce;

ALTER TABLE secret_sauce ENABLE ROW LEVEL SECURITY;

CREATE POLICY guc_security_definer ON secret_sauce
    USING (get_application_userid() = user_id);

-- This is the RLS user, make note of the index condition
EXPLAIN ANALYZE select * from secret_sauce;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using secret_sauce_user_id_idx on secret_sauce  (cost=0.69..48.53 rows=11 width=24) (actual time=0.099..0.107 rows=10 loops=1)
   Index Cond: (get_application_userid() = user_id)
 Planning time: 0.221 ms
 Execution time: 0.118 ms
(4 rows)

EXPLAIN ANALYZE SELECT * FROM secret_sauce WHERE user_id = 12489;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using secret_sauce_user_id_idx on secret_sauce  (cost=0.43..48.28 rows=11 width=24) (actual time=0.012..0.022 rows=10 loops=1)
   Index Cond: (user_id = 12489)
 Planning time: 0.064 ms
 Execution time: 0.082 ms
(4 rows)

BEGIN;

DROP TABLE sessions CASCADE;

CREATE TABLE IF NOT EXISTS sessions (session_id uuid PRIMARY KEY, user_id integer);


-- Populate 1,000,000 sessions
INSERT INTO sessions SELECT uuid_generate_v4() AS session_id, row_number() OVER () AS user_id  FROM generate_series(1, 1000*1000);

CREATE OR REPLACE FUNCTION get_application_userid()
        RETURNS integer AS $$
            SELECT user_id FROM sessions WHERE session_id = current_setting('app_name.session_id')::uuid LIMIT 1
        $$ LANGUAGE SQL
        SECURITY DEFINER
        STABLE;

DROP TABLE secret_sauce CASCADE;

CREATE TABLE IF NOT EXISTS secret_sauce (id serial primary key, user_id integer, secret uuid);

-- Populate 100,000,000 secret sauces, not-clustered by user_id to try to imitate real conditions

DO $$
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO secret_sauce (user_id, secret) SELECT row_number() OVER () AS user_id, uuid_generate_v4() AS secret FROM generate_series(1, 1000*1000);
    END LOOP;
END$$;

CREATE INDEX IF NOT EXISTS secret_sauce_user_id_idx ON secret_sauce (user_id);

ALTER TABLE secret_sauce ENABLE ROW LEVEL SECURITY;

CREATE POLICY guc_security_definer ON secret_sauce
    USING (get_application_userid() = user_id);

COMMIT;

VACUUM sessions;
VACUUM secret_sauce;

Hey- what is a GUC? I'm sorry to be a little daft here.
Also-I just posted an issue (that's really more of a feature request) that would make doing this with PostgREST much easier, it's #529-I think it would serve similarly to what you're asking for and be relatively performant- do you think so?

@davidkuep: GUC = grand unified configuration (see postgresql documentation)