Changing the concept of a DATABASE

On 21 May 2012 20:40, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

>> This is important. I like the idea of breaking down the barriers
>> between databases to allow it to be an option for one backend to
>> access tables in multiple databases. The current mechanism doesn't
>> actually prevent looking at data from other databases using internal
>> APIs, so full security doesn't exist. It's a very common user
>> requirement to wish to join tables stored in different databases,
>> which ought to be possible more cleanly with correct privileges.
>
> That's really a whole different ball of wax and I don't believe what
> Robert was proposing would actually allow that to happen due to the
> other database-level things which are needed to keep everything
> consistent...  That's my understanding, anyway.  I'd be happy as anyone
> if we could actually make it work, but isn't like the SysCache stuff per
> database?  Also, cross-database queries would actually make it more
> difficult to have per-database roles, which is one thing that I was
> hoping we might be able to work into this, though perhaps we could have
> a shared roles table and a per-database roles table and only 'global'
> roles would be able to issue cross-database queries..

So collecting a few requirements from various places:

* Ability to have a Role that can only access one Database

* Allow user info to be dumped with a database, to make a db
completely self-consistent

* Allow databases to be transportable

* Allow users to access tables in >1 database easily, with appropriate rights.

I don't see any reasons why these things would be against each other.

The main objectives are to make a Database a more easily used
administrative grouping. At present, people who use multiple Databases
face many problems - they aren't as separate as you'd like, but
neither can they be ignored when required.

The idea of "one main database per session" is fine, but wiring it so
closely into the backend has a few disadvantages, many of them weird
internal things.

Are there arguments against those requirements before we spend time on
design/thinking?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services