2

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.

1 Answer 1

2

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.

2
  • You'll also probably want to use SECURITY BARRIER views if you want to actually hide the underlying structure. Aug 16, 2013 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, 2013 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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