Thread

Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-07T10:51:52Z

When there is a specific reject rule, why does the server say 

FATAL:  no pg_hba.conf entry

That sounds like an administrative error, rather than a specific
decision on the part of an admin to reject the connection. Suggested
message would be

FATAL: connection rejected for host "xxx", user "xxxx", database "xxx"

Clearly needs to be secure. Does the second message give any information
to a would-be hacker than the first? I don't think so, but it certainly
helps an admin work out if they've missed something.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-07T14:46:14Z

Simon Riggs <simon@2ndQuadrant.com> writes:
> When there is a specific reject rule, why does the server say 
> FATAL:  no pg_hba.conf entry

It's intentional.  We try to expose the minimum amount of knowledge
about the contents of pg_hba.conf to potential attackers.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Jaime Casanova <jcasanov@systemguards.com.ec> — 2010-04-07T15:41:23Z

On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> When there is a specific reject rule, why does the server say
>> FATAL:  no pg_hba.conf entry
>
> It's intentional.  We try to expose the minimum amount of knowledge
> about the contents of pg_hba.conf to potential attackers.
>

i just tried it in CVS and in 8.4 and when i put a reject rule on
pg_hba.conf what i get is:
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "mic",
database "mic"

so we are giving a lot of info already changing "no pg_hba.conf entry"
for "connection rejected" doesn't seem like a lot more and the change
could be useful for a DBA understanding what happens

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-07T15:43:44Z

Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's intentional. We try to expose the minimum amount of knowledge
>> about the contents of pg_hba.conf to potential attackers.

> i just tried it in CVS and in 8.4 and when i put a reject rule on
> pg_hba.conf what i get is:
> psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "mic",
> database "mic"

> so we are giving a lot of info already

All three of those data values are known to the client; they don't add
knowledge about what is in pg_hba.conf.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Josh Berkus <josh@agliodbs.com> — 2010-04-07T16:53:35Z

> Clearly needs to be secure. Does the second message give any information
> to a would-be hacker than the first? I don't think so, but it certainly
> helps an admin work out if they've missed something.

I think this question needs a bona fide network security geek to decide,
rather than us database geeks.  Hello!  Is there a security hacker in
the house?

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-07T17:07:21Z

On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> When there is a specific reject rule, why does the server say
>> FATAL:  no pg_hba.conf entry
>
> It's intentional.  We try to expose the minimum amount of knowledge
> about the contents of pg_hba.conf to potential attackers.

The problem with the message is not that it's uninformative, but that
it's counterfactual.

...Robert

Re: Thoughts on pg_hba.conf rejection

Joshua Tolley <eggyknap@gmail.com> — 2010-04-08T23:12:31Z

On Wed, Apr 07, 2010 at 01:07:21PM -0400, Robert Haas wrote:
> On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Simon Riggs <simon@2ndQuadrant.com> writes:
> >> When there is a specific reject rule, why does the server say
> >> FATAL:  no pg_hba.conf entry
> >
> > It's intentional.  We try to expose the minimum amount of knowledge
> > about the contents of pg_hba.conf to potential attackers.
> 
> The problem with the message is not that it's uninformative, but that
> it's counterfactual.
> 
> ...Robert

I agree (I noticed and was bothered by this today, as a matter of irrelevant
fact). I can support the idea of exposing as little as possible of
pg_hba.conf, but ISTM the "no pg_hba.conf entry" is exposing too much, by that
standard. Just say something like "connection disallowed" and leave it at that
-- either it's disallowed by lack of a rule, or by existence of a "reject"
rule, or by something else entirely. As long as the message isn't clearly
wrong in the "reject" case, as it is now.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Re: Thoughts on pg_hba.conf rejection

Bruce Momjian <bruce@momjian.us> — 2010-04-14T20:19:14Z

Joshua Tolley wrote:
-- Start of PGP signed section.
> On Wed, Apr 07, 2010 at 01:07:21PM -0400, Robert Haas wrote:
> > On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Simon Riggs <simon@2ndQuadrant.com> writes:
> > >> When there is a specific reject rule, why does the server say
> > >> FATAL: ?no pg_hba.conf entry
> > >
> > > It's intentional. ?We try to expose the minimum amount of knowledge
> > > about the contents of pg_hba.conf to potential attackers.
> > 
> > The problem with the message is not that it's uninformative, but that
> > it's counterfactual.
> > 
> > ...Robert
> 
> I agree (I noticed and was bothered by this today, as a matter of irrelevant
> fact). I can support the idea of exposing as little as possible of
> pg_hba.conf, but ISTM the "no pg_hba.conf entry" is exposing too much, by that
> standard. Just say something like "connection disallowed" and leave it at that
> -- either it's disallowed by lack of a rule, or by existence of a "reject"
> rule, or by something else entirely. As long as the message isn't clearly
> wrong in the "reject" case, as it is now.

Did we come to any conclusion on this?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

Re: Thoughts on pg_hba.conf rejection

Aidan Van Dyk <aidan@highrise.ca> — 2010-04-14T20:24:13Z

* Bruce Momjian <bruce@momjian.us> [100414 16:20]:
> Joshua Tolley wrote:
> -- Start of PGP signed section.
> > On Wed, Apr 07, 2010 at 01:07:21PM -0400, Robert Haas wrote:
> > > On Wed, Apr 7, 2010 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > > Simon Riggs <simon@2ndQuadrant.com> writes:
> > > >> When there is a specific reject rule, why does the server say
> > > >> FATAL: ?no pg_hba.conf entry
> > > >
> > > > It's intentional. ?We try to expose the minimum amount of knowledge
> > > > about the contents of pg_hba.conf to potential attackers.
> > > 
> > > The problem with the message is not that it's uninformative, but that
> > > it's counterfactual.
> > > 
> > > ...Robert
> > 
> > I agree (I noticed and was bothered by this today, as a matter of irrelevant
> > fact). I can support the idea of exposing as little as possible of
> > pg_hba.conf, but ISTM the "no pg_hba.conf entry" is exposing too much, by that
> > standard. Just say something like "connection disallowed" and leave it at that
> > -- either it's disallowed by lack of a rule, or by existence of a "reject"
> > rule, or by something else entirely. As long as the message isn't clearly
> > wrong in the "reject" case, as it is now.
> 
> Did we come to any conclusion on this?

