Re: Virtual generated columns

On Wed, 8 Jan 2025 at 16:14, Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> One thing I could use some review on is the access control handling and
> security in general. You can create virtual generated columns that have
> their own access privileges but which can read columns that the user
> does not have access to. Kind of like a view. This all appears to work
> correctly, but maybe someone wants to poke a hole into it.

That looks correct to me. Permissions are checked on the columns
mentioned in the query, not whatever columns the virtual generated
column's expression refers to. If it were a view, there'd be
additional checks that the view owner had the required privileges on
the referenced columns, but for virtual columns in a table, there is
no separate view owner, so no additional checks are necessary.

> Here is an example:
>
> create user foo;
> create user bar;
> grant create on schema public to foo;
> \c - foo
> create table t1 (id int, ccnum text, ccredacted text generated always as
> (repeat('*', 12) || substr(ccnum, 13, 4)) virtual);
> grant select (id, ccredacted) on table t1 to bar;
> insert into t1 values (1, '1234567890123456');
> \c - bar
> select * from t1; -- permission denied
> select id, ccredacted from t1; -- ok

Makes sense.

Regards,
Dean