Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
    
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 at 21:53
    
@a_horse_with_no_name Client A makes direct request to the database. How can I intercept those requests ? –  Alex Mar 20 at 23:28

1 Answer 1

up vote 3 down vote accepted

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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