Proposal: Introduce row-level security templates

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