Thread: Getting rid of the flat authentication file
I've been looking into what it would take to eliminate the flat file for
pg_auth info. The implication of doing that is that authentication has
to be postponed until inside InitPostgres(), where we can read the
actual system catalogs instead.
The easy way to do it would be to postpone authentication until after we
have selected and entered a database. At that point we could use existing
code such as is_member_of_role(). There is a security disadvantage to
that: you would find out whether the database name you'd given was valid
before any authentication check occurred. Since database names are often
also user names, that would give a brute-force attacker a leg up on
discovering valid user names. Plan B is to use the same techniques for
reading pg_authid and pg_auth_members as InitPostgres is now using for
reading pg_database. That's perfectly doable; the main downside to it
is that if the shared relcache file were missing, we'd be reduced to
seqscan searches of these files, which could be pretty darn unpleasant
for role membership searches. However, the shared relcache file should
hardly ever be missing, and standard pg_hba.conf setups (with the role
column always ALL) don't result in role membership checks anyway. So
I'm leaning to plan B here.
Another issue is that currently, option switches supplied via PGOPTIONS
are processed at entry to PostgresMain (unless they are for SUSET GUC
variables). If we retained that behavior then they'd be applied before
authentication occurred. This worries me, though I can't immediately
point to a problem case. I'd be inclined to postpone the processing of
all user-supplied switches until after InitPostgres. This would
simplify the logic in PostgresMain, too, since we'd not have to process
SUSET variables separately from others. The only real downside I can
see is that it would make -W (post_auth_delay) pretty much useless for
its intended purpose of assisting debugging of InitPostgres-time
problems. We might as well remove it and just rely on pre_auth_delay.
This point is only of interest to hackers, and not all that often even
to us, so I don't feel that it's a critical objection.
So the disadvantages of not using the flat file for authentication
seem to boil down to
* more cycles expended before we can reject a bad username/password
* could be slow in the uncommon case that the shared relcache file is missing
* debugging InitPostgres problems will get more inconvenient
As against this, we'd be getting rid of a bunch of klugy, slow code with
assorted failure points.
Comments?
regards, tom lane
I'm back on the warpath about $SUBJECT. (Aside from any other reason to
do it, it occurs to me that we really need to get rid of the flat files
for Hot Standby. Otherwise we'd need some way to keep them up to date
during WAL replay.) I wrote earlier:
> The easy way to do it would be to postpone authentication until after we
> have selected and entered a database. At that point we could use existing
> code such as is_member_of_role(). There is a security disadvantage to
> that: you would find out whether the database name you'd given was valid
> before any authentication check occurred. Since database names are often
> also user names, that would give a brute-force attacker a leg up on
> discovering valid user names. Plan B is to use the same techniques for
> reading pg_authid and pg_auth_members as InitPostgres is now using for
> reading pg_database.
I've thought of an easier way to handle this: if the given database name
is invalid, connect to database "postgres" instead, and perform
authentication using normal access to the pg_auth catalogs. If
authentication succeeds, *then* throw the error about nonexistent
database. If "postgres" is not there, we'd still expose existence
of the original database name early, but how many installations don't
have that? (I thought about trying template1 and/or template0 as
fallbacks, but that's probably not a good thing. Backends that are
waiting on a client for authentication would pose a DOS problem for
sessions trying to do CREATE DATABASE, if they're connected to those.)
Because this would all happen before the checks on permission to connect
to the DB, it would still work as desired if, say, postgres exists but
is marked not datallowconn. So anyone who was paranoid enough to not
want DB postgres to be accessible could mark it that way instead of
deleting it.
This would avoid duplicating the is_member_of_role() logic so I'm
tempted to do it like this, but wanted to see if anyone had objections
or better ideas.
regards, tom lane
On Fri, 2009-08-28 at 11:52 -0400, Tom Lane wrote: > I've thought of an easier way to handle this: if the given database name > is invalid, connect to database "postgres" instead, and perform > authentication using normal access to the pg_auth catalogs. If > authentication succeeds, *then* throw the error about nonexistent > database. If "postgres" is not there, we'd still expose existence > of the original database name early, but how many installations don't > have that? I run into it all the time. People drop the postgres database as not needed. > (I thought about trying template1 and/or template0 as > fallbacks, but that's probably not a good thing. Backends that are > waiting on a client for authentication would pose a DOS problem for > sessions trying to do CREATE DATABASE, if they're connected to those.) What if there was a silent database, say pg_authdb. That isn't even visible under normal circumstances (need to be in single user mode)? Alternatively just make it so the postgres database can't be dropped. ERROR: The database postgres is a system database. It can not be dropped. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering
"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Fri, 2009-08-28 at 11:52 -0400, Tom Lane wrote:
>> I've thought of an easier way to handle this: if the given database name
>> is invalid, connect to database "postgres" instead, and perform
>> authentication using normal access to the pg_auth catalogs. If
>> authentication succeeds, *then* throw the error about nonexistent
>> database. If "postgres" is not there, we'd still expose existence
>> of the original database name early, but how many installations don't
>> have that?
> I run into it all the time. People drop the postgres database as not
> needed.
Well, it isn't, unless you are worried about a third-order security
issue like whether someone can identify database names by a brute
force attack. The only problem if it's not there is we'll throw the
"no such db" error before user validation instead of after. I'm feeling
that that isn't worth a large expenditure of effort, as long as there's
a reasonable way to configure the system so it is secure if you care
about that.
regards, tom lane
On Fri, Aug 28, 2009 at 12:12 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> On Fri, 2009-08-28 at 11:52 -0400, Tom Lane wrote: >>> I've thought of an easier way to handle this: if the given database name >>> is invalid, connect to database "postgres" instead, and perform >>> authentication using normal access to the pg_auth catalogs. If >>> authentication succeeds, *then* throw the error about nonexistent >>> database. If "postgres" is not there, we'd still expose existence >>> of the original database name early, but how many installations don't >>> have that? > >> I run into it all the time. People drop the postgres database as not >> needed. > > Well, it isn't, unless you are worried about a third-order security > issue like whether someone can identify database names by a brute > force attack. The only problem if it's not there is we'll throw the > "no such db" error before user validation instead of after. I'm feeling > that that isn't worth a large expenditure of effort, as long as there's > a reasonable way to configure the system so it is secure if you care > about that. Although this seems reasonably OK from a security point of view, it does seem to violate the POLA. ...Robert
On Fri, 2009-08-28 at 11:52 -0400, Tom Lane wrote: > I've thought of an easier way to handle this: if the given database name > is invalid, connect to database "postgres" instead, and perform > authentication using normal access to the pg_auth catalogs. If > authentication succeeds, *then* throw the error about nonexistent > database. If "postgres" is not there, we'd still expose existence > of the original database name early, but how many installations don't > have that? I run into it all the time. People drop the postgres database as not needed. > (I thought about trying template1 and/or template0 as > fallbacks, but that's probably not a good thing. Backends that are > waiting on a client for authentication would pose a DOS problem for > sessions trying to do CREATE DATABASE, if they're connected to those.) What if there was a silent database, say pg_authdb. That isn't even visible under normal circumstances (need to be in single user mode)? Alternatively just make it so the postgres database can't be dropped. ERROR: The database postgres is a system database. It can not be dropped. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering