We have the following TODO item:

	* Allow usernames to be specified directly in pg_hba.conf (Bruce)

My idea is to allow comma-separated usernames in the AUTH_ARGUMENT
column.  Right now we use it for ident user map files and secondary
password files.  It seems both easily already allow username
restrictions.  Adding usernames directly in pg_hba.conf is basically a
shortcut to creating such secondary files.

My idea is that if AUTH_ARGUMENT starts with "=", it represents a list
of comma-separated usernames.

  host       template1   192.168.12.10 255.255.255.255    md5 =bmomjian,jeffw

Do I need to allow usernames with spaces or quoted usernames?  I don't
think so.

For implementation, I was going to simulate a secondary password file
with no passwords.  We already support that internally as a username
restriction option. Those are loaded into memory as linked lists of text
lines, if I remember correclty.

-- 
  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

Sun, 10 Mar 2002 01:20:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Allowing usernames in pg_hba.conf
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We have the following TODO item:
> 	* Allow usernames to be specified directly in pg_hba.conf (Bruce)

> My idea is to allow comma-separated usernames in the AUTH_ARGUMENT
> column.  Right now we use it for ident user map files and secondary
> password files.  It seems both easily already allow username
> restrictions.  Adding usernames directly in pg_hba.conf is basically a
> shortcut to creating such secondary files.

> My idea is that if AUTH_ARGUMENT starts with "=", it represents a list
> of comma-separated usernames.

Ugh.  What of the auth methods that have another interpretation for
AUTH_ARGUMENT?

> Do I need to allow usernames with spaces or quoted usernames?  I don't
> think so.

I do.

This is definitely stressing pg_hba past its design limits --- heck, the
name of the file isn't even appropriate anymore, if usernames are part
of the match criteria.  Rather than contorting things to maintain a
pretense of backwards compatibility, it's time to abandon the current
file format, change the name, and start over.  (I believe there are
traces in the code of this having been done before.)  We could probably
arrange to read and convert the existing pg_hba format if we don't see
a new-style authentication config file out there.

My first thoughts are (a) add a column outright for matching username;
(b) for both database and username columns, allow a filename reference
so that a bunch of names can be stored separately from the master
authentication file.  I don't much care for sticking large lists of
names into the auth file itself.

It would be good to go back over the past complaints about "I can't
do this with pg_hba" to see if this is sufficient to solve them.

			regards, tom lane

Sun, 10 Mar 2002 02:31:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing usernames in pg_hba.conf 
Tom Lane writes:

> This is definitely stressing pg_hba past its design limits --- heck, the
> name of the file isn't even appropriate anymore, if usernames are part
> of the match criteria.  Rather than contorting things to maintain a
> pretense of backwards compatibility, it's time to abandon the current
> file format, change the name, and start over.

The pg_hba.conf thing is slowly growing to become a bad excuse for a
completely general authentication system, such as PAM.  Instead of
creating our own, maybe we could rip off the "BSD authentication" system
from some free *BSD.  I haven't seen it, but it's supposed to be like (or
"better than") PAM.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 21:32:02 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing usernames in pg_hba.conf 
> > This is definitely stressing pg_hba past its design limits --- heck, the
> > name of the file isn't even appropriate anymore, if usernames are part
> > of the match criteria.  Rather than contorting things to maintain a
> > pretense of backwards compatibility, it's time to abandon the current
> > file format, change the name, and start over.
>
> The pg_hba.conf thing is slowly growing to become a bad excuse for a
> completely general authentication system, such as PAM.  Instead of
> creating our own, maybe we could rip off the "BSD authentication" system
> from some free *BSD.  I haven't seen it, but it's supposed to be like (or
> "better than") PAM.

Hmmm...I've never heard of the "BSD authentication" system...?  As far as I
was aware, FreeBSD uses PAM:

man pam

PAM(8)                      PAM Manual                     PAM(8)

NAME
       PAM - Pluggable Authentication Modules

SYNOPSIS
       /etc/pam.conf

DESCRIPTION
       This  manual  is intended to offer a quick introduction to
       PAM.  For more information the reader is directed  to  the
       Linux-PAM system administrators' guide.

       PAM  Is  a system of libraries that handle the authentica-
       tion tasks of applications (services) on the system.   The
       library  provides  a stable general interface (Application
       Programming Interface - API) that privilege granting  pro-
       grams  (such  as  login(1)  and su(1)) defer to to perform
       standard authentication tasks.

...

Chris


