I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view.
The distinction between A, B and C would rely on their IP address.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You could use the session information function
inet_client_addr(), with limited reach. It reports the IP the server is receiving the current connection from. This is only useful with direct connections to the database, though.
As proof of concept:
SELECT CASE WHEN inet_client_addr() << '192.168.1.32/30'::inet THEN 'foo' ELSE 'bar' END AS odd_column
As @Craig mentioned in a comment, consider the
security_barrier option for a view like that:
CREATE VIEW my_odd_view WITH (security_barrier) AS ...
The whole idea is rather dubious, though. The proper way would be to use distinct roles and distinct views with grants only for privileged roles.