Thread

Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-26T13:48:53Z

Something to maybe add to the TODO list, if someone has the
time/inclination to work on it ...

The problem with the current auth system, as I see it, is that you can't
easily have seperate user lists and passwords per database ... its shared
across the system ...

The closest you can get is to have a database defined as 'password' in
pg_hba.conf, with an external password file from pg_shadow, which, for the
most part, is good ... but it doesn't lend itself well to a 'hands off'
server ...

Right now, with v7.2, we have two 'sub-processes' that start up for stats
collection ... has anyone thought about adding a 3rd as a password server?

Basically, it would be used to manage the pg_hba.conf file itself *while*
the server is/was live ...

For instance, CREATE DATABASE would need to get extended to have
something like "WITH AUTH '{trust|password|ident}' FROM '<IP>'" added to
it, which would add an appropriate line to pg_hba.conf ...

The database owner would have the ability to add users if (and only if)
the database was setup for 'password', and the password daemon would
automatically modify the password file(s) for that database ..

What would be even more cool ... to be able to do something like:

CREATE USER <user> FROM <IP> WITH PASSWORD <password>

which, if it didn't exist, would create a line in pg_hba.conf of:

host	<database>	<ip>	password	<database>

and create a <database> password file with that person in it ...

Re: Password sub-process ...

Jan Wieck <janwieck@yahoo.com> — 2002-07-26T14:02:58Z

"Marc G. Fournier" wrote:
> 
> Something to maybe add to the TODO list, if someone has the
> time/inclination to work on it ...
> 
> The problem with the current auth system, as I see it, is that you can't
> easily have seperate user lists and passwords per database ... its shared
> across the system ...

I don't see a problem with that in general. Other databases (like
Informix) do it even worse and you need a UNIX or NIS account where the
password is held!

What would be good is IMHO to have GRANT|REVOKE CONNECT which defaults
to REVOKE, so only superusers and the DB owner can connect, but that the
owner later can change it without the need to edit hba.conf.

Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: Password sub-process ...

Roderick A. Anderson <raanders@acm.org> — 2002-07-26T14:33:43Z

On Fri, 26 Jul 2002, Jan Wieck wrote:

> What would be good is IMHO to have GRANT|REVOKE CONNECT which defaults
> to REVOKE, so only superusers and the DB owner can connect, but that the
> owner later can change it without the need to edit hba.conf.

Oh, yes.  Me too please.  I think something close to this is coming with 
schemes - well at least my take on it indicates that.

Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."

Re: Password sub-process ...

Bruno Wolff III <bruno@wolff.to> — 2002-07-26T15:28:09Z

On Fri, Jul 26, 2002 at 10:48:53 -0300,
  "Marc G. Fournier" <scrappy@hub.org> wrote:
> 
> Something to maybe add to the TODO list, if someone has the
> time/inclination to work on it ...
> 
> The problem with the current auth system, as I see it, is that you can't
> easily have seperate user lists and passwords per database ... its shared
> across the system ...

If you look at the 7.3 docs you will see that you will be able to restrict
access to databases by user or group name. If you use group name and have
one group per database you will be able to administer access pretty easily.
If you have a lot of databases you can use group names matching database
names and not have to touch your conf file when making new databases.

Re: Password sub-process ...

Rod Taylor <rbt@zort.ca> — 2002-07-26T15:30:06Z

On Fri, 2002-07-26 at 11:28, Bruno Wolff III wrote:
> On Fri, Jul 26, 2002 at 10:48:53 -0300,
>   "Marc G. Fournier" <scrappy@hub.org> wrote:
> > 
> > Something to maybe add to the TODO list, if someone has the
> > time/inclination to work on it ...
> > 
> > The problem with the current auth system, as I see it, is that you can't
> > easily have seperate user lists and passwords per database ... its shared
> > across the system ...
> 
> If you look at the 7.3 docs you will see that you will be able to restrict
> access to databases by user or group name. If you use group name and have
> one group per database you will be able to administer access pretty easily.
> If you have a lot of databases you can use group names matching database
> names and not have to touch your conf file when making new databases.

This still doesn't allow john on db1 to be a different user than john on
db2.  To accomplish that (easily) we still need to install different
instances for each database.  Very minor issue, but it would be nice to
have the ability (for those selling PostgreSQL storage services).

Re: Password sub-process ...

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-26T15:48:01Z

