Re: Virtual generated columns

[Peter Eisentraut]

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.