Thread: Problem with reloading groups in pg_hba.conf
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, Pennsylvania19026
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, Pennsylvania19026
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
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, Pennsylvania19026
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
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, Pennsylvania19026
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
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, Pennsylvania19026
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
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, Pennsylvania19026
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.
pgman wrote:
> 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.
I have another idea. What if we had a default group for each database,
like pg_connect_{dbname}, and you can add/remove users from that group
to grant/remove connection privileges? Sort of like a default +dbname
in pg_hba.conf.
It sort of merges the group feature with pg_hba.conf connections.
-- 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,
Pennsylvania19026
Bruce Momjian writes:
> I have another idea. What if we had a default group for each database,
> like pg_connect_{dbname}, and you can add/remove users from that group
> to grant/remove connection privileges?
That strikes me as a very ugly abuse of the privilege system. If you want
to grant a privilege, use GRANT, not the name of a group.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > I have another idea. What if we had a default group for each database,
> > like pg_connect_{dbname}, and you can add/remove users from that group
> > to grant/remove connection privileges?
>
> That strikes me as a very ugly abuse of the privilege system. If you want
> to grant a privilege, use GRANT, not the name of a group.
We could use GRANT and internally do it with per-database system groups.
It would fit into our system cleanly, and could be dumped/reloaded
cleanly too. Unfortunately, that would give us two places to specify
the connecting users, pg_hba.conf and GRANT CONNECT. Is that a problem?
It would be tricky to grant access to only one db or all db's using
GRANT. Not sure how that would be specified. This is where we start to
get overlap and confusion because it doesn't behave just like
pg_hba.conf but also doesn't have the same flexibility of pg_hba.conf.
I am still looking for ideas.
-- 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,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Unfortunately, that would give us two places to specify
> the connecting users, pg_hba.conf and GRANT CONNECT. Is that a problem?
Yes. What if they conflict?
I don't think GRANT CONNECT fits into our setup at all. I also doubt
that it will be needed very much once we have schemas.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Unfortunately, that would give us two places to specify > > the connecting users, pg_hba.conf and GRANT CONNECT. Is that a problem? > > Yes. What if they conflict? > > I don't think GRANT CONNECT fits into our setup at all. I also doubt > that it will be needed very much once we have schemas. With groups, we are at least giving admins a way to do this that they didn't have before. That may be enough. -- 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, Pennsylvania19026
Tom Lane writes: > I don't think GRANT CONNECT fits into our setup at all. I also doubt > that it will be needed very much once we have schemas. People have many times asked for a way to alter the connection settings from within the database. For instance, you add users in the database, but then you need to go elsewhere to give that user any access. Consider GRANT CONNECT a built-in editor for pg_hba.conf. You don't have to actually store the information in two separate places. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Tom Lane writes: > > > I don't think GRANT CONNECT fits into our setup at all. I also doubt > > that it will be needed very much once we have schemas. > > People have many times asked for a way to alter the connection settings > from within the database. For instance, you add users in the database, > but then you need to go elsewhere to give that user any access. Consider > GRANT CONNECT a built-in editor for pg_hba.conf. You don't have to > actually store the information in two separate places. I don't know. Automatically modifying a manually maintained config file isn't too common a feature. One problem would be if you where modifying the file in your editor and the backend rewrote the file. I think groups will give use the ability to add/remove connection from within the database. You just need to mention the group name in the config file. My original idea was to automatically identify some group name for each database but maybe that is too smart. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think groups will give use the ability to add/remove connection from
> within the database. You just need to mention the group name in the
> config file.
Good point.
There's also the fact that people will probably start using one big
database divided into per-user schemas as soon as schema facilities
are available. So getting fancy with pg_hba controls at this point
may well prove to be like building a better buggy whip; good in its
own context but rendered irrelevant by events.
I agree with Bruce that group-level access controls in pg_hba seem
like a sufficient answer at this point. If it turns out not, we
can always improve further in future releases.
regards, tom lane
Bruce Momjian writes: > I don't know. Automatically modifying a manually maintained config file > isn't too common a feature. One problem would be if you where modifying > the file in your editor and the backend rewrote the file. That's not different from you modifying the file in your editor and someone else doing the same thing at the same time. Yes, the concurrency issues are not trivial, but they can be solved. > I think groups will give use the ability to add/remove connection from > within the database. You just need to mention the group name in the > config file. My original idea was to automatically identify some group > name for each database but maybe that is too smart. Yes, that is perfectly fine. I just want an additional interface that allows you to "mention the group name in the config file" while connected to the database. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Bruce Momjian writes: > > > I don't know. Automatically modifying a manually maintained config file > > isn't too common a feature. One problem would be if you where modifying > > the file in your editor and the backend rewrote the file. > > That's not different from you modifying the file in your editor and > someone else doing the same thing at the same time. Yes, the concurrency > issues are not trivial, but they can be solved. Well, hopefully there is only one administrator at a time modifying pg_hba.conf. Random user/group mods by any superuser seems like a much more frequent occurance. Another thing is that people duing database-level user/group changes may not even know they are modifying pg_hba.conf. > > I think groups will give use the ability to add/remove connection from > > within the database. You just need to mention the group name in the > > config file. My original idea was to automatically identify some group > > name for each database but maybe that is too smart. > > Yes, that is perfectly fine. I just want an additional interface that > allows you to "mention the group name in the config file" while connected > to the database. I understand. I think the only way to do this cleanly is to have a per-database system group that can be created and modified inside the database. We can even have an 'all' group to match pg_hba.conf's database column 'all'. It is actually trivial to do this in the code with my patch. -- 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, Pennsylvania19026
Peter Eisentraut wrote: > Tom Lane writes: > > > I don't think GRANT CONNECT fits into our setup at all. I also doubt > > that it will be needed very much once we have schemas. > > People have many times asked for a way to alter the connection settings > from within the database. For instance, you add users in the database, > but then you need to go elsewhere to give that user any access. Consider > GRANT CONNECT a built-in editor for pg_hba.conf. You don't have to > actually store the information in two separate places. OK, Peter, I have implemented a 'samegroup' keyword in pg_hba.conf that works just like sameuser, except it checks for user membership in a group that is the same name as the database. Two lines of code (plus docs), lots of flexibility. So, if people want to control everything from psql, then can just put samegroup in the database column and create groups for each database. If we want to extend this, we can add a GRANT CONNECT command that optionally creates the group and add/removes users from that group. This is part of my pg_hba.conf overhaul patch that I am still working on. -- 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, Pennsylvania19026