Thread: MySQL interview, no mention of PostgreSQL

Here's an interview in Linux Magazine that doesn't even mention
PostgreSQL. I wouldn't expect the interviewee to mention it, but it's
frustrating that the interviewer didn't even bring it up.

http://www.linux-mag.com/2003-07/monty_01.html

[Note: you have to manually increment the URL to go to the subsequent
pages. I didn't see a "next page" link.]

The most frustrating thing about it is that the MySQL guy is talking
about the big plans for all the new features MySQL will have, and it
reads more like PostgreSQL's HISTORY file (note: I stole that analogy
from someone else).

It would be nice if we had some mindshare among the LAMP crowd. Does
anyone think we can convince O'Reilly to change LAMP to squeeze in
PostgreSQL?

Linux-Apache-MySQL-PostgreSQL-PHP-Python-Perl?

I bet the win32 port will help advocacy a lot. The other thing that
would help I think would be to make it as friendly as possible for ISPs
to do virtual hosting. Schemas are probably helping that a lot already,
and the only other thing I can think of in that area would be resource
monitoring/control. I'm not suggesting that MySQL is better for virtual
hosting, but perhaps if PostgreSQL makes it easier than that will give
us an edge.

    Regards,
        Jeff Davis

The world rejoiced as jdavis-pgsql@empires.org (Jeff Davis) wrote:
> Here's an interview in Linux Magazine that doesn't even mention
> PostgreSQL. I wouldn't expect the interviewee to mention it, but it's
> frustrating that the interviewer didn't even bring it up.
>
> http://www.linux-mag.com/2003-07/monty_01.html

I saw that on the newsstand back in July; I would think it a perfectly
sensible idea for someone to rustle up Linux Magazine editors and Core
Team Folk to see if there could be a good PostgreSQL article fall out
of that.

In fairness, it would be absolutely appropriate for _that_ interview
to try to avoid making any mention of Certain Other Databases.
--
"cbbrowne","@","cbbrowne.com"
http://www.ntlug.org/~cbbrowne/linuxxian.html
If a cow laughed, would milk come out its nose?

On Tue, 14 Oct 2003, Jeff Davis wrote:

> I bet the win32 port will help advocacy a lot. The other thing that
> would help I think would be to make it as friendly as possible for ISPs
> to do virtual hosting. Schemas are probably helping that a lot already,
> and the only other thing I can think of in that area would be resource
> monitoring/control. I'm not suggesting that MySQL is better for virtual
> hosting, but perhaps if PostgreSQL makes it easier than that will give
> us an edge.

'K, I've been doing virtual hosting with PostgreSQL for 8 years now (god,
I feel old) ... what is hard about it?

