I am adding users and groups to pg_hba.conf.  The coding is done but I
am stuck on a reload issue.

As you may know, 7.2 tokenizes pg_hba.conf once, and reads those tokens
to test every connection request.  I have added code to dump the
group/user mappings into global/pg_group and the postmaster can read
that file and substitute group names for users lists during
tokenization.

I have also added code to dump a new pg_group every time a group/user is
modified.  (Users have to be done because of user renaming.)

The problem is when to retokenize pg_hba.conf after a new pg_group is
made.  Seems I can either force administrators to 'pg_ctl reload' to
update for group changes, or automatically retokenize pg_hba.conf every
time I update pg_group.  (We don't have any way of handling user renames
in pg_hba.conf because we enter those as strings, but pg_group will
handle them.)

Does anyone see another option?  I can write code so only pg_global is
retokenized, but right now the user tokens are pulled out for the
matching group and inlined into the token stream.  If I have a separate
token tree for pg_group, each connection will have to spin through the
tokens looking for matching group names.  I suppose it isn't a big deal,
but I want to make sure we want to prevent auto-reloading of pg_hba.conf
on user/group changes, and just reload pg_group.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 03:36:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Problem with reloading groups in pg_hba.conf

I think I have figured out a way to do this efficiently.  Instead of
making pg_group with groupname/username on each line, I will do
groupname/username,username, ... so I can spin through the group token
file much quicker;  that way, I can read just retokenize pg_group and
spin through it for each connection.  I think that is the way to go.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> I am adding users and groups to pg_hba.conf.  The coding is done but I
> am stuck on a reload issue.
> 
> As you may know, 7.2 tokenizes pg_hba.conf once, and reads those tokens
> to test every connection request.  I have added code to dump the
> group/user mappings into global/pg_group and the postmaster can read
> that file and substitute group names for users lists during
> tokenization.
> 
> I have also added code to dump a new pg_group every time a group/user is
> modified.  (Users have to be done because of user renaming.)
> 
> The problem is when to retokenize pg_hba.conf after a new pg_group is
> made.  Seems I can either force administrators to 'pg_ctl reload' to
> update for group changes, or automatically retokenize pg_hba.conf every
> time I update pg_group.  (We don't have any way of handling user renames
> in pg_hba.conf because we enter those as strings, but pg_group will
> handle them.)
> 
> Does anyone see another option?  I can write code so only pg_global is
> retokenized, but right now the user tokens are pulled out for the
> matching group and inlined into the token stream.  If I have a separate
> token tree for pg_group, each connection will have to spin through the
> tokens looking for matching group names.  I suppose it isn't a big deal,
> but I want to make sure we want to prevent auto-reloading of pg_hba.conf
> on user/group changes, and just reload pg_group.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   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, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 03:42:25 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The problem is when to retokenize pg_hba.conf after a new pg_group is
> made.  Seems I can either force administrators to 'pg_ctl reload' to
> update for group changes, or automatically retokenize pg_hba.conf every
> time I update pg_group.

Why exactly are you looking to reinvent the wheel, rather than doing
it the same way we currently handle pg_shadow updates?  Send the
postmaster a signal when you modify the flat file, and it can reread
the file on receipt of the signal.  See SendPostmasterSignal().

			regards, tom lane

Thu, 21 Mar 2002 11:31:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with reloading groups in pg_hba.conf 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > The problem is when to retokenize pg_hba.conf after a new pg_group is
> > made.  Seems I can either force administrators to 'pg_ctl reload' to
> > update for group changes, or automatically retokenize pg_hba.conf every
> > time I update pg_group.
> 
> Why exactly are you looking to reinvent the wheel, rather than doing
> it the same way we currently handle pg_shadow updates?  Send the
> postmaster a signal when you modify the flat file, and it can reread
> the file on receipt of the signal.  See SendPostmasterSignal().

I am handling it like pg_shadow. The problem is that because I expand
pg_group inside the pg_hba tokens, I have to retokenize pg_hba.conf too
after pg_group changes.  I assumed we didn't want pg_hba.conf
retokenized on a password change and only on a pg_ctl reload.

My new code has a separate pg_group token list which is not expanded
into the pg_hba.conf token list and is traversed for every connection.

Is this the right way to go?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 11:38:05 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
On Thu, Mar 21, 2002 at 11:38:05AM -0500, Bruce Momjian wrote:
> 
> I am handling it like pg_shadow. The problem is that because I expand
> pg_group inside the pg_hba tokens, I have to retokenize pg_hba.conf too
> after pg_group changes.  I assumed we didn't want pg_hba.conf
> retokenized on a password change and only on a pg_ctl reload.
> 
> My new code has a separate pg_group token list which is not expanded
> into the pg_hba.conf token list and is traversed for every connection.

