> * there is no column level security – e.g. I want to show payment_total to admin, but not to user
I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box.
For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like:
SECURITY LABEL FOR user ON COLUMN invoice.payment_total
IS 'MASKED WITH VALUE $$CONFIDENTIAL$$';
Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking.
To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design.
I think what you want is dynamic data masking. RDMBSes like IBM DB2[1] and SQL Server[2] offer it out of the box.
For PostgreSQL, there's the postgresql_anonymizer[3] extension. For which you could do something like:
Last time I tried it though, it didn't play well with transaction variables (`current_setting(my.username)`), so you could not combine it with RLS logic and application users[4]. I'll be exploring an alternative on https://github.com/steve-chavez/pg_masking.To be fair, this discussion is more related to a PostgreSQL feature than PostgREST. PostgREST relies on PostgreSQL for all authorization logic by design.
[1]: https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-...
[2]: https://learn.microsoft.com/en-us/sql/relational-databases/s...
[3]: https://postgresql-anonymizer.readthedocs.io/en/latest/decla...
[4]: https://www.2ndquadrant.com/en/blog/application-users-vs-row...