In fact, we run MySQL (ack!) for some clients, due to their requirements,
and with the recent changes in our IPs, we're having a bugger of a time
with IP based access and getting those clients switched to the new IPs
then we had with PostgreSQL (MySQL uses an SQL based ACL system for
connections, apparently, vs our 'text based, sed changable pg_hb.conf
file') ...

Jeff Davis wrote:
> It would be nice if we had some mindshare among the LAMP crowd. Does
> anyone think we can convince O'Reilly to change LAMP to squeeze in
> PostgreSQL?
>
> Linux-Apache-MySQL-PostgreSQL-PHP-Python-Perl?

Jeff, there is already a project similar to LAMP. It's named FreeGee and
is a bundle of: Python, PostgreSQL, wxWindows, Apache, omniORB, et al.

Platform is available as Beta release, but still start-up. Finals should
include 7.4 and newer versions of other integrated software. Preview
project site in English at:

http://www.go-gen.com/FG_EN/index_html.html

If you have any questions or comments, please feel free to tell.

@all: you may want to proof-read especially the information about
PostgreSQL (7.3) at the site, please see 'Benefits' and 'Modules/Database'

> I bet the win32 port will help advocacy a lot. The other thing that
> would help I think would be to make it as friendly as possible for ISPs

Yes, win32 port would help a lot. We develop a SDK to generate
FreeGee-based business software (Model Driven Architecture), but it
can't be used on Windows, so there is no local instant testing of
generated services possible.

Greetings,
Anastasios


--

http://www.go-gen.com/
http://sourceforge.net/projects/freegee/

On Tue, Oct 14, 2003 at 10:59:03PM -0700, Jeff Davis wrote:
> I bet the win32 port will help advocacy a lot. The other thing that
> would help I think would be to make it as friendly as possible for ISPs
> to do virtual hosting. Schemas are probably helping that a lot already,
> and the only other thing I can think of in that area would be resource
> monitoring/control. I'm not suggesting that MySQL is better for virtual
> hosting, but perhaps if PostgreSQL makes it easier than that will give
> us an edge.

I'd think a 'translation' guide would help just as much, if not
more. A guide that says in Oracle, you use 'describe tablename', in
Postgres, you use '\d tablename'. It's there, it's just different.
Same goes for MySQL stuff. I know this was one of my major
frustrations when learning Postgres, is just the minor differences
like that. I realize that this stuff is available via other means,
but having a translation guide would be very helpful as well.

Also, keep in mind that a lot of developers aren't DBA's. Many
people that use the 'LAMP' platform have only used MySQL, and maybe
a handful of other things. They're used to writing their
applications to have a fair amount of database logic in the
application itself, simply because it's not possible with MySQL.

What I had in mind on this topic would be some simple
databases and documentation  that
would outline some of the more advanced features of Postgres, like
triggers, stored proceedures and so on. First, describing what the
function is, or what it does, then moving on to why you should use
it. I suppose, an 'Advanced SQL in a nutshell' guide.

Just my $.02 worth. :)

Tim

Anastasios Hatzis wrote:

>
> Jeff Davis wrote:
>
>> It would be nice if we had some mindshare among the LAMP crowd. Does
>> anyone think we can convince O'Reilly to change LAMP to squeeze in
>> PostgreSQL?
>>
>> Linux-Apache-MySQL-PostgreSQL-PHP-Python-Perl?
>

I am talkin with O'Reilly already about this very thing.




--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

Tim Conrad wrote:
> On Tue, Oct 14, 2003 at 10:59:03PM -0700, Jeff Davis wrote:
>
>>I bet the win32 port will help advocacy a lot. The other thing that
>>would help I think would be to make it as friendly as possible for ISPs
>>to do virtual hosting. Schemas are probably helping that a lot already,
>>and the only other thing I can think of in that area would be resource
>>monitoring/control. I'm not suggesting that MySQL is better for virtual
>>hosting, but perhaps if PostgreSQL makes it easier than that will give
>>us an edge.
>
>
> I'd think a 'translation' guide would help just as much, if not
> more. A guide that says in Oracle, you use 'describe tablename', in
> Postgres, you use '\d tablename'. It's there, it's just different.
> Same goes for MySQL stuff. I know this was one of my major
> frustrations when learning Postgres, is just the minor differences
> like that. I realize that this stuff is available via other means,
> but having a translation guide would be very helpful as well.


Yes. Each time I need to search something about query I use the book
"LAN Times Guide to sql - Osborne" and I find in it this translation
guide for several DB. Some time it is what I need.

>
> Also, keep in mind that a lot of developers aren't DBA's. Many
> people that use the 'LAMP' platform have only used MySQL, and maybe
> a handful of other things. They're used to writing their
> applications to have a fair amount of database logic in the
> application itself, simply because it's not possible with MySQL.
>

One time a software is MySQL compliant it is hard to change even this
the "LAMP"


> What I had in mind on this topic would be some simple
> databases and documentation  that
> would outline some of the more advanced features of Postgres, like
> triggers, stored proceedures and so on. First, describing what the
> function is, or what it does, then moving on to why you should use
> it. I suppose, an 'Advanced SQL in a nutshell' guide.
>
> Just my $.02 worth. :)
>
> Tim
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

I think it is not harder to optimize Postgres than MySql if there is a
very good documentation about.
In a job, I used MySQL and when the time of optimization became, we
needed the help of a MySQL expert from Mysql inc.