Mon, 11 Mar 2002 10:38:32 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Allowing usernames in pg_hba.conf 
Christopher Kings-Lynne writes:

> Hmmm...I've never heard of the "BSD authentication" system...?  As far as I
> was aware, FreeBSD uses PAM:

I found a bsd_auth(3) man page on OpenBSD:

http://www.openbsd.org/cgi-bin/man.cgi?query=bsd_auth&apropos=0&sektion=0&manpath=OpenBSD+Current&arch=i386&format=html

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 21:57:49 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing usernames in pg_hba.conf 
OK,

FreeBSD doesn't have a bsd_auth man page, nor any of the functions mentioned
on that page.  It doesn't have the login_cap and login.conf references at
the bottom, however.

Chris

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net]
> Sent: Monday, 11 March 2002 10:58 AM
> To: Christopher Kings-Lynne
> Cc: Tom Lane; Bruce Momjian; PostgreSQL-development
> Subject: RE: [HACKERS] Allowing usernames in pg_hba.conf
>
>
> Christopher Kings-Lynne writes:
>
> > Hmmm...I've never heard of the "BSD authentication" system...?
> As far as I
> > was aware, FreeBSD uses PAM:
>
> I found a bsd_auth(3) man page on OpenBSD:
>
> http://www.openbsd.org/cgi-bin/man.cgi?query=bsd_auth&apropos=0&se
> ktion=0&manpath=OpenBSD+Current&arch=i386&format=html
>
> --
> Peter Eisentraut   peter_e@gmx.net
>


Mon, 11 Mar 2002 11:01:32 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Allowing usernames in pg_hba.conf 
> This is definitely stressing pg_hba past its design limits --- heck, the
> name of the file isn't even appropriate anymore, if usernames are part
> of the match criteria.  Rather than contorting things to maintain a
> pretense of backwards compatibility, it's time to abandon the current
> file format, change the name, and start over.  (I believe there are
> traces in the code of this having been done before.)  We could probably
> arrange to read and convert the existing pg_hba format if we don't see
> a new-style authentication config file out there.
> 
> My first thoughts are (a) add a column outright for matching username;
> (b) for both database and username columns, allow a filename reference
> so that a bunch of names can be stored separately from the master
> authentication file.  I don't much care for sticking large lists of
> names into the auth file itself.

OK, I have an idea.  I was never happy with the AUTH_ARGUMENT column. 
What I propose is adding an optional auth_type=val capability to the
file, so an AUTH_ARGUMENT column isn't needed.  If a username column
starts with @, it is a file name containing user names.  The same can be
done with the database column.  Seems very backward compatible..  If you
don't use auth_argument, it is totally compatible.  If you do, you need
to use the new format auth_type=val:

TYPE     DATABASE    IP_ADDRESS    MASK               AUTH_TYPE  USERNAMES
local      all                                          trust	 fred
host       all         127.0.0.1     255.255.255.255    trust	 @staff
host       all         127.0.0.1     255.255.255.255    ident=sales jimmy

I have thought about a redesign of the file, but I can't come up with
something that is as powerful, and cleaner.  Do others have ideas?

As far as missing features, I can't think of other things people have
asked for in pg_hba.conf except usernames.

-- 
  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

Mon, 11 Mar 2002 00:06:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing usernames in pg_hba.conf
Is there a way to grant another user access (full/limited) to an entire 
database?

Right now pg_hba.conf controls connectivity to a database.

However from the docs it seems that one has to do a grant for _every_ 
table.  if a new table is created the user can't access it. This can be 
annoying in some situations.

Am I missing something?

Thanks,
Link.

At 12:06 AM 11-03-2002 -0500, Bruce Momjian wrote:

>I have thought about a redesign of the file, but I can't come up with
>something that is as powerful, and cleaner.  Do others have ideas?
>
>As far as missing features, I can't think of other things people have
>asked for in pg_hba.conf except usernames.



Mon, 11 Mar 2002 16:30:03 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Allowing usernames in pg_hba.conf
Lincoln Yeoh wrote:
> Is there a way to grant another user access (full/limited) to an entire 
> database?
> 
> Right now pg_hba.conf controls connectivity to a database.
> 
> However from the docs it seems that one has to do a grant for _every_ 
> table.  if a new table is created the user can't access it. This can be 
> annoying in some situations.

Table access and database access are different issues.  One is controled
by pg_hba.conf and other by GRANT.  There is no mass-GRANT capability.

-- 
  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