I think it sort of just died.  I'm in favour of making sure we don't
give out any extra information, so if the objection to the message is
simply that "no pg_hba.conf entry" is "counterfactual" when there is an
entry rejecting it, how about:
   "No pg_hba.conf authorizing entry"

That's no longer counter-factual, and works for both no entry, and a
rejecting entry...

a.
-- 
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-14T20:28:44Z

On Wed, Apr 14, 2010 at 4:24 PM, Aidan Van Dyk <aidan@highrise.ca> wrote:
> I think it sort of just died.  I'm in favour of making sure we don't
> give out any extra information, so if the objection to the message is
> simply that "no pg_hba.conf entry" is "counterfactual" when there is an
> entry rejecting it, how about:
>   "No pg_hba.conf authorizing entry"
>
> That's no longer counter-factual, and works for both no entry, and a
> rejecting entry...

That works for me.  I don't have strong feelings about it so I'd
probably be OK to a variety of solutions subject to my previous
remarks, but that seems as good as anything.

...Robert

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-14T21:51:38Z

On Wed, Apr 14, 2010 at 4:28 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 14, 2010 at 4:24 PM, Aidan Van Dyk <aidan@highrise.ca> wrote:
>> I think it sort of just died.  I'm in favour of making sure we don't
>> give out any extra information, so if the objection to the message is
>> simply that "no pg_hba.conf entry" is "counterfactual" when there is an
>> entry rejecting it, how about:
>>   "No pg_hba.conf authorizing entry"
>>
>> That's no longer counter-factual, and works for both no entry, and a
>> rejecting entry...
>
> That works for me.  I don't have strong feelings about it so I'd
> probably be OK to a variety of solutions subject to my previous
> remarks, but that seems as good as anything.

Although on further reflection, part of me feels like it might be even
simpler and clearer to simply say:

connection not authorized

...Robert

Re: Thoughts on pg_hba.conf rejection

Jaime Casanova <jcasanov@systemguards.com.ec> — 2010-04-14T21:57:08Z

On Wed, Apr 14, 2010 at 4:51 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 14, 2010 at 4:28 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Apr 14, 2010 at 4:24 PM, Aidan Van Dyk <aidan@highrise.ca> wrote:
>>> I think it sort of just died.  I'm in favour of making sure we don't
>>> give out any extra information, so if the objection to the message is
>>> simply that "no pg_hba.conf entry" is "counterfactual" when there is an
>>> entry rejecting it, how about:
>>>   "No pg_hba.conf authorizing entry"
>>>
>>> That's no longer counter-factual, and works for both no entry, and a
>>> rejecting entry...
>>
>> That works for me.  I don't have strong feelings about it so I'd
>> probably be OK to a variety of solutions subject to my previous
>> remarks, but that seems as good as anything.
>
> Although on further reflection, part of me feels like it might be even
> simpler and clearer to simply say:
>
> connection not authorized
>

+1

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-14T22:46:35Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 14, 2010 at 4:24 PM, Aidan Van Dyk <aidan@highrise.ca> wrote:
>> I think it sort of just died. I'm in favour of making sure we don't
>> give out any extra information, so if the objection to the message is
>> simply that "no pg_hba.conf entry" is "counterfactual" when there is an
>> entry rejecting it, how about:
>>  "No pg_hba.conf authorizing entry"
>> 
>> That's no longer counter-factual, and works for both no entry, and a
>> rejecting entry...

> That works for me.

It needs copy-editing.  Maybe
	no pg_hba.conf entry allows access for host ... user ...

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-15T00:19:45Z

I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Wed, Apr 14, 2010 at 4:24 PM, Aidan Van Dyk <aidan@highrise.ca> wrote:
>>> I think it sort of just died. I'm in favour of making sure we don't
>>> give out any extra information, so if the objection to the message is
>>> simply that "no pg_hba.conf entry" is "counterfactual" when there is an
>>> entry rejecting it, how about:
>>>  "No pg_hba.conf authorizing entry"
>>> 
>>> That's no longer counter-factual, and works for both no entry, and a
>>> rejecting entry...

>> That works for me.

> It needs copy-editing.  Maybe
> 	no pg_hba.conf entry allows access for host ... user ...

Actually, on reflection, I'm not sure that these suggestions really do
anything for the "counter-factual" complaint.  The case where you'd
normally use an explicit REJECT entry is where you're REJECTing some
limited case in an entry that is before a wider-scope entry that would
accept it.  So it doesn't seem entirely accurate to say that there is no
pg_hba.conf entry that would accept the connection.  There is one but
it's not the one we chose.

I'm thinking there isn't anything much we can do here without using a
different message wording for a match to a REJECT entry.  So it's a
straight-up tradeoff of possible security information leakage against
whether a different wording is really helpful to the admin.  Both of
those seem like fairly marginal concerns, really, so I'm having a hard
time deciding which one ought to win.  But given that nobody complained
before this, is it worth changing?

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-15T00:22:30Z

On Wed, Apr 14, 2010 at 8:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm thinking there isn't anything much we can do here without using a
> different message wording for a match to a REJECT entry.  So it's a
> straight-up tradeoff of possible security information leakage against
> whether a different wording is really helpful to the admin.  Both of
> those seem like fairly marginal concerns, really, so I'm having a hard
> time deciding which one ought to win.  But given that nobody complained
> before this, is it worth changing?

What's wrong with something like "connection not permitted" or
"connection not authorized"?

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-15T00:27:55Z

Robert Haas <robertmhaas@gmail.com> writes:
> What's wrong with something like "connection not permitted" or
> "connection not authorized"?

