Re: MySQL interview, no mention of PostgreSQL

Josh Berkus wrote:

> Arcadius,
>
>
>>Once, I've asked about the pg_hb.conf file ....
>>IMHO, writing an app for parsing/editing that file *may* be a problem
>>for those who develop control panels used by web hosting companies ....
>>
>>IMHO, moving the info in pg_hb.conf into the DB itself may attract
>>control panel developers.
>
>
> 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?

Privileges could be used to solve this problem. And MySQL has managed to
get around it ....It seems they have a table named host(s) in the system
catalog where there is a combination of user<->host<->DB.

Systems which store their ACLs in the database (MSSQL) are continuously
> vulnerable to attacks that piggy-back on the authentication process to gain
> entry to the database, e.g. the "Slammer" worm.
>
> Also, users would risk a permanent fatal lockout if they mis-configure pg_hba.

In case a DB is used for storing the config, whenever a new user is
created, he should be allowed to connect to the server from localhost
.... and if he wants to connect from more hosts, either the superuser
adds a new host or GRANTs privilege to him to do it.
(as stated above, MySQL has some system table(s) that allow something like:
* user1 can connect to DB1 from host1
* user1 can connect to DB1 from host2
and new rows can be added as needed...
)

And in the worst case. if the superuser somehow manages to loose access
to the DB, a command-line utility could be used(as suggested by Neil)

Anyway, the MySQL model is worth having a look at....
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.

PS: There is no problem with pg_hba.conf if there is only few users in
the system ...
But when the number of users start growing, then editing/managing
pg_hba.conf becomes a bit tedious....and there is noway/(no supported
way) to allow individual users to do it.
PostgreSQL is a open source project....so I could start working on this
... but unfortunately, my skill is not there yet :-) ... I'm still
working on it :-)

Regards.

Arcadius.