Hmm, your trading performance on every connection for less work on the
rare event of a password change? What's wrong with reparsing pg_hba.conf
at password/group change? Streamline the common case, don't optimize for
the rare condition.

Ross


Thu, 21 Mar 2002 10:49:16 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Problem with reloading groups in pg_hba.conf
Ross J. Reedstrom wrote:
> On Thu, Mar 21, 2002 at 11:38:05AM -0500, Bruce Momjian wrote:
> > 
> > I am handling it like pg_shadow. The problem is that because I expand
> > pg_group inside the pg_hba tokens, I have to retokenize pg_hba.conf too
> > after pg_group changes.  I assumed we didn't want pg_hba.conf
> > retokenized on a password change and only on a pg_ctl reload.
> > 
> > My new code has a separate pg_group token list which is not expanded
> > into the pg_hba.conf token list and is traversed for every connection.
> 
> Hmm, your trading performance on every connection for less work on the
> rare event of a password change? What's wrong with reparsing pg_hba.conf
> at password/group change? Streamline the common case, don't optimize for
> the rare condition.

Yes, that was the issue.  We tell people pg_hba.conf only gets reloaded
when they tell the postmaster to do it.  We can't have it happening at
random times, e.g. password change.  My new coding will need to only
spin through a list of group names, not the list of users in each group.
That's why the new format for global/pg_group should make things ok for
doing this at connection time.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 11:52:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, that was the issue.  We tell people pg_hba.conf only gets reloaded
> when they tell the postmaster to do it.  We can't have it happening at
> random times, e.g. password change.

I agree on that: the signal should cause the postmaster to reload
pg_pwd/pg_group info *only*.  So you cannot integrate the data from
these files into the same datastructure as you use for pg_hba.conf;
they have to be separate datastructures.

I think what you are really asking is whether to expand groups by
substitution of user names during read of the file, vs doing it
on-the-fly when accepting a connection.  On that I agree with Ross:
better to move work out of the connection logic and into the file
reread logic as much as possible.

			regards, tom lane

Thu, 21 Mar 2002 12:02:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with reloading groups in pg_hba.conf 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, that was the issue.  We tell people pg_hba.conf only gets reloaded
> > when they tell the postmaster to do it.  We can't have it happening at
> > random times, e.g. password change.
> 
> I agree on that: the signal should cause the postmaster to reload
> pg_pwd/pg_group info *only*.  So you cannot integrate the data from
> these files into the same datastructure as you use for pg_hba.conf;
> they have to be separate datastructures.
> 
> I think what you are really asking is whether to expand groups by
> substitution of user names during read of the file, vs doing it
> on-the-fly when accepting a connection.  On that I agree with Ross:
> better to move work out of the connection logic and into the file
> reread logic as much as possible.

Yes, I am doing that.  pg_group will be tokenized into username tokens,
and on connection, the mention of a group token in pg_hba.conf will
cause a spin through the pg_group tokens to find a matching groupname,
then it will look for the requested username. 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 12:06:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
Bruce Momjian writes:

> I am adding users and groups to pg_hba.conf.

You know what would be cool?

GRANT CONNECT ON mydb TO GROUP myfriends;

and it rewrites pg_hba.conf accordingly.

Just a thought...

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 21 Mar 2002 21:29:37 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Problem with reloading groups in pg_hba.conf
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I am adding users and groups to pg_hba.conf.
> 
> You know what would be cool?
> 
> GRANT CONNECT ON mydb TO GROUP myfriends;
> 
> and it rewrites pg_hba.conf accordingly.
> 
> Just a thought...

We are actually not that far away.  If you create a group for each
database, you can grant access to just that group and add/delete users
from that group at will.  My new pg_group code will do that.

Now, as far as rewriting pg_hba.conf, that goes into an area where we
are not sure if the master connection information is in the file or in
the database.  We also get into a chicken and egg case where we have to
have the database loaded to connect to it.  I am interested to hear
where people think we should go with this.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  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, Pennsylvania 19026

Thu, 21 Mar 2002 21:37:00 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
Bruce Momjian wrote:
> 
> Now, as far as rewriting pg_hba.conf, that goes into an area where we
> are not sure if the master connection information is in the file or in
> the database.  We also get into a chicken and egg case where we have to
> have the database loaded to connect to it.  I am interested to hear
> where people think we should go with this.
> 

I would like to offer this opinion:

postmaster should connect to the database directory as the user who started it.

Thu, 21 Mar 2002 21:40:06 -0500
mlw <markw@mohawksoft.com>
Re: Problem with reloading groups in pg_hba.conf