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 19026