Rod Taylor <rbt@zort.ca> writes:
> This still doesn't allow john on db1 to be a different user than john on
> db2.  To accomplish that (easily) we still need to install different
> instances for each database.

Some people think that cross-database user names are a feature, not
a bug.  I cannot see any way to change that without creating huge
backward-compatibility headaches --- and it's not at all clear to
me that it's a step forward, anyway.

I think that it might be worth adding a CONNECT privilege at the
database level; that together with Bruce's recent revisions to
pg_hba.conf ought to be a pretty good improvement.

			regards, tom lane

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-26T16:45:42Z

On Fri, 26 Jul 2002, Bruno Wolff III wrote:

> On Fri, Jul 26, 2002 at 10:48:53 -0300,
>   "Marc G. Fournier" <scrappy@hub.org> wrote:
> >
> > Something to maybe add to the TODO list, if someone has the
> > time/inclination to work on it ...
> >
> > The problem with the current auth system, as I see it, is that you can't
> > easily have seperate user lists and passwords per database ... its shared
> > across the system ...
>
> If you look at the 7.3 docs you will see that you will be able to restrict
> access to databases by user or group name. If you use group name and have
> one group per database you will be able to administer access pretty easily.
> If you have a lot of databases you can use group names matching database
> names and not have to touch your conf file when making new databases.

Right, but, unless I'm missed something, you still won't have the ability
to have "two joeblows on the system, with two distinct passwords, having
access to two different databases" ...

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-26T16:48:53Z

On 26 Jul 2002, Rod Taylor wrote:

> On Fri, 2002-07-26 at 11:28, Bruno Wolff III wrote:
> > On Fri, Jul 26, 2002 at 10:48:53 -0300,
> >   "Marc G. Fournier" <scrappy@hub.org> wrote:
> > >
> > > Something to maybe add to the TODO list, if someone has the
> > > time/inclination to work on it ...
> > >
> > > The problem with the current auth system, as I see it, is that you can't
> > > easily have seperate user lists and passwords per database ... its shared
> > > across the system ...
> >
> > If you look at the 7.3 docs you will see that you will be able to restrict
> > access to databases by user or group name. If you use group name and have
> > one group per database you will be able to administer access pretty easily.
> > If you have a lot of databases you can use group names matching database
> > names and not have to touch your conf file when making new databases.
>
> This still doesn't allow john on db1 to be a different user than john on
> db2.  To accomplish that (easily) we still need to install different
> instances for each database.  Very minor issue, but it would be nice to
> have the ability (for those selling PostgreSQL storage services).

Actually, in an IS dept with several applications running, each with a
seperate group of users, this would be a plus ... if I have to create N
instances, I'm splitting up available memory/shared memory between them,
instead of creating one great big pool ...

