Thread: drupal.org MySQL database issues

Maybe they could be convinced to use the "other" database supported
by Drupal :).


http://drupal.org/node/144228

What's the deal?

The root of the problem that our database server is overloaded. This
is causing table locks:

Table locking is also disadvantageous under the following scenario:
* A client issues a SELECT that takes a long time to run.
* Another client then issues an UPDATE on the same table.
This client waits until the SELECT is finished.
* Another client issues another SELECT statement on the same
table. Because UPDATE has higher priority than SELECT, this
SELECT waits for the UPDATE to finish, and for the first
SELECT to finish.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

John DeSoi wrote:
> Maybe they could be convinced to use the "other" database supported by
> Drupal :).
>
>
> http://drupal.org/node/144228
>
> What's the deal?
>
> The root of the problem that our database server is overloaded. This is
> causing table locks:
>
> Table locking is also disadvantageous under the following scenario:
> * A client issues a SELECT that takes a long time to run.
> * Another client then issues an UPDATE on the same table.
> This client waits until the SELECT is finished.
> * Another client issues another SELECT statement on the same
> table. Because UPDATE has higher priority than SELECT, this
> SELECT waits for the UPDATE to finish, and for the first
> SELECT to finish.

Also by moving from MyISAM to InnoDB, they not only get row level
locking, they also get MVCC ..

regards,
Lukas

On 5/16/07, Lukas Kahwe Smith <smith@pooteeweet.org> wrote:
> Also by moving from MyISAM to InnoDB, they not only get row level
> locking, they also get MVCC ..

Yep, just sounds like they don't know how to use the database they already have.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

In the comments at the bottom of the article, some folks claim InnoDB
is going to make overall performance worse.

On May 16, 2007, at 9:05 AM, Jonah H. Harris wrote:

> On 5/16/07, Lukas Kahwe Smith <smith@pooteeweet.org> wrote:
>> Also by moving from MyISAM to InnoDB, they not only get row level
>> locking, they also get MVCC ..
>
> Yep, just sounds like they don't know how to use the database they
> already have.
>


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On 5/16/07, John DeSoi <desoi@pgedit.com> wrote:
> In the comments at the bottom of the article, some folks claim InnoDB
> is going to make overall performance worse.

That's possible, but again, it comes down to using MySQL properly...
which in this case means using InnoDB for updates and MyISAM tables as
materialized views.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

John DeSoi wrote:

> In the comments at the bottom of the article, some folks claim InnoDB
> is going to make overall performance worse.
>
I have never understood the purpose of having more than one "backend" to
the database.  As near as I can figure, it's only purpose is to allow
people to pick the wrong one.

Brian

On 5/16/07, Brian Hurt <bhurt@janestcapital.com> wrote:
> I have never understood the purpose of having more than one "backend" to
> the database.  As near as I can figure, it's only purpose is to allow
> people to pick the wrong one.

Umm, because MySQL copied others... like POSTGRES (which was one of
the first to implement that concept).  While their multiple storage
manager implementation lacks several items key to good performance, it
does give the end-user a choice to implement the one that fits their
particular needs and workload.

MySQL is *not* the only database which supports multiple storage engines.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

On Wednesday 16. May 2007 15:37, Brian Hurt wrote:

>I have never understood the purpose of having more than one "backend"
> to the database.  As near as I can figure, it's only purpose is to
> allow people to pick the wrong one.

In the case of MySQL, the multiple backends do serve obvious promotional
purposes. You may have noticed that pro-MySQL arguments often features
a mythical backend with the speed of MyISAM and the ACID of INNOdb.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

John DeSoi wrote:
> Maybe they could be convinced to use the "other" database supported by
> Drupal :).
>
>
> http://drupal.org/node/144228
>
> What's the deal?

As someone who has made a ton of money making Drupal work with
PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
the box. It needs help to get there.

I don't know that they would be happy unless they had someone with Pg
experience helping them.

Joshua D. Drake



>
> The root of the problem that our database server is overloaded. This is
> causing table locks:
>
> Table locking is also disadvantageous under the following scenario:
> * A client issues a SELECT that takes a long time to run.
> * Another client then issues an UPDATE on the same table.
> This client waits until the SELECT is finished.
> * Another client issues another SELECT statement on the same
> table. Because UPDATE has higher priority than SELECT, this
> SELECT waits for the UPDATE to finish, and for the first
> SELECT to finish.
>
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On Wed, May 16, 2007 at 09:26:45AM -0700, Joshua D. Drake wrote:
> John DeSoi wrote:
> >Maybe they could be convinced to use the "other" database supported by
> >Drupal :).
> >
> >
> >http://drupal.org/node/144228
> >
> >What's the deal?
>
> As someone who has made a ton of money making Drupal work with
> PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
> the box. It needs help to get there.
>
> I don't know that they would be happy unless they had someone with Pg
> experience helping them.

Is it the drupal code itself that's the issue or just a matter of tuning
PostgreSQL?
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Jim C. Nasby wrote:
> On Wed, May 16, 2007 at 09:26:45AM -0700, Joshua D. Drake wrote:
>> John DeSoi wrote:
>>> Maybe they could be convinced to use the "other" database supported by
>>> Drupal :).
>>>
>>>
>>> http://drupal.org/node/144228
>>>
>>> What's the deal?
>> As someone who has made a ton of money making Drupal work with
>> PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
>> the box. It needs help to get there.
>>
>> I don't know that they would be happy unless they had someone with Pg
>> experience helping them.
>
> Is it the drupal code itself that's the issue or just a matter of tuning
> PostgreSQL?

Both. Obviously tuning postgresql is a big part but they have some odd
query issues to deal with. And if they have *any* custom modules, oh boy.

Joshua D. Drake

Jim C. Nasby wrote:
>>>
>>> What's the deal?
>> As someone who has made a ton of money making Drupal work with
>> PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
>> the box. It needs help to get there.
>>
>> I don't know that they would be happy unless they had someone with Pg
>> experience helping them.
>
> Is it the drupal code itself that's the issue or just a matter of tuning
> PostgreSQL?


I should note however, that with the investment, we have shown that
Drupal on PostgreSQL kicks the pants off MySQL with Drupal :)

Joshua d. Drake

On Wed, May 16, 2007 at 09:42:36AM -0700, Joshua D. Drake wrote:
> Jim C. Nasby wrote:
> >On Wed, May 16, 2007 at 09:26:45AM -0700, Joshua D. Drake wrote:
> >>John DeSoi wrote:
> >>>Maybe they could be convinced to use the "other" database supported by
> >>>Drupal :).
> >>>
> >>>
> >>>http://drupal.org/node/144228
> >>>
> >>>What's the deal?
> >>As someone who has made a ton of money making Drupal work with
> >>PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
> >>the box. It needs help to get there.
> >>
> >>I don't know that they would be happy unless they had someone with Pg
> >>experience helping them.
> >
> >Is it the drupal code itself that's the issue or just a matter of tuning
> >PostgreSQL?
>
> Both. Obviously tuning postgresql is a big part but they have some odd
> query issues to deal with. And if they have *any* custom modules, oh boy.

Well, while they're in pain it sounds like a good time to get them
thinking about changing preferred databases as well as cleaning up some
of the code... I'm sure that if their preferred database was PostgreSQL
you'd be seeing more work. :)
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Jim C. Nasby wrote:
> On Wed, May 16, 2007 at 09:42:36AM -0700, Joshua D. Drake wrote:
>>>>
>>>> I don't know that they would be happy unless they had someone with Pg
>>>> experience helping them.
>>> Is it the drupal code itself that's the issue or just a matter of tuning
>>> PostgreSQL?
>> Both. Obviously tuning postgresql is a big part but they have some odd
>> query issues to deal with. And if they have *any* custom modules, oh boy.
>
> Well, while they're in pain it sounds like a good time to get them
> thinking about changing preferred databases as well as cleaning up some
> of the code... I'm sure that if their preferred database was PostgreSQL
> you'd be seeing more work. :)

Drupal 6 is dropping support for MySQl 3.x and going to a more
normalized data structure. Partly because of me, partly because of a lot
of others. I have spoken with Dries about it personally (via email) and
he is very happy with the idea of moving to a more sane structure overall.

However, there would be a *ton* of work to be done to get them to prefer
PostgreSQL.

Joshua D. Drake

On Wed, May 16, 2007 at 09:51:20AM -0700, Joshua D. Drake wrote:
> Jim C. Nasby wrote:
> >On Wed, May 16, 2007 at 09:42:36AM -0700, Joshua D. Drake wrote:
> >>>>
> >>>>I don't know that they would be happy unless they had someone with Pg
> >>>>experience helping them.
> >>>Is it the drupal code itself that's the issue or just a matter of tuning
> >>>PostgreSQL?
> >>Both. Obviously tuning postgresql is a big part but they have some odd
> >>query issues to deal with. And if they have *any* custom modules, oh boy.
> >
> >Well, while they're in pain it sounds like a good time to get them
> >thinking about changing preferred databases as well as cleaning up some
> >of the code... I'm sure that if their preferred database was PostgreSQL
> >you'd be seeing more work. :)
>
> Drupal 6 is dropping support for MySQl 3.x and going to a more
> normalized data structure. Partly because of me, partly because of a lot
> of others. I have spoken with Dries about it personally (via email) and
> he is very happy with the idea of moving to a more sane structure overall.
>
> However, there would be a *ton* of work to be done to get them to prefer
> PostgreSQL.

Seems like if switching made all their performance issues go away it
wouldn't be that hard...

Do they have any kind of benchmark they could use to test with?
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Wed, 2007-05-16 at 09:37 -0400, Brian Hurt wrote:
> I have never understood the purpose of having more than one "backend" to
> the database.  As near as I can figure, it's only purpose is to allow
> people to pick the wrong one.
>

PostgreSQL offers choice as well: there are Btree, GiST, and GIN index
access methods, all in common use. There are many plans available for
the same or similar operations: sort, index scan, bitmap index scan,
hash join, nested loop, merge join, hash aggregate, etc.

In PostgreSQL, changing access methods is as simple as creating the
right indexes and constraints, and letting the planner choose the new
plan when it happens to be the lowest cost.

