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

For a new product we came along with a pattern, where all the data tables are stored in one schema (lets call it storage), while the clients connect to another one (here called api). The api schema defines a set of views and stored functions in order to retrieve and modify the data.

The thought about this is that the client does not see the real table structure, but only an abstraction of it, so that the storage schema can be changed without breaking any client.

Is this a good approach to achieve a stable client interface, or are there some hidden problems with such a practice.

share|improve this question
up vote 1 down vote accepted

With properly set privileges this can be a good solution. You can define your stored procedures WITH SECURITY DEFINER, where the role with which you create them has the necessary access privileges to the tables. Then you have an api_caller role which has only function execution rights for the procedures in the API schema.

share|improve this answer
You'll also probably want to use SECURITY BARRIER views if you want to actually hide the underlying structure. – Craig Ringer Aug 16 '13 at 6:58
Thank you both. WITH SECURITY DEFINER was the missing puzzle part where I struggled when defining the roles. Currently I don't feel that I need SECURITY BARRIER, since the views don't protect any data. But I keep it in mind if this part changes sometime later. – Rudi Aug 19 '13 at 9:14

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.