Re: Proposal: Introduce row-level security templates

Hi Aadhav Vignesh,

Interestingly, Alexander asked for ideas for GSoC projects at Supabase
(I'm on the Auth team), and I proposed the RLS templates idea. As you
already pointed out, the idea comes out of us seeing how RLS policies
are used in real-life and the pain points associated with managing a
non-trivial number of them.

I am not a code contributor to Postgres, so please excuse my lack of
knowledge on how internals work or what's best to implement.

From your original message:
https://www.postgresql.org/message-id/CAMuaUMJ10_4CDxtHOTHbp%2BY%2Bh2uR2wxcVtJPbBvp9A9Njs5kUA%40mail.gmail.com

In my opinion, as a user of the policies, I prefer the direction of
option (2) with `CREATE POLICY TEMPLATE`. It’s much closer to the
existing `CREATE POLICY` statements, a lot of the internals could
probably be reused, which would make the project move along faster.

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 don’t really know enough about how this actually works internally,
but on this point:

> The major challenge here is the construction of the qualifiers for the
> policy, as the entire process [1] relies on a table ID, however, we don’t
> have access to any table names in this statement.
>
> I also find the aspect of constructing qualifiers directly from the
> plain-text state less ideal, and I honestly have no clue if this is
> possible.

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.

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. On the flip side, it’s probably going to be challenging to
implement this and there may be other implications too (like adding it
in `ALTER TABLE`, etc).

Some fringe ideas that I also think are worth exploring are:

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.
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.

Let me know what you think!

(Apology if this email is not threaded in your email client, I'm
writing it after subscribing to the list, which means the Reply option
is not available.)