The only thing that's the same in every case is the heap itself, and
that will change when HOT is implemented. Storage can also be changed by
placing objects into tablespaces residing on different underlying
storage systems. Or, you can use views and rules to present one relation
to applications while storing the underlying data in several tables (or
present one physical table as many logical tables).

What MySQL _really_ offers is a choice of implementations that affect
(read: compromise) the high-level semantics and break user queries.

In MySQL, changing the "storage engine" can break user queries and
always requires a full data migration to the new format.

I agree 100% that asking the MySQL user to make choices that affect both
the semantics of user queries and the long-term performance of the
application at the time the table is created is too much of a burden. It
works against the benefits that relational databases provide: that you
can change and scale up as requirements change without constant -- and
error prone -- data migrations.

Regards,
    Jeff Davis

Brian, Jeff,

> > I have never understood the purpose of having more than one "backend" to
> > the database.  As near as I can figure, it's only purpose is to allow
> > people to pick the wrong one.

Well, the real purpose of the backend storage engines is to make MySQL into
more than one kind of database.  MyISAM is for a single-user DB, InnoDB is
for transactions, NDB is clustered & object, Ericsson is telecom, MemDB is
in-memory, etc.  It makes up for the fact that MySQL does not excel in any
one database category if it is percieved to be adequate in all categories.

> In MySQL, changing the "storage engine" can break user queries and
> always requires a full data migration to the new format.

Sure, but MySQL users and potential users don't perceive it that way -- they
encounter the problems only long after they've made hard-to-revoke decisions
to adopt MySQL.  MySQL is still much, much better at marketing than we are.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On 5/16/2007 12:43 PM Joshua D. Drake wrote:
> Jim C. Nasby wrote:
>>>>
>>>> What's the deal?
>>> As someone who has made a ton of money making Drupal work with
>>> PostgreSQL, I would note that Durpal is not fast on PostgreSQL out of
>>> the box. It needs help to get there.
>>>
>>> I don't know that they would be happy unless they had someone with Pg
>>> experience helping them.
>>
>> Is it the drupal code itself that's the issue or just a matter of tuning
>> PostgreSQL?
>
>
> I should note however, that with the investment, we have shown that
> Drupal on PostgreSQL kicks the pants off MySQL with Drupal :)

I'll second the general sentiment, if not specific benchmarks - we've got openmfg.org running on Drupal/PostgreSQL, and
havebeen very happy.  Would love to see more PostgreSQL focus in the project - Josh, is there anything we can do to
help?

Josh Berkus ha scritto:
> to adopt MySQL.  MySQL is still much, much better at marketing than we are.
>
Hi Josh,

   please allow me to break into your discussion. The *marketing* issue
is something that - at least in Italy - is virtually non existant.

   We have just started a project within the PGDay whose aim is to
create a working group of ICT companies that work together with the
community in order to target their needs and to promote PostgreSQL better.

   My opinion is that people do not use PostgreSQL as they should because:

1) they do not know about it (believe me, that is true and an absolute
shame and - allow me the expression - a waste!!!)
2) they think it is too hard to setup and to maintain (that's absolutely
a misconception - it has got a steeper learning curve, but IMHO
PostgreSQL skills are highly transferrable - at least much more than
MySQL's)
3) there is no professional assistance
4) it lacks of diffused and cheap ways of using it (I refer to the LAMP
framework for instance).

   The aim of the working group is therefore to help the community to
get to know better the business requests and let companies know about
the community. I am interested into knowing if you have had similar
experiences in other parts of the world and what are your thoughts.

Ciao,
Gabriele

--
Gabriele Bartolini: Web Programmer, IWA/HWG Member
Current Location: Prato, Tuscany, Italy
gabriele.bartolini@gmail.com | www.gabrielebartolini.it
"If I had been born ugly, you would never have heard of Pelé", George Best

Another serious issue (at least in the US) is the relative lack of
PostgreSQL folks for permanent hire (ie: not contract). Is the situation
different in Italy?

On Wed, May 16, 2007 at 08:58:25PM +0200, Gabriele Bartolini wrote:
> Josh Berkus ha scritto:
> >to adopt MySQL.  MySQL is still much, much better at marketing than we are.
> >
> Hi Josh,
>
>   please allow me to break into your discussion. The *marketing* issue
> is something that - at least in Italy - is virtually non existant.
>
>   We have just started a project within the PGDay whose aim is to
> create a working group of ICT companies that work together with the
> community in order to target their needs and to promote PostgreSQL better.
>
>   My opinion is that people do not use PostgreSQL as they should because:
>
> 1) they do not know about it (believe me, that is true and an absolute
> shame and - allow me the expression - a waste!!!)
> 2) they think it is too hard to setup and to maintain (that's absolutely
> a misconception - it has got a steeper learning curve, but IMHO
> PostgreSQL skills are highly transferrable - at least much more than
> MySQL's)
> 3) there is no professional assistance
> 4) it lacks of diffused and cheap ways of using it (I refer to the LAMP
> framework for instance).
>
>   The aim of the working group is therefore to help the community to
> get to know better the business requests and let companies know about
> the community. I am interested into knowing if you have had similar
> experiences in other parts of the world and what are your thoughts.
>
> Ciao,
> Gabriele
>
> --
> Gabriele Bartolini: Web Programmer, IWA/HWG Member
> Current Location: Prato, Tuscany, Italy
> gabriele.bartolini@gmail.com | www.gabrielebartolini.it
> "If I had been born ugly, you would never have heard of Pel??", George Best
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Gabrielle,

>    We have just started a project within the PGDay whose aim is to
> create a working group of ICT companies that work together with the
> community in order to target their needs and to promote PostgreSQL
> better.

Let's talk about it while I'm in Italy.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Brian Hurt wrote:
> John DeSoi wrote:
>
>> In the comments at the bottom of the article, some folks claim InnoDB
>> is going to make overall performance worse.
>>
> I have never understood the purpose of having more than one "backend" to
> the database.  As near as I can figure, it's only purpose is to allow
> people to pick the wrong one.

It enables different producers of specialized database technology to
integrate their technology and thereby exposing them to a much bigger
market. I wrote a blog post on this a while back:
http://pooteeweet.org/blog/382

regards,
Lukas

Hi,

Most people use MySQL because thats what everybody else around them
uses. They lack the expertise, since they are usually "just" developers.
So picking the most popular makes the most sense to them. If its popular
it can't be bad and at least there are plenty of examples, applications
and people to ask.

I do not see PostgreSQL breaking this cycle. So to me the focus must be
on making sure that people that do have the expertise have little reason
to choose MySQL. So make sure that MySQL has no upper hand on features
(like in charset support).

regards,
Lukas

PS: I am not saying that all marketing is futile of course. I would
focus on Universities. I remember that at least at the TU Berlin I saw
much more MySQL than PostgreSQL.

Going back on-list

On Thu, May 17, 2007 at 11:33:46AM -0400, Andrew Sullivan wrote:
> On Wed, May 16, 2007 at 09:45:58PM -0500, Jim C. Nasby wrote:
> > Another serious issue (at least in the US) is the relative lack of
> > PostgreSQL folks for permanent hire (ie: not contract). Is the situation
>
> An interesting effect of this, in my experience, is that businesses
> also become reluctant to use Postgres because of this dearth of
> available employees.

Absolutely. What's frustrating is that we've got half the equation in
place already: there's at least 4 companies in the US that offer
PostgreSQL training. What we're missing is the advertising to database
folks that there's a bundle of money to be made here.
--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Going back on-list...

On Thu, May 17, 2007 at 08:38:44AM +0200, Gabriele Bartolini wrote:
> Jim C. Nasby ha scritto:
> >Another serious issue (at least in the US) is the relative lack of
> >PostgreSQL folks for permanent hire (ie: not contract). Is the situation
> >different in Italy?
> Unfortunately no. University either teach proprietary databases or
> MySQL.  I guess it is just a vicious circle. There cannot be an extended
> and diffused offer of DBA-related classes in the academic world about
> PostgreSQL, if the market is not well aware of its potential. That is at
> least the situation in Italy. I mostly it is due to ignorance (at least
> I hope it is just that).

It probably is. See my reply to Andrew about what we need to do to
change that. Though I don't know how many companies in Italy do
PostgreSQL training besides EnterpriseDB.
--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Jim C. Nasby wrote:
> Going back on-list
>
> On Thu, May 17, 2007 at 11:33:46AM -0400, Andrew Sullivan wrote:
>> On Wed, May 16, 2007 at 09:45:58PM -0500, Jim C. Nasby wrote:
>>> Another serious issue (at least in the US) is the relative lack of
>>> PostgreSQL folks for permanent hire (ie: not contract). Is the situation
>> An interesting effect of this, in my experience, is that businesses
>> also become reluctant to use Postgres because of this dearth of
>> available employees.
>
> Absolutely. What's frustrating is that we've got half the equation in
> place already: there's at least 4 companies in the US that offer
> PostgreSQL training. What we're missing is the advertising to database
> folks that there's a bundle of money to be made here.

Well... I don't know, I think many that *could* be postgresql employees
choose not to because, there is a bundle of money to be made here.

Joshua D. Drake



--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On May 17, 2007, at 9:17 AM, Lukas Kahwe Smith wrote:
> Most people use MySQL because thats what everybody else around them
> uses. They lack the expertise, since they are usually "just"
> developers. So picking the most popular makes the most sense to
> them. If its popular it can't be bad and at least there are plenty
> of examples, applications and people to ask.

Agree.

> I do not see PostgreSQL breaking this cycle. So to me the focus
> must be on making sure that people that do have the expertise have
> little reason to choose MySQL. So make sure that MySQL has no upper
> hand on features (like in charset support).

I think one of the keys to breaking it is to get large, well-known
sites to switch off of MySQL, and then do PR around that. Drupal
would be a good example. We also need good information on how hard/
easy it is to migrate from MySQL to PostgreSQL, and of course we need
to do what we can to push it to the easy side of that scale.

> PS: I am not saying that all marketing is futile of course. I would
> focus on Universities. I remember that at least at the TU Berlin I
> saw much more MySQL than PostgreSQL.

