Re: Can we get SQL Server-like cross database queries

On Wednesday, June 3, 2020, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote:
> > There's no doubt it's useful, and it's also part of the SQL spec,
> > which says you can do catalog.schema.table. I would guess that we
> > might get that as a byproduct of any project to make PostgreSQL
> > multithreaded. That mountain moving operation will require us to get
> > rid of all the global state that currently ties a whole process to one
> > session and one database, and replace it with heap objects with names
> > like Session and Database that can be passed around between worker
> > threads.
>
> I am -1 on cross-database queries.
>
> I think it is a desirable feature to have databases isolated from
> each other, so you don't have to worry about a permission you forgot
> that allows somebody to access a different database.
>
> I think this is particularly relevant since all databases share the
> same users.
>
> I understand that sometimes the opposite would be desirable, but
> foreign data wrappers have alleviated that pain.
>

I agree with the conclusion but not so much with the premise. Even with
global users you still need to grant permissions to individual databases
and its debatable whether its “more safe” to prevent a user from directly
accessing a database in the “catalog.schema” reference manner if they can
do so with a direct login.

I agree with the general premise that modularity and isolation are
generally positive qualities, especially as scale grows, and that expending
considerable resources strictly for the goal of adding this capability to
the system is not a direction that I would be in favor of. Now, if the
prereqs for this feature also have other concrete benefits that are worth
working toward, and in the end the sum of those makes cross-database
queries a relatively simple matter, I would entertain putting in the last
10% of effort to become standard compliant.

David J.