Обсуждение: Password sub-process ...
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 ...
"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 #
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..."
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.
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).
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
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" ...
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 ...
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?
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.
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.
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.
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, Pennsylvania19026
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, Pennsylvania19026
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 ...
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, Pennsylvania19026
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* required2. adds the username/password to a "password" file specific to that
instanceof 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>'?
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, Pennsylvania19026
"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
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, Pennsylvania19026
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
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, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
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' ...
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 :(
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 ...
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, Pennsylvania19026
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 ...
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
On Tue, 30 Jul 2002, Andrew Sullivan wrote: > On Tue, Jul 30, 2002 at 12:43:52AM -0300, Marc G. Fournier wrote: > > > since as soon as there are two 'bruce' users, only one can have a password > > I guess I don't understand why that's a problem. I mean, if you're > authenticating users, how can you have two with the same name? It's > just like UNIX usernames, to my mind: they have to be unique on the > system, no? I think that is the problem with everyone's "thinking" ... they are only dealing with 'small servers', where it only has a couple of databases ... I'm currently running a server with >100 domains on it, each one with *at least* one database ... each one of those domains, in reality, *could* have a user 'bruce' ... note that I run virtual machines ... so each one fo those 'domains' has their own password files, so I can't say to 'client A' that 'client B' already has user 'bruce', so you can't use it, even though its unique to your system ... And, I don't want to run 100 pgsql instances on the server, since either I'd have to have one helluva lot of RAM dedicated to PgSQL, or have little tiny shared memory segments available to each ... actually, let's add onto that ... let's say every one of those 100 pgsql databases is accessed by PHPPgAdmin, through the web ... so, with a 'common password' amongst all the various 'bruce's, I could, in theory, go to any other domain's PHPPgAdmin, login and see their databases (major security problem) ... the way it was before, I could setup a password file that contained a different password for each of those domains, so that bruce on domain 1 couldn't access domain 2's databases ... or vice versa ... I've CC'd this back into the list, mainly because I think others might be 'thinking within the box' on this :(
> ... amongst all the various 'bruce's...
Hmm. The "Monty Python scenario"? :)
- Thomas
On Tue, Jul 30, 2002 at 11:55:55AM -0300, Marc G. Fournier wrote: > I think that is the problem with everyone's "thinking" ... they are only > dealing with 'small servers', where it only has a couple of databases ... > I'm currently running a server with >100 domains on it, each one with *at > least* one database ... each one of those domains, in reality, *could* > have a user 'bruce' ... First off, I think the implementation of this functionality present in 7.2 was a big hack, and I'd rather not see it resurrected. However, it would be useful to be able to do something like this -- how about something like the following: - the auth system contains a list of 'auth domains' -- an identifier similar to a schema name - the combination of (domain, username) must be unique -- i.e. a username is unique within a domain - each database exists within a single domain; a domain can have 0, 1, or many databases - by default, the system ships with a single auth domain; when a user is created, the admin can specify the domainin which the user exists, otherwise it defaults to the default domain Anyway, just thinking out loud -- that may or may not make any sense... Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Marc G. Fournier wrote: > I think that is the problem with everyone's "thinking" ... they are only > dealing with 'small servers', where it only has a couple of databases ... > I'm currently running a server with >100 domains on it, each one with *at > least* one database ... each one of those domains, in reality, *could* > have a user 'bruce' ... > > note that I run virtual machines ... so each one fo those 'domains' has > their own password files, so I can't say to 'client A' that 'client B' > already has user 'bruce', so you can't use it, even though its unique to > your system ... > > And, I don't want to run 100 pgsql instances on the server, since either > I'd have to have one helluva lot of RAM dedicated to PgSQL, or have little > tiny shared memory segments available to each ... > > actually, let's add onto that ... let's say every one of those 100 pgsql > databases is accessed by PHPPgAdmin, through the web ... so, with a > 'common password' amongst all the various 'bruce's, I could, in theory, go > to any other domain's PHPPgAdmin, login and see their databases (major > security problem) ... the way it was before, I could setup a password file > that contained a different password for each of those domains, so that > bruce on domain 1 couldn't access domain 2's databases ... or vice versa > ... > > I've CC'd this back into the list, mainly because I think others might be > 'thinking within the box' on this :( How hard would it be to do something like this: 1. Add a column called usedatid to pg_shadow. This would contain an array of database oids to which a user is bound. Use the value 0 to mean "all databases". 2. Remove unique index on usename (we always know which database a user is logging in to, don't we?). Change unique index on usesysid to be over both usesysid and usedatid. 3. Add sufficient grammer to support specifying a specific database when creating a user. Default to all databases for BC. Add ability to bind to additional databases in ALTER USER. Just trying to think outside the box ;-) Joe
On Tue, 2002-07-30 at 16:55, Marc G. Fournier wrote: > On Tue, 30 Jul 2002, Andrew Sullivan wrote: > > > On Tue, Jul 30, 2002 at 12:43:52AM -0300, Marc G. Fournier wrote: > > > > > since as soon as there are two 'bruce' users, only one can have a password > > > > I guess I don't understand why that's a problem. I mean, if you're > > authenticating users, how can you have two with the same name? It's > > just like UNIX usernames, to my mind: they have to be unique on the > > system, no? > > I think that is the problem with everyone's "thinking" ... they are only > dealing with 'small servers', where it only has a couple of databases ... > I'm currently running a server with >100 domains on it, each one with *at > least* one database ... each one of those domains, in reality, *could* > have a user 'bruce' ... > > note that I run virtual machines ... so each one fo those 'domains' has > their own password files, so I can't say to 'client A' that 'client B' > already has user 'bruce', so you can't use it, even though its unique to > your system ... But if they are _really_ virtual machines then you can probably distinguish them by IP as was discussed earlier. Or you can declare each virtual machine to be its own "domain" and name db users user@domain (or //domain/user if you are inclined that way ;). both of these names are accepted by postgres as valid usernames. I guess you must doing something like that with their e-mail addresses already ;) > And, I don't want to run 100 pgsql instances on the server, since either > I'd have to have one helluva lot of RAM dedicated to PgSQL, or have little > tiny shared memory segments available to each ... > > actually, let's add onto that ... let's say every one of those 100 pgsql > databases is accessed by PHPPgAdmin, through the web ... so, with a > 'common password' amongst all the various 'bruce's, I could, in theory, go > to any other domain's PHPPgAdmin, login and see their databases (major > security problem) ... Bugzilla resolves the problem of "many bruces" by having e-mail address as a globally unique username. > the way it was before, I could setup a password file > that contained a different password for each of those domains, so that > bruce on domain 1 couldn't access domain 2's databases ... or vice versa > ... > > I've CC'd this back into the list, mainly because I think others might be > 'thinking within the box' on this :( Otoh, thinking that distinguishing users by password is a good idea can also be considered 'thinking within the box' by some ;) -------------------- Hannu
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" oruser user "user" oruseruser"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, Pennsylvania19026
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
Neil Conway writes: > However, it would be useful to be able to do something like this -- how > about something like the following: > > - the auth system contains a list of 'auth domains' -- an identifier > similar to a schema name > > - the combination of (domain, username) must be unique -- i.e. a > username is unique within a domain > > - each database exists within a single domain; a domain can have 0, > 1, or many databases > > - by default, the system ships with a single auth domain; when a > user is created, the admin can specify the domain in which the > user exists, otherwise it defaults to the default domain > > Anyway, just thinking out loud -- that may or may not make any sense... Actually, I was thinking just about the same thing. Essentially you're proposing virtual hosting, where "domain" is the same thing as a virtual host URI. Somewhere you'd need a configuration file that maps request parameters (host and port, basically) to a domain (not sure if I'd use that name, though). I like it. -- Peter Eisentraut peter_e@gmx.net
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
"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
> 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
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