One question I've got about that is... do the professors actually
understand what they're teaching well enough to understand why they
should be using PostgreSQL and not MySQL? ISTM that any CS professor
worth his salt when it comes to databases would use MySQL in a
classroom only as a last resort, at least for lower-level classes
(MySQL does have it's place, but you need to understand the basics
before you know what that place is).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Joshua D. Drake wrote:
> Well... I don't know, I think many that *could* be postgresql
> employees choose not to because, there is a bundle of money to be made
> here.
i suppose. i'm currently job hunting in upstate ny, and would be happy
to do a full time postgresql gig, but there's only one i've found in a
almost reasonable commuting distance, and they're not paying very well.

most of what's going on around here is at shops that have been heavily
invested in oracle for years, or sql server for perhaps a bit less time.
(plus one major bank that is gradually migrating from informix to db2.)

maybe it's just that there are few risk takers in Albany.

richard

On 17/05/07, Lukas Kahwe Smith <smith@pooteeweet.org> wrote:
> Most people use MySQL because thats what everybody else around them
> uses. They lack the expertise, since they are usually "just" developers.
> So picking the most popular makes the most sense to them. If its popular
> it can't be bad and at least there are plenty of examples, applications
> and people to ask.
>
> I do not see PostgreSQL breaking this cycle. So to me the focus must be
> on making sure that people that do have the expertise have little reason
> to choose MySQL. So make sure that MySQL has no upper hand on features
> (like in charset support).


PostgreSQL should work with Cpanel (WHM) and Plesk without pains. This
is the single most important reason that MySQL is what it is--because
more than half the planet uses these tools to do its hosting.
If PostgreSQL and PhpPgAdmin play well with Cpanel, at which they are
both miserable right now, the market for PG will suddenly go into
multitudes.

Shanx

> Absolutely. What's frustrating is that we've got half the equation in
> place already: there's at least 4 companies in the US that offer
> PostgreSQL training. What we're missing is the advertising to database
> folks that there's a bundle of money to be made here.


Four companies won't train diddly squat compared to the self-training and
online help that this generation is used to. That happens when you make a
tool available in a pain-free way that *enables* technologists to do what
they do best, instead of sitting searching the manual for their DB, or
posting to pgsql-general, or just plain tearing hair out.

The MySQL team has personally paid a great deal of attention to make sure
their software works with Cpanel and Plesk. Therein lies the "marketing"
savvy that tge PgSQL group sits and watches in confused awe. [Ok, it's not
quite that stark, but it pains me to see a superlative product take it on
the chin just because it isn't too clever with making itself usable. Make it
easy--and it certainly can be easier than it is today--and people will train
themselves.]

On 17/05/07, Lukas Kahwe Smith <smith@pooteeweet.org> wrote:
> Most people use MySQL because thats what everybody else around them
> uses. They lack the expertise, since they are usually "just" developers.
> So picking the most popular makes the most sense to them. If its popular
> it can't be bad and at least there are plenty of examples, applications
> and people to ask.
>
> I do not see PostgreSQL breaking this cycle. So to me the focus must be
> on making sure that people that do have the expertise have little reason
> to choose MySQL. So make sure that MySQL has no upper hand on features
> (like in charset support).


PostgreSQL should work with Cpanel (WHM) and Plesk without pains. This
is the single most important reason that MySQL is what it is --
because more than half the planet uses these tools to do its hosting.
If PostgreSQL and PhpPgAdmin play well with Cpanel, which they are
both miserable at right now, the market for PG will suddenly go into
multitudes.

Shanx

On May 17, 2007, at 12:21 PM, Jim Nasby wrote:

> I think one of the keys to breaking it is to get large, well-known
> sites to switch off of MySQL, and then do PR around that. Drupal
> would be a good example. We also need good information on how hard/
> easy it is to migrate from MySQL to PostgreSQL, and of course we
> need to do what we can to push it to the easy side of that scale.

Exactly why I started the thread -- hoping one of the PostgreSQL
superpowers will jump in and save the day for drupal.org :).




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Shashank Tripathi wrote:
>
> The MySQL team has personally paid a great deal of attention to make
> sure their software works with Cpanel and Plesk. Therein lies the
> "marketing" savvy that tge PgSQL group sits and watches in confused awe.

Uhmmm no. Nobody is confused. You seem to think we are competing with
MySQL. We are no more competing with MySQL than Oracle is.

MySQL is fine for relatively small, high velocity sites where data
integrity and high write concurrency is not an issue.

PostgreSQL is all about high velocity write concurrency and data
integrity. I can not count how many people have switched *from* MySQL to
PostgreSQL because they outgrow MySQL.

Except in maybe DW space, you generally will not outgrow PostgreSQL.

Lastly, what people like you who make these comments seem to forget is
that MySQL is a company, not a community, not an open source project.

It is a commercial open source *product*, developed by a heavily funded
private company (that is about to go public).

That is just a tad different than PostgreSQL.

Sincerely,

Joshua D. Drake


> [Ok, it's not quite that stark, but it pains me to see a superlative
> product take it on the chin just because it isn't too clever with making
> itself usable. Make it easy--and it certainly can be easier than it is
> today--and people will train themselves.]
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

> Uhmmm no. Nobody is confused. You seem to think we are competing with
> MySQL. We are no more competing with MySQL than Oracle is.

This is the very sentiment that will keep PG exactly where it is today. I
hate to break it to you, but even Oracle was worried about MySQL and the
humongous penetration it has amassed. It doesn't matter where MySQL is
today. If it comes out with a version, say 6.5, which is as robust as other
big alternatives (and it is getting there), then their installed base would
suddenly have taken a step up. This is why market share as a notion occupies
the time of many strategists.

> MySQL is fine for relatively small, high velocity sites where data
> integrity and high write concurrency is not an issue.

Yes and no. I have seen very high concurrency implementations of MySQL.
Depends on what one's capabilities are. Use it with the right mix of InnoDB
and MVs and you'll do pretty ok. Continue to bumble about with MYISAM, as
the sad Drupal group is up to, and it'll end in bafflement.

> PostgreSQL is all about high velocity write concurrency and data
> integrity. I can not count how many people have switched *from* MySQL to
> PostgreSQL because they outgrow MySQL.

This includes me. So you're preaching to the choir. My simple point is that
PgSQL should be, and can be, easier to integrate with market leading
platforms such as Cpanel and Plesk.

> It is a commercial open source *product*, developed by a heavily funded
> private company (that is about to go public). That is just a tad different
> than PostgreSQL.

MySQL = Database, PostgreSQL = Database
That's where the comparison begins and ends. Apples to apples.

So what if PgSQL is different from MySQL, more robust? So what if it is open
source as opposed to a company (note that MySQL shot to fame long before it
became so very formalized)? So what if it is not making a dime, unlike
MySQL? Is that our logic to not become better and more easily integrable
with market leading platforms?

Many a novice has come to the [pgsql-general] list with questions about
working with cpanel, but the attitude I have seen from this community is:
yeah we're this grand bunch of folks sitting on a superior product, so we
don't give a fig. You want PgSQL to work with Cpanel? Go talk to Cpanel.
That's a marked difference from the mysql-general list.

And that does not compute for me. If that's the attitude, let's stop
cribbing about market share on this advocacy list. I feel I need to voice it
because the effort required to trump MySQL is very, very small. It just
takes cognizance of what needs to be done. It wouldn't kill us to have
PostgreSQL implemented by a larger chunk of the market.

To the point of training, no one trains MySQL folks (by and large). They
train themselves. PgSQL is not significantly more difficult than MySQL,
certainly not for the tasks that MySQL is most commonly used for. But MySQL
becomes the DB of choice because it works with cpanel and plesk, works
without pains, and works super fast. All things that PgSQL can also do.

My 20 yen.
Shashank

Shashank Tripathi wrote:
>> Uhmmm no. Nobody is confused. You seem to think we are competing with
>> MySQL. We are no more competing with MySQL than Oracle is.
>
> This is the very sentiment that will keep PG exactly where it is today.

You mean gaining market share, providing profit and being deployed in
global 5000 companies?

> I hate to break it to you, but even Oracle was worried about MySQL and
> the humongous penetration it has amassed. It doesn't matter where MySQL
> is today. If it comes out with a version, say 6.5, which is as robust as
> other big alternatives (and it is getting there),

Hardly. I believe you should stop reading the marketing material and
look at how MySQL actually does things.

>
>> PostgreSQL is all about high velocity write concurrency and data
>> integrity. I can not count how many people have switched *from* MySQL
>> to PostgreSQL because they outgrow MySQL.
>
> This includes me. So you're preaching to the choir. My simple point is
> that PgSQL should be, and can be, easier to integrate with market
> leading platforms such as Cpanel and Plesk.

Do you have something specific, that says we are more difficult than
MySQL is some way? Everytime I see this, it seems more about people not
willing to RTFM.

>
>> It is a commercial open source *product*, developed by a heavily
>> funded private company (that is about to go public). That is just a
>> tad different than PostgreSQL.
>
> MySQL = Database, PostgreSQL = Database
> That's where the comparison begins and ends. Apples to apples.

Then you are not making a valid comparison because then I can say we
might as well all close up shop and run SQLLite. Yes MySQL is a
database, yes PostgreSQL is a database... so what? So is MS Access.

>
> So what if PgSQL is different from MySQL, more robust? So what if it is
> open source as opposed to a company (note that MySQL shot to fame long
> before it became so very formalized)? So what if it is not making a
> dime, unlike MySQL? Is that our logic to not become better and more
> easily integrable with market leading platforms?

Again.. evidence? What is it about postgresql that makes it tough for
Cpanel or Plesk?

>
> Many a novice has come to the [pgsql-general] list with questions about
> working with cpanel, but the attitude I have seen from this community
> is: yeah we're this grand bunch of folks sitting on a superior product,
> so we don't give a fig. You want PgSQL to work with Cpanel? Go talk to
> Cpanel. That's a marked difference from the mysql-general list.

Well to be fair, Cpanel is crap, so I agree with the sentiment. That
being said in the vein of market share... Here is the difference that I
was trying to make about MySQL above.

The PostgreSQL "project" has (generally speaking) no concern over
marketshare. It is not in the goal list.

