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, Pennsylvania 19026

Fri, 22 Mar 2002 00:30:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
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


Fri, 22 Mar 2002 01:27:52 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Problem with reloading groups in pg_hba.conf
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, Pennsylvania 19026

Fri, 22 Mar 2002 01:32:28 -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:
> 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

Fri, 22 Mar 2002 01:51:50 -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:
> > 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, Pennsylvania 19026

Fri, 22 Mar 2002 01:54:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
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


Fri, 22 Mar 2002 11:41:29 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Problem with reloading groups in pg_hba.conf 
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, Pennsylvania 19026

Fri, 22 Mar 2002 12:19:15 -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:
> 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

Fri, 22 Mar 2002 12:24:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with reloading groups in pg_hba.conf 
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


Fri, 22 Mar 2002 12:34:38 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Problem with reloading groups in pg_hba.conf
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, Pennsylvania 19026

Fri, 22 Mar 2002 12:38:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf
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, Pennsylvania 19026

Wed, 27 Mar 2002 11:16:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Problem with reloading groups in pg_hba.conf