Обсуждение: Re: New pg_pwd patch and stuff

Sorry for the response delay.  I was out of town.

I don't believe that pg_user needs to be readable by users in general.  They
don't really need to know who else has access to the DB, and they certainly
don't need to know what access they do have (e.g. usesuper and createuser).

As for the suggestion that the passwords don't need to be in the cache, this is
incorrect.  For the system (as I have designed it) to work, the postmaster must
check at each login to see if the user has a password.  Using another relation
along with a select to look up the password from pg_user is not as efficient,
and it is not possible from the postmaster.  In order for this to work, each
time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
must be performed between the two (essentially perform a select on a view that
performs the join) before the data can be copied to the pg_pwd file for the
postmaster to use.  I don't even know if the copy command will work with a view.
For these reasons I still believe that pg_user should just remain non-accessible
to the general public.

Todd A. Brandys

>
> Sorry for the response delay.  I was out of town.
>
> I don't believe that pg_user needs to be readable by users in general.  They
> don't really need to know who else has access to the DB, and they certainly
> don't need to know what access they do have (e.g. usesuper and createuser).
>
> As for the suggestion that the passwords don't need to be in the cache, this is
> incorrect.  For the system (as I have designed it) to work, the postmaster must
> check at each login to see if the user has a password.  Using another relation
> along with a select to look up the password from pg_user is not as efficient,
> and it is not possible from the postmaster.  In order for this to work, each
> time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
> must be performed between the two (essentially perform a select on a view that
> performs the join) before the data can be copied to the pg_pwd file for the
> postmaster to use.  I don't even know if the copy command will work with a view.
> For these reasons I still believe that pg_user should just remain non-accessible
> to the general public.
>
> Todd A. Brandys
>

Can't we create a function to get the info:

create function get_passwd returns text as
    'select passwd from pg_password'
    language 'sql';

And this will return a null for password not found, and a valid password
for others.  I don't think a view will work.  I think you would have to
do a SELECT ... INTO and do a COPY from that temp table.  Sounds like
some work.

Now this is done ONLY when a password changed is made, or a user is
deleted or added.  Is that correct?  Doesn't sound like too much of a
hit to me.  Now if it was done for every connection, we would have big
troubles.


--
Bruce Momjian
maillist@candle.pha.pa.us

On Wed, 14 Jan 1998, Bruce Momjian wrote:

> > Sorry for the response delay.  I was out of town.
> >
> > I don't believe that pg_user needs to be readable by users in general.  They
> > don't really need to know who else has access to the DB, and they certainly
> > don't need to know what access they do have (e.g. usesuper and createuser).
> >
> > As for the suggestion that the passwords don't need to be in the cache, this is
> > incorrect.  For the system (as I have designed it) to work, the postmaster must
> > check at each login to see if the user has a password.  Using another relation
> > along with a select to look up the password from pg_user is not as efficient,
> > and it is not possible from the postmaster.  In order for this to work, each
> > time that pg_user or pg_password (if we use a 2nd relation) is modified, a join
> > must be performed between the two (essentially perform a select on a view that
> > performs the join) before the data can be copied to the pg_pwd file for the
> > postmaster to use.  I don't even know if the copy command will work with a view.
> > For these reasons I still believe that pg_user should just remain non-accessible
> > to the general public.
> >
> > Todd A. Brandys
> >
>
> Can't we create a function to get the info:
>
> create function get_passwd returns text as
>     'select passwd from pg_password'
>     language 'sql';
>
> And this will return a null for password not found, and a valid password
> for others.  I don't think a view will work.  I think you would have to
> do a SELECT ... INTO and do a COPY from that temp table.  Sounds like
> some work.
>
> Now this is done ONLY when a password changed is made, or a user is
> deleted or added.  Is that correct?  Doesn't sound like too much of a
> hit to me.  Now if it was done for every connection, we would have big
> troubles.

    Just curious here, but why couldn't this be done in postgres vs
postmaster?  Essentially, the way I'm seeing things right now, we'd added
processing to the "startup loop" inside of the postmaster...while
postmaster is authenticating one user, the next person trying to connect
would have to wait that little bit longer...

    Fork off the postgres process first, then authenticate inside of
there...which would get rid of the problem with pg_user itself being a
text file vs a relation...no?