The case that we're trying to cater to with the existing wording is
novice DBAs, who are likely to stare at such a message and not even
realize that pg_hba.conf is what they need to change.  Frankly, by
the time anyone is using REJECT entries they are probably advanced
enough to not need much help from the error message; but what you
propose is an absolute lock to increase the number of newbie questions
on the lists by a large factor.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Bruce Momjian <bruce@momjian.us> — 2010-04-15T00:31:17Z

Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > What's wrong with something like "connection not permitted" or
> > "connection not authorized"?
> 
> The case that we're trying to cater to with the existing wording is
> novice DBAs, who are likely to stare at such a message and not even
> realize that pg_hba.conf is what they need to change.  Frankly, by
> the time anyone is using REJECT entries they are probably advanced
> enough to not need much help from the error message; but what you
> propose is an absolute lock to increase the number of newbie questions
> on the lists by a large factor.

Agreed.  I would rather have an inaccurate error message that mentions
pg_hba.conf than an accurate one that doesn't.

Error messages should always point at a solution, if possible.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-15T00:37:18Z

On Wed, Apr 14, 2010 at 8:31 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Tom Lane wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>> > What's wrong with something like "connection not permitted" or
>> > "connection not authorized"?
>>
>> The case that we're trying to cater to with the existing wording is
>> novice DBAs, who are likely to stare at such a message and not even
>> realize that pg_hba.conf is what they need to change.  Frankly, by
>> the time anyone is using REJECT entries they are probably advanced
>> enough to not need much help from the error message; but what you
>> propose is an absolute lock to increase the number of newbie questions
>> on the lists by a large factor.
>
> Agreed.  I would rather have an inaccurate error message that mentions
> pg_hba.conf than an accurate one that doesn't.
>
> Error messages should always point at a solution, if possible.

OK, how about "connection not authorized by pg_hba.conf"?

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-15T02:21:45Z

Robert Haas <robertmhaas@gmail.com> writes:
> OK, how about "connection not authorized by pg_hba.conf"?

This is still not especially helpful for novice DBAs.  We want to point
them in the direction that they need to add an entry to pg_hba.conf,
which is 99% likely to be what's wanted.  The current wording provides
that hint; vague statements like the above don't.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-15T02:55:31Z

On Wed, Apr 14, 2010 at 10:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> OK, how about "connection not authorized by pg_hba.conf"?
>
> This is still not especially helpful for novice DBAs.  We want to point
> them in the direction that they need to add an entry to pg_hba.conf,
> which is 99% likely to be what's wanted.  The current wording provides
> that hint; vague statements like the above don't.

*scratches head*

So you'd prefer a message that is sometimes flat-out wrong over a
message that is correct but less informative in the common case?  I
guess that could be right call, but it's not what I'd pick.

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-15T04:24:13Z

Robert Haas <robertmhaas@gmail.com> writes:
> So you'd prefer a message that is sometimes flat-out wrong over a
> message that is correct but less informative in the common case?  I
> guess that could be right call, but it's not what I'd pick.

Well, as I said, I think the only way to really improve this message
is to use a different wording for the REJECT case.  I'm unconvinced
that the problem justifies that, but if you're sufficiently hot about
it, that is the direction to go in; not making the the message less
useful for the 99% case.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-04-15T06:35:13Z

Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> So you'd prefer a message that is sometimes flat-out wrong over a
>> message that is correct but less informative in the common case?  I
>> guess that could be right call, but it's not what I'd pick.
> 
> Well, as I said, I think the only way to really improve this message
> is to use a different wording for the REJECT case.  I'm unconvinced
> that the problem justifies that, but if you're sufficiently hot about
> it, that is the direction to go in; not making the the message less
> useful for the 99% case.

How about a hint?

FATAL:  connection not authorized for host "[local]", user "foo",
database "postgres"
HINT:  Make sure that you have a matching accept line in pg_hba.conf

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Re: Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-15T07:17:46Z

On Thu, 2010-04-15 at 00:24 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > So you'd prefer a message that is sometimes flat-out wrong over a
> > message that is correct but less informative in the common case?  I
> > guess that could be right call, but it's not what I'd pick.
> 
> Well, as I said, I think the only way to really improve this message
> is to use a different wording for the REJECT case.  I'm unconvinced
> that the problem justifies that, but if you're sufficiently hot about
> it, that is the direction to go in; not making the the message less
> useful for the 99% case.

I think that would solve my original gripe, if I understood the idea.

So instead of the typical "reject" instruction we also add a
"rejectverbose" instruction that has a more verbose message. Docs would
describe it as an additional instruction to assist with debugging a
complex pg_hba.conf, with warning that if used it may assist the bad
guys also.

"pg_hba.conf rejects entry for host..."

Patch for that would be simple and clear; I can add that if we agree.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Stephen Frost <sfrost@snowman.net> — 2010-04-15T13:08:54Z

Simon,

* Simon Riggs (simon@2ndQuadrant.com) wrote:
> So instead of the typical "reject" instruction we also add a
> "rejectverbose" instruction that has a more verbose message. Docs would
> describe it as an additional instruction to assist with debugging a
> complex pg_hba.conf, with warning that if used it may assist the bad
> guys also.

Erm, so we'd add an option for this?  That strikes me as pretty
excessive.  Not to be a pain, but I feel like the 'connection not
authorized' argument plus a hint makes alot more sense.

> "pg_hba.conf rejects entry for host..."

"connection not authorized for host X user Y database Z"
"HINT: Make sure your pg_hba.conf has the entries needed and the user
has CONNECT privileges for the database"

Or something along those lines (I added the other CONNECT issue because
it's one I've run into in the past.. :).

I do also wonder if we should consider having the error that's reported
to the log differ from that which is sent to the user..  I realize
that's a much bigger animal and might not help the novice, but it could
help with debugging complex pg_hba's without exposing info to possible
bad guys.

	Thanks,

		Stephen

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-15T13:44:19Z

