Skip to content

Set up lower-privilege service role and repo table RLS policy

Warren Gifford requested to merge flying-robot/setup-repo-restrictions into main

Created by: flying-robot

This introduces an sg_service role to Postgres, which we can use as a target for row-level (or column-level) security policies[0].

As noted, the services will SET ROLE sg_service on startup to assume the role, and then configure rls.user_id and rls.permission on a per-query basis for the requesting actor. This will provide Postgres with enough information to decide which rows to return/update/etc.

# begin; set role sourcegraph; set local rls.user_id=1; set local
  rls.permission='write'; select count(1) from repo where private; commit;

  ┌───────┐
  │ count │
  ├───────┤
  │    36 │
  └───────┘
  (1 row)
  
  Time: 0.282 ms
  
# begin; set role sg_service; set local rls.user_id=1; set local
  rls.permission='write'; select count(1) from repo where private; commit;
  
  ┌───────┐
  │ count │
  ├───────┤
  │     4 │
  └───────┘
  (1 row)
  
  Time: 0.282 ms

This mechanism is intended to lower the privilege of the services (such as frontend) without developers having to remember to invoke particular code paths to ensure our security protocols are maintained.

# select rolname, rolsuper, rolinherit, rolbypassrls from pg_roles where
  rolname in ('sg_service', 'sourcegraph');
  
  ┌─────────────┬──────────┬────────────┬──────────────┐
  │   rolname   │ rolsuper │ rolinherit │ rolbypassrls │
  ├─────────────┼──────────┼────────────┼──────────────┤
  │ sourcegraph │ t        │ t          │ f            │
  │ sg_service  │ f        │ t          │ f            │
  └─────────────┴──────────┴────────────┴──────────────┘
  (2 rows)
  
  Time: 0.568 ms

[0] https://sourcegraph.atlassian.net/browse/COREAPP-109

Merge request reports