Re: Proposal: Introduce row-level security templates

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