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