3

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter.

Say you have a large set "X" of data that consists of multiple tables. For the sake of argument, let's say that "X" consists of all entries from an elaborate phone book.

You then create various databases for different applications, and each application takes a subset of "X" to build upon. It has no access to data of "X" outside the subset.

But if one application changes a tuple in its subset, this change should be propagated to the central store of "X" and thus be available to any other application accessing the same tuple.

To stay with the example of the phone book, application 1 might work with a subset of all people who are parents. Application 2 might work with a subset of all people under 30. These two subsets might intersect. So if application 1 changes the phone number of a parent under 30, this changed phone number should be pushed back to "X". And because application 2 uses the same tuple, it should have access to the updated version.

How do you solve this in practice? I'm using PostgreSQL myself but maybe there is a more universal solution to this question.

1

Use multiple schemas of one database.

You can use multiple databases, too, if you need to. That would allow a more thorough and secure separation, but you need to use functionality for cross-database access like SQL/MED or dblink. So let's keep it simple with separate schemas.

Create a master schema owned by some admin role and no access to other roles. Being logged in as admin:

CREATE SCHEMA x;
REVOKE ALL ON SCHEMA x FROM PUBLIC;

Put all your tables and data there, everything owned by your admin.

Create view(s) owned by the same admin role in each other schema where you need access, only to selected, relevant rows. Basic views are updatable by default in modern Postgres. The manual:

Access to tables referenced in the view is determined by permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering; see Section 38.5 for details.

This takes care of concurrency automatically.

As for "basic views", this was extended in Postgres 9.4. The release notes:

E.9.3.4.2. Views

...
Allow views to be automatically updated even if they contain some non-updatable columns (Dean Rasheed)

Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates. Now INSERTs, UPDATEs and DELETEs are supported, provided that they do not attempt to assign new values to any of the non-updatable columns.

Read the details at the link in the quote. It should work for your case: Create a "reference table" in each schema owned by the local role and join to the X tables to include only selected rows.

I suggest to additionally use WITH CHECK OPTION to restrict write operations to the scope of each view. The manual:

The CHECK OPTION may be used to prevent INSERT and UPDATE commands from creating such rows that are not visible through the view.

If you need more sophisticated / secure policies, consider the new row security features introduced with Postgres 9.5.

| improve this answer | |
  • Hi Erwin, thanks for your answer. So what you're saying is that I could have a schema "base", and then I could create schemas for each application that would start with views based on "base" but could be extended? I really know too little about schemas but I guess this could be an option if it worked. I should clarify, however, that the subsets are not created based on some specifiable rules (where city='xyz') but are created from manual selection. The information which tuples are affected would be stored in a reference table. I guess at that point we wouldn't speak of simple views anymore. – vic Mar 6 '16 at 22:50
  • @vic: Still simple enough. Consider the additions above. – Erwin Brandstetter Mar 9 '16 at 2:16

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.