endpoint: row-level security scheme

[erichanson]

A RLS policy is essentially a SQL where clause that is attached to the relation. Rethink RLS in terms of bundles, maybe a permissions table that's better than meta.policy and meta.policy_role.

[erichanson]

Given the bundle-centric nature of the architecture, we probably want to use bundles as the central organizing principle for privileges as well.

A question though, while it might make sense in some scenarios for bundles to setup their own security policies, in other scenarios they might be used in entirely different ways. Security policies are rows. Are these rows tracked by the bundle that they govern? Do bundles ship with a baked-in set of policies? Sometimes.

It's going to be essential to make sure that a row can only be tracked by a single bundle. Ideally this would be enforced globally (literally) but short of that, it raises the scenario that a bundle in a sense is "claiming" the rows that they will control privileges over, when they create a bundle.

Let's start with some simple options.

Choose from one of the following:
[  ] Superusers (only) have full control over this bundle's rows (no privileges assigned)
[  ] User-class based rules:
     - Users that are { anonymous | registered | superuser } can { select | insert | update | delete | all } from { relation | all }
     - ...

A user-class based rule might under the hood make a privilege like:

create policy x on endpoint.resource as permissive for select to "anonymous"
using (resource.id::text in (
    select (row_id).pk_value
        from bundle.head_commit_row
        where bundle_id = '2c28d445-e3dd-45ec-a81d-fdc84ddb045c')
);

For this to work, anonymous needs read access to the bundle.head_commit_row view. Do we want to do this as a global grant, or only allow them to access head_commit_rows that point to rows that they have actually been granted access to? This is kind of a chicken and egg thing, and bears some pondering.

So, if the user chose anonymous, select, all, this would create a policy for every relation that this bundle has rows in. These policies would need to be updated when a user tracks a row in a new relation. It would sure be nice to a) have a single policy that is applied to every relation, and b) not have to update the policy or anything else when rows are tracked or untracked from a bundle.

We could create a bundle.bundle_security_policy table for storing settings like the above.

create table bundle.bundle_security_policy (
    id uuid,
    role_id meta.role_id,
    action text, -- named action, not `command` like in meta.policy, as per SQL grammar
    relation_id meta.relation_id -- how do we specify ALL?  NULL?
);
insert into bundle.bundle_security_policy (role_id, action, relation_id) values
(meta.role_id('anonymous'), 'insert', meta.relation_id('chat','message'));

Then, a policy that checks this table might look like:

create policy x on chat.message as permissive for select to "anonymous"
using (select ....);

No. This doesn't make sense for insert. That's a different animal.

[erichanson]

We're going to want to show privileges to the user that already exist (in other bundles or as just untracked privileges manually granted) and affect rows in this bundle.