Обсуждение: Re: [INTERFACES] Re: [HACKERS] pgbash-1.1.1 release

> I have to connect to any database to issue
> exec_sql -l database

A database name is required to connect (by libpq? protocol?).  This
can be cured by using template1 as a default. However, the issue
naturally develops into a more serious concern: are we going to have
inter-database queries in any plausible future?

Thanks,

--Gene

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The idea of adding a database interface to sh, although I can
immediately see its usefulness, srikes me funny. We are apparently
looking to have an elisp interface soon (thanks to Eric Marsden);
sooner or later, one will be finding the SQL code stashed everywhere
throughout the system. Would it then be wrong to expect the whole
backend embedded into the unix kernel? That would definitely swallow a
share of IBM's server market ;-> Now think about this: after wrapping
it up into the kernel, what will prevent you from building a Prolog on
top of it? Next thing that comes to mind would be a neural network
interface to the postgres-enabled prolog-backtracking unix kernel. 

The chances of winnning a Rube Godberg contest are already high and
rising ...

http://www.anl.gov/OPA/rube/rubeolive.html

"Gene Selkov, Jr." <selkovjr@mcs.anl.gov> writes:
>> I have to connect to any database to issue
>> exec_sql -l database

> A database name is required to connect (by libpq? protocol?).

It's a security issue: Postgres' authentication model is that you are
allowed to connect if your hostaddress + username + databasename match
any of the entries in the pg_hba.conf file (and, depending on the
authentication method, you may also have to supply a password or
something).  Although people often leave the database name as a
wildcard, it's possible to have tighter security on some databases
than others, and I'm sure you can see that that is a useful feature.

So, you cannot do anything, not even list the databases, without having
authenticated yourself, and that means specifying a database name that
you have the right to connect to.

> This can be cured by using template1 as a default.

This raises an interesting problem: we do recommend that in the docs,
and several of the Postgres shell scripts connect to template1 for lack
of a better idea.  But if I were running a secure installation I'd sure
as heck have template1 locked up tighter than a drum, with *no* access
to anyone but the DB admin.  So, how could we allow someone to list
databases without requiring access to template1 and without making any
assumptions about the names of other databases in the system?  I don't
see a way to do it in the current system design.  A possible approach is
to invent a second standard database name, maybe "template0", which
allows nothing but selects from pg_database and perhaps one or two other
operations we deem safe.

> However, the issue naturally develops into a more serious concern: are
> we going to have inter-database queries in any plausible future?

There has been some talk of that, and I'm sure that if it ever happened
you'd be required to authenticate yourself for each database.
        regards, tom lane