Re: Proposal: Introduce row-level security templates

[Aadhav Vignesh]

Hi Stojan,


> I do think there should be the option to attach it immediately to
> tables, something like `CREATE POLICY TEMPLATE  ATTACH TO
>  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  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

Proposal: Introduce row-level security templates

[Aadhav Vignesh]

Hi,

I'm looking at an open proposal to introduce row-level security policy
templates [0], and I have been making some progress on it.

The proposal aims to introduce templates for RLS policies, where the idea
is to allow users to define policies as a template, and apply it to
multiple tables. The proposed benefit is that there is reduction in
management overhead as there are situations where policies are similar
across multiple tables.

However, ever since I started working on this proposal, I noticed that
there are a few open questions I wanted to ask to existing contributors
regarding how this functionality should be exposed.

There are two ways to address this proposal:

1. Introduction of new keywords/statements

Expected usage:

-- create template
CREATE RLS TEMPLATE rls_template
USING (user_id = current_user)
WITH (SELECT);

-- attach templates to tables
ALTER TABLE employees
ATTACH RLS TEMPLATE rls_template;

ALTER TABLE customers
ATTACH RLS TEMPLATE rls_template;

-- alter template
ALTER RLS TEMPLATE rls_template
WITH (SELECT, UPDATE);

This option is non-intrusive, and can possibly operate in complete
isolation from existing row-level security logic, however, this also brings
the difficulty of introducing divergent behavior between normal RLS policy
creation and template creation as both of them would have a different SQL
syntax. This is undesired. This also requires users to learn the
newly-introduced syntax.

2. Modifying existing CREATE POLICY logic (or introduce a new CREATE POLICY
TEMPLATE statement)

We could consider adding a new statement called CREATE POLICY TEMPLATE with
the similar options but without the table name:

CREATE POLICY TEMPLATE name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER
} [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]

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.

or, we could integrate it in CREATE POLICY as an option (but in this case,
the table name is required, rendering the template creation
table-dependent):

CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER
} [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]
[ TEMPLATE template_name ]

Would love to hear any thoughts on the preferred way to introduce this
functionality.

Apologies for any mistakes I might have made in the above statements, I'm
fairly new to pgsql-hackers (this is my first post here!), and this is my
first time taking a look at existing RLS logic, so I might be wrong on the
interpretation of qualifier expr constructions.

Regards,
Aadhav

[0]: https://wiki.postgresql.org/wiki/GSoC_2024#Row-level_security_templates
[1]:
https://github.com/postgres/postgres/blob/bb5604ba9e53e3a0fb9967f960e36cff4d36b0ab/src/backend/commands/policy.c#L633-L659