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, Pennsylvania 19026Marc 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 19026On 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, Pennsylvania 19026On 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>'?
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"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 passwordUh, 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 19026Bruce 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 passwordUh, 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 19026Tom 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 19026Tom 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 19026On 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 passwordUh, 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 passwordUh, 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, Pennsylvania 19026On 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
bruce003I'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 IP2and 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 :(
Import Notes
Reply to msg id not found: 20020730104700.F5295@mail.libertyrms.com | Resolved by subject fallback
... amongst all the various 'bruce's...
Hmm. The "Monty Python scenario"? :)
- Thomas
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, 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 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...
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Marc G. Fournier wrote:
so, I can easily do something like:
host database bruce IP1
host database bruce IP2and 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 IP1in 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 19026Bruce 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
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
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
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 domainAnyway, 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
Import Notes
Reply to msg id not found: JohnLiusmessageofWed31Jul2002111230-0500