Command Prompt, EnterpriseDB, OTG... are who are concerned with Market
Share.

>
> And that does not compute for me. If that's the attitude, let's stop
> cribbing about market share on this advocacy list. I feel I need to
> voice it because the effort required to trump MySQL is very, very small.
> It just takes cognizance of what needs to be done. It wouldn't kill us
> to have PostgreSQL implemented by a larger chunk of the market.

Actually it might kill us because we are developing a superior product
and if we are tied up dealing with people running Cpanel we can hardly
afford time to do things like Synchronous replication and HOT.

> To the point of training, no one trains MySQL folks (by and large). They
> train themselves. PgSQL is not significantly more difficult than MySQL,

I call B.S. on this one. I *know* MySQL has a huge profit center in
training. Businesses by training. Lots of businesses.

No, not everyone, especially not your general hacker but it is generally
the general hacker that needs training the most.

Sincerely,

Joshua D. Drake


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Fri, May 18, 2007 at 02:33:17AM +0800, Shashank Tripathi wrote:

> My simple point is that PgSQL should be, and can be, easier to
> integrate with market leading platforms such as Cpanel and Plesk.

You've now made this claim three times in the thread without any
argument for it.  For those of us who happily tra-la through life
without ever having to use either of these platforms, perhaps you
could outline what the barriers are and how you think those could be
reduced.  That would be more helpful to your goal, I think, than
repeating that MySQL has good marketing.  I don't think the latter is
exactly news hereabouts.

One other note:

> To the point of training, no one trains MySQL folks (by and large). They
> train themselves.

You're right about this, and every time I have to go through the
trial of implementing anything originally designed for MySQL, I am
reminded of it.  So there is another requirement that you appear not
to be acknowledging: the PostgreSQL community isn't that interested
in gaining market share if it requires sacrificing correctness.  I
have seen many proposals for, "Make PostgreSQL easier," which
actually unpack into, "Make PostgreSQL less rigourous."  Those
proposals do tend to get a frosty reception, and if you expect that
to stop, I think you will be disappointed.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
        --Scott Morris

Andrew,

> On Fri, May 18, 2007 at 02:33:17AM +0800, Shashank Tripathi wrote:
>
>> My simple point is that PgSQL should be, and can be, easier to
>> integrate with market leading platforms such as Cpanel and Plesk.
>
> You've now made this claim three times in the thread without any
> argument for it.  For those of us who happily tra-la through life

I think the claim itself is an argument.  Here's how I view it:

1. The end goal is increased usage of PostgreSQL.
2. Reaching the end goal can be accelerated via effort on our part.
3. Effort can be any of:
     a) Promoting PG through:
         i.   Writing Whitepapers
         ii.  Giving Talks at Conferences
         iii. Manning booths at conferences,
         iv.  et al.
     b) Contributing to other apps with wide usage, such as Plesk and
         CPanel, such that their wide usage coupled with making PG
         easy to use with them increases PG's usage.

I won't argue for or against any team members doing item b.  However,
Shashank noted that there *are* MySQL people doing this, and their effort
is paying off.  Ergo, if some of our community wanted to expend effort in
"integration efforts" rather than "promotion efforts", Postgres would gain
additional traction in the web hosting community.  That's what Shashank is
saying.

Cheers,
-J

On Thu, May 17, 2007 at 02:52:41PM -0400, Joshua Kramer wrote:
> I won't argue for or against any team members doing item b.  However,
> Shashank noted that there *are* MySQL people doing this, and their effort
> is paying off.  Ergo, if some of our community wanted to expend effort in
> "integration efforts" rather than "promotion efforts", Postgres would gain
> additional traction in the web hosting community.  That's what Shashank is
> saying.

Well, Shashank also said quite clearly that PostgreSQL is harder to
use than MySQL in these cases.  Harder how?  I don't know, and I am
surely not the person who can learn from experience, because nothing
that I do puts me into any contact with making PostgreSQL play with
plesk or any of that other stuff.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
        --Damien Katz

On Thursday 17 May 2007 15:15, Andrew Sullivan wrote:
> On Thu, May 17, 2007 at 02:52:41PM -0400, Joshua Kramer wrote:
> > I won't argue for or against any team members doing item b.  However,
> > Shashank noted that there *are* MySQL people doing this, and their effort
> > is paying off.  Ergo, if some of our community wanted to expend effort in
> > "integration efforts" rather than "promotion efforts", Postgres would
> > gain additional traction in the web hosting community.  That's what
> > Shashank is saying.
>
> Well, Shashank also said quite clearly that PostgreSQL is harder to
> use than MySQL in these cases.  Harder how?  I don't know, and I am
> surely not the person who can learn from experience, because nothing
> that I do puts me into any contact with making PostgreSQL play with
> plesk or any of that other stuff.
>

mysql's remote administration capabilities (especially wrt user management and
securing shared environments) are much easier (like you can actualy do it)
than postgresql's, which aiui are critical to plesk's web-based
administration suite.

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

> Well, Shashank also said quite clearly that PostgreSQL is harder to
> use than MySQL in these cases.  Harder how?  I don't know, and I am

I would suspect that at one point, MySQL was also "hard to use" with Plesk
and Cpanel; but the MySQL folks put forth the effort to integrate the two
and make it "easy to use".  Similarly, if some PG folks put forth some
effort towards Plesk and CPanel, Postgres would become "easy to use" as
well.  I think this is what Shashank is tring to get at.

> surely not the person who can learn from experience, because nothing
> that I do puts me into any contact with making PostgreSQL play with
> plesk or any of that other stuff.

Right, but if someone with PG and PHP skills put forth some effort to make
Plesk / CPanel play well with PG, then the marketing efforts would be that
much easier and the use of PG would probably increase.  That's all that is
being said here.

Thanks,
-josh

Joshua Kramer wrote:
>
>> Well, Shashank also said quite clearly that PostgreSQL is harder to
>> use than MySQL in these cases.  Harder how?  I don't know, and I am
>
> I would suspect that at one point, MySQL was also "hard to use" with
> Plesk and Cpanel; but the MySQL folks put forth the effort to integrate
> the two and make it "easy to use".  Similarly, if some PG folks put
> forth some effort towards Plesk and CPanel, Postgres would become "easy
> to use" as well.  I think this is what Shashank is tring to get at.
>
>> surely not the person who can learn from experience, because nothing
>> that I do puts me into any contact with making PostgreSQL play with
>> plesk or any of that other stuff.
>
> Right, but if someone with PG and PHP skills put forth some effort to
> make Plesk / CPanel play well with PG, then the marketing efforts would
> be that much easier and the use of PG would probably increase.  That's
> all that is being said here.

Well let me be the first to point out....

Feel free to submit a patch on behalf of the community.

:P

Joshua D. Drake


>
> Thanks,
> -josh
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Thu, May 17, 2007 at 03:40:04PM -0400, Joshua Kramer wrote:
> Right, but if someone with PG and PHP skills put forth some effort to make
> Plesk / CPanel play well with PG, then the marketing efforts would be that
> much easier and the use of PG would probably increase.  That's all that is
> being said here.

I understand that.  But if the community is to take the argument
seriously, someone needs to say what "play well" means there.  So far
I keep hearing that it doesn't, but only Robert Treat has suggested
any meaningful detail about what that means.  Is that all we're
saying, that remote administration needs to be better?

If so, I would suggest that a worthy project would be something like
pg_remote_hand_daemon or something, which would be an optional
component that you could use to do remote control of a postgres
server.  All the commands would go through the daemon, and it would
be responsible for communicating with the clients (you do it this way
to isolate the places where you have to work on the security model).
Then you have it perform the configuration tasks on the postgresql
node the way a human might do it -- alter the config file, SIGHUP,
&c.  (This is if you want humans manually changing your database
systems, which I don't.  That's what things like cfengine were
invented to stop.)

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
        --Alexander Hamilton

> If so, I would suggest that a worthy project would
be something like
> pg_remote_hand_daemon or something

You guys come up with the specs and a developer, I'm
good for 2k via SPI (or direct, if possible).

Can't code, but I can sign a check.

Better hurry, I think postage is going up soon.

Thanks,
Mike Ellsworth

ajs@crankycanuck.ca (Andrew Sullivan) writes:
> On Fri, May 18, 2007 at 02:33:17AM +0800, Shashank Tripathi wrote:
>
>> My simple point is that PgSQL should be, and can be, easier to
>> integrate with market leading platforms such as Cpanel and Plesk.
>
> You've now made this claim three times in the thread without any
> argument for it.  For those of us who happily tra-la through life
> without ever having to use either of these platforms, perhaps you
> could outline what the barriers are and how you think those could be
> reduced.  That would be more helpful to your goal, I think, than
> repeating that MySQL has good marketing.  I don't think the latter
> is exactly news hereabouts.

The CPanel "issue" isn't exactly new.  I have pointed it out on a
number of occasions, and I think it remains a true factor.

http://archives.postgresql.org/pgsql-advocacy/2005-07/msg00144.php
http://archives.postgresql.org/pgsql-hackers/2002-06/msg00852.php
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg19226.html

CPanel is widely used by ISPs to help their users manage "web hosting"
services, and its support for MySQL(tm)[1] has been a lot better than
that for PostgreSQL.  I think that most certainly has been a factor in
the adoption of MySQL for little web applications out there.

The fact that I administer all my PostgreSQL DBs via the command line,
and would prefer to do so in a web hosting environment, does not
prevent me from imagining that people may have chosen to use
MySQL(tm)[1] due to the "ease of access" that CPanel provided.

A reason for preference for MySQL(tm)[1] would evaporate if CPanel had
better PostgreSQL support.

Footnotes:
[1]  MySQL(tm) is a trademark of MySQL AB
--
output = ("cbbrowne" "@" "linuxdatabases.info")
http://cbbrowne.com/info/
Signs of a Klingon  Programmer - 5. "Indentation?! -  I will  show you
how to indent when I indent your skull!"

Gavin M. Roy wrote:
> I think for one, mysql uses tables for all of its access control.
> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
> a bit more work, I would imagine.

In a lot of environments, it'd certainly be impossible, at least until
we make it possible to edit the config files remote... (oops, recap of
endless amounts of discussions on letting pgadmin do that..)