Stephen Frost <sfrost@snowman.net> writes:
> * Simon Riggs (simon@2ndQuadrant.com) wrote:
>> So instead of the typical "reject" instruction we also add a
>> "rejectverbose" instruction that has a more verbose message.

> Erm, so we'd add an option for this?  That strikes me as pretty
> excessive.

I think Simon's point was that we'd need a different uaReject enum
value internally in the code, so that the place where the message
gets issued would be able to distinguish explicit REJECT entry from
falling off the end of the file.  Changing what the user is expected
to put in the file would be silly.  (I don't care for "rejectverbose"
though.  Maybe uaImplicitReject for the end-of-file case would be
the most readable way.)

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

David Fetter <david@fetter.org> — 2010-04-15T15:28:17Z

On Wed, Apr 14, 2010 at 08:37:18PM -0400, Robert Haas wrote:
> On Wed, Apr 14, 2010 at 8:31 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Tom Lane wrote:
> >> Robert Haas <robertmhaas@gmail.com> writes:
> >> > What's wrong with something like "connection not permitted" or
> >> > "connection not authorized"?
> >>
> >> The case that we're trying to cater to with the existing wording
> >> is novice DBAs, who are likely to stare at such a message and not
> >> even realize that pg_hba.conf is what they need to change.
> >>  Frankly, by the time anyone is using REJECT entries they are
> >> probably advanced enough to not need much help from the error
> >> message; but what you propose is an absolute lock to increase the
> >> number of newbie questions on the lists by a large factor.
> >
> > Agreed.  I would rather have an inaccurate error message that
> > mentions pg_hba.conf than an accurate one that doesn't.
> >
> > Error messages should always point at a solution, if possible.
> 
> OK, how about "connection not authorized by pg_hba.conf"?

+1.  It's clear, and if an attacker can compromise pg_hba.conf,
there's nothing PostgreSQL can do to help.

I'd like to bring up the idea of an attacker who both has that access
and doesn't know about pg_hba.conf just to dismiss it.  Such a person
might exist, but we don't need to bend things around a case so rare
that it makes being struck by lightning look like a certainty. :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

Re: Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-19T19:10:49Z

On Thu, 2010-04-15 at 09:44 -0400, Tom Lane wrote:
> Maybe uaImplicitReject for the end-of-file case would be
> the most readable way.

uaImplicitReject capability added.

We're now free to bikeshed on exact wording. After much heavy thinking,
message is "pg_hba.conf rejects..." with no hint (yet?).

Point of note on giving information to the bad guys: if a
should-be-rejected connection request attempts to connect to a
non-existent database, we say "database does not exist". If db does
exist we say "pg_hba.conf rejects...". To me that looks like giving info
away... if an IP address range is rejected always then telling them
whether or not a particular database name exists seems like something I
would not wish to expose.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-19T20:30:21Z

Simon Riggs <simon@2ndQuadrant.com> writes:
> Point of note on giving information to the bad guys: if a
> should-be-rejected connection request attempts to connect to a
> non-existent database, we say "database does not exist".

Yeah.  This was an acknowledged shortcoming of the changes to eliminate
flat-file storage of authentication information --- as of 9.0, it's
necessary to connect to some database in order to proceed with auth
checking.  We discussed it at the time and agreed it was an acceptable
loss.

The only way I can think of to improve that without going back to flat
files would be to develop a way for backends to switch databases after
initial startup, so that auth could be done in a predetermined database
(say, "postgres") before switching to the requested DB.  This has enough
potential gotchas, in regards to catalog caching for instance, that I'm
not eager to go there.

Alternatively we could lie, and produce an auth failure message of some
sort rather than admitting the DB doesn't exist.  But that seems like
it's going to create enough confusion to not be acceptable.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-19T20:45:07Z

On Mon, 2010-04-19 at 16:30 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > Point of note on giving information to the bad guys: if a
> > should-be-rejected connection request attempts to connect to a
> > non-existent database, we say "database does not exist".
> 
> Yeah.  This was an acknowledged shortcoming of the changes to eliminate
> flat-file storage of authentication information --- as of 9.0, it's
> necessary to connect to some database in order to proceed with auth
> checking.  

With code as currently, yes, though I see that there is a way to do
this. 

Rules that have an "all" in the database field of the hba can be applied
prior to attempting to select the database, as long as the "all" rule is
above any database-specific rules. So its possible, we just need to
special case the code so we call it once before db is assigned for "all"
rules only and then again later, as is now, including 100% of rules. (I
say 100% to avoid using the word all in two contexts in same sentence).

> We discussed it at the time and agreed it was an acceptable
> loss.
> 
> The only way I can think of to improve that without going back to flat
> files would be to develop a way for backends to switch databases after
> initial startup, so that auth could be done in a predetermined database
> (say, "postgres") before switching to the requested DB.  This has enough
> potential gotchas, in regards to catalog caching for instance, that I'm
> not eager to go there.
> 
> Alternatively we could lie, and produce an auth failure message of some
> sort rather than admitting the DB doesn't exist.  But that seems like
> it's going to create enough confusion to not be acceptable.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-19T20:59:14Z

On Mon, Apr 19, 2010 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> Point of note on giving information to the bad guys: if a
>> should-be-rejected connection request attempts to connect to a
>> non-existent database, we say "database does not exist".
>
> Yeah.  This was an acknowledged shortcoming of the changes to eliminate
> flat-file storage of authentication information --- as of 9.0, it's
> necessary to connect to some database in order to proceed with auth
> checking.  We discussed it at the time and agreed it was an acceptable
> loss.
>
> The only way I can think of to improve that without going back to flat
> files would be to develop a way for backends to switch databases after
> initial startup, so that auth could be done in a predetermined database
> (say, "postgres") before switching to the requested DB.  This has enough
> potential gotchas, in regards to catalog caching for instance, that I'm
> not eager to go there.

Would it be possible to set up a skeleton environment where we can
access shared catalogs only and then decide on which database we're
using later?

...Robert

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-19T21:04:07Z