for instance, when we upgraded archives.postgresql.org's memory a while
back, we created a shared memory segment of 1.5Gig of RAM for all the
databases (except fts, she's still under the old v7.1.3 db).  Which means
that if all databases are quiet, and one comes to life to do a nice big
query, it has a nice big pool of RAM to work with ...

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-26T16:55:58Z

On Fri, 26 Jul 2002, Tom Lane wrote:

> Rod Taylor <rbt@zort.ca> writes:
> > This still doesn't allow john on db1 to be a different user than john on
> > db2.  To accomplish that (easily) we still need to install different
> > instances for each database.
>
> Some people think that cross-database user names are a feature, not
> a bug.  I cannot see any way to change that without creating huge
> backward-compatibility headaches --- and it's not at all clear to
> me that it's a step forward, anyway.
>
> I think that it might be worth adding a CONNECT privilege at the
> database level; that together with Bruce's recent revisions to
> pg_hba.conf ought to be a pretty good improvement.

Note that I'm not looking to get rid of any functionality, only suggesting
that we should look at improving the ability to do remote administration
(ie. eliminate the requirement to manually change files) ...

As an example ... at the University I work at, we've started to use PgSQL
for more and more of our internal stuff, and/or let the students start to
use it for their projects ... so we have PgSQL running on one server,
while its being access by other ones around campus.  I'd like to be able
to be able to streamline things so that operations could easily create a
new database for a student (or faculty) on the server as a simple SQL
"CREATE DATABASE/USER" command, vs risking them making a mistake when they
manually edit the pg_hba.conf file ...

Also, I thnk I might have missed the point of the whole CONNECT privilege
thing ... if I have two ppl named joe on the system, each with different
passwords, how does the CONNECT know which one is the one that has access
to that database?

Re: Password sub-process ...

Bruno Wolff III <bruno@wolff.to> — 2002-07-26T17:21:50Z

On Fri, Jul 26, 2002 at 13:55:58 -0300,
  "Marc G. Fournier" <scrappy@hub.org> wrote:
> 
> As an example ... at the University I work at, we've started to use PgSQL
> for more and more of our internal stuff, and/or let the students start to
> use it for their projects ... so we have PgSQL running on one server,
> while its being access by other ones around campus.  I'd like to be able
> to be able to streamline things so that operations could easily create a
> new database for a student (or faculty) on the server as a simple SQL
> "CREATE DATABASE/USER" command, vs risking them making a mistake when they
> manually edit the pg_hba.conf file ...

From what I read in the development docs, in 7.3 you will be able to just
do a createuser and createdb to make things work. There will be a "sameuser"
user specification which will allow access to a database with a matching name.

> Also, I thnk I might have missed the point of the whole CONNECT privilege
> thing ... if I have two ppl named joe on the system, each with different
> passwords, how does the CONNECT know which one is the one that has access
> to that database?

I think for something like a University or IT shop you would want to use
IDs that are consistant accross all servers. Unfortunately when you are
providing hosting service for other companies it may not be feasible to
do that.

Re: Password sub-process ...

Rod Taylor <rbt@zort.ca> — 2002-07-26T17:27:47Z

On Fri, 2002-07-26 at 11:48, Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> > This still doesn't allow john on db1 to be a different user than john on
> > db2.  To accomplish that (easily) we still need to install different
> > instances for each database.
> 
> Some people think that cross-database user names are a feature, not
> a bug.  I cannot see any way to change that without creating huge
> backward-compatibility headaches --- and it's not at all clear to
> me that it's a step forward, anyway.

I've been considering ways to come up with 2 classes of user.  One which
is global, the other which is local.  But it's not nearly enough of an
inconvenience to warrant it.

Re: Password sub-process ...

Rod Taylor <rbt@zort.ca> — 2002-07-26T17:49:30Z

On Fri, 2002-07-26 at 12:55, Marc G. Fournier wrote:
> On Fri, 26 Jul 2002, Tom Lane wrote:
> 
> > Rod Taylor <rbt@zort.ca> writes:
> > > This still doesn't allow john on db1 to be a different user than john on
> > > db2.  To accomplish that (easily) we still need to install different
> > > instances for each database.
> >
> > Some people think that cross-database user names are a feature, not
> > a bug.  I cannot see any way to change that without creating huge
> > backward-compatibility headaches --- and it's not at all clear to
> > me that it's a step forward, anyway.
> >
> > I think that it might be worth adding a CONNECT privilege at the
> > database level; that together with Bruce's recent revisions to
> > pg_hba.conf ought to be a pretty good improvement.

> Also, I thnk I might have missed the point of the whole CONNECT privilege
> thing ... if I have two ppl named joe on the system, each with different
> passwords, how does the CONNECT know which one is the one that has access
> to that database?

Well.. right now we call one db1_joe and db2_joe.  I meant adding the
ability to lock some users to specific DBs -- and only exist there. 
Authentication would use destination DB as well as username.

Where DB is null, the user is a global user.  Usernames would still be
unique per database.

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T03:02:17Z

Bruno Wolff III wrote:
> On Fri, Jul 26, 2002 at 13:55:58 -0300,
>   "Marc G. Fournier" <scrappy@hub.org> wrote:
> > 
> > As an example ... at the University I work at, we've started to use PgSQL
> > for more and more of our internal stuff, and/or let the students start to
> > use it for their projects ... so we have PgSQL running on one server,
> > while its being access by other ones around campus.  I'd like to be able
> > to be able to streamline things so that operations could easily create a
> > new database for a student (or faculty) on the server as a simple SQL
> > "CREATE DATABASE/USER" command, vs risking them making a mistake when they
> > manually edit the pg_hba.conf file ...
> 
> >From what I read in the development docs, in 7.3 you will be able to just
> do a createuser and createdb to make things work. There will be a "sameuser"
> user specification which will allow access to a database with a matching name.

Actually, there is a 'samegroup' iu 7.3 too, so you can create the db,
create the group, and add whoever you want to the group.  Pretty simple.

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

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T03:03:11Z

Marc G. Fournier wrote:
> 
> Something to maybe add to the TODO list, if someone has the
> time/inclination to work on it ...
> 
> The problem with the current auth system, as I see it, is that you can't
> easily have seperate user lists and passwords per database ... its shared
> across the system ...
> 
> The closest you can get is to have a database defined as 'password' in
> pg_hba.conf, with an external password file from pg_shadow, which, for the
> most part, is good ... but it doesn't lend itself well to a 'hands off'
> server ...

Actually, that is removed in 7.3.  It was too weird a syntax and format
and the original idea of sharing /etc/passwd there didn't work anymore
on most systems.

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

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T03:12:01Z

On Mon, 29 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> >
> > Something to maybe add to the TODO list, if someone has the
> > time/inclination to work on it ...
> >
> > The problem with the current auth system, as I see it, is that you can't
> > easily have seperate user lists and passwords per database ... its shared
> > across the system ...
> >
> > The closest you can get is to have a database defined as 'password' in
> > pg_hba.conf, with an external password file from pg_shadow, which, for the
> > most part, is good ... but it doesn't lend itself well to a 'hands off'
> > server ...
>
> Actually, that is removed in 7.3.  It was too weird a syntax and format
> and the original idea of sharing /etc/passwd there didn't work anymore
> on most systems.

whoa ... what replaced it?  weird it might have been, but it worked great
if you knew about it ...

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T03:16:10Z

Marc G. Fournier wrote:
> On Mon, 29 Jul 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > >
> > > Something to maybe add to the TODO list, if someone has the
> > > time/inclination to work on it ...
> > >
> > > The problem with the current auth system, as I see it, is that you can't
> > > easily have seperate user lists and passwords per database ... its shared
> > > across the system ...
> > >
> > > The closest you can get is to have a database defined as 'password' in
> > > pg_hba.conf, with an external password file from pg_shadow, which, for the
> > > most part, is good ... but it doesn't lend itself well to a 'hands off'
> > > server ...
> >
> > Actually, that is removed in 7.3.  It was too weird a syntax and format
> > and the original idea of sharing /etc/passwd there didn't work anymore
> > on most systems.
> 
> whoa ... what replaced it?  weird it might have been, but it worked great
> if you knew about it ...

Well, I asked and no one answered. ;-)

Actually, it is replaced by encrypted pg_shadow by default in 7.3, and
the new USER (users or groups) column in pg_hba.conf that will be in
7.3 that can restrict based on user/group.  This replaces the use of the
secondary file for just usernames.  You can now specify a filename in
pg_hba.conf listing these.  Would you look over the pg_hba.conf in CVS
and tell me what additional things are needed.

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

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T03:43:52Z

On Mon, 29 Jul 2002, Bruce Momjian wrote:

> Actually, it is replaced by encrypted pg_shadow by default in 7.3, and
> the new USER (users or groups) column in pg_hba.conf that will be in 7.3
> that can restrict based on user/group.  This replaces the use of the
> secondary file for just usernames.  You can now specify a filename in
> pg_hba.conf listing these.  Would you look over the pg_hba.conf in CVS
> and tell me what additional things are needed.

Wow, what a change ... some nice stuff in there, mind you, but unless I'm
missing something, you've thrown out some *major* functionality that we
had before :( And since I missed this, its quite possible that i am
missing something :)

First and foremost in my mind ... how do you have two users in the system
with seperate passwords?

For instance, I have an application that right now that each authenticated
user has a seperate userid/pass in pg_user ... this doesn't deal will with
running multiple instances of this app on the same instance of PgSQL,
since as soon as there are two 'bruce' users, only one can have a password
... I could run two instances of PgSQL, but then you have to split the
resources between the two, instead of, for instance, having one great big
shared memory pool attached to one instance to cover both ...