> Do we really want to pursue making PostgreSQL easier to admin for the
> non-system admin?  Cpanel and plesk and like tools are pretty far down
> the list of important things to support or code for.

If we want to make inroads into shared-hosting environments, it would
certainly help...

//Magnus

On 5/17/07, Gavin M. Roy <gmr@myyearbook.com> wrote:
> Do we really want to pursue making PostgreSQL easier to admin for the
> non-system admin?  Cpanel and plesk and like tools are pretty far down the
> list of important things to support or code for.

From my perspective, the PostgreSQL development community focuses
mostly on stability and standards than on nifty features,
administration, and usability.  It's great that we have a solid
database, but that's worth nothing more than a mere hobby if no one
chooses to use it.

Microsoft, Oracle, and even MySQL have realized that administration
and management play a significant role in adoption.  Good
administration tools get people into using the system without having
to fully understand the nuts-and-bolts of the system... which is
basically required if you want to tune PostgreSQL worth a darn.  Sure,
there are tools out there for PostgreSQL that are great at
administration and development, but they're standalone, not well known
outside the community, and don't really integrate with anything
commonly used.

It would be nice if we had community development members that would
focus on adding PostgreSQL support into major OSS projects, but it
doesn't seem like we either don't have that crowd, or none are
especially eager to volunteer for those tasks.

In this thread, I've seen several people say something to the effect
of, "why is PostgreSQL harder to use than MySQL?"  IMHO, it's
difficult for us to answer that question because we're all experienced
with PostgreSQL and have somewhat biased opinions on what it takes to
administer and manage PostgreSQL compared with other systems.

Rather than shooting down newbie suggestions, as we sometimes do,
perhaps we should keep them in mind.  It's a difficult situation to be
in, and I don't see any simple answers.

Just my 2 cents.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

Magnus Hagander wrote:
> Gavin M. Roy wrote:
>> I think for one, mysql uses tables for all of its access control.
>> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
>> a bit more work, I would imagine.
>
> In a lot of environments, it'd certainly be impossible, at least until
> we make it possible to edit the config files remote... (oops, recap of
> endless amounts of discussions on letting pgadmin do that..)

Well more to the point. There really is zero reason why we can't have a
table representation of pg_hba_conf that is the pg_hba.conf file that
has triggers that right out the file.


>
>> Do we really want to pursue making PostgreSQL easier to admin for the
>> non-system admin?  Cpanel and plesk and like tools are pretty far down
>> the list of important things to support or code for.
>
> If we want to make inroads into shared-hosting environments, it would
> certainly help...

It is not just shared hosting... dedicated hosting starts as little as
69.00 with Cpanel :)...

Note that I am not advocating making it easier for Cpanel. I am just
making a point that it is not limited to shared hosting.

I am however advocating that it is pretty dumb that our conf files are
*required* as a little text file on the filesystem and can not be
managed via the database.

Joshua D. Drake



>
> //Magnus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Gavin M. Roy wrote:
> There is something to be said though with the security of not allowing
> the daemon to alter pg_hba.conf.

You make it so only a superuser can insert into the table (it would be a
pg_ table). Further only a super user could call the pg_reload function
that we already allow.

   What I think would work is a two step
> auth process that uses a pg_hba table then falls back to pg_hba.conf if
> there is no match.  This keeps the complete security of preventing
> compromised database from altering the text file.
>
> Thoughts?
>

Well consider this :). If they can alter the pg_hba.conf file, it
doesn't matter what happens next (or before for that matter).

Joshua D. Drake



> Gavin
>
> On 5/17/07, *Joshua D. Drake* <jd@commandprompt.com
> <mailto:jd@commandprompt.com>> wrote:
>
>     Magnus Hagander wrote:
>      > Gavin M. Roy wrote:
>      >> I think for one, mysql uses tables for all of its access control.
>      >> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres
>     would take
>      >> a bit more work, I would imagine.
>      >
>      > In a lot of environments, it'd certainly be impossible, at least
>     until
>      > we make it possible to edit the config files remote... (oops,
>     recap of
>      > endless amounts of discussions on letting pgadmin do that..)
>
>     Well more to the point. There really is zero reason why we can't have a
>     table representation of pg_hba_conf that is the pg_hba.conf file that
>     has triggers that right out the file.
>
>
>      >
>      >> Do we really want to pursue making PostgreSQL easier to admin
>     for the
>      >> non-system admin?  Cpanel and plesk and like tools are pretty
>     far down
>      >> the list of important things to support or code for.
>      >
>      > If we want to make inroads into shared-hosting environments, it
>     would
>      > certainly help...
>
>     It is not just shared hosting... dedicated hosting starts as little as
>     69.00 with Cpanel :)...
>
>     Note that I am not advocating making it easier for Cpanel. I am just
>     making a point that it is not limited to shared hosting.
>
>     I am however advocating that it is pretty dumb that our conf files are
>     *required* as a little text file on the filesystem and can not be
>     managed via the database.
>
>     Joshua D. Drake
>
>
>
>      >
>      > //Magnus
>      >
>      > ---------------------------(end of
>     broadcast)---------------------------
>      > TIP 6: explain analyze is your friend
>      >
>
>
>     --
>
>            === The PostgreSQL Company: Command Prompt, Inc. ===
>     Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>     Providing the most comprehensive  PostgreSQL solutions since 1997
>                   http://www.commandprompt.com/
>     <http://www.commandprompt.com/>
>
>     Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>     PostgreSQL Replication: http://www.commandprompt.com/products/
>     <http://www.commandprompt.com/products/>
>
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On 5/17/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Shashank Tripathi wrote:
> >
> > The MySQL team has personally paid a great deal of attention to make
> > sure their software works with Cpanel and Plesk. Therein lies the
> > "marketing" savvy that tge PgSQL group sits and watches in confused awe.
>
> Uhmmm no. Nobody is confused. You seem to think we are competing with
> MySQL. We are no more competing with MySQL than Oracle is.
>
> MySQL is fine for relatively small, high velocity sites where data
> integrity and high write concurrency is not an issue.
>
> PostgreSQL is all about high velocity write concurrency and data
> integrity. I can not count how many people have switched *from* MySQL to
> PostgreSQL because they outgrow MySQL.
>
> Except in maybe DW space, you generally will not outgrow PostgreSQL.
>
> Lastly, what people like you who make these comments seem to forget is
> that MySQL is a company, not a community, not an open source project.
>
> It is a commercial open source *product*, developed by a heavily funded
> private company (that is about to go public).
>
> That is just a tad different than PostgreSQL.

I agree.  The problem is that when the company outgrows MySQL they
need a big fat hint that there is another way than buying Oracle/DB2 or
heavily modifying application to workaround MySQL.  This is where
PostgreSQL has power, and all it takes is right person in right place and
a lot of patience and persistence. ;)

   Regards,
       Dawid

PS: Enterprise features, like online backups or online (concurrent) index
builds really help in this regard!  So will HOT, hierarchical queries and
analytical functions.

On Thursday 17 May 2007 17:54, Joshua D. Drake wrote:
> Magnus Hagander wrote:
> > Gavin M. Roy wrote:
> >> I think for one, mysql uses tables for all of its access control.
> >> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
> >> a bit more work, I would imagine.
> >
> > In a lot of environments, it'd certainly be impossible, at least until
> > we make it possible to edit the config files remote... (oops, recap of
> > endless amounts of discussions on letting pgadmin do that..)
>

Really this is already pretty doable at this point if your willing to be
intrusive about things (which I think the plesk people probably are).

> Well more to the point. There really is zero reason why we can't have a
> table representation of pg_hba_conf that is the pg_hba.conf file that
> has triggers that right out the file.
>

It's not quite that simple, since tables are conceptually unordered, but the
pg_hba uses order to make it work, but this could probably be worked out if
you could get people to agree that user management in the db was acceptable.
Personally if you're just going to stick with a system tied to the
pg_hba.conf, I don't think this step is really needed.

> >> Do we really want to pursue making PostgreSQL easier to admin for the
> >> non-system admin?  Cpanel and plesk and like tools are pretty far down
> >> the list of important things to support or code for.
> >
> > If we want to make inroads into shared-hosting environments, it would
> > certainly help...
>
> It is not just shared hosting... dedicated hosting starts as little as
> 69.00 with Cpanel :)...
>
> Note that I am not advocating making it easier for Cpanel. I am just
> making a point that it is not limited to shared hosting.
>
> I am however advocating that it is pretty dumb that our conf files are
> *required* as a little text file on the filesystem and can not be
> managed via the database.
>

agreed.

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

On Thursday 17 May 2007 17:16, Jonah H. Harris wrote:
> On 5/17/07, Gavin M. Roy <gmr@myyearbook.com> wrote:
> > Do we really want to pursue making PostgreSQL easier to admin for the
> > non-system admin?  Cpanel and plesk and like tools are pretty far down
> > the list of important things to support or code for.
>
> From my perspective, the PostgreSQL development community focuses
> mostly on stability and standards than on nifty features,
> administration, and usability.  It's great that we have a solid
> database, but that's worth nothing more than a mere hobby if no one
> chooses to use it.
>
> Microsoft, Oracle, and even MySQL have realized that administration
> and management play a significant role in adoption.  Good
> administration tools get people into using the system without having
> to fully understand the nuts-and-bolts of the system... which is
> basically required if you want to tune PostgreSQL worth a darn.  Sure,
> there are tools out there for PostgreSQL that are great at
> administration and development, but they're standalone, not well known
> outside the community, and don't really integrate with anything
> commonly used.
>
> It would be nice if we had community development members that would
> focus on adding PostgreSQL support into major OSS projects, but it
> doesn't seem like we either don't have that crowd, or none are
> especially eager to volunteer for those tasks.
>

Yep... been preaching this for years, but we just dont seem to have people
interested in it, and IMHO we have some who are are just as happy that we
don't make inroads to certain communites.

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

