Re: Proposal: Introduce row-level security templates

Hi Stojan,

> I do think there should be the option to attach it immediately to
> tables, something like `CREATE POLICY TEMPLATE <name> ATTACH TO
> <tables> AS ...` but it’s not a deal-breaker for me.

I would say that because templates are not active until they’re
> attached, the logic for “validating” the query part should come when
> the template is being attached on the table. So a non-qualifying
> `USING` clause would error out on the `ATTACH` command. Similarly,
> altering the policy template would force validation on the clause over
> the already attached tables to the policy.

That's an interesting idea. I believe that it could be achieved with some
modification, but I'm thinking about the preferred behavior on attaching
templates to tables: do we want to error out instantly if we encounter a
case where the qualifier isn't applicable to a particular table, or do we
let the template get attached to other tables silently?

I would also suggest looking into the ability to attach a template to
> a schema. There are some obvious benefits of this — creating a table
> in the schema automatically gets the correct RLS behavior without
> having to explicitly attach a template or policies, making it
> secure-by-default. There is some interesting behavior of this as well
> which _may_ be beneficial. For example, if you create a schema-wide
> template, say `user_id = current_user`, and you create a table that
> doesn’t have a `user_id` column — the creation would fail. This is in
> many practical situations beneficial, as it lessens the likelihood of
> creating a table that can’t be properly secured in that application
> context.

I like this idea, as a schema-level template would be beneficial in some
cases, but this change would introduce more rigidity or disruptions. For
example, if a table needs to be created in a schema without any
restrictions on access, it would fail as the schema now enforces RLS checks
on table creation. I do feel that this proposal has its benefits, but this
also introduces a binary/dichotomous decision: either you enable RLS on
each table in the schema, or you don't.

One way to solve this is to manually modify each table that doesn't need
RLS checks by disabling it: `ALTER TABLE <table_name> DISABLE ROW LEVEL
SECURITY;`, but I'm not sure if this is ideal, as this introduces more
operational/administration complexity.

1. Make the `ON table_name` part of the `CREATE POLICY` statement
> optional, which would create the “template.” This would require
> altering the internals of the policy-table relationship to support 0,
> 1, 2, … tables instead of the current 1. Again I have no idea how this
> is implemented internally, but it could be a fairly simple change
> without having to introduce new concepts, objects, and commands.

Interesting, what does `0` entail in this case? Current behavior is to
enforce a policy on a table, if that's made optional, would that mean if no
tables are specified in `CREATE POLICY`, would it be considered as a
schema-level policy?

Wouldn't it be better if we had a way to explicitly specify when a
schema-level policy is required to be created? With the proposed behavior,
there might be cases where users might accidentally trigger/enforce a
schema-level policy if they failed to specify any table names.

2. Have templates only as the object that enables the one-to-many
> relationship between a policy and table. For example, you could create
> a policy like `CREATE POLICY owned_by_user ON table ...`, and then you
> could do something like `CREATE POLICY TEMPLATE owned_by_user AS
> POLICY schema.table.owned_by_user ATTACH TO tables...`. So essentially
> the “template object” just references an already existing policy
> attached to a table, and it allows you to attach it to other tables
> too.

I believe that's possible by utilizing the system catalogs, and finding
references to the policy as you mentioned, but it's highly sensitive to
cases where the original policy is deleted, as now you can't refer to the
original policy. There can be modifications made to `DROP POLICY` to also
remove the top-level/parent template when the original policy is deleted,
but I'm not sure if that behavior is preferred.

Thanks,
Aadhav