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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '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
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() << ''::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
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 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.