Robert Treat wrote:
> On Thursday 17 May 2007 17:54, Joshua D. Drake wrote:
>> Magnus Hagander wrote:
>>> Gavin M. Roy wrote:
>>>> I think for one, mysql uses tables for all of its access control.
>>>> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
>>>> a bit more work, I would imagine.
>>> In a lot of environments, it'd certainly be impossible, at least until
>>> we make it possible to edit the config files remote... (oops, recap of
>>> endless amounts of discussions on letting pgadmin do that..)
>
> Really this is already pretty doable at this point if your willing to be
> intrusive about things (which I think the plesk people probably are).
>
>> Well more to the point. There really is zero reason why we can't have a
>> table representation of pg_hba_conf that is the pg_hba.conf file that
>> has triggers that right out the file.
>>
>
> It's not quite that simple, since tables are conceptually unordered, but the
> pg_hba uses order to make it work, but this could probably be worked out if
> you could get people to agree that user management in the db was acceptable.

You just have a sort order within the table that the function that
writes out the file understands.

> Personally if you're just going to stick with a system tied to the
> pg_hba.conf, I don't think this step is really needed.
>

True enough.

>> I am however advocating that it is pretty dumb that our conf files are
>> *required* as a little text file on the filesystem and can not be
>> managed via the database.
>>
>
> agreed.
>

Joshua D. Drake



--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Thursday 17 May 2007 22:41, Shashank Tripathi wrote:
> On 18/05/07, Robert Treat <xzilla@users.sourceforge.net> wrote:
> > On Thursday 17 May 2007 15:15, Andrew Sullivan wrote:
> > > On Thu, May 17, 2007 at 02:52:41PM -0400, Joshua Kramer wrote:
> > > > I won't argue for or against any team members doing item b.  However,
> > > > Shashank noted that there *are* MySQL people doing this, and their
> > > > effort is paying off.  Ergo, if some of our community wanted to
> > > > expend effort in "integration efforts" rather than "promotion
> > > > efforts", Postgres would gain additional traction in the web hosting
> > > > community.  That's what Shashank is saying.
> > >
> > > Well, Shashank also said quite clearly that PostgreSQL is harder to
> > > use than MySQL in these cases.  Harder how?  I don't know, and I am
> > > surely not the person who can learn from experience, because nothing
> > > that I do puts me into any contact with making PostgreSQL play with
> > > plesk or any of that other stuff.
>
> Yes that is precisely what I said. MySQL and PgSQL both come as icons
> on my Cpanel. Now, because PgSQL had to be separately installed. With
> 8.x this is not as cumbersome as it used to be before, which
> practically kept people off it. Most people anyway, who did not want
> to spend days just to get something installed and working. MySQL by
> comparison was always a cinch to get working.
>
> But even today, when I have both MySQL and PgSQL, here is how they
> function:
>
> 1. MySQL: I login to cpanel, then I click on PhpMyAdmin and boom, I'm
> using the database. It.just.works.
>
> 2. PgSQL: I login to cpanel, then I click on PhpPgAdmin, and the login
> does not work. I try the password I have given to my entire root
> account, that doesn't work either. I then try the username and
> password for user 'psql' and that doesn't work either. I write to my
> hosting provider, and wait. Google finds this -
> http://faq.cpanel.net/show.cgi?qa=110252867609499 - which I personally
> understand, but I think, hmm this is why Johnny Average doesn't use
> Postgres yet.
>

The funny part about this is that when I have tried to look into things like
this in the past, I've been given the run-around, because c-panel is
proprietary code.  I suspect this probably is some kind of license violation
on code that I have helped write, but I try not to hold it against them...

> As for Cpanel, as much as people think it is 'crap', it is used for
> hosting nearly half the dedicated servers on the planet -- and with
> add-ons and skinning, it is among the most professional platforms out
> there; if not, please share your thoughts on alternatives.
>
> MySQL is being used on some of the biggest forums online, inter alia,
> which have about 20,000 users logged in at *any given time*. Imagine
> the number of queries going in from a BBS script. If Postgres could
> have a bit of this "it just works" mindshare, I am sure BBS would come
> with Postgres as the default too.
>
> And no, seeing Postgres being used on more hosts around the world will
> not kill us. We don't need to sit around tuning Postgres for anyone or
> any particular tools, or drop any effort for HOT and so forth. I am
> merely speaking of making just a little bit of extra effort, supported
> by this "open source group", to make it work with the big widespread
> platforms.
>

You seem to think that anyone who wants to fix this problem just can.
Remember, part of why mysql is so successful is that they can pay people to
do this.  What's more, they can pay the cpanel/plesk people to let them set
this up.  I've tried to help a number of people over the years with these
systems, and if the plesk/cpanel people want to hire me to work on this I
wouldn't turn them down, but honestly I'm not going to pay them to let me fix
thier broken system in my off hours.

> I do not run any Postgres-related training shops or businesses so I do
> not have any vested interests in promoting it, beyond simply loving
> the product, so feel free to take this suggestion with a grain of
> salt. I won't be surprised :)

I'm curious... you seem to have bridged the cpanel/postgres gap, have you
emailed your vendor with a list of changes they need to integrate into thier
product? Have you gotten any response from them?

I've said this before to people and it's true, this is not a problem we can
fix because cpanel is a commercial application, you need to put pressure on
the cpanel people.  If they need changes in PostgreSQL we can look at that,
but without them working on this problem it is never going to change.

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

xzilla@users.sourceforge.net (Robert Treat) writes:
> I've said this before to people and it's true, this is not a problem we can
> fix because cpanel is a commercial application, you need to put pressure on
> the cpanel people.  If they need changes in PostgreSQL we can look at that,
> but without them working on this problem it is never going to change.

It would quite likely represent a pretty intrusive upgrade to CPanel,
and thus more than likely only affect new installs.  The hosting
providers that are happy enough with elder versions of CPanel may not
upgrade, so this mayn't affect anything that matters.

Further, this might not even *actually* help anything.

The window for a DBMS achieving prominence via ubiquitous availability
in web hosting environments may very well have passed.  MySQL[tm] used
that window; even if we got the CPanel folks to do everything we
dreamed, I don't know that we'd actually gain significant prominence
from it.

I still think having better CPanel support in hosting environments
*would* be a good thing, but I wouldn't expect "world-shaking" results
from this...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://linuxfinances.info/info/unix.html
"I will not be numbered, stamped, briefed, debriefed, or filed!"
-- Number Six

On 18/05/07, Robert Treat <xzilla@users.sourceforge.net> wrote:
> On Thursday 17 May 2007 15:15, Andrew Sullivan wrote:
> > On Thu, May 17, 2007 at 02:52:41PM -0400, Joshua Kramer wrote:
> > > I won't argue for or against any team members doing item b.  However,
> > > Shashank noted that there *are* MySQL people doing this, and their effort
> > > is paying off.  Ergo, if some of our community wanted to expend effort in
> > > "integration efforts" rather than "promotion efforts", Postgres would
> > > gain additional traction in the web hosting community.  That's what
> > > Shashank is saying.
> >
> > Well, Shashank also said quite clearly that PostgreSQL is harder to
> > use than MySQL in these cases.  Harder how?  I don't know, and I am
> > surely not the person who can learn from experience, because nothing
> > that I do puts me into any contact with making PostgreSQL play with
> > plesk or any of that other stuff.


Yes that is precisely what I said. MySQL and PgSQL both come as icons
on my Cpanel. Now, because PgSQL had to be separately installed. With
8.x this is not as cumbersome as it used to be before, which
practically kept people off it. Most people anyway, who did not want
to spend days just to get something installed and working. MySQL by
comparison was always a cinch to get working.

But even today, when I have both MySQL and PgSQL, here is how they function:

1. MySQL: I login to cpanel, then I click on PhpMyAdmin and boom, I'm
using the database. It.just.works.

2. PgSQL: I login to cpanel, then I click on PhpPgAdmin, and the login
does not work. I try the password I have given to my entire root
account, that doesn't work either. I then try the username and
password for user 'psql' and that doesn't work either. I write to my
hosting provider, and wait. Google finds this -
http://faq.cpanel.net/show.cgi?qa=110252867609499 - which I personally
understand, but I think, hmm this is why Johnny Average doesn't use
Postgres yet.

As for Cpanel, as much as people think it is 'crap', it is used for
hosting nearly half the dedicated servers on the planet -- and with
add-ons and skinning, it is among the most professional platforms out
there; if not, please share your thoughts on alternatives.

MySQL is being used on some of the biggest forums online, inter alia,
which have about 20,000 users logged in at *any given time*. Imagine
the number of queries going in from a BBS script. If Postgres could
have a bit of this "it just works" mindshare, I am sure BBS would come
with Postgres as the default too.

And no, seeing Postgres being used on more hosts around the world will
not kill us. We don't need to sit around tuning Postgres for anyone or
any particular tools, or drop any effort for HOT and so forth. I am
merely speaking of making just a little bit of extra effort, supported
by this "open source group", to make it work with the big widespread
platforms.

I do not run any Postgres-related training shops or businesses so I do
not have any vested interests in promoting it, beyond simply loving
the product, so feel free to take this suggestion with a grain of
salt. I won't be surprised :)

On Thu, May 17, 2007 at 05:16:36PM -0400, Jonah H. Harris wrote:

> In this thread, I've seen several people say something to the effect
> of, "why is PostgreSQL harder to use than MySQL?"  IMHO, it's
> difficult for us to answer that question because we're all experienced
> with PostgreSQL and have somewhat biased opinions on what it takes to
> administer and manage PostgreSQL compared with other systems.

I agree, but we do seem to have some people who are saying "MySQL is
easier to use with Cpanel or Plesk than is PostgreSQL."  Exactly one
of those people has taken me up on my request to outline _how_ it is
easier.  So far, it sounds to me like the main complaint is that
pg_hba.conf is on the filesystem, which makes it hard to use.  I
agree with Josh Drake on that one: if that's the _real_ barrier, it
shouldn't be that hard to hack up some module to make it work better.
But I don't know whether that is in fact the problem, and nobody
seems to be confirming or denying that that's the issue.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
        --Alexander Hamilton

