0

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.

  • The only thing I can think of, is to write a function that checks the client's IP address and then runs different SELECT statements based on that. – a_horse_with_no_name Mar 20 '14 at 21:53
  • @a_horse_with_no_name Client A makes direct request to the database. How can I intercept those requests ? – Stephan Mar 20 '14 at 23:28
3

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

<<.. "is contained within" operator.

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 ...

Details in the manual here and here.

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.

  • 2
    You'd want to use a security_barrier view too, to prevent leakage via user-defined-function predicates. – Craig Ringer Mar 21 '14 at 1:21
  • @CraigRinger: Excellent point. I added a bit to the answer. – Erwin Brandstetter Mar 21 '14 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.