Robert Haas escribió:
> On Mon, Apr 19, 2010 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > The only way I can think of to improve that without going back to flat
> > files would be to develop a way for backends to switch databases after
> > initial startup, so that auth could be done in a predetermined database
> > (say, "postgres") before switching to the requested DB.  This has enough
> > potential gotchas, in regards to catalog caching for instance, that I'm
> > not eager to go there.
> 
> Would it be possible to set up a skeleton environment where we can
> access shared catalogs only and then decide on which database we're
> using later?

Eh?  We already do that ... In fact the autovac launcher is always
connected to shared catalogs, without being connected to any one
database in particular (cf. get_database_list)

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

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-19T21:08:51Z

On Mon, Apr 19, 2010 at 5:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Robert Haas escribió:
>> On Mon, Apr 19, 2010 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> > The only way I can think of to improve that without going back to flat
>> > files would be to develop a way for backends to switch databases after
>> > initial startup, so that auth could be done in a predetermined database
>> > (say, "postgres") before switching to the requested DB.  This has enough
>> > potential gotchas, in regards to catalog caching for instance, that I'm
>> > not eager to go there.
>>
>> Would it be possible to set up a skeleton environment where we can
>> access shared catalogs only and then decide on which database we're
>> using later?
>
> Eh?  We already do that ... In fact the autovac launcher is always
> connected to shared catalogs, without being connected to any one
> database in particular (cf. get_database_list)

Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
connect to some database in order to proceed with auth checking".  Why
is that necessary,  if we can access shared catalogs without it?

...Robert

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-19T21:12:31Z

Robert Haas escribió:
> On Mon, Apr 19, 2010 at 5:04 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > Robert Haas escribió:
> >> On Mon, Apr 19, 2010 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> >> > The only way I can think of to improve that without going back to flat
> >> > files would be to develop a way for backends to switch databases after
> >> > initial startup, so that auth could be done in a predetermined database
> >> > (say, "postgres") before switching to the requested DB.  This has enough
> >> > potential gotchas, in regards to catalog caching for instance, that I'm
> >> > not eager to go there.
> >>
> >> Would it be possible to set up a skeleton environment where we can
> >> access shared catalogs only and then decide on which database we're
> >> using later?
> >
> > Eh?  We already do that ... In fact the autovac launcher is always
> > connected to shared catalogs, without being connected to any one
> > database in particular (cf. get_database_list)
> 
> Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
> connect to some database in order to proceed with auth checking".  Why
> is that necessary,  if we can access shared catalogs without it?

Hmm, yeah, why did he say that?  Maybe the order of operations during
startup is such that we only do auth checking after connecting to a
database for some reason.

Whatever it is, I don't think a badly worded error message is enough
grounds for fooling with this at this time of release process, though.
To be discussed for 9.1?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-19T21:22:37Z

On Mon, 2010-04-19 at 17:08 -0400, Robert Haas wrote:

> Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
> connect to some database in order to proceed with auth checking".  Why
> is that necessary

It's not, I just explained how to do it without.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-19T21:47:15Z

Simon Riggs escribió:
> On Mon, 2010-04-19 at 17:08 -0400, Robert Haas wrote:
> 
> > Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
> > connect to some database in order to proceed with auth checking".  Why
> > is that necessary
> 
> It's not, I just explained how to do it without.

You mean purely using pg_hba.conf "all" rules?  That seems a bit
unsatisfactory ...

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-19T21:51:20Z

On Mon, Apr 19, 2010 at 5:12 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Robert Haas escribió:
>> On Mon, Apr 19, 2010 at 5:04 PM, Alvaro Herrera
>> <alvherre@commandprompt.com> wrote:
>> > Robert Haas escribió:
>> >> On Mon, Apr 19, 2010 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >
>> >> > The only way I can think of to improve that without going back to flat
>> >> > files would be to develop a way for backends to switch databases after
>> >> > initial startup, so that auth could be done in a predetermined database
>> >> > (say, "postgres") before switching to the requested DB.  This has enough
>> >> > potential gotchas, in regards to catalog caching for instance, that I'm
>> >> > not eager to go there.
>> >>
>> >> Would it be possible to set up a skeleton environment where we can
>> >> access shared catalogs only and then decide on which database we're
>> >> using later?
>> >
>> > Eh?  We already do that ... In fact the autovac launcher is always
>> > connected to shared catalogs, without being connected to any one
>> > database in particular (cf. get_database_list)
>>
>> Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
>> connect to some database in order to proceed with auth checking".  Why
>> is that necessary,  if we can access shared catalogs without it?
>
> Hmm, yeah, why did he say that?  Maybe the order of operations during
> startup is such that we only do auth checking after connecting to a
> database for some reason.
>
> Whatever it is, I don't think a badly worded error message is enough
> grounds for fooling with this at this time of release process, though.
> To be discussed for 9.1?

I'm not proposing to fix the issue right now; but I wanted to try to
understand it while it's fresh in my mind.  I'm still not seeing the
issue for some reason.

...Robert

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-19T21:52:56Z

On Mon, Apr 19, 2010 at 5:22 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Mon, 2010-04-19 at 17:08 -0400, Robert Haas wrote:
>
>> Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
>> connect to some database in order to proceed with auth checking".  Why
>> is that necessary
>
> It's not, I just explained how to do it without.

Your explanation seems to presuppose that we somehow can't process the
database-specific rules before selecting a database.  I don't
understand why that would be the case.  Why can't we just check all
the rules and then, if we decide to allow the connection, select the
database?

...Robert

Re: Thoughts on pg_hba.conf rejection

Simon Riggs <simon@2ndquadrant.com> — 2010-04-19T22:03:06Z

On Mon, 2010-04-19 at 17:52 -0400, Robert Haas wrote:
> On Mon, Apr 19, 2010 at 5:22 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Mon, 2010-04-19 at 17:08 -0400, Robert Haas wrote:
> >
> >> Oh.  Then I'm confused.  Tom said: "as of 9.0, it's necessary to
> >> connect to some database in order to proceed with auth checking".  Why
> >> is that necessary
> >
> > It's not, I just explained how to do it without.
> 
> Your explanation seems to presuppose that we somehow can't process the
> database-specific rules before selecting a database.  I don't
> understand why that would be the case.  Why can't we just check all
> the rules and then, if we decide to allow the connection, select the
> database?