Andrew Sullivan wrote:
> On Thu, May 17, 2007 at 05:16:36PM -0400, Jonah H. Harris wrote:
>
>> In this thread, I've seen several people say something to the effect
>> of, "why is PostgreSQL harder to use than MySQL?"  IMHO, it's
>> difficult for us to answer that question because we're all experienced
>> with PostgreSQL and have somewhat biased opinions on what it takes to
>> administer and manage PostgreSQL compared with other systems.
>
> I agree, but we do seem to have some people who are saying "MySQL is
> easier to use with Cpanel or Plesk than is PostgreSQL."  Exactly one
> of those people has taken me up on my request to outline _how_ it is
> easier.  So far, it sounds to me like the main complaint is that
> pg_hba.conf is on the filesystem, which makes it hard to use.  I
> agree with Josh Drake on that one: if that's the _real_ barrier, it
> shouldn't be that hard to hack up some module to make it work better.
> But I don't know whether that is in fact the problem, and nobody
> seems to be confirming or denying that that's the issue.

It can be. The problem with Cpanel or Plesk is they assume there is no
access to the filesystem by the end user. So it is certainly easier for
a Cpanel or Plesk to just use MySQL because MySQL allows all control
directly from the database and thus whatever Cpanel or Plesk uses to
connect to the database.

So enabling pg_hba.conf (and frankly postgresql.conf) to be managed from
the DB could be a boon. It would certainly make my life as a DBA easier.

Sincerely,

Joshua D. Drake

>
> A
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Andrew Sullivan wrote:
>> On Thu, May 17, 2007 at 05:16:36PM -0400, Jonah H. Harris wrote:
>>
>>> In this thread, I've seen several people say something to the effect
>>> of, "why is PostgreSQL harder to use than MySQL?"  IMHO, it's
>>> difficult for us to answer that question because we're all experienced
>>> with PostgreSQL and have somewhat biased opinions on what it takes to
>>> administer and manage PostgreSQL compared with other systems.
>>
>> I agree, but we do seem to have some people who are saying "MySQL is
>> easier to use with Cpanel or Plesk than is PostgreSQL."  Exactly one
>> of those people has taken me up on my request to outline _how_ it is
>> easier.  So far, it sounds to me like the main complaint is that
>> pg_hba.conf is on the filesystem, which makes it hard to use.  I
>> agree with Josh Drake on that one: if that's the _real_ barrier, it
>> shouldn't be that hard to hack up some module to make it work better.
>> But I don't know whether that is in fact the problem, and nobody
>> seems to be confirming or denying that that's the issue.
>
> It can be. The problem with Cpanel or Plesk is they assume there is no
> access to the filesystem by the end user. So it is certainly easier for
> a Cpanel or Plesk to just use MySQL because MySQL allows all control
> directly from the database and thus whatever Cpanel or Plesk uses to
> connect to the database.
>
> So enabling pg_hba.conf (and frankly postgresql.conf) to be managed from
> the DB could be a boon. It would certainly make my life as a DBA easier.

It can be using pgAdmin and the adminpack - there's no reason why
phpPgAdmin couldn't do that as well. Admittedly it's not an nice API
that anyone less tightly tied to PostgreSQL would likely want to use.

Still, I guess I'm missing something though because if you can't access
the database because pg_hba.conf is setup incorrectly, how does allowing
you to edit it through PG help you fix the problem?? Surely the answer
is for the host/Cpanel to setup sensible defaults at installation or
database/user creation time?

Regards, Dave.

Joshua D. Drake wrote:

> Magnus Hagander wrote:
>
>> Gavin M. Roy wrote:
>>
>>> I think for one, mysql uses tables for all of its access control.
>>> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
>>> a bit more work, I would imagine.
>>
>>
>> In a lot of environments, it'd certainly be impossible, at least until
>> we make it possible to edit the config files remote... (oops, recap of
>> endless amounts of discussions on letting pgadmin do that..)
>
>
> Well more to the point. There really is zero reason why we can't have
> a table representation of pg_hba_conf that is the pg_hba.conf file
> that has triggers that right out the file.

There are two advantages to having pg_hba.conf a file and not a table:
1) It allows me to configure access permissions *before* bringing up the
database, and
2) If I screw up and forget the postgres password, I can set local host
to "trust", log in, and change it.

Brian

On Friday 18 May 2007 10:34, Dave Page wrote:
> Joshua D. Drake wrote:
> > Andrew Sullivan wrote:
> >> On Thu, May 17, 2007 at 05:16:36PM -0400, Jonah H. Harris wrote:
> >>> In this thread, I've seen several people say something to the effect
> >>> of, "why is PostgreSQL harder to use than MySQL?"  IMHO, it's
> >>> difficult for us to answer that question because we're all experienced
> >>> with PostgreSQL and have somewhat biased opinions on what it takes to
> >>> administer and manage PostgreSQL compared with other systems.
> >>
> >> I agree, but we do seem to have some people who are saying "MySQL is
> >> easier to use with Cpanel or Plesk than is PostgreSQL."  Exactly one
> >> of those people has taken me up on my request to outline _how_ it is
> >> easier.  So far, it sounds to me like the main complaint is that
> >> pg_hba.conf is on the filesystem, which makes it hard to use.  I
> >> agree with Josh Drake on that one: if that's the _real_ barrier, it
> >> shouldn't be that hard to hack up some module to make it work better.
> >> But I don't know whether that is in fact the problem, and nobody
> >> seems to be confirming or denying that that's the issue.
> >
> > It can be. The problem with Cpanel or Plesk is they assume there is no
> > access to the filesystem by the end user. So it is certainly easier for
> > a Cpanel or Plesk to just use MySQL because MySQL allows all control
> > directly from the database and thus whatever Cpanel or Plesk uses to
> > connect to the database.
> >
> > So enabling pg_hba.conf (and frankly postgresql.conf) to be managed from
> > the DB could be a boon. It would certainly make my life as a DBA easier.
>
> It can be using pgAdmin and the adminpack - there's no reason why
> phpPgAdmin couldn't do that as well. Admittedly it's not an nice API
> that anyone less tightly tied to PostgreSQL would likely want to use.
>

Yeah, we don't use it in phppgadmin because I don't like the api, but it is
very doable.  From what I understand the plesk/cpanel people already make
some changes into the systems they take from, so I'm sure thier installer
could be made to set this all up correctly.

> Still, I guess I'm missing something though because if you can't access
> the database because pg_hba.conf is setup incorrectly, how does allowing
> you to edit it through PG help you fix the problem?? Surely the answer
> is for the host/Cpanel to setup sensible defaults at installation or
> database/user creation time?
>

Right... as the op said, when he installed cpanel, he couldn't login via
phppgadmin... clearly this is a case where the initial setup just hasn't been
done very well in the cpanel code, and with a little effort it should be able
to be fixed (other lapp stack type applications have done it), but you've got
to get them to do it.

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

Brian Hurt wrote:

> There are two advantages to having pg_hba.conf a file and not a table:
> 1) It allows me to configure access permissions *before* bringing up the
> database, and

No problem -- just set tight default permissions in the first place.
(We do have an initdb switch to control this, actually).

> 2) If I screw up and forget the postgres password, I can set local host
> to "trust", log in, and change it.

No problem -- stop the postmaster, start a standalone backend, change
the password, restart.  This is the fix we suggest for when you revoke
superuserness from all users, for example.

The only problem with this solution is that it requires a restart,
whereas the one you propose only requires you to reload.  But then, if
you reload with very open permissions to be able to change the password,
do you really want to be allowing anybody else near the database?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Dave Page wrote:
> Joshua D. Drake wrote:
>> Andrew Sullivan wrote:
>>> On Thu, May 17, 2007 at 05:16:36PM -0400, Jonah H. Harris wrote:

> Still, I guess I'm missing something though because if you can't access
> the database because pg_hba.conf is setup incorrectly, how does allowing
> you to edit it through PG help you fix the problem?? Surely the answer
> is for the host/Cpanel to setup sensible defaults at installation or
> database/user creation time?

Well I was speaking from overall maintenance perspective. Obviously
during initial configuration there should be some reasonable defaults
configured.

What I don't like is the need to log into the server at the operating
system level and open a text file that has no constraints on mistakes
that can be made within it.

The overall idea at least from my perspective, regardless of Cpanel or
Plesk is that we shouldn't have to edit from the operating system. SQL
should be enough.

Sincerely,

Joshua D. Drake

>
> Regards, Dave.
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Brian Hurt wrote:
> Joshua D. Drake wrote:
as triggers that right out the file.
>
> There are two advantages to having pg_hba.conf a file and not a table:
> 1) It allows me to configure access permissions *before* bringing up the
> database, and

We could still do that, either via initdb or if after initdb have a
command line utility that allows it.

> 2) If I screw up and forget the postgres password, I can set local host
> to "trust", log in, and change it.

Obviously this would be a safe guard that would have to be put in place
just like any other remote protocol there would need to be away around a
potential problem like that.


Joshua D. Drake

>
> Brian
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> we shouldn't have to edit from the operating system. SQL
> should be enough.

Chicken; meet egg. Egg; meet chicken.

:-p

/D

Dave Page wrote:
> Joshua D. Drake wrote:
>> we shouldn't have to edit from the operating system. SQL
>> should be enough.
>
> Chicken; meet egg. Egg; meet chicken.

No, not really. There are solutions that work for this.

Joshua D. Drake







>
> :-p
>
> /D
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Dave Page wrote:
>> Joshua D. Drake wrote:
>>> we shouldn't have to edit from the operating system. SQL
>>> should be enough.
>>
>> Chicken; meet egg. Egg; meet chicken.
>
> No, not really. There are solutions that work for this.

You missed the :-p

/D

On Fri, May 18, 2007 at 04:15:38PM +0100, Dave Page wrote:
> Joshua D. Drake wrote:
> > we shouldn't have to edit from the operating system. SQL
> > should be enough.
>
> Chicken; meet egg. Egg; meet chicken.
>
> :-p
>
> /D
>

What if we had an entry in pg_hba.conf that would allow access
control for particular DB's/IP's/... to use an SQL table:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

host    all         all         127.0.0.1         255.255.255.255   sql

Ken

> On Thu, May 17, 2007 at 08:38:44AM +0200, Gabriele Bartolini wrote:
>> Jim C. Nasby ha scritto:
>>> Another serious issue (at least in the US) is the relative lack of
>>> PostgreSQL folks for permanent hire (ie: not contract). Is the
>>> situation
>>> different in Italy?
>> Unfortunately no. University either teach proprietary databases or
>> MySQL.  I guess it is just a vicious circle. There cannot be an
>> extended
>> and diffused offer of DBA-related classes in the academic world about
>> PostgreSQL, if the market is not well aware of its potential. That
>> is at
>> least the situation in Italy. I mostly it is due to ignorance (at
>> least
>> I hope it is just that).
>
> It probably is. See my reply to Andrew about what we need to do to
> change that. Though I don't know how many companies in Italy do
> PostgreSQL training besides EnterpriseDB.

I know for fact that at least one college in the US teaches
PostgreSQL religiously.  Problem is, it's small.  Not sure how it'd
play in, but would it be couth to create a page of colleges/
universities that use PostgreSQL as their DB teaching platform?

Kevin

On May 17, 2007, at 7:41 PM, Shashank Tripathi wrote:

>
> But even today, when I have both MySQL and PgSQL, here is how they
> function:
>
> 1. MySQL: I login to cpanel, then I click on PhpMyAdmin and boom, I'm
> using the database. It.just.works.
>
> 2. PgSQL: I login to cpanel, then I click on PhpPgAdmin, and the login
> does not work. I try the password I have given to my entire root
> account, that doesn't work either. I then try the username and
> password for user 'psql' and that doesn't work either. I write to my
> hosting provider, and wait. Google finds this -
> http://faq.cpanel.net/show.cgi?qa=110252867609499 - which I personally
> understand, but I think, hmm this is why Johnny Average doesn't use
> Postgres yet.
>

It sounds like CPanel is broken. This doesn't surprise me, but
I'm surprised you're not recognizing what you're seeing.

I distribute an application that bundles Postgresql. It preloads
user accounts and sets up authentication automatically at
installation. It's not entirely trivial to get everything right, but
it only took me a couple of hours to write the code to do it.
Works fine, many deployed sites, no reports of problems ever.

So... wrapping an application specific installation and
post-installation configuration wrapper around postgresql
is pretty trivial.

Have you contacted Cpanel and asked them to fix their
code? What was their response?

Cheers,
   Steve

Joshua D. Drake schrieb:
> Magnus Hagander wrote:
>> Gavin M. Roy wrote:
>>> I think for one, mysql uses tables for all of its access control.
>>> Coding plesk/cpanel to modify pg_hba.conf and rehup postgres would take
>>> a bit more work, I would imagine.
>>
>> In a lot of environments, it'd certainly be impossible, at least until
>> we make it possible to edit the config files remote... (oops, recap of
>> endless amounts of discussions on letting pgadmin do that..)
>
> Well more to the point. There really is zero reason why we can't have a
> table representation of pg_hba_conf that is the pg_hba.conf file that
> has triggers that right out the file.
>
>
>>
>>> Do we really want to pursue making PostgreSQL easier to admin for the
>>> non-system admin?  Cpanel and plesk and like tools are pretty far down
>>> the list of important things to support or code for.
>>
>> If we want to make inroads into shared-hosting environments, it would
>> certainly help...
>
> It is not just shared hosting... dedicated hosting starts as little as
> 69.00 with Cpanel :)...
>
> Note that I am not advocating making it easier for Cpanel. I am just
> making a point that it is not limited to shared hosting.
>
> I am however advocating that it is pretty dumb that our conf files are
> *required* as a little text file on the filesystem and can not be
> managed via the database.

Furthermore, you would even want to have more options on the entire
permission and account management.

One way I could envision is a setting in postgresql.conf to tell
the postmaster which access control to use and make it pluggable.

This way you can use pg_hba.conf, dedicated system tables or
maybe LDAP one day. (or just another postgres database)

Does it sound too easy? I hope so :-)

Regards
Tino

Tino Wildenhain wrote:

> This way you can use pg_hba.conf, dedicated system tables or
> maybe LDAP one day. (or just another postgres database)
>
> Does it sound too easy? I hope so :-)

Actually, that sounds bad. PostgreSQL should be the source of its own auth.

Joshua D. Drake

>
> Regards
> Tino
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Sun, May 20, 2007 at 09:32:10AM -0700, Joshua D. Drake wrote:
>
> Actually, that sounds bad. PostgreSQL should be the source of its own auth.

In a managed-hosting environment, are you sure that's the case?

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
                --Brad Holland

Joshua D. Drake schrieb:
> Tino Wildenhain wrote:
>
>> This way you can use pg_hba.conf, dedicated system tables or
>> maybe LDAP one day. (or just another postgres database)
>>
>> Does it sound too easy? I hope so :-)
>
> Actually, that sounds bad. PostgreSQL should be the source of its own auth.

Well but this makes it harder to have it play nicely in bigger
environments where you have a natural preference for centralistic
user and permission management. And for people like you you
would just use the file based (e.g. pg_hba.conf) system :-)

Regards
Tino

jd@commandprompt.com ("Joshua D. Drake") writes:
> Tino Wildenhain wrote:
>
>> This way you can use pg_hba.conf, dedicated system tables or
>> maybe LDAP one day. (or just another postgres database)
>> Does it sound too easy? I hope so :-)
>
> Actually, that sounds bad. PostgreSQL should be the source of its own auth.

If there's a clear *OTHER* authority in the matter (e.g. - an LDAP
instance that manages numerous other things), then that's manifestly
not the case.

Making a selection of mechanisms configurable seems entirely
reasonable to me.

In a web hosting environment, it would seem quite reasonable for
authentication to be controlled in some central way that's *not*
necessarily PG-based.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/internet.html
"Don't use C; In  my opinion, C is  a library programming language not
an  app programming language."   --   Owen  Taylor  (GTK+  and   ORBit
developer)

On Sun, May 20, 2007 at 10:07:14PM -0400, Chris Browne wrote:
> jd@commandprompt.com ("Joshua D. Drake") writes:
> > Tino Wildenhain wrote:
> >
> >> This way you can use pg_hba.conf, dedicated system tables or
> >> maybe LDAP one day. (or just another postgres database) Does it
> >> sound too easy? I hope so :-)
> >
> > Actually, that sounds bad. PostgreSQL should be the source of its
> > own auth.
>
> If there's a clear *OTHER* authority in the matter (e.g. - an LDAP
> instance that manages numerous other things), then that's manifestly
> not the case.

There is a math problem with this, namely that LDAP auth systems
assume a tree, where PostgreSQL's ROLEs are actually a directed
acyclic graph.

> Making a selection of mechanisms configurable seems entirely
> reasonable to me.
>
> In a web hosting environment, it would seem quite reasonable for
> authentication to be controlled in some central way that's *not*
> necessarily PG-based.

It's far from clear to me that creating a high-value target with
catastrophic cascading failure modes--a single sign-on system is an
example of this--is a design goal we should "help" people implement.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On Sun, 20 May 2007, Chris Browne wrote:

> Making a selection of mechanisms configurable seems entirely
> reasonable to me.

But, whilst creating these systems, we must keep in mind security.
Instead of replacing the auth routines, how about:

1. Keep the auth routines currently present in PG, so that as a DB admin I
*can* restrict DB's via file only as I do now, and

2. To support those who want LDAP or table-based auth, add a setting to
pg_hba.conf so that a second code-path can be activated on a per-db basis.

I view MySQL's table-based auth mechanisms as a bug, not a feature.

Cheers,
-J

On Mon, May 21, 2007 at 07:57:57AM -0700, David Fetter wrote:
> It's far from clear to me that creating a high-value target with
> catastrophic cascading failure modes--a single sign-on system is an
> example of this--is a design goal we should "help" people implement.

Well, remember, this thread started with claims that Postgres was too
hard to use with CPanel and Plesk.  So if the goal is to make that
less painful, that needn't entail that everyone has to use some
single-sign-on mechanism.  (I'll also note that systems without
single sign on have plenty of equivalently bad failure modes.  Any
time you let people into computers, you start to have a problem.)

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
        --Damien Katz

On Fri, 18 May 2007 01:29:56 -0400
Robert Treat <xzilla@users.sourceforge.net> wrote:

> > I do not run any Postgres-related training shops or businesses so I do
> > not have any vested interests in promoting it, beyond simply loving
> > the product, so feel free to take this suggestion with a grain of
> > salt. I won't be surprised :)
>
> I'm curious... you seem to have bridged the cpanel/postgres gap, have you
> emailed your vendor with a list of changes they need to integrate into thier
> product? Have you gotten any response from them?
>
> I've said this before to people and it's true, this is not a problem we can
> fix because cpanel is a commercial application, you need to put pressure on
> the cpanel people.  If they need changes in PostgreSQL we can look at that,
> but without them working on this problem it is never going to change.

I agree. and, FWIW, Plesk also supports PosgreSQL ( at least since 8.x )...

as to the 'PGSQL being hard to manage remotely'... i dont' know what you are
on. Any system that has been locked down properly (or defaults to locked down)
will have to be reconfigured to move off the default state. I really don't see
how pgsql is any harder for plesk or cpanel to  support than mysql,other than ,
as you say, they've been paid to do it.

_________________________
{Beto|Norberto|Numard} Meijome

"Unix is very simple, but it takes a genius to understand the simplicity."
   Dennis Ritchie

I speak for myself, not my employer. Contents may be hot. Slippery when wet.
Reading disclaimers makes you go blind. Writing them is worse. You have been
Warned.

On Thu, 17 May 2007 16:52:34 -0400
"Gavin M. Roy" <gmr@myyearbook.com> wrote:

> I think for one, mysql uses tables for all of its access control.  Coding
> plesk/cpanel to modify pg_hba.conf and rehup postgres would take a bit more
> work, I would imagine.

at least for plesk, it already does all kinds of those kinds of modifications
on system files. All the information is backed in psa's own dB, and then
written out. I dont think it'd be anything new for swsoft to handle. Anyway,
they already support pgsql, so it may be already done.
_________________________
{Beto|Norberto|Numard} Meijome

Bug: a feature that can't be turned off.

I speak for myself, not my employer. Contents may be hot. Slippery when wet.
Reading disclaimers makes you go blind. Writing them is worse. You have been
Warned.