So, I recode the app (yes, I have an app that was coded like this that I
have to fix ... we weren't thinking when we wrote that section) so that
when I add a new user to the application it does two things:

	1. adds the username to pg_user *if* required
	2. adds the username/password to a "password" file specific to
	   that instance of the application

So, unless I've missed something, in v7.3, this won't be possible?

Somehow, I need to be able to have two users Bruce in pg_users, each with
seperate passwords, with Bruce with pass1 having access to database1 and
Bruce with pass2 having access to database2 ...

Now, to knock out some thoughts here ... would it be possible to add a
field to pg_{user,shadow} to state what database that userid/passwd pair
belongs to?  so, if AUTHTYPE == md5 or password, authentication would be
based on all those users that 'belong' to that database?  This could add
the ability for a database owner to easily add a user for his/her
database, in that if a user is created within a specific database by a
non-superuse account, it automatically assigns that user to that database?

CREATE USER would have an extra, option paramater of 'FOR <database>'?

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T03:59:09Z

Marc G. Fournier wrote:
> On Mon, 29 Jul 2002, Bruce Momjian wrote:
> 
> > Actually, it is replaced by encrypted pg_shadow by default in 7.3, and
> > the new USER (users or groups) column in pg_hba.conf that will be in 7.3
> > that can restrict based on user/group.  This replaces the use of the
> > secondary file for just usernames.  You can now specify a filename in
> > pg_hba.conf listing these.  Would you look over the pg_hba.conf in CVS
> > and tell me what additional things are needed.
> 
> Wow, what a change ... some nice stuff in there, mind you, but unless I'm
> missing something, you've thrown out some *major* functionality that we
> had before :( And since I missed this, its quite possible that i am
> missing something :)
> 
> First and foremost in my mind ... how do you have two users in the system
> with seperate passwords?

No, it doesn't seem possible now.  I didn't know anyone was still using
that secondary password feature, and if they were, I thought they were
using only the 'username-list' version where no password was supplied,
not the username-crypted-password version.

Actually, it is hard to argue that having two users in pg_shadow, but
having them as different people with different passwords makes much
sense, though I can see why you would want to do that.

The idea of removing it was that it wasn't used much, and that the
syntax of an optional password file at the end was pretty weird,
especially now that we have a USER column.

Not sure what to do now.  We can re-add it but the code that did it is
gone, and we now cache everything, so the code has to be refactored to
cache that username/cryptpassword content.

I actually added to code to make administration easier, but in your
case, I seem to have made it harder.

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

Re: Password sub-process ...

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T04:17:18Z

"Marc G. Fournier" <scrappy@hub.org> writes:
> First and foremost in my mind ... how do you have two users in the system
> with seperate passwords?  ...
> since as soon as there are two 'bruce' users, only one can have a password

Uh, we've *never* supported "two bruce users" ... users have always been
installation-wide.  I am not sure what the notion of a database-owning
user means if user names are not of wider scope than databases.

No doubt we could redesign the system so that user names are local to a
database, and break a lot of existing setups in the process.  But what's
the value?  If you want separate usernames you can set up separate
postmasters.  If we change, and you don't want separate user names
across databases, you'll be out of luck.

			regards, tom lane

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T04:33:08Z

Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> > First and foremost in my mind ... how do you have two users in the system
> > with seperate passwords?  ...
> > since as soon as there are two 'bruce' users, only one can have a password
> 
> Uh, we've *never* supported "two bruce users" ... users have always been
> installation-wide.  I am not sure what the notion of a database-owning
> user means if user names are not of wider scope than databases.
> 
> No doubt we could redesign the system so that user names are local to a
> database, and break a lot of existing setups in the process.  But what's
> the value?  If you want separate usernames you can set up separate
> postmasters.  If we change, and you don't want separate user names
> across databases, you'll be out of luck.

He was being tricky by having different passwords for the same user on
each database, so one user couldn't get into the other database, even
though it was the same name.  He could actually have a user access
databases 1,2,3 and another user with a different password access
databases 4,5,6 because of the username/password files.  Now, he can't
do that.

Having those file function as username lists is already implemented
better in the new code.  The question is whether using those secondary
passwords is widespread enough that I need to get that into the code
too.  It was pretty confusing for users, so I am hesitant to re-add it,
but I hate for Marc to lose functionality he had in the past.

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

Re: Password sub-process ...

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T04:39:51Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Uh, we've *never* supported "two bruce users" ...

> He was being tricky by having different passwords for the same user on
> each database, so one user couldn't get into the other database, even
> though it was the same name.

But the system didn't realize they were two different users.  (Try
dropping just one of them.)  And what if they happened to choose the
same password?  I think this is a fragile kluge not a supported feature.

> The question is whether using those secondary
> passwords is widespread enough that I need to get that into the code
> too.  It was pretty confusing for users, so I am hesitant to re-add it,
> but I hate for Marc to lose functionality he had in the past.

I'd like to think of a better answer, not put back that same kluge.
Ideas anyone?

			regards, tom lane

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T04:41:38Z

