Re: Virtual generated columns

On 09.01.25 09:38, jian he wrote:
>> 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
>
> I think this is expected.
> however once the user can access the pg_catalog,
> then he can use pg_get_expr
> figure out the generation expression.
>
> so here "bar" can figure out the column value of ccnum, i think.

Having access to the expression definition doesn't help you reverse the
computation, if the computation itself is not reversible.