Mon, 11 Mar 2002 10:03:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing usernames in pg_hba.conf

On Mon, 11 Mar 2002, Bruce Momjian wrote:

> Lincoln Yeoh wrote:
> > Is there a way to grant another user access (full/limited) to an entire
> > database?
> >
> > Right now pg_hba.conf controls connectivity to a database.
> >
> > However from the docs it seems that one has to do a grant for _every_
> > table.  if a new table is created the user can't access it. This can be
> > annoying in some situations.
>
> Table access and database access are different issues.  One is controled
> by pg_hba.conf and other by GRANT.  There is no mass-GRANT capability.

I'd started a long-ish post about how pgsql should have a proper
permission model for user-to-database access - when someone pointed me to
the following url, which I'd like to bring to everybody's attention:

http://candle.pha.pa.us/cgi-bin/pgtodo?privileges

Is this something PeterE's still looking at doing for 7.(I guess 3, now?)



-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/


Mon, 11 Mar 2002 12:08:36 -0600 (CST)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: Allowing usernames in pg_hba.conf
Dominic J. Eidson wrote:
> 
> On Mon, 11 Mar 2002, Bruce Momjian wrote:
> 
> > Lincoln Yeoh wrote:
> > > Is there a way to grant another user access (full/limited) to an entire
> > > database?
> > >
> > > Right now pg_hba.conf controls connectivity to a database.
> > >
> > > However from the docs it seems that one has to do a grant for _every_
> > > table.  if a new table is created the user can't access it. This can be
> > > annoying in some situations.
> >
> > Table access and database access are different issues.  One is controled
> > by pg_hba.conf and other by GRANT.  There is no mass-GRANT capability.
> 
> I'd started a long-ish post about how pgsql should have a proper
> permission model for user-to-database access - when someone pointed me to
> the following url, which I'd like to bring to everybody's attention:
> 
> http://candle.pha.pa.us/cgi-bin/pgtodo?privileges
> 
> Is this something PeterE's still looking at doing for 7.(I guess 3, now?)

I assume it is coming in as part of schemas.  Tom?

-- 
  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

Mon, 11 Mar 2002 13:10:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing usernames in pg_hba.conf
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> http://candle.pha.pa.us/cgi-bin/pgtodo?privileges
>> 
>> Is this something PeterE's still looking at doing for 7.(I guess 3, now?)

> I assume it is coming in as part of schemas.  Tom?

Privileges on schemas should improve matters, but I do not know whether
that will fully satisfy people ...

			regards, tom lane

Mon, 11 Mar 2002 20:11:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing usernames in pg_hba.conf 
Dominic J. Eidson writes:

> I'd started a long-ish post about how pgsql should have a proper
> permission model for user-to-database access - when someone pointed me to
> the following url, which I'd like to bring to everybody's attention:
>
> http://candle.pha.pa.us/cgi-bin/pgtodo?privileges
>
> Is this something PeterE's still looking at doing for 7.(I guess 3, now?)

I guess the implementation ideas have changes a little, but the code has
been generalized enough so that you can add privileges on almost anything.
Function and language privleges are available in the 7.3 branch.  Those
are the ones most people wanted.

I guess you could add privileges to databases, too.  But I'm wary about
keeping the connection permissions in the database because you can easily
lock yourself out that way.  However, there are plenty of other ways you
can lock yourself out and in most cases you can start a standalone backend
to fix the situation.  So may that would be a possibility.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 11 Mar 2002 22:56:20 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing usernames in pg_hba.conf
At 10:03 AM 11-03-2002 -0500, Bruce Momjian wrote:
>Lincoln Yeoh wrote:
> > Is there a way to grant another user access (full/limited) to an entire
> > database?
> >
> > Right now pg_hba.conf controls connectivity to a database.
> >
> > However from the docs it seems that one has to do a grant for _every_
> > table.  if a new table is created the user can't access it. This can be
> > annoying in some situations.
>
>Table access and database access are different issues.  One is controled
>by pg_hba.conf and other by GRANT.  There is no mass-GRANT capability.

Actually I don't want a mass (table level?) grant. I'm looking for a way to 
granting users access on a database level. I want a database level grant. I 
don't need it, it's just a want :).

Because my assumption is if new tables (etc) are created after a manual 
mass grant, the nonowner won't have access to them.

Am I trying to do things the wrong way tho?

Regards,
Link.



Tue, 12 Mar 2002 14:41:40 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Allowing usernames in pg_hba.conf