Postgresql – Hide column values for some rows in auto-updatable views while retaining the “write” capability for them in PostgreSQL

postgresqlview

Suppose i have a view defined like this

create or replace view clients as
    select 
    id, name, 
    case app_user_type() -- hide column content for everyone except the administrator
        when 'administrator' then address 
        else null 
    end as address
from data.clients

The purpose is to hide the contents of the address column in some cases (current_user <> 'administrator').

The problem is that the "address" column becomes readonly.
Is there a way to have the same effect (hide the column value) while retaing the updatable property?

Best Answer

I went with something like this

create or replace view clients as
    select id, name, address
    from data.clients;
grant select (id, name) on clients to employee;

The only downside is that for employee role select * will fail, but for my use case, specifying the columns is ok

Related Question