Re: Using tables in other PostGreSQL database

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 irrespective of 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 & having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a valid concern).

For example, any user may need to be able to create databases, add data & use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata 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(dot)marlowe(at)gmail(dot)com> 29/03/08 4:37 AM >>>
On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar
<swami(at)giveexam(dot)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.