Tom Lane wrote:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> > First and foremost in my mind ... how do you have two users in the system
> > with seperate passwords?  ...
> > since as soon as there are two 'bruce' users, only one can have a password
> 
> Uh, we've *never* supported "two bruce users" ... users have always been
> installation-wide.  I am not sure what the notion of a database-owning
> user means if user names are not of wider scope than databases.
> 
> No doubt we could redesign the system so that user names are local to a
> database, and break a lot of existing setups in the process.  But what's
> the value?  If you want separate usernames you can set up separate
> postmasters.  If we change, and you don't want separate user names
> across databases, you'll be out of luck.

On the topic of whether Marc gets extra consideration for feature
requests, here is a funny joke about Jerry Pournelle from Byte Magazine:

	http://www.netfunny.com/rhf/jokes/95q1/jpreviews.html

I love the helicopter tech support.

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

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T04:42:16Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Uh, we've *never* supported "two bruce users" ...
> 
> > He was being tricky by having different passwords for the same user on
> > each database, so one user couldn't get into the other database, even
> > though it was the same name.
> 
> But the system didn't realize they were two different users.  (Try
> dropping just one of them.)  And what if they happened to choose the
> same password?  I think this is a fragile kluge not a supported feature.
> 
> > The question is whether using those secondary
> > passwords is widespread enough that I need to get that into the code
> > too.  It was pretty confusing for users, so I am hesitant to re-add it,
> > but I hate for Marc to lose functionality he had in the past.
> 
> I'd like to think of a better answer, not put back that same kluge.
> Ideas anyone?

Agreed.  A clear kludge.  I just feel guilty because I removed it.

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

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T04:43:48Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Uh, we've *never* supported "two bruce users" ...
> 
> > He was being tricky by having different passwords for the same user on
> > each database, so one user couldn't get into the other database, even
> > though it was the same name.
> 
> But the system didn't realize they were two different users.  (Try
> dropping just one of them.)  And what if they happened to choose the
> same password?  I think this is a fragile kluge not a supported feature.
> 
> > The question is whether using those secondary
> > passwords is widespread enough that I need to get that into the code
> > too.  It was pretty confusing for users, so I am hesitant to re-add it,
> > but I hate for Marc to lose functionality he had in the past.
> 
> I'd like to think of a better answer, not put back that same kluge.
> Ideas anyone?

I just thought a little more.  Basically, I can't imagine any better
answer because they _should_ be the same user, and any trickery that
allows the same user to have two different passwords for two different
database will appear to be bad design.

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

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T05:01:54Z

On Tue, 30 Jul 2002, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> > First and foremost in my mind ... how do you have two users in the system
> > with seperate passwords?  ...
> > since as soon as there are two 'bruce' users, only one can have a password
>
> Uh, we've *never* supported "two bruce users" ... users have always been
> installation-wide.  I am not sure what the notion of a database-owning
> user means if user names are not of wider scope than databases.

Sorry, you mis-understand here ... pg_user/shadow only has one bruce user
in it ... but the way it was up until now, with the password file in
pg_hba.conf, I could assign bruce with a different password for database1
vs database2 ... effectively, have 'two bruce users' ...

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T05:03:28Z

On Tue, 30 Jul 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > "Marc G. Fournier" <scrappy@hub.org> writes:
> > > First and foremost in my mind ... how do you have two users in the system
> > > with seperate passwords?  ...
> > > since as soon as there are two 'bruce' users, only one can have a password
> >
> > Uh, we've *never* supported "two bruce users" ... users have always been
> > installation-wide.  I am not sure what the notion of a database-owning
> > user means if user names are not of wider scope than databases.
> >
> > No doubt we could redesign the system so that user names are local to a
> > database, and break a lot of existing setups in the process.  But what's
> > the value?  If you want separate usernames you can set up separate
> > postmasters.  If we change, and you don't want separate user names
> > across databases, you'll be out of luck.
>
> He was being tricky by having different passwords for the same user on
> each database, so one user couldn't get into the other database, even
> though it was the same name.  He could actually have a user access
> databases 1,2,3 and another user with a different password access
> databases 4,5,6 because of the username/password files.  Now, he can't
> do that.
>
> Having those file function as username lists is already implemented
> better in the new code.  The question is whether using those secondary
> passwords is widespread enough that I need to get that into the code
> too.  It was pretty confusing for users, so I am hesitant to re-add it,
> but I hate for Marc to lose functionality he had in the past.

You seem to have done a nice job with the + and @ for 'maps' ... how about
third on that states that the map file has a username:password pair in it?

