Re: MySQL interview, no mention of PostgreSQL

xzilla(at)users(dot)sourceforge(dot)net (Robert Treat) writes:
> On Fri, 2003-10-17 at 07:23, Bruce Momjian wrote:
>> Neil Conway wrote:
>> > On Thu, 2003-10-16 at 12:54, Josh Berkus wrote:
>> > > While one could write a utility in Postgres to create/process the file, the
>> > > "live" version of pg_hba.conf *must* be outside the database. If our ACL
>> > > was in the database, then how would we know who has the rights to read the
>> > > ACL?
>> >
>> > I don't see why this is a show-stopping problem. Can you elaborate?
>>
>> We don't want to fire up a backend until we know this is a valid user.
>> You could easily bring a server to a standstill by just sending false
>> connection requests. Sure, you can still do that by flooding the
>> machine, but a database lookup is significantly more expensive than
>> checking a connection packet.
>
> <devils advocate>
> why not hav a guc available in postgresql.conf that switches
> authentication from a pg_hba.conf file to a pg_hba table inside the
> database? this would allow people to choose a database based
> authentication scheme if their willing to shoulder the "risks" involved,
> and would prevent database lockout since you could always flip the guc
> and restart the database to authenticate against the file to allow
> admins back into the system
> </devils advocate>

Hmm.

"Arcadius" also points out, with some legitimacy, that:

>And as one of the famous Codd's 12(or 13) laws says:
>All information about the RDBMS should be stored in the system catalog
>and accessible by using a well defined/structured language.....( ok,
>this is not exactly what the law says, but ... :-) )
>IMHO, as users and databases are part of the system catalog, I think
>it's a good idea to keep all that in consistent state in the RDBMS.

He makes an observation that is suggestive, at least to me:

>PS: There is no problem with pg_hba.conf if there is only few users
>in the system ...

The users that it is ABSOLUTELY VITAL to have managed via the .conf
file are the superusers. If things are broken, those are the users
that could conceivably fix things up. And that should be a relatively
small number of users.

In contrast, it would make a lot of sense to have "ordinary" users
managed within the DBMS.

To this end, a _thought_ would be for the pg_hba.conf configuration to
apply solely to superusers, and for ordinary users' access control to
be controlled inside the database. That would probably change "best
practices" a fair bit from what they are now, so I'm just suggesting
it as a "thought" as opposed to as a serious proposal.

Bruce Momjian commented...

>We don't want to fire up a backend until we know this is a valid
>user. You could easily bring a server to a standstill by just
>sending false connection requests. Sure, you can still do that by
>flooding the machine, but a database lookup is significantly more
>expensive than checking a connection packet.

Question: Is it _necessarily_ more expensive to do a database lookup
than to check [whatever data structure the config gets loaded into]?
If pummelling a server with false connection requests brings it to its
knees _because of the database lookups_, that would seem troublesome
for other reasons, no?
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)