Some rules are user-specific, but I see that doesn't matter and you are
right. 

We can process the whole pg_hba.conf to see if it returns reject or
implicitreject before attempting to confirm the existence of any
database or any user. Any other result must be implemented during
ClientAuthentication(). So we may as well run the whole set of rules,
work out which rule applies and then remember that for later use. Just
as efficient, better security.

-- 
 Simon Riggs           www.2ndQuadrant.com

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-19T23:09:47Z

Simon Riggs <simon@2ndQuadrant.com> writes:
> With code as currently, yes, though I see that there is a way to do
> this. 

> Rules that have an "all" in the database field of the hba can be applied
> prior to attempting to select the database, as long as the "all" rule is
> above any database-specific rules.

Well, that's nice, but it's an awfully small subset of what the
pg_hba.conf rules might contain.  In particular you can't do anything
that involves group membership checks without access to the catalogs;
and I think a large fraction of installations that are exposed to
untrustworthy connections will be using password auth for them, which
means they still need to connect to the catalogs to get the password.

Now it's possible that we could have a prefilter that rejects
connections if they're coming from an IP address that cannot match
*any* of the pg_hba.conf rules.  Not sure how useful that would really
be in practice though.  It wouldn't do anything extra for people who
keep their DB server behind a firewall.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-19T23:18:57Z

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Robert Haas escribi:
>> Would it be possible to set up a skeleton environment where we can
>> access shared catalogs only and then decide on which database we're
>> using later?

> Eh?  We already do that ... In fact the autovac launcher is always
> connected to shared catalogs, without being connected to any one
> database in particular (cf. get_database_list)

Hmm.  The AV launcher is only permitted to touch pg_database.
At the time there were considerable advantages to that restriction,
but subsequent changes (like getting rid of the need for manual
maintenance of pg_attribute entries for bootstrap catalogs) might
mean that it wouldn't be too painful to extend this capability to
pg_authid etc.  Could be worth thinking about.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-19T23:26:41Z

On Mon, Apr 19, 2010 at 7:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Robert Haas escribió:
>>> Would it be possible to set up a skeleton environment where we can
>>> access shared catalogs only and then decide on which database we're
>>> using later?
>
>> Eh?  We already do that ... In fact the autovac launcher is always
>> connected to shared catalogs, without being connected to any one
>> database in particular (cf. get_database_list)
>
> Hmm.  The AV launcher is only permitted to touch pg_database.
> At the time there were considerable advantages to that restriction,
> but subsequent changes (like getting rid of the need for manual
> maintenance of pg_attribute entries for bootstrap catalogs) might
> mean that it wouldn't be too painful to extend this capability to
> pg_authid etc.  Could be worth thinking about.

Perhaps we should add a TODO.

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T00:11:31Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Apr 19, 2010 at 7:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm. The AV launcher is only permitted to touch pg_database.

> Perhaps we should add a TODO.