Bruno

Jeff,

> Here's an interview in Linux Magazine that doesn't even mention
> PostgreSQL. I wouldn't expect the interviewee to mention it, but it's
> frustrating that the interviewer didn't even bring it up.
>
> http://www.linux-mag.com/2003-07/monty_01.html

Why should he bring it up?   It's an interview with Monty, not a database
survey.

I know the editor of Linux Magazine, and he is not in any way biased toward
MySQL ... except that their crowd produce more articles.   The answer to this
article is not to hassle LM, but rather for one of us to work on a feature
article on 7.4 for the release.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 2003-10-15 at 10:46, Joshua D. Drake wrote:
> Anastasios Hatzis wrote:
>
> >
> > Jeff Davis wrote:
> >
> >> It would be nice if we had some mindshare among the LAMP crowd. Does
> >> anyone think we can convince O'Reilly to change LAMP to squeeze in
> >> PostgreSQL?
> >>
> >> Linux-Apache-MySQL-PostgreSQL-PHP-Python-Perl?
> >
>
> I am talkin with O'Reilly already about this very thing.
>
see sig...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Wed, 2003-10-15 at 02:47, Marc G. Fournier wrote:
> On Tue, 14 Oct 2003, Jeff Davis wrote:
>
> > I bet the win32 port will help advocacy a lot. The other thing that
> > would help I think would be to make it as friendly as possible for ISPs
> > to do virtual hosting. Schemas are probably helping that a lot already,
> > and the only other thing I can think of in that area would be resource
> > monitoring/control. I'm not suggesting that MySQL is better for virtual
> > hosting, but perhaps if PostgreSQL makes it easier than that will give
> > us an edge.
>
> 'K, I've been doing virtual hosting with PostgreSQL for 8 years now (god,
> I feel old) ... what is hard about it?
>
> In fact, we run MySQL (ack!) for some clients, due to their requirements,
> and with the recent changes in our IPs, we're having a bugger of a time
> with IP based access and getting those clients switched to the new IPs
> then we had with PostgreSQL (MySQL uses an SQL based ACL system for
> connections, apparently, vs our 'text based, sed changable pg_hb.conf
> file') ...
>

That's why I said "I'm not suggesting that MySQL is better for virtual
hosting...". I've been virtual hosting with PostgreSQL for years also
(not 8 years though, that's impressive).

I was just kind of thinking out loud. It would be kind of interesting if
there were resource controls, but I think that Tom already said that
would be too resource-intensive and not worth it (although I don't
recall the exact context).

Regards,
    Jeff Davis

"Arcadius A." <ahouans@sh.cvut.cz_!!!NOSPAM!!!> writes:
> 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.

... Then create a table, along with a trigger/procedure that can dump
the contents out to pg_hba.conf, and initiate a "kill -HUP".

Shouldn't be /too/ terribly difficult to implement.

And that would indeed be somewhat useful.
--
let name="cbbrowne" and tld="libertyrms.info" in name ^ "@" ^ tld;;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

Marc G. Fournier wrote:
> On Tue, 14 Oct 2003, Jeff Davis wrote:
>
>
>>I bet the win32 port will help advocacy a lot. The other thing that
>>would help I think would be to make it as friendly as possible for ISPs
>>to do virtual hosting. Schemas are probably helping that a lot already,
>>and the only other thing I can think of in that area would be resource
>>monitoring/control. I'm not suggesting that MySQL is better for virtual
>>hosting, but perhaps if PostgreSQL makes it easier than that will give
>>us an edge.
>
>
> 'K, I've been doing virtual hosting with PostgreSQL for 8 years now (god,
> I feel old) ... what is hard about it?
>
> In fact, we run MySQL (ack!) for some clients, due to their requirements,
> and with the recent changes in our IPs, we're having a bugger of a time
> with IP based access and getting those clients switched to the new IPs
> then we had with PostgreSQL (MySQL uses an SQL based ACL system for
> connections, apparently, vs our 'text based, sed changable pg_hb.conf
> file') ...

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.

Regards.

Arcadius.

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?  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.

--
Josh Berkus
Aglio Database Solutions
San Francisco

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?

>   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.

How does storing ACLs in the database have anything to do with Slammer,
which exploited some buffer overruns in the UDP authentication service
used by SQL server?

> Also, users would risk a permanent fatal lockout if they mis-configure pg_hba.

There are plenty of ways to get around that, however (e.g. a
command-line tool that effectively started a standalone backend and
allowed the DBA to bypass the ACL system).

-Neil

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.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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>

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> 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>

I guess we could do it, but more easily we could dump a table to the
output file pg_hba.conf just like we do for pg_pwd and pg_group now.
It could be a global table like pg_shadow and pg_group.  Of course, you
have the problem of getting the database started to modify the table.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Fri, 2003-10-17 at 07:23, Bruce Momjian wrote:
> We don't want to fire up a backend until we know this is a valid user.

We do the fork() before we even look at the client's connection packet,
so I'm sceptical that going through all that trouble really makes us
significantly more resistant to a DOS attack.

-Neil

On Fri, 17 Oct 2003, Neil Conway wrote:

> On Fri, 2003-10-17 at 07:23, Bruce Momjian wrote:
> > We don't want to fire up a backend until we know this is a valid user.
>
> We do the fork() before we even look at the client's connection packet,
> so I'm sceptical that going through all that trouble really makes us
> significantly more resistant to a DOS attack.

The point is that its alot faster for the backend to open a text file and
parse that, then it is to open up a full database connection just to SQL
query a table to see if the person is allowed to open the database
connection in the first place ...

Then again, doesn't that almost defeat the purpose of checking in the
first place? :)

On Fri, 2003-10-17 at 15:21, Marc G. Fournier wrote:
> On Fri, 17 Oct 2003, Neil Conway wrote:
> > We do the fork() before we even look at the client's connection packet,
> > so I'm sceptical that going through all that trouble really makes us
> > significantly more resistant to a DOS attack.
>
> The point is that its alot faster for the backend to open a text file and
> parse that

Actually, we don't do that: the file is pre-parsed and already in
memory, so we just binary search through it.

And yes, I realize that this is somewhat faster than doing a system
catalog lookup. I'm just sceptical that the in-memory search is THAT
much faster that it's worth worrying about, considering (a) we need to
do the fork() regardless, which is expensive (b) the content of the
system catalog will almost certainly be in the buffer pool. (The fact
that the code in question is (IMHO) rather ugly doesn't add to my
sympathy for it...)

-Neil

Christopher Browne wrote:
> "Arcadius A." <ahouans@sh.cvut.cz_!!!NOSPAM!!!> writes:
>
>>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.
>
>
> ... Then create a table, along with a trigger/procedure that can dump
> the contents out to pg_hba.conf, and initiate a "kill -HUP".
>
> Shouldn't be /too/ terribly difficult to implement.
>
> And that would indeed be somewhat useful.

Christopher, thanks for the input :-)

But when you have 100+ user accounts in the RDBMS, is It secure to be
dumping a DB to a text file?(remember, system can fail while data is
being written to the text file .... )
And do not forget that there is a defined order for the rules in the
pg_hba.conf file, so,it's not obvious to just dump a DB into it...

BTW: assuming that your trigger/procedure works well without corrupting
  pg_hba.conf, then what's the point of keeping the pg_hba.conf file and
duplicating data since all the info are already available in the RDBMS?

Regards.

Arcadius.

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.

xzilla@users.sourceforge.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)

Arcadius,

> 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.

Actually, I find the MySQL model very ugly and difficult to manage (and I do
manage a public webserver based on MySQL).  If we do implement a in-database
ACL, then I would urge that it *not* be based on MySQL.   They have a few
features we would do well to emulate, but this isn't one of them.

> > 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.

I don't see how this solves the problem.    I've had to re-build MySQL because
of a mistake updating the users table before causing total lockout.

> 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.....

That's Rule #1, and it does not apply to Access Control Lists, which are not
part of the data but are a technical implementation detail.

> 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.

That's your first persuasive argument.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco