Thread: secondary password files

Currently, pg_passwd allows the creation of secondary password file that
can be used as part of 'password' pg_hba.conf entries.

Why do we bother supporting passwords in pg_shadow and secondary files? 
Seems we could just allow usernames in the secondary files, and use the
user passwords from pg_shadow.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Why do we bother supporting passwords in pg_shadow and secondary files? 

So the same user can have different passwords for different databases.

It's a pretty crude hack, since there isn't any support for updating
the secondary password files except via manual editing done by the
dbadmin.  But I wouldn't be in favor of taking it out until we can
replace that functionality elsewhere.
        regards, tom lane

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Why do we bother supporting passwords in pg_shadow and secondary files? 
> 
> So the same user can have different passwords for different databases.
> 
> It's a pretty crude hack, since there isn't any support for updating
> the secondary password files except via manual editing done by the
> dbadmin.  But I wouldn't be in favor of taking it out until we can
> replace that functionality elsewhere.

We have pg_passwd which does allow updating of the files.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> It's a pretty crude hack, since there isn't any support for updating
>> the secondary password files except via manual editing done by the
>> dbadmin.  But I wouldn't be in favor of taking it out until we can
>> replace that functionality elsewhere.

> We have pg_passwd which does allow updating of the files.

Say again?  I see a pg_shadow table and a pg_user view of it.
No pg_passwd table.

Since pg_shadow can't hold more than one password per user, it's
fundamentally incapable of supporting this function.

If we wanted to handle this better, I'd be inclined to remove passwords
from pg_shadow (then the need for a separate pg_user view would go away)
and make a pg_passwd table holding <username, dbname, password> triples
with some provision for an "any other db" wildcard.  (Not dbname = NULL,
because we'd want to treat <username, dbname> as primary key.  Maybe
dbname = '*' would be OK.)  There'd need to be two flat files for the
postmaster to consult, one shadowing each of these tables.

Peter may already have better ideas as part of his protection-system
rework, though.
        regards, tom lane

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> It's a pretty crude hack, since there isn't any support for updating
> >> the secondary password files except via manual editing done by the
> >> dbadmin.  But I wouldn't be in favor of taking it out until we can
> >> replace that functionality elsewhere.
> 
> > We have pg_passwd which does allow updating of the files.
> 
> Say again?  I see a pg_shadow table and a pg_user view of it.
> No pg_passwd table.
> 
> Since pg_shadow can't hold more than one password per user, it's
> fundamentally incapable of supporting this function.

There is a pg_passwd binary in /bin.

> 
> If we wanted to handle this better, I'd be inclined to remove passwords
> from pg_shadow (then the need for a separate pg_user view would go away)
> and make a pg_passwd table holding <username, dbname, password> triples
> with some provision for an "any other db" wildcard.  (Not dbname = NULL,
> because we'd want to treat <username, dbname> as primary key.  Maybe
> dbname = '*' would be OK.)  There'd need to be two flat files for the
> postmaster to consult, one shadowing each of these tables.

Good ideas.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026