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.


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.

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