I do like how the pg_hba.conf has changed, just don't like the lose of
functionality :(

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T05:04:35Z

On Tue, 30 Jul 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> Uh, we've *never* supported "two bruce users" ...
> >
> > > He was being tricky by having different passwords for the same user on
> > > each database, so one user couldn't get into the other database, even
> > > though it was the same name.
> >
> > But the system didn't realize they were two different users.  (Try
> > dropping just one of them.)  And what if they happened to choose the
> > same password?  I think this is a fragile kluge not a supported feature.
> >
> > > The question is whether using those secondary
> > > passwords is widespread enough that I need to get that into the code
> > > too.  It was pretty confusing for users, so I am hesitant to re-add it,
> > > but I hate for Marc to lose functionality he had in the past.
> >
> > I'd like to think of a better answer, not put back that same kluge.
> > Ideas anyone?
>
> Agreed.  A clear kludge.  I just feel guilty because I removed it.

don't feel guilty ... it *wasn't* the nicest implementation of a feature,
but it was definitely useful ...

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T05:07:25Z

Marc G. Fournier wrote:
> You seem to have done a nice job with the + and @ for 'maps' ... how about
> third on that states that the map file has a username:password pair in it?
> 
> I do like how the pg_hba.conf has changed, just don't like the lose of
> functionality :(

OK, but the only logic for using it is your duplicate users.  There
would be no other reason someone would use such a feature, right?

I assume it would be MD5?

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

Re: Password sub-process ...

Marc G. Fournier <scrappy@hub.org> — 2002-07-30T05:40:20Z

On Tue, 30 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > You seem to have done a nice job with the + and @ for 'maps' ... how about
> > third on that states that the map file has a username:password pair in it?
> >
> > I do like how the pg_hba.conf has changed, just don't like the lose of
> > functionality :(
>
> OK, but the only logic for using it is your duplicate users.  There
> would be no other reason someone would use such a feature, right?

Hrmmm ... let's make this simpler ... there was a thread going around
asking why MySQL vs PgSQL, and one of the answers had to do with ISPs ...
from a 'shared host' point of view, what is done for v7.3 makes it very
difficult for an ISP to 'save resources' by running one instance, without
them starting to look like hotmail:

bruce
bruce001
bruce002
bruce003

I'm lucky, I don't do virtual hosting, so I can use host/ip based
restrictions on our databases, with a select few requiring a password ...
but most out there do virtual hosting, which means that all the domains
connecting to the database look like they are coming from the same IP ...

so, I can easily do something like:

host database bruce IP1
host database bruce IP2

and know that client on IP1 can't look at client on IP2s database, even
with the same user ... but in a VH environment, you have:

host database bruce IP1
host database bruce IP1

in the old system, I could make both password based, so that altho both
bruce's were looking to come from the same IP, only the one with the right
password could connect, so Client on IP1's bruce wouldn't be able to look
in Client on IP2's database, since he wouldn't have the required password
to connect ...

> I assume it would be MD5?

I've been using DES, but MD5 would work too ...

Re: Password sub-process ...

Hannu Krosing <hannu@tm.ee> — 2002-07-30T07:49:52Z

On Tue, 2002-07-30 at 10:40, Marc G. Fournier wrote:
> On Tue, 30 Jul 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > > You seem to have done a nice job with the + and @ for 'maps' ... how about
> > > third on that states that the map file has a username:password pair in it?
> > >
> > > I do like how the pg_hba.conf has changed, just don't like the lose of
> > > functionality :(
> >
> > OK, but the only logic for using it is your duplicate users.  There
> > would be no other reason someone would use such a feature, right?
> 
> Hrmmm ... let's make this simpler ... there was a thread going around
> asking why MySQL vs PgSQL, and one of the answers had to do with ISPs ...
> from a 'shared host' point of view, what is done for v7.3 makes it very
> difficult for an ISP to 'save resources' by running one instance, without
> them starting to look like hotmail:
> 
> bruce
> bruce001
> bruce002
> bruce003
> 
> I'm lucky, I don't do virtual hosting, so I can use host/ip based
> restrictions on our databases, with a select few requiring a password ...
> but most out there do virtual hosting, which means that all the domains
> connecting to the database look like they are coming from the same IP ...
> 
> so, I can easily do something like:
> 
> host database bruce IP1
> host database bruce IP2
> 
> and know that client on IP1 can't look at client on IP2s database, even
> with the same user ... but in a VH environment, you have:
> 
> host database bruce IP1
> host database bruce IP1

Why can't you just name the user user@database ?

It should not be /too/ hard to explain to user bruce that his username
at database accounts is bruce@accounts ?

> in the old system, I could make both password based, so that altho both
> bruce's were looking to come from the same IP, only the one with the right
> password could connect, so Client on IP1's bruce wouldn't be able to look
> in Client on IP2's database, since he wouldn't have the required password
> to connect ...

But still, what happens if both bruces want to set their password to
"brucessecretpassword" ?

----------------
Hannu

Re: Password sub-process ...

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T15:55:15Z

Marc G. Fournier wrote:
> so, I can easily do something like:
> 
> host database bruce IP1
> host database bruce IP2
> 
> and know that client on IP1 can't look at client on IP2s database, even
> with the same user ... but in a VH environment, you have:
> 
> host database bruce IP1
> host database bruce IP1
> 
> in the old system, I could make both password based, so that altho both
> bruce's were looking to come from the same IP, only the one with the right
> password could connect, so Client on IP1's bruce wouldn't be able to look
> in Client on IP2's database, since he wouldn't have the required password
> to connect ...
> 
> > I assume it would be MD5?
> 
> I've been using DES, but MD5 would work too ...

OK, I have one idea.  Right now the file format for usernames can be:

	user, user, "user"
or
	user user "user"
or
	user
	user
	"user"

so we don't really have columns in the file.  What we could do is to
allow the username to be specified as "user:pass" and the "pass" could
be in plaintext or md5.  You could actually specify the "pass" in
pg_hba.conf or in a secondary file.  The code currently makes no
distinction between them.

This does make the code a little more complex, but it is documenting
this that be cause the most confusion.

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

Re: Password sub-process ...

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T16:24:43Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, I have one idea.  Right now the file format for usernames can be:

But this is just reimplementing the original functionality, which was
quite broken IMHO.  The setup Marc is describing doesn't really have
users per-database, it's only faking it.  And what if he wants to use
some non-password-based auth method, like IDENT?

I am wondering if we could have a configure-time or install-time
option to make pg_shadow (and pg_group I guess) be database-local
instead of installation-wide.  I am not sure about the implications
of this --- in particular, is the notion of a database owner still
meaningful?  How could the postmaster cope with it (I'd guess we'd
need multiple flat files, one per DB, for the postmaster to read)?

If we're going to do work to support this concept, then let's really
support it.

			regards, tom lane

Re: Password sub-process ...

Sander Steffann <sander@steffann.nl> — 2002-07-30T18:18:49Z

Hi,

> I am wondering if we could have a configure-time or install-time
> option to make pg_shadow (and pg_group I guess) be database-local
> instead of installation-wide.  I am not sure about the implications
> of this --- in particular, is the notion of a database owner still
> meaningful?  How could the postmaster cope with it (I'd guess we'd
> need multiple flat files, one per DB, for the postmaster to read)?

I realy like the idea, but how would you handle the postgres (super)user in
this scenario? One global postgres user, or a separate one for each db? In
the last case, the DB owner would be the DB-specific postgres user. A global
superuser would still be needed for backups and other maintainance tasks...

Sander

many idle processes

John Liu <johnl@synthesys.com> — 2002-07-31T16:12:30Z

I tried to understand what causes
too many pgsql idle processes. Can
postmaster automatically aged and
cleaning up those unused idle process?

Is there a catalog to track those
psql processes - what their functions, who
issues, etc.?

thanks.

johnl

Re: many idle processes

Doug McNaught <doug@wireboard.com> — 2002-07-31T16:24:46Z

"John Liu" <johnl@synthesys.com> writes:

> I tried to understand what causes
> too many pgsql idle processes. Can
> postmaster automatically aged and
> cleaning up those unused idle process?

Those processes are attached to open client connections.  If you don't
like them, change your client to close connections when it's not using
them, but your app will be slower since creating a new connection
(and backend process) takes some time.

> Is there a catalog to track those
> psql processes - what their functions, who
> issues, etc.?

There is one backend process per open client connection, plus the
postmaster, which handles creating new connections.

-Doug

Re: many idle processes

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-01T01:41:15Z

> Is there a catalog to track those
> psql processes - what their functions, who
> issues, etc.?
> 
> thanks.
> 
> johnl  

If you have it enabled in your postgresql.conf, just go:

select * from pg_stat_activity;

Chris