Actually, while I'm looking at that code, a more immediate TODO is
"fix walsender".  Somebody has inserted an absolutely flight-of-fantasy
code path into InitPostgres.  (Hint: template1 can be dropped.
ESPECIALLY when you're deliberately not taking any lock on it.)

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-20T03:03:23Z

On Mon, Apr 19, 2010 at 8:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Apr 19, 2010 at 7:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Hmm.  The AV launcher is only permitted to touch pg_database.
>
>> Perhaps we should add a TODO.
>
> Actually, while I'm looking at that code, a more immediate TODO is
> "fix walsender".  Somebody has inserted an absolutely flight-of-fantasy
> code path into InitPostgres.  (Hint: template1 can be dropped.
> ESPECIALLY when you're deliberately not taking any lock on it.)

Off-topic to that, but on-topic to the original topic of this thread,
check out this link that Karen Padir just blogged about on
planet.postgresql.org:

http://blog.metasploit.com/2010/02/postgres-fingerprinting.html

Assuming the situation really is as described here, I am wondering if
we should suppress the F, L, and R output in this and similar cases
and back-patch it all the way back.  This seems like it is entirely
too helpful.

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T14:05:09Z

Robert Haas <robertmhaas@gmail.com> writes:
> http://blog.metasploit.com/2010/02/postgres-fingerprinting.html

> Assuming the situation really is as described here, I am wondering if
> we should suppress the F, L, and R output in this and similar cases
> and back-patch it all the way back.  This seems like it is entirely
> too helpful.

[ yawn.. ]  I'm unimpressed: should we also ensure that neither ASCII
nor translated texts of authentication failure messages ever change?
IIRC, you were lobbying *for* such a change only a day or two ago.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T14:19:45Z

I wrote:
> Actually, while I'm looking at that code, a more immediate TODO is
> "fix walsender".  Somebody has inserted an absolutely flight-of-fantasy
> code path into InitPostgres.  (Hint: template1 can be dropped.
> ESPECIALLY when you're deliberately not taking any lock on it.)

Now that I look more closely, it seems what we have actually got there
is an incorrect attempt to solve the problem of authenticating without
selecting any particular database.  So we could solve both this and
the original complaint in the thread if we can arrange for all
authentication to be done on the basis of shared-catalog access under
rules similar to what the AV launcher does with pg_database.  At a
minimum that will require marking the pg_auth catalogs as
BKI_SCHEMA_MACRO, but that's far less painful than it used to be.
I don't recall what other consequences there are, but will go looking.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T18:24:35Z

I wrote:
> ... So we could solve both this and
> the original complaint in the thread if we can arrange for all
> authentication to be done on the basis of shared-catalog access under
> rules similar to what the AV launcher does with pg_database.  At a
> minimum that will require marking the pg_auth catalogs as
> BKI_SCHEMA_MACRO, but that's far less painful than it used to be.
> I don't recall what other consequences there are, but will go looking.

I've been looking at this and it seems do-able, though I don't have
working code yet.  Downsides appear to be:

1. We'd have to force an initdb because of a couple of small catalog
changes.  This doesn't seem like a showstopper at this phase of the
release cycle, but it's slightly annoying.  pg_migrator could be used
if anyone's really in need of it.

2. We don't have infrastructure that would allow access to out-of-line
toasted fields during startup.  Rather than try to add such, I propose
removing pg_authid's toast table, with the consequence that rolpassword
cannot be long enough to require out-of-line storage (note it could
still be compressed in-line).  I cannot imagine any real situation where
this would be an issue --- does anyone else?  (BTW, I'm fairly sure that
we couldn't support an out-of-line rolpassword in the past anyway,
because of restrictions in the old flatfiles code.)

3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
this would affect performance in any material way, but it would eat a
few more kbytes of storage per backend.

None of these seem like reasons not to do it.  Objections?

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-20T19:30:28Z

Tom Lane escribió:

> 1. We'd have to force an initdb because of a couple of small catalog
> changes.  This doesn't seem like a showstopper at this phase of the
> release cycle, but it's slightly annoying.  pg_migrator could be used
> if anyone's really in need of it.

check

> 2. We don't have infrastructure that would allow access to out-of-line
> toasted fields during startup.  Rather than try to add such, I propose
> removing pg_authid's toast table, with the consequence that rolpassword
> cannot be long enough to require out-of-line storage (note it could
> still be compressed in-line).  I cannot imagine any real situation where
> this would be an issue --- does anyone else?  (BTW, I'm fairly sure that
> we couldn't support an out-of-line rolpassword in the past anyway,
> because of restrictions in the old flatfiles code.)

In the past rolconfig could have been a problem too, but fortunately we
got that moved out.  I really doubt that a password of "only" about 2kB
compressed is going to be a limitation to anyone on this planet.  (Hmm,
isn't it really 8kB in row length that's the hard limit?)

This could perhaps be an issue if we were to store an SSL certificate in
rolpassword or something like that, but I don't think we support that.

> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
> relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
> this would affect performance in any material way, but it would eat a
> few more kbytes of storage per backend.

This doesn't limit that VACUUM FULL or other commands are applied to
those catalogs, right?

> None of these seem like reasons not to do it.  Objections?

None here.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-20T19:35:08Z

On Tue, Apr 20, 2010 at 2:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 1. We'd have to force an initdb because of a couple of small catalog
> changes.  This doesn't seem like a showstopper at this phase of the
> release cycle, but it's slightly annoying.  pg_migrator could be used
> if anyone's really in need of it.

Fine.

> 2. We don't have infrastructure that would allow access to out-of-line
> toasted fields during startup.  Rather than try to add such, I propose
> removing pg_authid's toast table, with the consequence that rolpassword
> cannot be long enough to require out-of-line storage (note it could
> still be compressed in-line).  I cannot imagine any real situation where
> this would be an issue --- does anyone else?  (BTW, I'm fairly sure that
> we couldn't support an out-of-line rolpassword in the past anyway,
> because of restrictions in the old flatfiles code.)

I think that's OK.

> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
> relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
> this would affect performance in any material way, but it would eat a
> few more kbytes of storage per backend.

Hmm, I'm not sure I understand why this is necessary or what our other
options are.

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T21:02:22Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Apr 20, 2010 at 2:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
>> relcache, because relcache.c isn't prepared to cope otherwise. I doubt
>> this would affect performance in any material way, but it would eat a
>> few more kbytes of storage per backend.

> Hmm, I'm not sure I understand why this is necessary or what our other
> options are.

relcache.c assumes that "critical" relations (those for which we have
hard-wired descriptors in schemapg.h) are always nailed-in-cache.  In
the general case this is necessary because we'd not be able to rebuild
the cache entry if it got discarded; eg, without a pg_class entry you're
dead in the water.  It's possible we could decouple these attributes;
for instance develop a notion of being nailed only until authentication
finishes, or something like that.  I'm not thinking it's worth it
though.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T21:43:14Z

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane escribió:
>> 2. We don't have infrastructure that would allow access to out-of-line
>> toasted fields during startup.  Rather than try to add such, I propose
>> removing pg_authid's toast table, with the consequence that rolpassword
>> cannot be long enough to require out-of-line storage (note it could
>> still be compressed in-line).  I cannot imagine any real situation where
>> this would be an issue --- does anyone else?  (BTW, I'm fairly sure that
>> we couldn't support an out-of-line rolpassword in the past anyway,
>> because of restrictions in the old flatfiles code.)

> In the past rolconfig could have been a problem too, but fortunately we
> got that moved out.  I really doubt that a password of "only" about 2kB
> compressed is going to be a limitation to anyone on this planet.  (Hmm,
> isn't it really 8kB in row length that's the hard limit?)

Actually, rolconfig would be OK because it doesn't have to be accessed
until after we've completed authentication.  However there's no really
nice way to ensure that rolpassword doesn't get toasted if another
column can be.  I suppose we could have initdb force its attstorage to
PLAIN or some such.

> This could perhaps be an issue if we were to store an SSL certificate in
> rolpassword or something like that, but I don't think we support that.

Nope, not that I know of.  Anyway we could solve the problem if it ever
came up --- I don't think there's anything insurmountable about
accessing shared toast tables here, we'd just need some support to allow
them to be nailed-in-cache.

>> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
>> relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
>> this would affect performance in any material way, but it would eat a
>> few more kbytes of storage per backend.

> This doesn't limit that VACUUM FULL or other commands are applied to
> those catalogs, right?

Nope, it's no different from pg_database.

Attached is a draft patch --- it looks pretty reasonable, but I've not
tested the impact on walsender yet.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-20T21:51:14Z

On Tue, Apr 20, 2010 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Apr 20, 2010 at 2:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
>>> relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
>>> this would affect performance in any material way, but it would eat a
>>> few more kbytes of storage per backend.
>
>> Hmm, I'm not sure I understand why this is necessary or what our other
>> options are.
>
> relcache.c assumes that "critical" relations (those for which we have
> hard-wired descriptors in schemapg.h) are always nailed-in-cache.  In
> the general case this is necessary because we'd not be able to rebuild
> the cache entry if it got discarded; eg, without a pg_class entry you're
> dead in the water.  It's possible we could decouple these attributes;
> for instance develop a notion of being nailed only until authentication
> finishes, or something like that.  I'm not thinking it's worth it
> though.

Well that just begs the question - why do we need a hard-wired
descriptor?  Presumably we should only need to hard-wired descriptors
for the relations are used by the relcache code itself to build more
descriptors - so clearly pg_cache and pg_attribute, but beyond that I
don't get it.  In particular, I can't see any reason why we couldn't
just build the descriptor for pg_authid etc. by scanning pg_class and
pg_attribute.

...Robert

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-20T22:12:36Z

On Tue, Apr 20, 2010 at 5:51 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Apr 20, 2010 at 5:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> On Tue, Apr 20, 2010 at 2:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> 3. We'd have to nail pg_authid, pg_auth_members, and their indexes into
>>>> relcache, because relcache.c isn't prepared to cope otherwise.  I doubt
>>>> this would affect performance in any material way, but it would eat a
>>>> few more kbytes of storage per backend.
>>
>>> Hmm, I'm not sure I understand why this is necessary or what our other
>>> options are.
>>
>> relcache.c assumes that "critical" relations (those for which we have
>> hard-wired descriptors in schemapg.h) are always nailed-in-cache.  In
>> the general case this is necessary because we'd not be able to rebuild
>> the cache entry if it got discarded; eg, without a pg_class entry you're
>> dead in the water.  It's possible we could decouple these attributes;
>> for instance develop a notion of being nailed only until authentication
>> finishes, or something like that.  I'm not thinking it's worth it
>> though.
>
> Well that just begs the question - why do we need a hard-wired
> descriptor?  Presumably we should only need to hard-wired descriptors
> for the relations are used by the relcache code itself to build more
> descriptors - so clearly pg_cache and pg_attribute, but beyond that I
> don't get it.  In particular, I can't see any reason why we couldn't
> just build the descriptor for pg_authid etc. by scanning pg_class and
> pg_attribute.

I suppose the problem here is that pg_attribute and pg_class are not
shared catalogs, so we can't read them without selecting a database.
What about making a fake version of these relations that includes only
the shared catalogs?

...Robert

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-20T22:48:17Z

Robert Haas escribió:

> I suppose the problem here is that pg_attribute and pg_class are not
> shared catalogs, so we can't read them without selecting a database.
> What about making a fake version of these relations that includes only
> the shared catalogs?

Hmm, interesting.  I wonder if something of this sort would allow one to
create a shared relation at the user level -- right now the set of
shared relations is hardcoded so this cannot work.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-20T23:13:18Z

Robert Haas <robertmhaas@gmail.com> writes:
> I suppose the problem here is that pg_attribute and pg_class are not
> shared catalogs, so we can't read them without selecting a database.

Among other things.

> What about making a fake version of these relations that includes only
> the shared catalogs?

Well, after you solve the few dozen problems standing in the way
of that, go right ahead.  I'm not holding up 9.0 for it though.

(You might want to look back at the archived discussions about how to
avoid storing entries for temp tables in these catalogs; that poses
many of the same issues.)

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Robert Haas <robertmhaas@gmail.com> — 2010-04-21T04:14:33Z

On Tue, Apr 20, 2010 at 7:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I suppose the problem here is that pg_attribute and pg_class are not
>> shared catalogs, so we can't read them without selecting a database.
>
> Among other things.
>
>> What about making a fake version of these relations that includes only
>> the shared catalogs?
>
> Well, after you solve the few dozen problems standing in the way
> of that, go right ahead.  I'm not holding up 9.0 for it though.
>
> (You might want to look back at the archived discussions about how to
> avoid storing entries for temp tables in these catalogs; that poses
> many of the same issues.)

Do you happen to know what a good search term might be?  I tried
searching for things like "pg_class temp tables" and "pg_class
temporary tables" and didn't come up with much.  The closest thing I
found was a discussion about global temp tables (subject aws "idea:
global temp tables") in which Greg Stark was arguing that there wasn't
much point in implementing them without solving this issue (and others
were disagreeing) but it didn't get into any of the technical issues
at all.

...Robert

Re: Thoughts on pg_hba.conf rejection

Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-21T14:50:24Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Apr 20, 2010 at 7:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (You might want to look back at the archived discussions about how to
>> avoid storing entries for temp tables in these catalogs; that poses
>> many of the same issues.)

> Do you happen to know what a good search term might be?  I tried
> searching for things like "pg_class temp tables" and "pg_class
> temporary tables" and didn't come up with much.

I found this thread:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00593.php
I claimed in that message that there were previous discussions but
I did not come across them right away.

			regards, tom lane

Re: Thoughts on pg_hba.conf rejection

Alvaro Herrera <alvherre@commandprompt.com> — 2010-04-22T21:14:00Z

Tom Lane escribió:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Tue, Apr 20, 2010 at 7:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> (You might want to look back at the archived discussions about how to
> >> avoid storing entries for temp tables in these catalogs; that poses
> >> many of the same issues.)
> 
> > Do you happen to know what a good search term might be?  I tried
> > searching for things like "pg_class temp tables" and "pg_class
> > temporary tables" and didn't come up with much.
> 
> I found this thread:
> http://archives.postgresql.org/pgsql-hackers/2008-07/msg00593.php
> I claimed in that message that there were previous discussions but
> I did not come across them right away.

I vaguely remember that there was a discussion about pg_attribute and
the extra rows for system rows for all tables, which diverged into a
discussion about temp tables and those other extra rows.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support