I have mixed feelings,
I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this
restriction.Business cases & preferences do not necessarily follow database design preferences or capabilities, so
irrespectiveof whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is
possible.Enough such users & the capability may well be implemented.
I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres &
havingto shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we
wantto increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a
validconcern).
For example, any user may need to be able to create databases, add data & use referentials in a corporate database of
lookuprecords. Using schemas, everyone needs to have create table privileges to the database by default, then this
needsto be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to
corporatemetadata for data integrity rules to be applied to any database any user creates. Agreed, not a common
requirement,but one where schemas are less flexible & less secure.
Cheers,
Brent Wood
>>> "Scott Marlowe" <scott.marlowe@gmail.com> 29/03/08 4:37 AM >>>
On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar
<swami@giveexam.com> wrote:
> I am fairly new to Postgres. However, I have to say that I agree with
> Barry's comments.
The real problem here is that you are not using the db properly. You
should have one db with all these data in it in different schemas.
PostgreSQL provides you with the ability to segregate these data via
schemas and fine grained (by the table) ACLs.
Your refusal to use multiple schemas in one database due to some
perceived problem with them all being in the same database is what's
causing your issues.
Put your data into various schemas in one database and you can then
use access control to decide who sees what.
