Thread

Re: Open 7.3 items

Ron Snyder <snyder@roguewave.com> — 2002-07-31T23:06:42Z

> 
> Yes, is that your pg_hba.conf line?  'password' is insecure over
> networks you don't trust.

Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
network (so far).

-ron

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T01:05:13Z

Ron Snyder wrote:
> > 
> > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > networks you don't trust.
> 
> Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> network (so far).

That is another major limitation to secondary password files.  In fact,
md5 will not even work because we assume the username is used as the
salt for the md5 encryption.  We don't store the salt as part of the
encrypted password like crypt does.  

This was another reason secondary password files were discouraged.

Let me look at adding the colon password capability and see what it
looks like.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-01T01:50:34Z

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Ron Snyder wrote:
> > >
> > > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > > networks you don't trust.
> >
> > Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> > network (so far).
>
> That is another major limitation to secondary password files.  In fact,
> md5 will not even work because we assume the username is used as the
> salt for the md5 encryption.  We don't store the salt as part of the
> encrypted password like crypt does.
>
> This was another reason secondary password files were discouraged.

discouraged??  where? :)

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T02:37:31Z

Marc G. Fournier wrote:
> On Wed, 31 Jul 2002, Bruce Momjian wrote:
> 
> > Ron Snyder wrote:
> > > >
> > > > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > > > networks you don't trust.
> > >
> > > Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> > > network (so far).
> >
> > That is another major limitation to secondary password files.  In fact,
> > md5 will not even work because we assume the username is used as the
> > salt for the md5 encryption.  We don't store the salt as part of the
> > encrypted password like crypt does.
> >
> > This was another reason secondary password files were discouraged.
> 
> discouraged??  where? :)

Well. I meant that they had very limited usefulness. You had to trust
your network.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-01T02:44:33Z

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> >
> > > Ron Snyder wrote:
> > > > >
> > > > > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > > > > networks you don't trust.
> > > >
> > > > Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> > > > network (so far).
> > >
> > > That is another major limitation to secondary password files.  In fact,
> > > md5 will not even work because we assume the username is used as the
> > > salt for the md5 encryption.  We don't store the salt as part of the
> > > encrypted password like crypt does.
> > >
> > > This was another reason secondary password files were discouraged.
> >
> > discouraged??  where? :)
>
> Well. I meant that they had very limited usefulness. You had to trust
> your network.

that is the case for alot of software, and alot of networks nowadays are
moving towards encrypted at the switch level, so the local network itself
is considered to be 'secure' ...

But, personally, you sooooooo sold me on that GUC thing that if we could
implement that in time for v7.3, I think alot of ppl would find that
*quite* valuable ...

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T02:48:40Z

Marc G. Fournier wrote:
> On Wed, 31 Jul 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> > >
> > > > Ron Snyder wrote:
> > > > > >
> > > > > > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > > > > > networks you don't trust.
> > > > >
> > > > > Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> > > > > network (so far).
> > > >
> > > > That is another major limitation to secondary password files.  In fact,
> > > > md5 will not even work because we assume the username is used as the
> > > > salt for the md5 encryption.  We don't store the salt as part of the
> > > > encrypted password like crypt does.
> > > >
> > > > This was another reason secondary password files were discouraged.
> > >
> > > discouraged??  where? :)
> >
> > Well. I meant that they had very limited usefulness. You had to trust
> > your network.
> 
> that is the case for alot of software, and alot of networks nowadays are
> moving towards encrypted at the switch level, so the local network itself
> is considered to be 'secure' ...
> 
> But, personally, you sooooooo sold me on that GUC thing that if we could
> implement that in time for v7.3, I think alot of ppl would find that
> *quite* valuable ...
> 

I am working on it now.  I decided against doing any kind of database
prepending at the user level.  You create the user as 'dbname.username'.
That is clearer, rather than prepending based on the db you are
connected to.  The only code change is in the postmaster authentication
lookup and ownership setting from the backend connection.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

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

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> >
> > > Marc G. Fournier wrote:
> > > > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> > > >
> > > > > Ron Snyder wrote:
> > > > > > >
> > > > > > > Yes, is that your pg_hba.conf line?  'password' is insecure over
> > > > > > > networks you don't trust.
> > > > > >
> > > > > > Yes, we're using 'password password' in our pg_hba.conf file.  I trust my
> > > > > > network (so far).
> > > > >
> > > > > That is another major limitation to secondary password files.  In fact,
> > > > > md5 will not even work because we assume the username is used as the
> > > > > salt for the md5 encryption.  We don't store the salt as part of the
> > > > > encrypted password like crypt does.
> > > > >
> > > > > This was another reason secondary password files were discouraged.
> > > >
> > > > discouraged??  where? :)
> > >
> > > Well. I meant that they had very limited usefulness. You had to trust
> > > your network.
> >
> > that is the case for alot of software, and alot of networks nowadays are
> > moving towards encrypted at the switch level, so the local network itself
> > is considered to be 'secure' ...
> >
> > But, personally, you sooooooo sold me on that GUC thing that if we could
> > implement that in time for v7.3, I think alot of ppl would find that
> > *quite* valuable ...
> >
>
> I am working on it now.  I decided against doing any kind of database
> prepending at the user level.  You create the user as 'dbname.username'.
> That is clearer, rather than prepending based on the db you are
> connected to.  The only code change is in the postmaster authentication
> lookup and ownership setting from the backend connection.

Okay, just a couple of questions ... if there any way of provide
'superuse' access a user of the database for creating new users?  Say one
creates a dbname.pgsql account, could it be given 'create user' privileges
for other users with a prefix of dbname.*?

and, what happens if one doesn't specify dbname.*?  does that user become
'global', or have access to nothing?

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T03:17:45Z

Marc G. Fournier wrote:
> > I am working on it now.  I decided against doing any kind of database
> > prepending at the user level.  You create the user as 'dbname.username'.
> > That is clearer, rather than prepending based on the db you are
> > connected to.  The only code change is in the postmaster authentication
> > lookup and ownership setting from the backend connection.
> 
> Okay, just a couple of questions ... if there any way of provide
> 'superuse' access a user of the database for creating new users?  Say one
> creates a dbname.pgsql account, could it be given 'create user' privileges
> for other users with a prefix of dbname.*?

Uh, that will be tough.

Super-user account will not be qualified by dbname for simplicity.  

> and, what happens if one doesn't specify dbname.*?  does that user become
> 'global', or have access to nothing?

Access to nothing.  I could actually try to quality by dbname.username,
then fall back to just username, but that seems insecure.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-01T03:27:43Z

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > > I am working on it now.  I decided against doing any kind of database
> > > prepending at the user level.  You create the user as 'dbname.username'.
> > > That is clearer, rather than prepending based on the db you are
> > > connected to.  The only code change is in the postmaster authentication
> > > lookup and ownership setting from the backend connection.
> >
> > Okay, just a couple of questions ... if there any way of provide
> > 'superuse' access a user of the database for creating new users?  Say one
> > creates a dbname.pgsql account, could it be given 'create user' privileges
> > for other users with a prefix of dbname.*?
>
> Uh, that will be tough.
>
> Super-user account will not be qualified by dbname for simplicity.
>
> > and, what happens if one doesn't specify dbname.*?  does that user become
> > 'global', or have access to nothing?
>
> Access to nothing.  I could actually try to quality by dbname.username,
> then fall back to just username, but that seems insecure.

No, that's cool ... just questions I thought of ...

Okay ... hmmm ... just making sure that I understand ... I setup a server,
when does this dbname.* come into play?  Only if I enable password/md5 in
pg_hba.conf for a specific database?  all others would still use a plain
'username' still works?  or are you getting rid of the 'global usernames'
altogether (which is cool too, just want to clarify) ...

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T03:31:14Z

Marc G. Fournier wrote:
> > Access to nothing.  I could actually try to quality by dbname.username,
> > then fall back to just username, but that seems insecure.
> 
> No, that's cool ... just questions I thought of ...

OK.

> Okay ... hmmm ... just making sure that I understand ... I setup a server,
> when does this dbname.* come into play?  Only if I enable password/md5 in
> pg_hba.conf for a specific database?  all others would still use a plain
> 'username' still works?  or are you getting rid of the 'global usernames'
> altogether (which is cool too, just want to clarify) ...

There will be a GUC param db_user_namespace which will turn it on/off
for all access to the cluster _except_ for the super-user.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

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

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > > Access to nothing.  I could actually try to quality by dbname.username,
> > > then fall back to just username, but that seems insecure.
> >
> > No, that's cool ... just questions I thought of ...
>
> OK.
>
> > Okay ... hmmm ... just making sure that I understand ... I setup a server,
> > when does this dbname.* come into play?  Only if I enable password/md5 in
> > pg_hba.conf for a specific database?  all others would still use a plain
> > 'username' still works?  or are you getting rid of the 'global usernames'
> > altogether (which is cool too, just want to clarify) ...
>
> There will be a GUC param db_user_namespace which will turn it on/off
> for all access to the cluster _except_ for the super-user.

Okay ... cluster == database server, or a subset of databases within the
server?  I know what I think of as a cluster, and somehow I suspect this
has to do with the new schema stuff, which means I *really* have to find
time to do some catch-up reading ;)  need more hours in day, days in week
;(

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T03:59:51Z

Marc G. Fournier wrote:
> On Wed, 31 Jul 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > > > Access to nothing.  I could actually try to quality by dbname.username,
> > > > then fall back to just username, but that seems insecure.
> > >
> > > No, that's cool ... just questions I thought of ...
> >
> > OK.
> >
> > > Okay ... hmmm ... just making sure that I understand ... I setup a server,
> > > when does this dbname.* come into play?  Only if I enable password/md5 in
> > > pg_hba.conf for a specific database?  all others would still use a plain
> > > 'username' still works?  or are you getting rid of the 'global usernames'
> > > altogether (which is cool too, just want to clarify) ...
> >
> > There will be a GUC param db_user_namespace which will turn it on/off
> > for all access to the cluster _except_ for the super-user.
> 
> Okay ... cluster == database server, or a subset of databases within the
> server?  I know what I think of as a cluster, and somehow I suspect this
> has to do with the new schema stuff, which means I *really* have to find
> time to do some catch-up reading ;)  need more hours in day, days in week

Cluster is db server in this case.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-01T04:08:10Z

On Wed, 31 Jul 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> >
> > > Marc G. Fournier wrote:
> > > > > Access to nothing.  I could actually try to quality by dbname.username,
> > > > > then fall back to just username, but that seems insecure.
> > > >
> > > > No, that's cool ... just questions I thought of ...
> > >
> > > OK.
> > >
> > > > Okay ... hmmm ... just making sure that I understand ... I setup a server,
> > > > when does this dbname.* come into play?  Only if I enable password/md5 in
> > > > pg_hba.conf for a specific database?  all others would still use a plain
> > > > 'username' still works?  or are you getting rid of the 'global usernames'
> > > > altogether (which is cool too, just want to clarify) ...
> > >
> > > There will be a GUC param db_user_namespace which will turn it on/off
> > > for all access to the cluster _except_ for the super-user.
> >
> > Okay ... cluster == database server, or a subset of databases within the
> > server?  I know what I think of as a cluster, and somehow I suspect this
> > has to do with the new schema stuff, which means I *really* have to find
> > time to do some catch-up reading ;)  need more hours in day, days in week
>
> Cluster is db server in this case.

'K, cool, thanks :)

Okay, final request .. how hard would it be to pre-pend the current
database name if GUC value is on?  ie. if I'm in db1 and run CREATE USER,
it will add db1. to the username if I hadn't already?   Sounds to me it
would be simple to do, and it would "fix" the point I made about being
able to have a db "owner" account with create user privileges (ie. if I'm
in db1 and run CREATE USER db2.bruce, it should reject that unless I've
got create database prileges *and* create user) ...

Other then that, most elegant solution, IMHO :)

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T04:09:46Z

Marc G. Fournier wrote:
> Okay, final request .. how hard would it be to pre-pend the current
> database name if GUC value is on?  ie. if I'm in db1 and run CREATE USER,
> it will add db1. to the username if I hadn't already?   Sounds to me it
> would be simple to do, and it would "fix" the point I made about being
> able to have a db "owner" account with create user privileges (ie. if I'm
> in db1 and run CREATE USER db2.bruce, it should reject that unless I've
> got create database prileges *and* create user) ...

OK, let me get the easy part working first.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T05:25:50Z

OK, I have attached a patch for testing.  Sample output is:

	$ sql -U guest test
	psql: FATAL:  user "test.guest" does not exist
	$ createuser test.guest
	Shall the new user be allowed to create databases? (y/n) n
	Shall the new user be allowed to create more new users? (y/n) n
	CREATE USER
	#$ sql -U guest test
	Welcome to psql, the PostgreSQL interactive terminal.
	
	Type:  \copyright for distribution terms
	       \h for help with SQL commands
	       \? for help on internal slash commands
	       \g or terminate with semicolon to execute query
	       \q to quit
	
	test=> 

The patch is quite small.  All it does is prepend the database name to
the user name supplied with the connection request when
db_user_namespace is true.

This is not ready for application.  I can find no way from the
postmaster to determine if the user is the super-user and hence bypass
the database prepending.  I was going to do that _only_ for the username
who created the installation for initdb.  Maybe I have to dump that name
out to a file and read it in from the postmaster.  Other ideas?

It also needs documentation.

I am unsure about auto-prepending the dbname for CREATE USER and other
cases.  That could get confusing, especially because createuser accesses
template1, and we would have to handle all other username mentions, like
in GRANT.  We may be better just leaving it along and telling admins
they have to quality the username in those cases.

---------------------------------------------------------------------------

Marc G. Fournier wrote:
> On Wed, 31 Jul 2002, Bruce Momjian wrote:
> 
> > Marc G. Fournier wrote:
> > > On Wed, 31 Jul 2002, Bruce Momjian wrote:
> > >
> > > > Marc G. Fournier wrote:
> > > > > > Access to nothing.  I could actually try to quality by dbname.username,
> > > > > > then fall back to just username, but that seems insecure.
> > > > >
> > > > > No, that's cool ... just questions I thought of ...
> > > >
> > > > OK.
> > > >
> > > > > Okay ... hmmm ... just making sure that I understand ... I setup a server,
> > > > > when does this dbname.* come into play?  Only if I enable password/md5 in
> > > > > pg_hba.conf for a specific database?  all others would still use a plain
> > > > > 'username' still works?  or are you getting rid of the 'global usernames'
> > > > > altogether (which is cool too, just want to clarify) ...
> > > >
> > > > There will be a GUC param db_user_namespace which will turn it on/off
> > > > for all access to the cluster _except_ for the super-user.
> > >
> > > Okay ... cluster == database server, or a subset of databases within the
> > > server?  I know what I think of as a cluster, and somehow I suspect this
> > > has to do with the new schema stuff, which means I *really* have to find
> > > time to do some catch-up reading ;)  need more hours in day, days in week
> >
> > Cluster is db server in this case.
> 
> 'K, cool, thanks :)
> 
> Okay, final request .. how hard would it be to pre-pend the current
> database name if GUC value is on?  ie. if I'm in db1 and run CREATE USER,
> it will add db1. to the username if I hadn't already?   Sounds to me it
> would be simple to do, and it would "fix" the point I made about being
> able to have a db "owner" account with create user privileges (ie. if I'm
> in db1 and run CREATE USER db2.bruce, it should reject that unless I've
> got create database prileges *and* create user) ...
> 
> Other then that, most elegant solution, IMHO :)
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-01T20:05:53Z

Bruce Momjian writes:

> OK, I have attached a patch for testing.  Sample output is:
>
> 	$ sql -U guest test
> 	psql: FATAL:  user "test.guest" does not exist
> 	$ createuser test.guest

I will object to any scheme that makes any characters in the user name
magic.  Two reasons:  First, do it right, make a separate column.
Second, several tools use URI syntax to specify data sources.  This will
break any feature that relies on being able to put special characters into
the user name.

The right solution to having database-local user names is putting extra
information into pg_shadow regarding which database this user applies to.
It could be an array or some separate "authentication domain" thing.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T21:11:24Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > OK, I have attached a patch for testing.  Sample output is:
> >
> > 	$ sql -U guest test
> > 	psql: FATAL:  user "test.guest" does not exist
> > 	$ createuser test.guest
> 
> I will object to any scheme that makes any characters in the user name
> magic.  Two reasons:  First, do it right, make a separate column.
> Second, several tools use URI syntax to specify data sources.  This will
> break any feature that relies on being able to put special characters into
> the user name.
> 
> The right solution to having database-local user names is putting extra
> information into pg_shadow regarding which database this user applies to.
> It could be an array or some separate "authentication domain" thing.

OK, if you object, you can say goodbye to this feature for 7.3.  I can
supply the patch to Marc and anyone else who wants it but I am not
inclined nor convinced we need that level of work for this feature.

So we end up with nothing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-01T21:13:52Z

On Thu, 1 Aug 2002, Bruce Momjian wrote:

> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> >
> > > OK, I have attached a patch for testing.  Sample output is:
> > >
> > > 	$ sql -U guest test
> > > 	psql: FATAL:  user "test.guest" does not exist
> > > 	$ createuser test.guest
> >
> > I will object to any scheme that makes any characters in the user name
> > magic.  Two reasons:  First, do it right, make a separate column.
> > Second, several tools use URI syntax to specify data sources.  This will
> > break any feature that relies on being able to put special characters into
> > the user name.
> >
> > The right solution to having database-local user names is putting extra
> > information into pg_shadow regarding which database this user applies to.
> > It could be an array or some separate "authentication domain" thing.
>
> OK, if you object, you can say goodbye to this feature for 7.3.  I can
> supply the patch to Marc and anyone else who wants it but I am not
> inclined nor convinced we need that level of work for this feature.
>
> So we end up with nothing.

Stupid qustion .. but why can't you just add a 'domain' column to
pg_passwd/pg_shadow so that its stored as two fields instead of one?
Which I believe is what Pter is/was suggesting ...

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T21:20:48Z

Marc G. Fournier wrote:
> > > I will object to any scheme that makes any characters in the user name
> > > magic.  Two reasons:  First, do it right, make a separate column.
> > > Second, several tools use URI syntax to specify data sources.  This will
> > > break any feature that relies on being able to put special characters into
> > > the user name.
> > >
> > > The right solution to having database-local user names is putting extra
> > > information into pg_shadow regarding which database this user applies to.
> > > It could be an array or some separate "authentication domain" thing.
> >
> > OK, if you object, you can say goodbye to this feature for 7.3.  I can
> > supply the patch to Marc and anyone else who wants it but I am not
> > inclined nor convinced we need that level of work for this feature.
> >
> > So we end up with nothing.
> 
> Stupid qustion .. but why can't you just add a 'domain' column to
> pg_passwd/pg_shadow so that its stored as two fields instead of one?
> Which I believe is what Pter is/was suggesting ...

Right now, pg_pwd only dumps users with passwords, and as I remember, it
is only accessed when the protocol needs to lookup a password.  It
wasn't designed for anything more advanced.  If you want separate
columns, you have to dump out everyone, and modify CREATE USER,
createuser, ALTER USER, ... to handle those new domain names, and you
have to make this API visible to everyone even if they are not using
domains.  That's where things really get ugly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-02T10:56:12Z

On Thu, 2002-08-01 at 23:20, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > > > I will object to any scheme that makes any characters in the user name
> > > > magic.  Two reasons:  First, do it right, make a separate column.
> > > > Second, several tools use URI syntax to specify data sources.  This will
> > > > break any feature that relies on being able to put special characters into
> > > > the user name.

This should be settable using a GUC variable (in postgresql.conf as it
makes no sense once you are connected).

> > > > The right solution to having database-local user names is putting extra
> > > > information into pg_shadow regarding which database this user applies to.
> > > > It could be an array or some separate "authentication domain" thing.
> > >
> > > OK, if you object, you can say goodbye to this feature for 7.3.  I can
> > > supply the patch to Marc and anyone else who wants it but I am not
> > > inclined nor convinced we need that level of work for this feature.
> > >
> > > So we end up with nothing.
> > 
> > Stupid qustion .. but why can't you just add a 'domain' column to
> > pg_passwd/pg_shadow so that its stored as two fields instead of one?
> > Which I believe is what Pter is/was suggesting ...
> 
> Right now, pg_pwd only dumps users with passwords, and as I remember, it
> is only accessed when the protocol needs to lookup a password.  It
> wasn't designed for anything more advanced.  If you want separate
> columns, you have to dump out everyone, and modify CREATE USER,
> createuser, ALTER USER, ... to handle those new domain names, and you
> have to make this API visible to everyone even if they are not using
> domains.  That's where things really get ugly.

Actually _not_ modifying the commands (and thus leaving the
pg_shadow.usedomain column empty) will give us exactly the old
behaviour. For advanced uses it should be an acceptable interim solution
to have the superuser update the pg_shadow manually.

But if noone has time to work on it more than just mangling usernames at
connect time, that should also be ok for 7.3. We just have to document
it and warn of a new change to real domain  users in 7.4 (or later).

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

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-06T21:17:50Z

Bruce Momjian writes:

> OK, if you object, you can say goodbye to this feature for 7.3.  I can
> supply the patch to Marc and anyone else who wants it but I am not
> inclined nor convinced we need that level of work for this feature.

The right solution, IMO, is to resurrect the feature we had and think
about a fully-featured solution for the next release.  Or try to sell the
proposed solutions as fully-featured . . .

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-07T01:09:14Z

It had such limited usefulness ('password' only, only crypted-hashed
passwords in the file) that it doesn't make much sense to resurect it.

To directly address your point, I don't think this new feature will be
used enough to add the capability to the user admin commands.

I know you object, so I am going to ask for a vote.

OK, here is the request for vote.  Do we want:

	1)  the old secondary passwords re-added
	2)  the new prefixing of the database name to the username when enabled
	3)  do nothing


---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > OK, if you object, you can say goodbye to this feature for 7.3.  I can
> > supply the patch to Marc and anyone else who wants it but I am not
> > inclined nor convinced we need that level of work for this feature.
> 
> The right solution, IMO, is to resurrect the feature we had and think
> about a fully-featured solution for the next release.  Or try to sell the
> proposed solutions as fully-featured . . .
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-07T01:21:55Z

> OK, here is the request for vote.  Do we want:

> 	2)  the new prefixing of the database name to the username when enabled

I vote 2.

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-07T01:24:16Z

On Tue, 6 Aug 2002, Bruce Momjian wrote:

>
> It had such limited usefulness ('password' only, only crypted-hashed
> passwords in the file) that it doesn't make much sense to resurect it.

It had limited usefulness to you ... but how many sites out there are
going to break when they try to upgraded without it there?  I do agree
that it needs to improved / replaced, but without a suitable replacement
in place, the old should be resurrected until such a suitable one is in
place ...

> I know you object, so I am going to ask for a vote.

How can you request a vote of such a limited audience?  *Adding*
functionality is easy ... removing functionality with at least a release
for-warning is easy ... removing a feature without any forewarning is akin
to cutting our own throats ...

> OK, here is the request for vote.  Do we want:
>
> 	1)  the old secondary passwords re-added
> 	2)  the new prefixing of the database name to the username when enabled
> 	3)  do nothing

If 2 can be done in such a way to be transparent, as well as to allow a
database owner to be able to create users for his/her database, then I
think it would be great ... and would far exceed what we have now ...

If you can't do 2 as a complete solution, which, IMHO, includes a db owner
being able to create db.users for his own database, then my vote is for 1
... if 2 can be done completely, then I vote for 2, as it would definitely
be much more useful ...

Hrmmm ... I was just thinking of another scenario where such a feature
would be great ... educational.  The ability to setup a database server,
but to give a professor a database for a course that he could create
'accounts' for each of the students ...

Re: Open 7.3 items

Greg Copeland <greg@copelandconsulting.net> — 2002-08-07T01:30:32Z

I would personally like to see 2, however, Marc is correct IMHO.  I cast
my vote using the qualifiers that Marc laid out below.

Greg


On Tue, 2002-08-06 at 20:24, Marc G. Fournier wrote:
> On Tue, 6 Aug 2002, Bruce Momjian wrote:
> 
> >
> > It had such limited usefulness ('password' only, only crypted-hashed
> > passwords in the file) that it doesn't make much sense to resurect it.
> 
> It had limited usefulness to you ... but how many sites out there are
> going to break when they try to upgraded without it there?  I do agree
> that it needs to improved / replaced, but without a suitable replacement
> in place, the old should be resurrected until such a suitable one is in
> place ...
> 
> > I know you object, so I am going to ask for a vote.
> 
> How can you request a vote of such a limited audience?  *Adding*
> functionality is easy ... removing functionality with at least a release
> for-warning is easy ... removing a feature without any forewarning is akin
> to cutting our own throats ...
> 
> > OK, here is the request for vote.  Do we want:
> >
> > 	1)  the old secondary passwords re-added
> > 	2)  the new prefixing of the database name to the username when enabled
> > 	3)  do nothing
> 
> If 2 can be done in such a way to be transparent, as well as to allow a
> database owner to be able to create users for his/her database, then I
> think it would be great ... and would far exceed what we have now ...
> 
> If you can't do 2 as a complete solution, which, IMHO, includes a db owner
> being able to create db.users for his own database, then my vote is for 1
> ... if 2 can be done completely, then I vote for 2, as it would definitely
> be much more useful ...
> 
> Hrmmm ... I was just thinking of another scenario where such a feature
> would be great ... educational.  The ability to setup a database server,
> but to give a professor a database for a course that he could create
> 'accounts' for each of the students ...
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-07T01:50:08Z

> How can you request a vote of such a limited audience?  *Adding*
> functionality is easy ... removing functionality with at least a release
> for-warning is easy ... removing a feature without any forewarning is akin
> to cutting our own throats ...


Yea, but it was such an ugly feature and I honestly thought no one was
using it.  In fact, you aren't even using it in the indended way of
sharing /etc/passwd.  You are using it to implement a different
capability that I never even imagined.  :-)

> 
> > OK, here is the request for vote.  Do we want:
> >
> > 	1)  the old secondary passwords re-added
> > 	2)  the new prefixing of the database name to the username when enabled
> > 	3)  do nothing
> 
> If 2 can be done in such a way to be transparent, as well as to allow a
> database owner to be able to create users for his/her database, then I
> think it would be great ... and would far exceed what we have now ...
> 
> If you can't do 2 as a complete solution, which, IMHO, includes a db owner
> being able to create db.users for his own database, then my vote is for 1
> ... if 2 can be done completely, then I vote for 2, as it would definitely
> be much more useful ...

Well, as it currently stands in the patch, a db owner can create any
user they want, including users for just their dbs.  However, remember
that Once someone can create a user, they can create a superuser, so
security for those folks is impossible.  The patch does not prevent them
from creating user for other databases, if that is what you wanted, but
did your previous solution allow this?


> 
> Hrmmm ... I was just thinking of another scenario where such a feature
> would be great ... educational.  The ability to setup a database server,
> but to give a professor a database for a course that he could create
> 'accounts' for each of the students ...

Yep, with no conflicting names.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Neil Conway <nconway@klamath.dyndns.org> — 2002-08-07T02:25:38Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, here is the request for vote.  Do we want:
> 
> 	1)  the old secondary passwords re-added
> 	2)  the new prefixing of the database name to the username when enabled
> 	3)  do nothing

I'd vote #3, for the following reasons:

        - The functionality that Marc is worried about (in effect,
		  allowing multiple database users with the same name) is
		  pretty obscure, and the implementation is even more so. I
		  doubt whether there is *anyone* other than Marc actually
		  using it (if that's not the case, please speak up).

          Given that it was completely undocumented and a pretty clear
          abuse of the existing code, I don't think it's unreasonable
          for us to break backward compatibility on this issue.

        - The old way of doing things is broken, for reasons Bruce has
          elaborated on. Unless there's a compelling reason why we
          *need* this feature in the standard distribution, I'd rather
          we not go back to the old way of doing things.

        - I'm not perfectly happy with the scheme Bruce suggested as
          an interim fix (#2). If we're going to implement this
          feature, let's do it properly. In particular, I'm not
          convinced that this feature is urgently needed enough to
          justify a short-term kludge, and I dislike using a GUC
          variable to toggle between two quite different
          authentication processes.

So I'd say leave things as they are. One thing I'd like to see anyway
is a more prominent listing of the client-visible incompatibilities in
the release notes -- I'd be content to add an entry to that list for
the 7.3 release and talk about a more elaborate scheme during the 7.4
development cycle.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-07T04:27:05Z

On Tue, 6 Aug 2002, Bruce Momjian wrote:

> > How can you request a vote of such a limited audience?  *Adding*
> > functionality is easy ... removing functionality with at least a release
> > for-warning is easy ... removing a feature without any forewarning is akin
> > to cutting our own throats ...
>
>
> Yea, but it was such an ugly feature and I honestly thought no one was
> using it.  In fact, you aren't even using it in the indended way of
> sharing /etc/passwd.  You are using it to implement a different
> capability that I never even imagined.  :-)

Can you point me to where this documentation is on its intended use?
*raised eyebrow*  Just bcause you couldn't imagine it being used the way I
am, doesn't mean that wasn't what it was intended for :)

> Well, as it currently stands in the patch, a db owner can create any
> user they want, including users for just their dbs.  However, remember
> that Once someone can create a user, they can create a superuser, so
> security for those folks is impossible.  The patch does not prevent them
> from creating user for other databases, if that is what you wanted, but
> did your previous solution allow this?

But, the patch should ... how hard is it to add code in that says "if
connected to db1 *and* have creat user privs, then allow create of
db1.<username>"?

Personally, from using cyrus-imapd for much much too long, I think what
we're looking at is 'realms' ... if 'enable_realms' is enabled in
postmaster.conf, then a user creatd wile connetd to db1 shuld have db1
appended automagically ...

then again, i do think its "a Bad Thing" to have this enable/disableable,
since it will cause some serious confusion ... its kinda like everyone's
argument against Thomas' recent patch about XLOG ... what if you forget?

it should be an initdb option (--enable-realms) so that its a
one-time-only decision when you create the database instance, not
something that you can flip on/off ... default would be disabled, to
reflect current behaviour (minus the password file) ...

or, another option would be 'CREATE DATABASE <DB> WITH REALMS', so that
you could have some with, some without ... so, if a DATABASE was creatd
with REALMS, a flag would be set in pg_database stating that only those
users with db. prefix have access to that database ...

then again, another neat thing would be he ability to 'group' databases
... CREATE DATABASE <DB> IN GROUP <dbgroup>, so that users would be named
dbgroup.* and would b able to login to any database within that group ...

but those are just ideas thrown out ... IMHO, critical for v7.3, if we
don't revert the patch, is to have *either* '--enable-realms' to set an
instance in that mode, *or* have it on a per database basis ... I think
having it as an on/off setting in postmaster.conf is just askng for
trouble ...

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-07T04:43:19Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, here is the request for vote.  Do we want:

> 	1)  the old secondary passwords re-added
> 	2)  the new prefixing of the database name to the username when enabled
> 	3)  do nothing

I vote for 2b), username@database ...

			regards, tom lane

Re: Open 7.3 items

Joe Conway <mail@joeconway.com> — 2002-08-07T04:49:38Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>OK, here is the request for vote.  Do we want:
> 
> 
>>	1)  the old secondary passwords re-added
>>	2)  the new prefixing of the database name to the username when enabled
>>	3)  do nothing
> 
> 
> I vote for 2b), username@database ...
> 

I like that too -- and it has the added benefit of being similar to 
Oracle (username@tns_servicename; tns_servicename is really just a 
pointer to the IP/port of a specific Oracle database).

Joe

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-07T12:16:06Z

>         - The functionality that Marc is worried about (in effect,
> 		  allowing multiple database users with the same name) is
> 		  pretty obscure, and the implementation is even more so. I
> 		  doubt whether there is *anyone* other than Marc actually
> 		  using it (if that's not the case, please speak up).

I would use database specific users for a similar area -- shared
hosting.  But, could live with a longer (128 byte) namedatalen to allow
a unique user%domain.

Re: Open 7.3 items

Neil Conway <nconway@klamath.dyndns.org> — 2002-08-07T13:29:46Z

Rod Taylor <rbt@zort.ca> writes:

> >         - The functionality that Marc is worried about (in effect,
> > 		  allowing multiple database users with the same name) is
> > 		  pretty obscure, and the implementation is even more so. I
> > 		  doubt whether there is *anyone* other than Marc actually
> > 		  using it (if that's not the case, please speak up).
> 
> I would use database specific users for a similar area -- shared
> hosting.

I agree that the functionality Marc is looking for is useful -- I'm
just saying that I would bet that *no one* is using the current
implementation of it in PostgreSQL (i.e. so I don't see the need to
keep backward compatibility, or the harm in removing the feature for
the next release until a better solution is designed & implemented).

> But, could live with a longer (128 byte) namedatalen to allow
> a unique user%domain.

That seems like a serviceable solution to me -- it seems quite easy to
implement this functionality outside the database proper (at least
until a proper solution is devised). Keep in mind that the current
FE/BE protocol limits database and user names to 64 characters.
That's another thing I'd like to fix in 7.4.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-07T13:37:03Z

> > But, could live with a longer (128 byte) namedatalen to allow
> > a unique user%domain.
> 
> That seems like a serviceable solution to me -- it seems quite easy to
> implement this functionality outside the database proper (at least
> until a proper solution is devised). Keep in mind that the current
> FE/BE protocol limits database and user names to 64 characters.
> That's another thing I'd like to fix in 7.4.

Aw shoot.  64 characters isn't enough to hold a good chunk of our
clients domain names let alone usernames in front.   I'm not looking
forward to trimming domains either.

I hope 7.4 that a protocol change for 7.4 is warranted.  Looks like
there are a fair number of things in that area.

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-07T14:14:26Z

Neil Conway <nconway@klamath.dyndns.org> writes:
> Keep in mind that the current
> FE/BE protocol limits database and user names to 64 characters.

That seems to be a good reason to combine the two on the postmaster
side, a la Bruce's proposed patch.  If the client side does it then
the "user@database" has to all fit in 64 characters.

> That's another thing I'd like to fix in 7.4.

Yup.  Do we have a list going of the things we want to fix in the next
protocol change?  Offhand I remember

* redesign startup packet to eliminate fixed field widths
* fix COPY protocol to allow resync after errors, support binary data
* less brain-dead protocol for fast-path function calls
* allow NOTIFY to include parameters
* richer ERROR reports (error codes, other stuff)

and I'm sure there's more.  None of this stuff seems to be in the TODO
list though.

			regards, tom lane

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-07T14:43:20Z

On Tuesday 06 August 2002 09:24 pm, Marc G. Fournier wrote:
> On Tue, 6 Aug 2002, Bruce Momjian wrote:
> > It had such limited usefulness ('password' only, only crypted-hashed
> > passwords in the file) that it doesn't make much sense to resurect it.

> It had limited usefulness to you ... but how many sites out there are
> going to break when they try to upgraded without it there?  I do agree
> that it needs to improved / replaced, but without a suitable replacement
> in place, the old should be resurrected until such a suitable one is in
> place ...

While it appears I'll be outvoted on this issue, and even though I agree that 
the existing functionality is broken, and even though I am not using the 
functionality, I am reminded of the overall policy that we have historically 
had about removing even broken features.  Fair Warning must be given. If that 
policy is going to be changed, then it needs to be applied with equal vigor 
to all affected cases.

Even if Marc is the only one using this feature, we should follow established 
policy -- that is, after all, what policy is for.  To me it seems it is being 
yanked gratuitously without fair warning.  If every question is answered on a 
case-by-case basis like this, we will descend to anarchy, I'm afraid.  And, 
Bruce, I even agree with your reasons -- I just disagree with the method.

Is it going to cause a major problem for it to remain one release cycle while 
someone works on a suitable replacement, with the warning in the release 
notes that while this feature is there for backwards compatibility that it 
will be yanked at the next release?  And I'm not talking about a minor 
problem like 'more people will start using it' -- I'm talking 'if it stays we 
will be in danger of massive data corruption or exposure' -- of course, 
documenting that there is a degree of exposure of data if not set up in an 
exacting method, as Marc seems to have done.

Some may say Marc has fair warning now -- but does anyone know for sure that 
NO ONE ELSE in the whole world isn't using this feature?  Marc is more in the 
know than most, granted -- but if he found this use for the feature others 
may have as well that we don't even know about.

But if the feature is not going to remain it needs to be prominently 
documented as being removed in the release notes.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Neil Conway <nconway@klamath.dyndns.org> — 2002-08-07T15:20:19Z

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Do we have a list going of the things we want to fix in the next
> protocol change?  Offhand I remember
> 
> * redesign startup packet to eliminate fixed field widths
> * fix COPY protocol to allow resync after errors, support binary data
> * less brain-dead protocol for fast-path function calls
> * allow NOTIFY to include parameters
> * richer ERROR reports (error codes, other stuff)

Some kind of parameter binding or improved support for prepareable
statements would require changes to the FE/BE protocol -- being able
to accept parameters without passing them through the parser, for
example.

Allowing clients to cleanly determine the current transaction state
will require FE/BE protocol changes, I think. (Or at least, that's my
vague recollection of the discussion on -hackers from a couple months ago).

That's all I can think of -- there's probably more stuff...

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-07T15:29:29Z

Neil Conway <nconway@klamath.dyndns.org> writes:
> Some kind of parameter binding or improved support for prepareable
> statements would require changes to the FE/BE protocol -- being able
> to accept parameters without passing them through the parser, for
> example.

Right.  This is nearly the same, perhaps could be made actually the
same, as a fast-path function call.

The existing FPF call mechanism only supports binary data, but I think
it would be useful to allow either binary data or ASCII data in both FPF
and prepared-statement cases.  The ASCII path would require invoking a
datatype's conversion function on the backend side, but you'd still get
to skip the SQL statement parsing/planning overhead.

(Wanders away wondering whether COPY might not be made to fit into this
same mold...)

			regards, tom lane

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-07T21:12:02Z

On Thu, 2002-08-08 at 03:27, Marc G. Fournier wrote:
> On Wed, 7 Aug 2002, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > OK, here is the request for vote.  Do we want:
> > >
> > > > 	1)  the old secondary passwords re-added
> > > > 	2)  the new prefixing of the database name to the username when enabled
> > > > 	3)  do nothing
> > >
> > > I vote for 2b), username@database ...
> >
> > Yes, the format was going to be my second vote, dbname.username or
> > username@dbname.  Guess I will not need that vote.  ;-)
> 
> Actually, I kinda like dbname.username myself ... it means that wne you do
> a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
> grouped by database)

use a view :

create view pg_shadow_with_domain as
    select
        usename as fullname,
        case when (strpos(usename,'@') > 0)
             then substr(usename,1,strpos(usename,'@')-1)
             else usename
              end as usename,
        case when (strpos(usename,'@') > 0)
             then substr(usename,strpos(usename,'@')+1)
             else ''
              end as usedomain,
        usesysid,
        usecreatedb,
        usetrace,
        usesuper,
        usecatupd,
        passwd,
        valuntil
    from pg_shadow;

and sort as you wish ;)

For example, to get all bruces in all domains starting with an 'acc'
just do

select *
  from pg_shadow_with_domain 
 where usename = 'bruce'
   and domain like 'acc%' ;

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

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-07T22:02:31Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, here is the request for vote.  Do we want:
> 
> > 	1)  the old secondary passwords re-added
> > 	2)  the new prefixing of the database name to the username when enabled
> > 	3)  do nothing
> 
> I vote for 2b), username@database ...

Yes, the format was going to be my second vote, dbname.username or
username@dbname.  Guess I will not need that vote.  ;-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Open 7.3 items

Marc G. Fournier <scrappy@hub.org> — 2002-08-07T22:27:19Z

On Wed, 7 Aug 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > OK, here is the request for vote.  Do we want:
> >
> > > 	1)  the old secondary passwords re-added
> > > 	2)  the new prefixing of the database name to the username when enabled
> > > 	3)  do nothing
> >
> > I vote for 2b), username@database ...
>
> Yes, the format was going to be my second vote, dbname.username or
> username@dbname.  Guess I will not need that vote.  ;-)

Actually, I kinda like dbname.username myself ... it means that wne you do
a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
grouped by database)

LRE: Open 7.3 items

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-08T02:26:31Z

> Some may say Marc has fair warning now -- but does anyone know
> for sure that
> NO ONE ELSE in the whole world isn't using this feature?  Marc is
> more in the
> know than most, granted -- but if he found this use for the
> feature others
> may have as well that we don't even know about.
>
> But if the feature is not going to remain it needs to be prominently
> documented as being removed in the release notes.

And just remember all those reasons why people find MySQL easier to use than
Postgres - the upgrade process...

Chris

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-08T02:31:12Z

Hannu Krosing <hannu@tm.ee> writes:
> On Thu, 2002-08-08 at 03:27, Marc G. Fournier wrote:
>> Actually, I kinda like dbname.username myself ... it means that wne you do
>> a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
>> grouped by database)

Hmm, Marc's got a point there...

> use a view :

Yeah, but it's painful to do that.

			regards, tom lane

Re: Open 7.3 items

Greg Copeland <greg@copelandconsulting.net> — 2002-08-08T02:58:10Z

Ssshhhh....don't tell Curt that!  ;)

Greg

On Wed, 2002-08-07 at 21:31, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > On Thu, 2002-08-08 at 03:27, Marc G. Fournier wrote:
> >> Actually, I kinda like dbname.username myself ... it means that wne you do
> >> a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
> >> grouped by database)
> 
> Hmm, Marc's got a point there...
> 
> > use a view :
> 
> Yeah, but it's painful to do that.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-08T03:30:37Z

On Thu, 2002-08-08 at 07:31, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > On Thu, 2002-08-08 at 03:27, Marc G. Fournier wrote:
> >> Actually, I kinda like dbname.username myself ... it means that wne you do
> >> a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
> >> grouped by database)
> 
> Hmm, Marc's got a point there...
> 
> > use a view :
> 
> Yeah, but it's painful to do that.

Not if the view is installed with the system.

So the plan could be:

1 .give the new functionality in a "light" version - ie just checking at
connect time, full name must be used when creating user.

2. modify pg_user to show it usename usedomain as two separate fields
for eas of use (join pg_user and pg_shadow on usesysid if you need to
see passwords)

3. in version 7.4 modify CREATE USER and ALTER USER to save the domain
info in pg_shadow.usedomain.

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

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-08T05:42:15Z

Hannu Krosing <hannu@tm.ee> writes:
> 2. modify pg_user to show it usename usedomain as two separate fields
> for eas of use (join pg_user and pg_shadow on usesysid if you need to
> see passwords)

This is already more mechanism than I wanted to buy into, and less
forethought than I think we need.  For example, is it a good idea if
pg_user shows usernames that cannot be identified with those shown by
ACL lists?  If not, how will you modify ACL I/O formats?  What about
the has_table_privilege functions?

What I'm envisioning is an extremely limited facility that just maps
connection parameters into an internal username that is of the form
username@dbname or dbname.username.  Trying to hide that internal
username for inside-the-database activities does not strike me as a
good plan.

This may prove to be just a stopgap measure that we'll replace down the
road (as indeed the secondary-passwords thing was just a stopgap, IMO).
Let's not add features that will create extra compatibility problems
if we abandon the whole approach later.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-11T02:41:57Z

I would like to address this email.  

Lamar is mentioning that it is unfair to remove a feature without
warning.

Let me give a little history.  The secondary password file was created
at a time when we didn't encrypt with random salt over the wire, and we
had people who wanted to share their /etc/passwd file with PostgreSQL.

Later, people wanted to use the secondary password file for just
usernames, so you could list usernames in the file and limit db access
by user.  This is the current usage for 99% of secondary password users.
This capability is better served in 7.3 with the new USER column in
pg_shadow and the ability to specify filenames or groups in that file. 
Keeping the secondary password file to specify a user list while a new
USER column exists in 7.3 is just confusing to administrators.  Our
pg_hba.conf system is pretty complex, so anything we can do to simplify
helps.

Now, on to Marc's case, where he does use the file for usernames _and_
passwords.  However, he is using it only so he can have more than one
person with the same username and restrict access based on the password
in the secondary password file.  While this does work, my submitted
patch makes this much easier and cleaner.

Marc had mentioned that this should be an initdb flag.  However, our
standard procedure is to put stuff in initdb only when it can't be
changed after initdb.  While strange, this feature can be
enabled-disabled after initdb.  A quick update of pg_shadow can change
usernames and you can go in and out of this mode.

Someone talked about pushing this capability into a separate pg_shadow
column, and making CREATE/ALTER user and createuser aware of this. 
While this can be done, and it sort of becomes user schemas, there isn't
enough people wanting this to add complexity to those commands.  A GUC
flag will meet most peoples needs at this point.

Some mentioned using user@dbname, though the idea of sorting made
several recant their votes.

So, based on the voting, I think dbname.username is an agreed-upon
feature addition for 7.3.  I will work on a final patch with
documentation and post it to the patches list for more comment.

---------------------------------------------------------------------------

Lamar Owen wrote:
> On Tuesday 06 August 2002 09:24 pm, Marc G. Fournier wrote:
> > On Tue, 6 Aug 2002, Bruce Momjian wrote:
> > > It had such limited usefulness ('password' only, only crypted-hashed
> > > passwords in the file) that it doesn't make much sense to resurect it.
> 
> > It had limited usefulness to you ... but how many sites out there are
> > going to break when they try to upgraded without it there?  I do agree
> > that it needs to improved / replaced, but without a suitable replacement
> > in place, the old should be resurrected until such a suitable one is in
> > place ...
> 
> While it appears I'll be outvoted on this issue, and even though I agree that 
> the existing functionality is broken, and even though I am not using the 
> functionality, I am reminded of the overall policy that we have historically 
> had about removing even broken features.  Fair Warning must be given. If that 
> policy is going to be changed, then it needs to be applied with equal vigor 
> to all affected cases.
> 
> Even if Marc is the only one using this feature, we should follow established 
> policy -- that is, after all, what policy is for.  To me it seems it is being 
> yanked gratuitously without fair warning.  If every question is answered on a 
> case-by-case basis like this, we will descend to anarchy, I'm afraid.  And, 
> Bruce, I even agree with your reasons -- I just disagree with the method.
> 
> Is it going to cause a major problem for it to remain one release cycle while 
> someone works on a suitable replacement, with the warning in the release 
> notes that while this feature is there for backwards compatibility that it 
> will be yanked at the next release?  And I'm not talking about a minor 
> problem like 'more people will start using it' -- I'm talking 'if it stays we 
> will be in danger of massive data corruption or exposure' -- of course, 
> documenting that there is a degree of exposure of data if not set up in an 
> exacting method, as Marc seems to have done.
> 
> Some may say Marc has fair warning now -- but does anyone know for sure that 
> NO ONE ELSE in the whole world isn't using this feature?  Marc is more in the 
> know than most, granted -- but if he found this use for the feature others 
> may have as well that we don't even know about.
> 
> But if the feature is not going to remain it needs to be prominently 
> documented as being removed in the release notes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-13T01:28:41Z

On Saturday 10 August 2002 10:41 pm, Bruce Momjian wrote:
> Let me give a little history.  The secondary password file was created
> at a time when we didn't encrypt with random salt over the wire, and we
> had people who wanted to share their /etc/passwd file with PostgreSQL.
[snip]

> So, based on the voting, I think dbname.username is an agreed-upon
> feature addition for 7.3.  I will work on a final patch with
> documentation and post it to the patches list for more comment.

I can live with this, if the documentation is prominently referred to in the 
changelog.

As to the feature itself, I believe Bruce's proposed solution is the best, and 
believed that from the beginning -- I just wanted to deal with the 'fair 
warning' issue alone.

As to fair warning:  watch for the next RPM release.  Fair Warning is being 
given that upgrades within the RPM context will not be supported in any form 
for the final release of PostgreSQL 7.3. 

I had a 'd'oh' moment (and I don't watch the Simpsons....) when I realized 
that I could quite easily prevent anyone from even attempting an RPM upgrade, 
unless that take matters into their own grubby little hands with special 
switches to the rpm command line. 

It will not be yanked this next set, but the following set will be 
unupgradable.  Sorry, but the packaged kludge isn't reliable enough for the 
state of PostgreSQL reliability, and I don't want the RPMset's shortcomings 
(due to the whole RPM mechanism forcing the issue) causing bad blood towards 
PostgreSQL in general. The Debian packages don't have much of the limitations 
and restrictions I have to deal with, and until a good upgrade utility is 
available I'm just going to have to do this.

I have been so swamped with Fortran work for work that I've not even looked at 
the python code Hannu so kindly sent me, nor have I played any more with 
pg_fsck.  Groundwave propagation modeling in Fortran has been more 
important...

Likewise, my focus as RPM maintainer is changing with this next release.  
Since the distributions, such as Red Hat, are doing a great job keeping up to 
date, I'm going to not bother much with building RPMs that are, frankly, 
redundant at this point.  Three years ago it wasn't this nice.  Trond has 
done a good job on the Red Hat bleeding edge front, Reinhard Max has done 
similarly for SuSE.  There are PLD, Connectiva, TurboLinux, Caldera, and 
Mandrake maintainers as well -- and they seem to be doing fine.

I'm going to now go to the lagging plane -- building newer PostgreSQL for 
older Red Hat (and maybe others, if I can get enough hard drives available).  
The source RPM will still be useful to the newer distribution's maintainers 
-- but the requests I see more of on the lists is newer PostgreSQL on older 
linux.  So I'm going to try to rise to that occassion, and take this 
opportunity to apologize for not seeing it sooner.

I welcome comments on this change of focus.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

kdebisschop@alert.infoplease.com — 2002-08-13T01:51:11Z

On Mon, 2002-08-12 at 21:28, Lamar Owen wrote:

> I'm going to now go to the lagging plane -- building newer PostgreSQL for 
> older Red Hat (and maybe others, if I can get enough hard drives available).  
> The source RPM will still be useful to the newer distribution's maintainers 
> -- but the requests I see more of on the lists is newer PostgreSQL on older 
> linux.  So I'm going to try to rise to that occassion, and take this 
> opportunity to apologize for not seeing it sooner.
> 
> I welcome comments on this change of focus.

Even though we run redhat on our systems, as close to stock as we can, I
have found that your RPMs build more reliably than Trond's.

My bad for being unable to diagnose the build problems with the RedHat
SRPM, my double-bad for letting that failure prevent my reporting the
issu to him. 

But I for one will miss your lead on the bleeding edge of RPM
development.

--
Karl DeBisschop

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-13T02:49:37Z

On Monday 12 August 2002 09:51 pm, Karl DeBisschop wrote:
> On Mon, 2002-08-12 at 21:28, Lamar Owen wrote:
> > I'm going to now go to the lagging plane -- building newer PostgreSQL for
> > older Red Hat (and maybe others, if I can get enough hard drives
> > available). The source RPM will still be useful to the newer
> > distribution's maintainers -- but the requests I see more of on the lists
> > is newer PostgreSQL on older linux.  So I'm going to try to rise to that
> > occassion, and take this opportunity to apologize for not seeing it
> > sooner.

> But I for one will miss your lead on the bleeding edge of RPM
> development.

Oh, I've misstated, apparently.  I'll continue on the 'bleeding edge' as far 
as versions of PostgreSQL are concerned -- I'm just shifting focus to 
providing prebuilt binaries on older dists.  As I do some other bleeding edge 
work, I typically will make sure my source RPM's build on the latest and 
greatest -- they just won't be optimized for it.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Sean Chittenden <sean@chittenden.org> — 2002-08-13T21:25:53Z

> Some mentioned using user@dbname, though the idea of sorting made
> several recant their votes.
>
> So, based on the voting, I think dbname.username is an agreed-upon
> feature addition for 7.3.  I will work on a final patch with
> documentation and post it to the patches list for more comment.

The nice thing about using an @ sign, amongst being more consistent
with kerberos and email, is that it doesn't preclude the use of .'s in
a database name.  For simplicity's sake, I'd really like to be able to
continue issuing database names that are identical to the domain that
they serve and worry that relying on a "." will either make the use of
a dot in the username or database impossible.  An @ sign, on the other
hand, is the ubiquitously agreed upon username/host separator and
makes it all that much more consistent for users and administrators.

Username: john.doe
Database: foo.com
possible pg_shadow entry #1: john.doe.foo.com
possible pg_shadow entry #2: john.doe@foo.com

If people are worried about the sorting, ORDER BY domain, username.
My $0.02.  -sc

-- 
Sean Chittenden

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-13T22:22:30Z

Sean Chittenden wrote:
> > Some mentioned using user@dbname, though the idea of sorting made
> > several recant their votes.
> >
> > So, based on the voting, I think dbname.username is an agreed-upon
> > feature addition for 7.3.  I will work on a final patch with
> > documentation and post it to the patches list for more comment.
> 
> The nice thing about using an @ sign, amongst being more consistent
> with kerberos and email, is that it doesn't preclude the use of .'s in
> a database name.  For simplicity's sake, I'd really like to be able to
> continue issuing database names that are identical to the domain that
> they serve and worry that relying on a "." will either make the use of
> a dot in the username or database impossible.  An @ sign, on the other
> hand, is the ubiquitously agreed upon username/host separator and
> makes it all that much more consistent for users and administrators.
> 
> Username: john.doe
> Database: foo.com
> possible pg_shadow entry #1: john.doe.foo.com
> possible pg_shadow entry #2: john.doe@foo.com
> 
> If people are worried about the sorting, ORDER BY domain, username.
> My $0.02.  -sc

Well, they aren't separate fields so you can't ORDER BY domain.  The dot
was used so it looks like a schema based on dbname.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Sean Chittenden <sean@chittenden.org> — 2002-08-14T00:09:34Z

> > > Some mentioned using user@dbname, though the idea of sorting made
> > > several recant their votes.
> > >
> > > So, based on the voting, I think dbname.username is an agreed-upon
> > > feature addition for 7.3.  I will work on a final patch with
> > > documentation and post it to the patches list for more comment.
> > 
> > The nice thing about using an @ sign, amongst being more consistent
> > with kerberos and email, is that it doesn't preclude the use of .'s in
> > a database name.  For simplicity's sake, I'd really like to be able to
> > continue issuing database names that are identical to the domain that
> > they serve and worry that relying on a "." will either make the use of
> > a dot in the username or database impossible.  An @ sign, on the other
> > hand, is the ubiquitously agreed upon username/host separator and
> > makes it all that much more consistent for users and administrators.
> > 
> > Username: john.doe
> > Database: foo.com
> > possible pg_shadow entry #1: john.doe.foo.com
> > possible pg_shadow entry #2: john.doe@foo.com
> > 
> > If people are worried about the sorting, ORDER BY domain, username.
> > My $0.02.  -sc
> 
> Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> was used so it looks like a schema based on dbname.

Sorry, I know it's a single field and that there is no split()
function (that I'm aware of), but that seems like such a small and
easy to fix problem that I personally place a higher value on the more
standard nomeclature and use of an @ sign.  I understand the value of
. for schemas and whatnot, but isn't a user going to be in their own
schema to begin with?  As for the order by, I've got a list of users
per "account" (sales account), so doing the order by is on two columns
and the pg_shadow table is generated periodically from our inhouse
tables.  -sc

-- 
Sean Chittenden

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T01:00:10Z

Sean Chittenden wrote:
> > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > was used so it looks like a schema based on dbname.
> 
> Sorry, I know it's a single field and that there is no split()
> function (that I'm aware of), but that seems like such a small and
> easy to fix problem that I personally place a higher value on the more
> standard nomeclature and use of an @ sign.  I understand the value of
> . for schemas and whatnot, but isn't a user going to be in their own
> schema to begin with?  As for the order by, I've got a list of users
> per "account" (sales account), so doing the order by is on two columns
> and the pg_shadow table is generated periodically from our inhouse
> tables.  -sc

I have no personal preference between period and @ or whatever.  See if
you can get some other votes for @ because most left @ when the ORDER BY
idea came up from Marc.

As for it being a special character, it really isn't because the code
prepends the database name and a period.  It doesn't look to see if
there is a period in the already or anything.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T01:36:43Z

OK, here is the patch to implement db_user_namespace.  It includes
documentation.

I had to add to initdb to create a file /data/PG_INSTALLER and have the
postmaster read that on startup to determine the installing user.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 
> I would like to address this email.  
> 
> Lamar is mentioning that it is unfair to remove a feature without
> warning.
> 
> Let me give a little history.  The secondary password file was created
> at a time when we didn't encrypt with random salt over the wire, and we
> had people who wanted to share their /etc/passwd file with PostgreSQL.
> 
> Later, people wanted to use the secondary password file for just
> usernames, so you could list usernames in the file and limit db access
> by user.  This is the current usage for 99% of secondary password users.
> This capability is better served in 7.3 with the new USER column in
> pg_shadow and the ability to specify filenames or groups in that file. 
> Keeping the secondary password file to specify a user list while a new
> USER column exists in 7.3 is just confusing to administrators.  Our
> pg_hba.conf system is pretty complex, so anything we can do to simplify
> helps.
> 
> Now, on to Marc's case, where he does use the file for usernames _and_
> passwords.  However, he is using it only so he can have more than one
> person with the same username and restrict access based on the password
> in the secondary password file.  While this does work, my submitted
> patch makes this much easier and cleaner.
> 
> Marc had mentioned that this should be an initdb flag.  However, our
> standard procedure is to put stuff in initdb only when it can't be
> changed after initdb.  While strange, this feature can be
> enabled-disabled after initdb.  A quick update of pg_shadow can change
> usernames and you can go in and out of this mode.
> 
> Someone talked about pushing this capability into a separate pg_shadow
> column, and making CREATE/ALTER user and createuser aware of this. 
> While this can be done, and it sort of becomes user schemas, there isn't
> enough people wanting this to add complexity to those commands.  A GUC
> flag will meet most peoples needs at this point.
> 
> Some mentioned using user@dbname, though the idea of sorting made
> several recant their votes.
> 
> So, based on the voting, I think dbname.username is an agreed-upon
> feature addition for 7.3.  I will work on a final patch with
> documentation and post it to the patches list for more comment.
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-14T02:42:49Z

On Wed, 2002-08-14 at 06:00, Bruce Momjian wrote:
> Sean Chittenden wrote:
> > > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > > was used so it looks like a schema based on dbname.

IMHO it should look like an user in domain ;)
 
> > Sorry, I know it's a single field and that there is no split()
> > function (that I'm aware of), but that seems like such a small and
> > easy to fix problem that I personally place a higher value on the more
> > standard nomeclature and use of an @ sign.  I understand the value of
> > . for schemas and whatnot, but isn't a user going to be in their own
> > schema to begin with?  As for the order by, I've got a list of users
> > per "account" (sales account), so doing the order by is on two columns
> > and the pg_shadow table is generated periodically from our inhouse
> > tables.  -sc
> 
> I have no personal preference between period and @ or whatever.  See if
> you can get some other votes for @ because most left @ when the ORDER BY
> idea came up from Marc.

I still like @ . And I posted code that could be put in the pg_user view
to split out domain you could ORDER BY.
 
> As for it being a special character, it really isn't because the code
> prepends the database name and a period.  It doesn't look to see if
> there is a period in the already or anything.
-----------
Hannu

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T04:11:10Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have no personal preference between period and @ or whatever.  See if
> you can get some other votes for @ because most left @ when the ORDER BY
> idea came up from Marc.

FWIW, I still lean to username@database, so I think we're roughly at a
tie.  It would be good to get more votes ...

			regards, tom lane

Re: Open 7.3 items

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-08-14T04:15:26Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have no personal preference between period and @ or whatever.  See if
> > you can get some other votes for @ because most left @ when the ORDER BY
> > idea came up from Marc.
>
> FWIW, I still lean to username@database, so I think we're roughly at a
> tie.  It would be good to get more votes ...

Sorry guys, I'm staying out of this one as my vote would be entirely
arbitrary...

Chris

Re: Open 7.3 items

Joe Conway <mail@joeconway.com> — 2002-08-14T04:45:37Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>I have no personal preference between period and @ or whatever.  See if
>>you can get some other votes for @ because most left @ when the ORDER BY
>>idea came up from Marc.
> 
> 
> FWIW, I still lean to username@database, so I think we're roughly at a
> tie.  It would be good to get more votes ...
> 

I'm in favor of username@database too.

Joe

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T04:45:57Z

OK, the vote is not shifting from '.' to '@'.  Is that how we want to
go?  I like the pg_user enhancement.  Marc, comments?  This was your baby.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Wed, 2002-08-14 at 06:00, Bruce Momjian wrote:
> > Sean Chittenden wrote:
> > > > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > > > was used so it looks like a schema based on dbname.
> 
> IMHO it should look like an user in domain ;)
>  
> > > Sorry, I know it's a single field and that there is no split()
> > > function (that I'm aware of), but that seems like such a small and
> > > easy to fix problem that I personally place a higher value on the more
> > > standard nomeclature and use of an @ sign.  I understand the value of
> > > . for schemas and whatnot, but isn't a user going to be in their own
> > > schema to begin with?  As for the order by, I've got a list of users
> > > per "account" (sales account), so doing the order by is on two columns
> > > and the pg_shadow table is generated periodically from our inhouse
> > > tables.  -sc
> > 
> > I have no personal preference between period and @ or whatever.  See if
> > you can get some other votes for @ because most left @ when the ORDER BY
> > idea came up from Marc.
> 
> I still like @ . And I posted code that could be put in the pg_user view
> to split out domain you could ORDER BY.
>  
> > As for it being a special character, it really isn't because the code
> > prepends the database name and a period.  It doesn't look to see if
> > there is a period in the already or anything.
> -----------
> Hannu
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-14T06:11:59Z

On Wed, 2002-08-14 at 12:45, Sean Chittenden wrote:
> > > > > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > > > > was used so it looks like a schema based on dbname.
> > 
> > IMHO it should look like an user in domain ;)
> 
> Agreed, but there is something to be said for doing a sort of users
> per domain.  This wouldn't be an issue, I don't think, if there was a
> split_before() and split_after() like functions.
> 
> # SELECT split_before('user@domain.com','@'), split_after('user@domain.com', '@');
>  ?column? |  ?column?
> ----------+------------
>  user     | domain.com
> 
> What would you guys say to submissions for a patch that would add the
> function listed above? 

create function split_before(text,text) returns text as '
 select case when (strpos($1,$2) > 0)
             then substr($1,1,strpos($1,$2)-1)
             else $1
              end as usename
' language 'SQL';

create function split_after(text,text) returns text as '
 select case when (strpos($1,$2) > 0)
             then substr($1,strpos($1,$2)+1)
             else ''''
              end as usedomain
' language 'SQL' ;

hannu=# select split_before('me@somewhere','@'),
split_after('me@somewhere','@');
 split_before | split_after 
--------------+-------------
 me           | somewhere
(1 row)

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

Re: Open 7.3 items

Sean Chittenden <sean@chittenden.org> — 2002-08-14T07:45:47Z

> > > > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > > > was used so it looks like a schema based on dbname.
> 
> IMHO it should look like an user in domain ;)

Agreed, but there is something to be said for doing a sort of users
per domain.  This wouldn't be an issue, I don't think, if there was a
split_before() and split_after() like functions.

# SELECT split_before('user@domain.com','@'), split_after('user@domain.com', '@');
 ?column? |  ?column?
----------+------------
 user     | domain.com

What would you guys say to submissions for a patch that would add the
function listed above?  Maybe just a function called get_user(text)
and get_domain(text)? ::shrug:: Just some thoughts since there is
validity to being able to parse/operate on this data efficiently.  If
those functions existed, then I think everyone would be able to have
their pie as they want it.  -sc

-- 
Sean Chittenden

Re: Open 7.3 items

Sean Chittenden <sean@chittenden.org> — 2002-08-14T09:03:42Z

> > > > > > Well, they aren't separate fields so you can't ORDER BY domain.  The dot
> > > > > > was used so it looks like a schema based on dbname.
> > > 
> > > IMHO it should look like an user in domain ;)
> > 
> > Agreed, but there is something to be said for doing a sort of users
> > per domain.  This wouldn't be an issue, I don't think, if there was a
> > split_before() and split_after() like functions.
> > 
> > # SELECT split_before('user@domain.com','@'), split_after('user@domain.com', '@');
> >  ?column? |  ?column?
> > ----------+------------
> >  user     | domain.com
> > 
> > What would you guys say to submissions for a patch that would add the
> > function listed above? 
> 
> create function split_before(text,text) returns text as '
>  select case when (strpos($1,$2) > 0)
>              then substr($1,1,strpos($1,$2)-1)
>              else $1
>               end as usename
> ' language 'SQL';
> 
> create function split_after(text,text) returns text as '
>  select case when (strpos($1,$2) > 0)
>              then substr($1,strpos($1,$2)+1)
>              else ''''
>               end as usedomain
> ' language 'SQL' ;
> 
> hannu=# select split_before('me@somewhere','@'),
> split_after('me@somewhere','@');
>  split_before | split_after 
> --------------+-------------
>  me           | somewhere
> (1 row)

Oh that was handy and fast!  I didn't know of strpos().  Cool, who
says 'ya can't learn something every day?  :~) Now with an alias or
subselect, it should be very easy to order users in a domain in any
way that SQL allows.  :~) Thanks Hannu.  -sc

-- 
Sean Chittenden

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-14T12:08:46Z

I'm going to vote for either @ or %.

On Wed, 2002-08-14 at 00:11, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have no personal preference between period and @ or whatever.  See if
> > you can get some other votes for @ because most left @ when the ORDER BY
> > idea came up from Marc.
> 
> FWIW, I still lean to username@database, so I think we're roughly at a
> tie.  It would be good to get more votes ...

Re: Open 7.3 items

ngpg@grymmjack.com — 2002-08-14T13:46:59Z

 
> OK, the vote is not shifting from '.' to '@'.  Is that how we want to
> go?  I like the pg_user enhancement.  Marc, comments?  This was your
> baby. 
> 

Would it be hard to setup an internal PG variable for the actual character 
to be used?

Re: Open 7.3 items

Joe Conway <mail@joeconway.com> — 2002-08-14T14:08:12Z

Sean Chittenden wrote:
> Agreed, but there is something to be said for doing a sort of users
> per domain.  This wouldn't be an issue, I don't think, if there was a
> split_before() and split_after() like functions.
> 
> # SELECT split_before('user@domain.com','@'), split_after('user@domain.com', '@');
>  ?column? |  ?column?
> ----------+------------
>  user     | domain.com
> 
> What would you guys say to submissions for a patch that would add the
> function listed above?  Maybe just a function called get_user(text)
> and get_domain(text)? ::shrug:: Just some thoughts since there is
> validity to being able to parse/operate on this data efficiently.  If
> those functions existed, then I think everyone would be able to have
> their pie as they want it.  -sc
> 

I already have a function in contrib/dblink, currently called 
dblink_strtok(), which I was going to turn into a builtin function per 
recent discussion (renamed of course). It would work for this but is 
more general:

dblink_strtok(text inputstring, text delimiter, int posn) RETURNS text

Inputs
   inputstring
     any string you want to parse a token out of;
     e.g. 'f=1&g=3&h=4'
   delimiter
     a single character to use as the delimiter;
     e.g. '&' or '='
   posn
     the position of the token of interest, 0 based;
     e.g. 1

Should it be called splitstr() (similar to substr())?

Joe

Re: Open 7.3 items

Andrew Sullivan <andrew@libertyrms.info> — 2002-08-14T14:23:24Z

On Wed, Aug 14, 2002 at 12:11:10AM -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have no personal preference between period and @ or whatever.  See if
> > you can get some other votes for @ because most left @ when the ORDER BY
> > idea came up from Marc.
> 
> FWIW, I still lean to username@database, so I think we're roughly at a
> tie.  It would be good to get more votes ...

My non-coding vote goes to user@database, too.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: Open 7.3 items

Hannu Krosing <hannu@tm.ee> — 2002-08-14T15:27:37Z

On Wed, 2002-08-14 at 16:08, Joe Conway wrote:
> I already have a function in contrib/dblink, currently called 
> dblink_strtok(), which I was going to turn into a builtin function per 
> recent discussion (renamed of course). It would work for this but is 
> more general:
> 
> dblink_strtok(text inputstring, text delimiter, int posn) RETURNS text
> 
> Inputs
>    inputstring
>      any string you want to parse a token out of;
>      e.g. 'f=1&g=3&h=4'
>    delimiter
>      a single character to use as the delimiter;
>      e.g. '&' or '='
>    posn
>      the position of the token of interest, 0 based;
>      e.g. 1
> 
> Should it be called splitstr() (similar to substr())?

What about functions

1. split(text,text,int) returns text

2. split(text,text) returns text[]

and why not

3. split(text,text,text) returns text

which returns text from $1 delimited by $2 and $3

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

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T16:12:23Z

On Tuesday 13 August 2002 07:21 pm, Sander Steffann wrote:
> I think choosing . as the delimiter is a dangerous choice... People have
> not expected it to be special until now, so maybe another character can be
> chosen? I would suggest a colon if possible, so you would get dbname:user.
> I don't expect that a lot of people use a colon as the dbname or username,
> but I could be very wrong here.

The choices have been enumerated as . and @.  I personally vote for either:
user@db
OR
db!user
(sorry, having been a UUCP node admin shows at times...)  To my eyes the bang 
notation is more of a 'divider' than the @.  Unless there is some _really_ 
good reason to not use !, that is. :-)
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Alvaro Herrera <alvherre@atentus.com> — 2002-08-14T16:25:08Z

ngpg@grymmjack.com dijo: 

> > OK, the vote is not shifting from '.' to '@'.  Is that how we want to
> > go?  I like the pg_user enhancement.  Marc, comments?  This was your
> > baby. 
> 
> Would it be hard to setup an internal PG variable for the actual character 
> to be used?

That'd be good, because almost any character people wants to use as
delimiter is actually valid in database and user names.  So giving
people a choice is a good thing.

For example someone may want to use email address as usernames, and that
messes up the splitting on @.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Cuando miro a alguien, mas me atrae como cambia que quien es" (J. Binoche)

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T17:04:20Z

We are clearly going for user@db now.

---------------------------------------------------------------------------

Lamar Owen wrote:
> On Tuesday 13 August 2002 07:21 pm, Sander Steffann wrote:
> > I think choosing . as the delimiter is a dangerous choice... People have
> > not expected it to be special until now, so maybe another character can be
> > chosen? I would suggest a colon if possible, so you would get dbname:user.
> > I don't expect that a lot of people use a colon as the dbname or username,
> > but I could be very wrong here.
> 
> The choices have been enumerated as . and @.  I personally vote for either:
> user@db
> OR
> db!user
> (sorry, having been a UUCP node admin shows at times...)  To my eyes the bang 
> notation is more of a 'divider' than the @.  Unless there is some _really_ 
> good reason to not use !, that is. :-)
> -- 
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-14T17:16:46Z

Bruce Momjian writes:

> I had to add to initdb to create a file /data/PG_INSTALLER and have the
> postmaster read that on startup to determine the installing user.

I object to treating one user specially.  There should be a general
mechanism, such as a separate column in pg_shadow.

I also object to fixing the name during initdb.  We just got rid of that
requirement.

If it mattered, I would also object to the choice of the file name.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T17:19:57Z

OK, what I didn't want to do we to over-complexify something that is for
only a few users.  In a way that user has to be special for this case
because of the requirement that at least one person be able to connect
when you flip that flag.

Also, I don't want to add a column to pg_shadow.  Seems like overkill.

Please suggest another name for the file.

Basically, I am not going to stop working on something when one person
objects or this will never get done, and I think we have had enough
feedback on this that people do want this done.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I had to add to initdb to create a file /data/PG_INSTALLER and have the
> > postmaster read that on startup to determine the installing user.
> 
> I object to treating one user specially.  There should be a general
> mechanism, such as a separate column in pg_shadow.
> 
> I also object to fixing the name during initdb.  We just got rid of that
> requirement.
> 
> If it mattered, I would also object to the choice of the file name.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T17:35:25Z

This email brings up another issue I have seen recently.  The use of the
word "object", "strongly object", or "*object*" with stars is a very
confrontational way to express things.  It does not foster discussion;
it really puts your heal in the ground and presents a very unswerving
attitude when it really isn't necessary nor valuable.

It is not just this email, but several people on this list who are doing
that now, and it is making for more negative discussions.  Thomas has
mentioned it too.

As I have said before, everyone gets one vote.  It doesn't matter how
hard to "object" to something. It is the force of your argument that
affects the votes, not how strongly you express your dislike of
something.

One effect of this environment is that you end up coding to avoid
"objections" rather than coding to meet users needs.  Certainly the
people who express objections are providing valuable feedback to help
improve patches/features, but it should be done in a way that doesn't
give the impression they are in a courtroom and when you post something
incorrect, some lawyer is going to jump up and yell "object".

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I had to add to initdb to create a file /data/PG_INSTALLER and have the
> > postmaster read that on startup to determine the installing user.
> 
> I object to treating one user specially.  There should be a general
> mechanism, such as a separate column in pg_shadow.
> 
> I also object to fixing the name during initdb.  We just got rid of that
> requirement.
> 
> If it mattered, I would also object to the choice of the file name.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T17:48:56Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In a way that user has to be special for this case
> because of the requirement that at least one person be able to connect
> when you flip that flag.

Why does anyone need to be special?  The behavior should be to try the
given user name, and if that's not found then to try user@db.  I see no
need to special-case any user.

> Basically, I am not going to stop working on something when one person
> objects or this will never get done,

He didn't say to stop working on it.  He said to fix the misdesigned
parts.  And I quite agree that those parts are misdesigned.

			regards, tom lane

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-14T18:03:25Z

I believe the dictionary meaning of 'object' in this context would be 'a
cause for concern or attention'.  Each of Peters uses of the word is
highly appropriate, as he was concerned and I'd agree with the
sentiments that those concepts needed attention.

Anyway, object with stars and strongly object are definitely leaning
towards abuse of the word.

On Wed, 2002-08-14 at 13:35, Bruce Momjian wrote:
> 
> This email brings up another issue I have seen recently.  The use of the
> word "object", "strongly object", or "*object*" with stars is a very

> > > I had to add to initdb to create a file /data/PG_INSTALLER and have the
> > > postmaster read that on startup to determine the installing user.
> > 
> > I object to treating one user specially.  There should be a general
> > mechanism, such as a separate column in pg_shadow.
> > 
> > I also object to fixing the name during initdb.  We just got rid of that
> > requirement.
> > 
> > If it mattered, I would also object to the choice of the file name.

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T18:24:50Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In a way that user has to be special for this case
> > because of the requirement that at least one person be able to connect
> > when you flip that flag.
> 
> Why does anyone need to be special?  The behavior should be to try the
> given user name, and if that's not found then to try user@db.  I see no
> need to special-case any user.

Oh, so try it with and without.  I can do that, but it seems more of a
security problem where you were trying two names instead of one.  Do
people like that?  It is easy to do, except for the fact we have to
match pg_hba.conf with a username, though we could do the double-test
there too, if that isn't too weird.

> > Basically, I am not going to stop working on something when one person
> > objects or this will never get done,
> 
> He didn't say to stop working on it.  He said to fix the misdesigned
> parts.  And I quite agree that those parts are misdesigned.

I will fix them as long as the fixes don't generate new objections, like
adding a new column to pg_shadow.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T18:34:38Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Oh, so try it with and without.  I can do that, but it seems more of a
> security problem where you were trying two names instead of one.  Do
> people like that?

The nice thing about it is you can have any combination of people with
installation-wide access (create them as joeblow) and people with
one-database access (create them as joeblow@joesdatabase).  A special
case for only the postgres user is much less flexible.

> It is easy to do, except for the fact we have to
> match pg_hba.conf with a username, though we could do the double-test
> there too, if that isn't too weird.

It'd probably be better to first look at the flat-file copy of pg_shadow
to determine whether user or user@database is the form to use, and then
run through pg_hba.conf only once using the correct form.  Otherwise
there are going to be all sorts of weird corner cases: user might match
a different pg_hba row than user@database does.

Also, if you do it this way then the substitution only has to be done in
one place: you can pass down the correct form to the backend, which'd
otherwise have to repeat the test to see which username is found.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T18:38:04Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Oh, so try it with and without.  I can do that, but it seems more of a
> > security problem where you were trying two names instead of one.  Do
> > people like that?
> 
> The nice thing about it is you can have any combination of people with
> installation-wide access (create them as joeblow) and people with
> one-database access (create them as joeblow@joesdatabase).  A special
> case for only the postgres user is much less flexible.

Oh, yes, clearly a nice addition, but see below.

> > It is easy to do, except for the fact we have to
> > match pg_hba.conf with a username, though we could do the double-test
> > there too, if that isn't too weird.
> 
> It'd probably be better to first look at the flat-file copy of pg_shadow
> to determine whether user or user@database is the form to use, and then
> run through pg_hba.conf only once using the correct form.  Otherwise
> there are going to be all sorts of weird corner cases: user might match
> a different pg_hba row than user@database does.

Problem is that pg_shadow flat file _only_ has users with passwords.  I
do a btree search of that file, but I am not sure I want to add a dump
of _all_ users just to allow this.  Do we?

> Also, if you do it this way then the substitution only has to be done in
> one place: you can pass down the correct form to the backend, which'd
> otherwise have to repeat the test to see which username is found.

Yes, certainly a big win.  What we _could_ do is to allow connections to
template1 be unsuffixed by the dbname, but that makes everyone
connecting to template1 have problems, and just seemed too weird.

Ideas?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-14T18:40:40Z

On Wed, 2002-08-14 at 14:34, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Oh, so try it with and without.  I can do that, but it seems more of a
> > security problem where you were trying two names instead of one.  Do
> > people like that?
> 
> The nice thing about it is you can have any combination of people with
> installation-wide access (create them as joeblow) and people with
> one-database access (create them as joeblow@joesdatabase).  A special
> case for only the postgres user is much less flexible.
> 
> > It is easy to do, except for the fact we have to
> > match pg_hba.conf with a username, though we could do the double-test
> > there too, if that isn't too weird.
> 
> It'd probably be better to first look at the flat-file copy of pg_shadow
> to determine whether user or user@database is the form to use, and then
> run through pg_hba.conf only once using the correct form.  Otherwise
> there are going to be all sorts of weird corner cases: user might match
> a different pg_hba row than user@database does.
> 
> Also, if you do it this way then the substitution only has to be done in
> one place: you can pass down the correct form to the backend, which'd
> otherwise have to repeat the test to see which username is found.

If there is a global 'user', then a database specific 'user@database'
should be rejected shouldn't it?  Otherwise we wind up with two
potential 'user@database' users (globals users are really user@<each
database>) but with a single ID.

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T18:51:36Z

On Wednesday 14 August 2002 02:38 pm, Bruce Momjian wrote:
> Tom Lane wrote:
> > The nice thing about it is you can have any combination of people with
> > installation-wide access (create them as joeblow) and people with
> > one-database access (create them as joeblow@joesdatabase).  A special
> > case for only the postgres user is much less flexible.

> > Also, if you do it this way then the substitution only has to be done in
> > one place: you can pass down the correct form to the backend, which'd
> > otherwise have to repeat the test to see which username is found.

> Yes, certainly a big win.  What we _could_ do is to allow connections to
> template1 be unsuffixed by the dbname, but that makes everyone
> connecting to template1 have problems, and just seemed too weird.

> Ideas?

Appending '@template1' to unadorned usernames, and giving inherited rights 
across the installation to users with template1 rights?  Then you have the 
unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have 
access to template1, right?  Or am I misunderstanding the feature?
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T19:04:35Z

Lamar Owen <lamar.owen@wgcr.org> writes:
> Appending '@template1' to unadorned usernames, and giving inherited rights 
> across the installation to users with template1 rights?  Then you have the 
> unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have 
> access to template1, right?

If not, standard things like "psql -l" won't work for lowen@pari.  I don't
think we can get away with a scheme that depends on disallowing access
to template1 for most people.

It should also be noted that the whole point of this little project was
to do something *simple* ... checking access to some other database to
decide what we will allow is getting a bit far afield from simple.

			regards, tom lane

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T19:05:40Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Problem is that pg_shadow flat file _only_ has users with passwords.  I
> do a btree search of that file, but I am not sure I want to add a dump
> of _all_ users just to allow this.  Do we?

Why not?  Doesn't seem like a big penalty ...

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-14T19:29:38Z

On Wed, 14 Aug 2002, Tom Lane wrote:

> Lamar Owen <lamar.owen@wgcr.org> writes:
> > Appending '@template1' to unadorned usernames, and giving inherited rights
> > across the installation to users with template1 rights?  Then you have the
> > unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari wouldn't have
> > access to template1, right?
>
> If not, standard things like "psql -l" won't work for lowen@pari.  I don't
> think we can get away with a scheme that depends on disallowing access
> to template1 for most people.
>
> It should also be noted that the whole point of this little project was
> to do something *simple* ... checking access to some other database to
> decide what we will allow is getting a bit far afield from simple.

Hate to complicate things more, but back to a global username, say
you have user "lowen" that should have access to all databases.  What
happens if there's already a lowen@somedb that's an unprivileged user.
Assuming lowen is a db superuser, what happens in somedb?  If there's
a global user "lowen" and you try to create a lowen@somedb later, will
it be allowed?

One possible simplification would be to make the username the full
username "lowen@somedb", "lowen", ...  Right now we can create a
"lowen@somedb" and it's a different user than "lowen" and we can
already restrict a user to one database, can't we?  Hmmm.  Just
checked and I guess not - I thought we had a record type of "user".

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T19:31:07Z

On Wednesday 14 August 2002 03:04 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > Appending '@template1' to unadorned usernames, and giving inherited
> > rights across the installation to users with template1 rights?  Then you
> > have the unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari
> > wouldn't have access to template1, right?

> If not, standard things like "psql -l" won't work for lowen@pari.  I don't
> think we can get away with a scheme that depends on disallowing access
> to template1 for most people.

Ok, maybe I'm really off base, but if I connect to database pari as 
lowen@pari, isn't pg_database present there?  I just tried here:
createdb pari
psql pari
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

pari=# select datname from pg_database;
  datname
------------
 acs-test
 maillabels
 testing2
 template1
 template0
 pari
(6 rows)

So AFAICT if I were psql I would parse the unadorned lowen as 
'lowen@template1' and connect to template1 if not otherwise specified.  If 
the fully qualified database user (FQDU) is present, parse the database name 
out and connect to that database, then issue the SQL to do the -l or 
whatever.  The @pari would just override the normal default of template1, 
right?  So a 'psql -U lowen@pari -l '  would connect to database pari 
(subject to permissions) and select datname from pg_database there.

What else am I missing, Tom?  ISTM I don't need access to template1 -- 
although I wasn't necessarily suggesting eliminating that.  I was more 
suggesting:
lowen@pari has read access to those parts of template1 necessary for normal 
functioning, full access (subject ot GRANT/REVOKE) of pari, and no access to 
other databases;
lowen@template1 has access across the install (subject to GRANT/REVOKE, of 
course). lowen@template1 = lowen (unadorned).  That was the answer, I 
thought, to the question Bruce had.  There would be NO unadorned usernames 
then, and no special handling EXCEPT of the template1 database, which is 
already a special case.

Now, can we support the idea of 'postgres@pari' being a superuser for pari but 
not for the rest of the install?  Meaning no CREATE DATABASE right, as that 
would require write access to template1?  That's OK I believe, as I would 
assume a 'tied to a database' superuser shouldn't be allowed to create a new 
database to which he isn't going to have access..... The full ramifications 
of this structure could prove interesting.

The supersuperuser 'postgres' becomes postgres@template1 -- template1 becoming 
the consistent default database (for connecting as well as user membership).  
As anything added to template1 becomes part of any subsequently added 
databases, being a user in template1 becomes an installation-wide user.

And the user never really has to explicitly state @template1 -- they could 
just leave off the @template1 and everything works as it does now.

Yes, there are complications, but not great ones, no?
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T19:32:15Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Problem is that pg_shadow flat file _only_ has users with passwords.  I
> > do a btree search of that file, but I am not sure I want to add a dump
> > of _all_ users just to allow this.  Do we?
> 
> Why not?  Doesn't seem like a big penalty ...

Well, in most cases pg_pwd doesn't even get created unless someone has a
password.  We would be creating that file in all cases, or at least in
all cases wher db_user_namespace is set, and again, that is a SIGHUP
param, so you would need to make sure pg_pwd has the right contents if
it was enabled during a sighup.  Frankly, I would recommend a new file
that just contains user names and is always created.

We are basically heading down the road to complexity here.

In fact, pg_hba.conf is just a microcosm of how we are going to handle
pg_shadow matching.  If we create dave@db1, then when dave tries to
connect to db1, he comes in as dave@db1, but when he goes to connect to
db2, if there is a plain 'dave', he will connect as 'dave' to db2, if
possible.

If people are OK with that, then I can easily push the double-testing
down into the authentication system.  It merely means testing the new
pg_hba.conf USER column for two values, and pg_shadow for two values,
but I would test with @db first.

The double testing just seems strange to me because it splits the user
namespace into two parts one with @ and one without, and conflicting
user parts in the two namespaces do interact when @db does not match. 
That seems strange, but hey, if no one else thinks it is strange, it is
easy to code.  It is basically the same as testing pg_pwd, just doing it
later in the code.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T19:36:00Z

On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote:
> Hate to complicate things more, but back to a global username, say
> you have user "lowen" that should have access to all databases.  What
> happens if there's already a lowen@somedb that's an unprivileged user.
> Assuming lowen is a db superuser, what happens in somedb?  If there's
> a global user "lowen" and you try to create a lowen@somedb later, will
> it be allowed?

If the user 'lowen' is then expanded to 'lowen@template1' it would be stored 
that way -- and lowen@template1 is different from lowen@pari, for instance.  
The lowen@template1 user could be a superuser and lowen@pari might not -- but 
they become distinct users.  Although I do understand the difficulty if the 
FQDU isn't stored in full in the appropriate places.  So I guess the solution 
is that wherever a user name is to be stored, the fully qualified form must 
be used and checked against, with @template1 being a 'this user is 
everywhere' shorthand.

But maybe I'm just misunderstanding the implementation.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T19:49:19Z

Lamar Owen wrote:
> On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote:
> > Hate to complicate things more, but back to a global username, say
> > you have user "lowen" that should have access to all databases.  What
> > happens if there's already a lowen@somedb that's an unprivileged user.
> > Assuming lowen is a db superuser, what happens in somedb?  If there's
> > a global user "lowen" and you try to create a lowen@somedb later, will
> > it be allowed?
> 
> If the user 'lowen' is then expanded to 'lowen@template1' it would be stored 
> that way -- and lowen@template1 is different from lowen@pari, for instance.  
> The lowen@template1 user could be a superuser and lowen@pari might not -- but 
> they become distinct users.  Although I do understand the difficulty if the 
> FQDU isn't stored in full in the appropriate places.  So I guess the solution 
> is that wherever a user name is to be stored, the fully qualified form must 
> be used and checked against, with @template1 being a 'this user is 
> everywhere' shorthand.

Yes, Vince is on to something with his quote above.

If we have users with and without @, we get into the situation where
users without @ may become users with @ when their usernames collide
with existing user/db combinations already created.  The point is that
those two namespaces do collide and will cause confusion.

Then you start to get into the situation where you always add @ and make
@template1 a special case.  However, remember that this flag can be
turned on and off after initdb, so you need to be able to get in to set
things up without great complexity _and_ the @template1 would not be
passed in from the client, if for no other reason that the username is
only 32 characters. It is the backend doing the flagging, and therefore
the user can't say 'I am dave@templatge1' vs 'I am dave@connectdb'.

This is how I got to the installuser hack in the first place.  In fact,
even the install user, typically 'postgres' has a problem because if you
create 'postgres@db1', 'postgres' will have trouble connecing to db1 as
themselves. I think we can live with one user who is special/global, but
not more than one because of the confusion it would create.

I can change the way this works, but we need a solution without holes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-14T19:55:15Z

On Wed, 14 Aug 2002, Lamar Owen wrote:

> On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote:
> > Hate to complicate things more, but back to a global username, say
> > you have user "lowen" that should have access to all databases.  What
> > happens if there's already a lowen@somedb that's an unprivileged user.
> > Assuming lowen is a db superuser, what happens in somedb?  If there's
> > a global user "lowen" and you try to create a lowen@somedb later, will
> > it be allowed?
>
> If the user 'lowen' is then expanded to 'lowen@template1' it would be stored
> that way -- and lowen@template1 is different from lowen@pari, for instance.
> The lowen@template1 user could be a superuser and lowen@pari might not -- but
> they become distinct users.  Although I do understand the difficulty if the
> FQDU isn't stored in full in the appropriate places.  So I guess the solution
> is that wherever a user name is to be stored, the fully qualified form must
> be used and checked against, with @template1 being a 'this user is
> everywhere' shorthand.
>
> But maybe I'm just misunderstanding the implementation.

I may be too, but what's wrong with just "lowen" being shorthand for
'this user is everywhere'?  Does it also mean that we'd have a user
postgres@template1?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T20:34:13Z

On Wednesday 14 August 2002 03:55 pm, Vince Vielhaber wrote:
> On Wed, 14 Aug 2002, Lamar Owen wrote:
> > If the user 'lowen' is then expanded to 'lowen@template1' it would be
> > stored that way -- and lowen@template1 is different from lowen@pari, for

> > But maybe I'm just misunderstanding the implementation.
>
> I may be too, but what's wrong with just "lowen" being shorthand for
> 'this user is everywhere'?  Does it also mean that we'd have a user
> postgres@template1?

WE could still use the form without @template1, but the backend would assume 
the @template1 user was being meant when the unqualified shorthand was used.  
So the former plain 'postgres' user could still be such to us, to client 
programs, etc, but the backend would assume that that meant 
postgres@template1 -- no namespace collision, and the special case is that 
anyone@template1 has the behavior the unadorned plain user now has.

I do see Bruce's points, however.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-14T21:01:06Z

On Wednesday 14 August 2002 03:49 pm, Bruce Momjian wrote:
> Lamar Owen wrote:
> > On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote:
> > > Hate to complicate things more, but back to a global username, say
> > > you have user "lowen" that should have access to all databases.  What

> > places.  So I guess the solution is that wherever a user name is to be
> > stored, the fully qualified form must be used and checked against, with
> > @template1 being a 'this user is everywhere' shorthand.

> Yes, Vince is on to something with his quote above.

> If we have users with and without @, we get into the situation where
> users without @ may become users with @ when their usernames collide
> with existing user/db combinations already created.  The point is that
> those two namespaces do collide and will cause confusion.

But that's the exact problem I was trying to address -- as far as the backend 
is concerned, there isn't a user without @ -- the incoming connection from a 
user without @ is translated into a connection coming from user@template1.

> Then you start to get into the situation where you always add @ and make
> @template1 a special case.  However, remember that this flag can be
> turned on and off after initdb, so you need to be able to get in to set
> things up without great complexity _and_ the @template1 would not be
> passed in from the client, if for no other reason that the username is
> only 32 characters. It is the backend doing the flagging, and therefore
> the user can't say 'I am dave@templatge1' vs 'I am dave@connectdb'.

Ok, how do I as a client specify the @dbname for the user?  By the database 
I'm connecting to?  That IS a wrinkle.  But it does make sense, as lowen@pari 
won't be able to connect to any other database, right?  So, where's this new 
notation going to get used, again?

I must have misunderstood something.

So, if we have a namespace collision -- then we have to make the 
implementation have the restriction that a global username can't exist as a 
database-specific username -- but two or more database-specific usernames can 
be the same.  So, have a trigger on insertion of a user that checks for an 
existing user attached to template1 (again, for consistency -- installation 
wide templates are in template1 -- installation-wide users should be too) -- 
and then aborts the CREATE USER if so.

> This is how I got to the installuser hack in the first place.  In fact,
> even the install user, typically 'postgres' has a problem because if you
> create 'postgres@db1', 'postgres' will have trouble connecing to db1 as
> themselves. I think we can live with one user who is special/global, but
> not more than one because of the confusion it would create.

If you say CREATE USER lowen@pari for the syntax, the create user trips the 
trigger, which checks for lowen@template1 and aborts if so.  CREATE USER 
lowen@template1 does the same, checking for ANY user lowen.  Namespace 
collision averted?  CREATE USER lowen would be the same as CREATE USER 
lowen@connecteddb, so that the subsuperuser for connecteddb can just CREATE 
USER without qualifying -- the command line createdb could take the @dbname 
argument, splitting it out and connecting to the proper database.  This has 
ramifications, I admit.  And just saying that unqualified CREATE USER's 
should create the user@template1 introduces its own problems.

> I can change the way this works, but we need a solution without holes.

Trigger on the holes.  But if I can't (or shouldn't) be able to specify the 
@dbname from the client, there is GOING to be a namespace collision if 
installation-wide users of ANY name are allowed (which is what you've already 
said -- just repeating for emphasis).  Or we will have to forbid the postgres 
user from being reused -- trigger on CREATE USER and abort if user=postgres, 
I guess.

Now as to the toggling of the feature -- what happens when you have lowen@pari 
and lowen@wgcr coexisting, and you turn off the feature?  Which password 
becomes valid for the resultant singular user lowen?  IMHO, if two or more 
users of the same name occurs, then you shouldn't be able to turn the feature 
off.

I know you've already put alot of work into this, Bruce.  But what if the 
feature isn't toggled, but always there, just waiting to be exploited by 
CREATE USER user@db, with the default CREATE USER always putting the user 
into association with the currently connected database?  Is there bad 
overhead involved?  Is it something that could break installations not using 
the feature?  Or should CREATE USER with an unqualified username default to 
@template1 (what I originally thought it should).
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T21:44:28Z

Lamar Owen <lamar.owen@wgcr.org> writes:
> So the former plain 'postgres' user could still be such to us, to client 
> programs, etc, but the backend would assume that that meant 
> postgres@template1 -- no namespace collision, and the special case is that 
> anyone@template1 has the behavior the unadorned plain user now has.

The trouble with that scheme is that there is zero interoperability
between the plain-vanilla mode (postgres is postgres in pg_shadow) and
the @-mode (postgres is postgres@template1 in pg_shadow).  Flip the
configuration switch, in either direction, and you can't log in anymore.
We'd almost have to make it a frozen-at-initdb setting so that initdb
would know which form to put into pg_shadow for the superuser, and so
that entry wouldn't break thereafter.

The reason I like the "lowen" vs "lowen@somedb" pattern is that
database-global users can log in the same way whether the feature is
turned on or not; this eliminates the getting-started problem, as well
as the likelihood of shooting yourself in the foot.

It is true that if you have a global user lowen you'd want to avoid
creating any local users lowen@somedb, and that the existing code
wouldn't be able to enforce that.  We could possibly add a few lines
to CREATE USER to warn about this mistake.  (It should be a warning not
an error, since if you have no intention of ever using the @-feature
then there's no reason to restrict your choice of usernames.)

			regards, tom lane

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-14T22:02:58Z

Bruce Momjian writes:

> OK, what I didn't want to do we to over-complexify

That's reasonable, but not when you break other things along the way that
were themselves meant to decomplexify things.

> something that is for only a few users.

If it's only for a few users, please send private patches to them.  Face
it, it's not going to happen.  It's going to be in the release notes,
everyone's going to see it, and there's going to be a Slashdot thread
about how "they" broke the password files.  So let's design a feature for
everyone.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T22:28:26Z

OK, I have a new idea.  Seems most don't like that 'postgres' is a
special user in this context.

How about if we just document that they have to create a
postgres@template1 user before flipping the switch.  That way, there is
no special user, no PG_INSTALLER file, and no double-tests for user
names.

It doesn't give us a global user, but frankly, it seems that such a
system is never going to work reliably.

Trying to prevent namespace conflicts by checking for users without @
that may match will make @ a special character in the user namespace,
and people won't like that.

---------------------------------------------------------------------------

Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > So the former plain 'postgres' user could still be such to us, to client 
> > programs, etc, but the backend would assume that that meant 
> > postgres@template1 -- no namespace collision, and the special case is that 
> > anyone@template1 has the behavior the unadorned plain user now has.
> 
> The trouble with that scheme is that there is zero interoperability
> between the plain-vanilla mode (postgres is postgres in pg_shadow) and
> the @-mode (postgres is postgres@template1 in pg_shadow).  Flip the
> configuration switch, in either direction, and you can't log in anymore.
> We'd almost have to make it a frozen-at-initdb setting so that initdb
> would know which form to put into pg_shadow for the superuser, and so
> that entry wouldn't break thereafter.
> 
> The reason I like the "lowen" vs "lowen@somedb" pattern is that
> database-global users can log in the same way whether the feature is
> turned on or not; this eliminates the getting-started problem, as well
> as the likelihood of shooting yourself in the foot.
> 
> It is true that if you have a global user lowen you'd want to avoid
> creating any local users lowen@somedb, and that the existing code
> wouldn't be able to enforce that.  We could possibly add a few lines
> to CREATE USER to warn about this mistake.  (It should be a warning not
> an error, since if you have no intention of ever using the @-feature
> then there's no reason to restrict your choice of usernames.)
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T22:56:26Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How about if we just document that they have to create a
> postgres@template1 user before flipping the switch.  That way, there is
> no special user, no PG_INSTALLER file, and no double-tests for user
> names.

... and no useful superuser account; if you can't connect to anything
except template1 then you ain't much of a superuser.

To get around that you'd have to create postgres@db1, postgres@db2,
postgres@db3, etc etc.  This would be a huge pain in the neck; I think
it'd render the scheme impractical.  (Keep in mind that anybody who'd be
interested in this feature at all has probably got quite a number of
databases to contend with.)

			regards, tom lane

Re: Open 7.3 items

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2002-08-14T23:30:05Z

On Wed, 14 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have no personal preference between period and @ or whatever.  See if
> > you can get some other votes for @ because most left @ when the ORDER BY
> > idea came up from Marc.
> 
> FWIW, I still lean to username@database, so I think we're roughly at a
> tie.  It would be good to get more votes ...

Seeing as this is rumbling on I'll throw in my fraction of a vote.

I too like the user@database form, partly because it 'reads'. On the other hand
I can see the the reasons to like database.user and it does match the style of
database.schema.object.

Unfortunately for this second form, as '.' is a valid character in a database
name then I can see this causing problems, especially with the behind the
scenes combination of the two names. I don't see this problem with the '@' form
because I can't see that character being used in a 'unqualified' user name.
Hmmm...not sure that makes a terribly good arguement for my vote for 'user@db',
is there a third choice for us confused folks to go for? A
compromise: database@username ?

[BTW, I did check and '@' seems to be a valid character in database and user
names.]

-- 
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-14T23:44:25Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How about if we just document that they have to create a
> > postgres@template1 user before flipping the switch.  That way, there is
> > no special user, no PG_INSTALLER file, and no double-tests for user
> > names.
> 
> ... and no useful superuser account; if you can't connect to anything
> except template1 then you ain't much of a superuser.
> 
> To get around that you'd have to create postgres@db1, postgres@db2,
> postgres@db3, etc etc.  This would be a huge pain in the neck; I think
> it'd render the scheme impractical.  (Keep in mind that anybody who'd be
> interested in this feature at all has probably got quite a number of
> databases to contend with.)

Yes, I hear you, but that brings us around full-circle to the original
patch with one super-user who is the install user. 

I don't know where else to go with the patch at this point.  I think
increasing the number of 'global' users is polluting the namespace too
much, and having none seems to be unappealing.  This is why I am back to
just the install user.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-14T23:58:31Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't know where else to go with the patch at this point.  I think
> increasing the number of 'global' users is polluting the namespace too
> much,

Why?  If the installation needs N global users, then it needs N global
users; who are you to make that value judgment for them?

In practice I think an installation that's using this feature is going
to have a pretty small number of global users, and so the issue of
collisions with local usernames isn't really as big as it's been painted
in this thread.  We could ignore that issue (except for documenting it)
and have a perfectly serviceable feature.

But I don't think it's a wise idea to design the thing in a way that
makes it impossible to have more than one global user.

If you don't like including all the pg_shadow entries in the flat file
(though I really don't see any problem with that), could we replace
PG_INSTALL with a pg_global_users config file that lists the global user
names?  I think it would be good enough to let this be hand-maintained,
with initdb initializing it to contain the install user's name.

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-14T23:59:33Z

On Wed, 14 Aug 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > How about if we just document that they have to create a
> > > postgres@template1 user before flipping the switch.  That way, there is
> > > no special user, no PG_INSTALLER file, and no double-tests for user
> > > names.
> >
> > ... and no useful superuser account; if you can't connect to anything
> > except template1 then you ain't much of a superuser.
> >
> > To get around that you'd have to create postgres@db1, postgres@db2,
> > postgres@db3, etc etc.  This would be a huge pain in the neck; I think
> > it'd render the scheme impractical.  (Keep in mind that anybody who'd be
> > interested in this feature at all has probably got quite a number of
> > databases to contend with.)
>
> Yes, I hear you, but that brings us around full-circle to the original
> patch with one super-user who is the install user.
>
> I don't know where else to go with the patch at this point.  I think
> increasing the number of 'global' users is polluting the namespace too
> much, and having none seems to be unappealing.  This is why I am back to
> just the install user.

I wouldn't be in favor of that.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T00:30:47Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I don't know where else to go with the patch at this point.  I think
> > increasing the number of 'global' users is polluting the namespace too
> > much,
> 
> Why?  If the installation needs N global users, then it needs N global
> users; who are you to make that value judgment for them?
> 
> In practice I think an installation that's using this feature is going
> to have a pretty small number of global users, and so the issue of
> collisions with local usernames isn't really as big as it's been painted
> in this thread.  We could ignore that issue (except for documenting it)
> and have a perfectly serviceable feature.

The original idea was that Marc wanted people who could create their own
users for their own databases.  If we make the creation of global users
too easy, all of a sudden people don't have control over their db
usernames because they have to avoid all the global user names already
defined.  By adding multiple global users, it is diluting the usefulness
of the feature.

I suppose a pg_global_users file would be a compromise because only the
admin could actually add people to that file.  If it was more automatic,
like writing pg_shadow, someone could create a user without an @ and
block access for other users to other database, which is bad.

I still don't like the fact that people think they have control over
their db namespace, when they really don't, but no one else seems to see
that as a problem.  The namespace conflicts just yell of poor design.

OK, I have another idea.  What if we make global users end with an @, so
dave@ is a global user.  We can easily check for that in the postmaster
and not append the dbname.  I know it makes @ a special character, but
considering the problem of namespace collision, it seems better than
what we have now.  We could add the install user too if we wish, or just
tell them to make sure they add a user@ before turning on the feature.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

ngpg@grymmjack.com — 2002-08-15T01:57:51Z

pgman@candle.pha.pa.us (Bruce Momjian) wrote:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> > I don't know where else to go with the patch at this point.  I
>> > think increasing the number of 'global' users is polluting the
>> > namespace too much,
>> 
>> Why?  If the installation needs N global users, then it needs N
>> global users; who are you to make that value judgment for them?
>> 
>> In practice I think an installation that's using this feature is
>> going to have a pretty small number of global users, and so the issue
>> of collisions with local usernames isn't really as big as it's been
>> painted in this thread.  We could ignore that issue (except for
>> documenting it) and have a perfectly serviceable feature.
> 
> The original idea was that Marc wanted people who could create their
> own users for their own databases.  If we make the creation of global
> users too easy, all of a sudden people don't have control over their
> db usernames because they have to avoid all the global user names
> already defined.  By adding multiple global users, it is diluting the
> usefulness of the feature.
> 

Maybe I am missing something here but shouldnt db access really be part
of the privileges system?  If all we are talking about is a quick hack
until this can be implemented correctly, what is the concern with having
so much functionality in the hack?  Why does it matter what the actual
usernames can or cant be?  For example you could just make everyone with
a username NNNNNN@dbname (where N's are int) local accounts and then
leave everything else alone.  The only issue I could see with something
like this would be that someone trying to use this hack wont be able to
give their users names like pudgy@dbname, but who cares?  I mean if you
are giving access to a bunch of developers, how is it going to affect
them if you tell them to login with 123456@yourdb instead of
jsmith@yourdb?  If they cant remember it or something maybe they can
write it down?  I dunno...

Re: Open 7.3 items

Joe Conway <mail@joeconway.com> — 2002-08-15T06:12:12Z

Hannu Krosing wrote:
> What about functions
> 
> 1. split(text,text,int) returns text
> 
> 2. split(text,text) returns text[]
> 
> and why not
> 
> 3. split(text,text,text) returns text
> 
> which returns text from $1 delimited by $2 and $3

Given the time remaining before beta, I'll be happy just to get #1 done.

I can see the utility of #2 (or perhaps even a table function which 
breaks the string into individual rows). I'm not sure I understand #3.

I am concerned about the name though -- only in that there are usually 
objections raised to function names that are too likely to conflict with 
user created function names. But "split" is good from the standpoint 
that it is used in other languages, so people should find it familiar.

Anyone have comments on the name?

Joe

Re: Open 7.3 items

Mike Mascari <mascarm@mascari.com> — 2002-08-15T07:27:06Z

Joe Conway wrote:
> 
> Hannu Krosing wrote:
> > What about functions
> >
> > 1. split(text,text,int) returns text
> >
> > 2. split(text,text) returns text[]
> >
> > and why not
> >
> > 3. split(text,text,text) returns text
> >
> > which returns text from $1 delimited by $2 and $3
> 
> Given the time remaining before beta, I'll be happy just to get #1 done.
> 
> I can see the utility of #2 (or perhaps even a table function which
> breaks the string into individual rows). I'm not sure I understand #3.
> 
> I am concerned about the name though -- only in that there are usually
> objections raised to function names that are too likely to conflict with
> user created function names. But "split" is good from the standpoint
> that it is used in other languages, so people should find it familiar.
> 
> Anyone have comments on the name?

Actually, I've been wondering if it wouldn't be a good idea with schemas
coming to think now about how to divide up namespaces for all sorts of
things, including PostgreSQL's built in functions, the contrib code,
etc. I think a naming scheme with which both PostgreSQL and the
community would comply, a la Java's reverse DNS scheme for namespaces
would be neat. So when a database is installed, the following schemas
are automatically created:

org.postgresql.system <- System tables and core functions
org.postgresql.text <- Text related functions
org.postgresql.math <- Math related functions
org.postgresql.fts <- Full-Text schema

or perhaps:

org.postgresql.contrib.fts <- Full-Text schema

etc.

I don't even know if "." is allowed in the schema names, but you get the
idea. Then, a users search_path (or whatever it's called, I haven't used
the development version in a while), would be the equivalent of Java's
"import" statement, or C++'s "using" statement. So "split" would be a
function in the org.postgresql.text schema.

How about them apples?

If this is an insane idea, its 3:32 A.M. my time ;-)

Mike Mascari
mascarm@mascari.com

> 
> Joe

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-15T13:18:30Z

Mike Mascari <mascarm@mascari.com> writes:
> I don't even know if "." is allowed in the schema names,

It isn't, and we couldn't invent such a scheme without seriously
diverging from SQL compliance: the next naming level up from schemas is
reserved for catalogs (think databases).  I don't know that we'll ever
support cross-database access, but we shouldn't foreclose the
possibility in pursuit of a naming scheme that doesn't really add very
much value.

You could possibly fake it with schema names like org_postgresql_foo,
but I can't get very excited about that ...

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T15:54:17Z

OK, no one complained/commented on my idea of having global users have a
trailing '@', so here is a patch that implements that.  It has the
advantages of:

	no special install user (create global user before enabling feature)
	no /data/PG_INSTALLER file
	allows multiple global users to be easily added
	no namespace collisions because globals have a trailing @
	easy for postmaster to recognize global users
	no double-user lookups of pg_pwd changes
	very small patch footprint

The only downside is that it treats '@' as a special character when it
is enabled, but frankly, because we are appending @dbname anyway, having
'@' as a special character in that case makes sense.

Comments?

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > I don't know where else to go with the patch at this point.  I think
> > > increasing the number of 'global' users is polluting the namespace too
> > > much,
> > 
> > Why?  If the installation needs N global users, then it needs N global
> > users; who are you to make that value judgment for them?
> > 
> > In practice I think an installation that's using this feature is going
> > to have a pretty small number of global users, and so the issue of
> > collisions with local usernames isn't really as big as it's been painted
> > in this thread.  We could ignore that issue (except for documenting it)
> > and have a perfectly serviceable feature.
> 
> The original idea was that Marc wanted people who could create their own
> users for their own databases.  If we make the creation of global users
> too easy, all of a sudden people don't have control over their db
> usernames because they have to avoid all the global user names already
> defined.  By adding multiple global users, it is diluting the usefulness
> of the feature.
> 
> I suppose a pg_global_users file would be a compromise because only the
> admin could actually add people to that file.  If it was more automatic,
> like writing pg_shadow, someone could create a user without an @ and
> block access for other users to other database, which is bad.
> 
> I still don't like the fact that people think they have control over
> their db namespace, when they really don't, but no one else seems to see
> that as a problem.  The namespace conflicts just yell of poor design.
> 
> OK, I have another idea.  What if we make global users end with an @, so
> dave@ is a global user.  We can easily check for that in the postmaster
> and not append the dbname.  I know it makes @ a special character, but
> considering the problem of namespace collision, it seems better than
> what we have now.  We could add the install user too if we wish, or just
> tell them to make sure they add a user@ before turning on the feature.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T16:06:31Z

On Thu, 15 Aug 2002, Bruce Momjian wrote:

>
> OK, no one complained/commented on my idea of having global users have a
> trailing '@', so here is a patch that implements that.  It has the
> advantages of:

Probably because not everyone saw it.  I know I didn't.  This entire
issue is growing more and more complex.  How about a configure item
to not even compile it in?  Or better yet, a configure item to put
it there with the default off.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T16:13:25Z

Vince, you were in the CC, and it went to hackers:
	
	Message 772/835 Bruce Momjian                                           
	                Aug 14, 2002 08:30:47 pm -0400
	Subject: Re: [HACKERS] Open 7.3 items
	To: Tom Lane <tgl@sss.pgh.pa.us>
	Date: Wed, 14 Aug 2002 20:30:47 -0400 (EDT)
	cc: Lamar Owen <lamar.owen@wgcr.org>, Vince Vielhaber <vev@michvhf.com>,
	           PostgreSQL-development <pgsql-hackers@postgresql.org>
	X-Virus-Scanned: by AMaViS new-20020517
	Precedence: bulk
	Sender: pgsql-hackers-owner@postgresql.org
	X-Virus-Scanned: by AMaViS new-20020517

---------------------------------------------------------------------------

Vince Vielhaber wrote:
> On Thu, 15 Aug 2002, Bruce Momjian wrote:
> 
> >
> > OK, no one complained/commented on my idea of having global users have a
> > trailing '@', so here is a patch that implements that.  It has the
> > advantages of:
> 
> Probably because not everyone saw it.  I know I didn't.  This entire
> issue is growing more and more complex.  How about a configure item
> to not even compile it in?  Or better yet, a configure item to put
> it there with the default off.
> 
> Vince.
> -- 
> ==========================================================================
> Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
>          56K Nationwide Dialup from $16.00/mo at Pop4 Networking
>       http://www.camping-usa.com      http://www.cloudninegifts.com
>    http://www.meanstreamradio.com       http://www.unknown-artists.com
> ==========================================================================
> 
> 
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T16:16:32Z

Vince Vielhaber wrote:
> On Thu, 15 Aug 2002, Bruce Momjian wrote:
> 
> >
> > OK, no one complained/commented on my idea of having global users have a
> > trailing '@', so here is a patch that implements that.  It has the
> > advantages of:
> 
> Probably because not everyone saw it.  I know I didn't.  This entire
> issue is growing more and more complex.  How about a configure item
> to not even compile it in?  Or better yet, a configure item to put
> it there with the default off.

I think I am prety close, and I don't see a configure flag as any better
than a GUC option that is off by default.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T16:34:40Z

On Thu, 15 Aug 2002, Bruce Momjian wrote:

>
> Vince, you were in the CC, and it went to hackers:

Oh, I'm not saying I didn't get it, I'm saying I didn't see it in
the message.  It looked as if you were only replying to Tom so after
reading the jist of it I moved on.  When you included it a little
while ago I wondered what you were referring to so I read the whole
thing more carefully and realized that I missed the end.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T16:38:45Z

On Thu, 15 Aug 2002, Bruce Momjian wrote:

> Vince Vielhaber wrote:
> > On Thu, 15 Aug 2002, Bruce Momjian wrote:
> >
> > >
> > > OK, no one complained/commented on my idea of having global users have a
> > > trailing '@', so here is a patch that implements that.  It has the
> > > advantages of:
> >
> > Probably because not everyone saw it.  I know I didn't.  This entire
> > issue is growing more and more complex.  How about a configure item
> > to not even compile it in?  Or better yet, a configure item to put
> > it there with the default off.
>
> I think I am prety close, and I don't see a configure flag as any better
> than a GUC option that is off by default.

But how many people would even use it?  I can't see adding the bloat
unnecessarily and risking it accidently being turned on.  Am I wrong
and really alot of people actually want/need this?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T16:41:10Z

Vince Vielhaber wrote:
> On Thu, 15 Aug 2002, Bruce Momjian wrote:
> 
> > Vince Vielhaber wrote:
> > > On Thu, 15 Aug 2002, Bruce Momjian wrote:
> > >
> > > >
> > > > OK, no one complained/commented on my idea of having global users have a
> > > > trailing '@', so here is a patch that implements that.  It has the
> > > > advantages of:
> > >
> > > Probably because not everyone saw it.  I know I didn't.  This entire
> > > issue is growing more and more complex.  How about a configure item
> > > to not even compile it in?  Or better yet, a configure item to put
> > > it there with the default off.
> >
> > I think I am prety close, and I don't see a configure flag as any better
> > than a GUC option that is off by default.
> 
> But how many people would even use it?  I can't see adding the bloat
> unnecessarily and risking it accidently being turned on.  Am I wrong
> and really alot of people actually want/need this?

Well, the demand seems to be larger than I thought, considering the
number of people who have chimed in and want certain features, like
multiple global users.  I see this being using more by ISP's and
universities that need better user/db partitioning.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T16:50:58Z

On Thu, 15 Aug 2002, Bruce Momjian wrote:

> Vince Vielhaber wrote:
> > On Thu, 15 Aug 2002, Bruce Momjian wrote:
> >
> > > Vince Vielhaber wrote:
> > > > On Thu, 15 Aug 2002, Bruce Momjian wrote:
> > > >
> > > > >
> > > > > OK, no one complained/commented on my idea of having global users have a
> > > > > trailing '@', so here is a patch that implements that.  It has the
> > > > > advantages of:
> > > >
> > > > Probably because not everyone saw it.  I know I didn't.  This entire
> > > > issue is growing more and more complex.  How about a configure item
> > > > to not even compile it in?  Or better yet, a configure item to put
> > > > it there with the default off.
> > >
> > > I think I am prety close, and I don't see a configure flag as any better
> > > than a GUC option that is off by default.
> >
> > But how many people would even use it?  I can't see adding the bloat
> > unnecessarily and risking it accidently being turned on.  Am I wrong
> > and really alot of people actually want/need this?
>
> Well, the demand seems to be larger than I thought, considering the
> number of people who have chimed in and want certain features, like
> multiple global users.  I see this being using more by ISP's and
> universities that need better user/db partitioning.

I don't know that concern over a possible limited number of global
users is directly proportional to the desire for the feature.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-15T16:56:49Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> + /* We append database name if db_user_namespace true. */
> + #define SM_DATABASE_USER (SM_DATABASE+SM_USER)

Is this calculation correct?  I'd think you'd need at least one more
character to allow for the "@".  And I'm not sure about whether trailing
nulls are or need to be counted.  There seem to be some places in your
patch where things are dimensioned SM_DATABASE_USER and some where it's
SM_DATABASE_USER+1; why the inconsistency, and which is right?

Other than getting the array sizes right, it does look like a nice
patch; very small, which is what I'd hoped for.  The notion of having to
say "postgres@" still seems kinda ugly, but given the simplicity of the
patch I'm willing to live with that.

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T17:00:37Z

On Thu, 15 Aug 2002, Tom Lane wrote:

> Other than getting the array sizes right, it does look like a nice
> patch; very small, which is what I'd hoped for.  The notion of having to
> say "postgres@" still seems kinda ugly, but given the simplicity of the
> patch I'm willing to live with that.

Going from postgres to postgres@ ???  I don't care how simple the patch
is, I'd rather it was configurable to keep it out completely.  That's
not just ugly, that's coyote ugly!

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-15T17:04:16Z

On Thursday 15 August 2002 11:54 am, Bruce Momjian wrote:
> OK, no one complained/commented on my idea of having global users have a
> trailing '@', so here is a patch that implements that.  It has the
> advantages of:

As it's substantially the same as user@template1, I am of course OK with it. 
:-)  Easier to type than user@template1, too.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-15T17:07:34Z

Vince Vielhaber <vev@michvhf.com> writes:
> Going from postgres to postgres@ ???  I don't care how simple the patch
> is, I'd rather it was configurable to keep it out completely.  That's
> not just ugly, that's coyote ugly!

Yeah, but it doesn't affect you unless you turn on the GUC parameter.
Most people will never even know this code is there.

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T17:13:52Z

On Thu, 15 Aug 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > Going from postgres to postgres@ ???  I don't care how simple the patch
> > is, I'd rather it was configurable to keep it out completely.  That's
> > not just ugly, that's coyote ugly!
>
> Yeah, but it doesn't affect you unless you turn on the GUC parameter.
> Most people will never even know this code is there.

But it doesn't need to affect anyone, even if it's enabled.  Isn't
the lack of an @ just as good as an @ at the end of the username?
Gets rid of the ugliness and won't break things if it's suddenly
enabled.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-15T17:21:26Z

Vince Vielhaber <vev@michvhf.com> writes:
> But it doesn't need to affect anyone, even if it's enabled.  Isn't
> the lack of an @ just as good as an @ at the end of the username?

No, because there isn't any @ in the incoming connection request in the
normal-user case: just a user name and a database name, which *we* have
to assemble into user@database.

We can't really expect the users to do this for us (give user@database
as their full user name).  There are a number of reasons why I don't
wanna do that, but the real showstopper is that the username field of
the connection request packet is only 32 bytes wide, and we cannot
enlarge it without a protocol breakage.  Fitting "user@database" in 32
bytes would be awfully restrictive about your user and database names.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-15T17:43:01Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > + /* We append database name if db_user_namespace true. */
> > + #define SM_DATABASE_USER (SM_DATABASE+SM_USER)
> 
> Is this calculation correct?  I'd think you'd need at least one more
> character to allow for the "@".  And I'm not sure about whether trailing
> nulls are or need to be counted.  There seem to be some places in your
> patch where things are dimensioned SM_DATABASE_USER and some where it's
> SM_DATABASE_USER+1; why the inconsistency, and which is right?

Yes, there was some inconsistency.  The new patch fixes that up; 
attached.

> Other than getting the array sizes right, it does look like a nice
> patch; very small, which is what I'd hoped for.  The notion of having to
> say "postgres@" still seems kinda ugly, but given the simplicity of the
> patch I'm willing to live with that.

Glad we have something now everyone likes, or at least can live with.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-15T18:10:24Z

> But how many people would even use it?  I can't see adding the bloat
> unnecessarily and risking it accidently being turned on.  Am I wrong
> and really alot of people actually want/need this?

At an absolute minimum there are two.  Myself and Marc.

That said, this is a semi-required step to offerring Postgresql as a
service to clients.  The refined permissions where a much more important
step.

So, take the number of people actively watching -hackers and use that as
a percentage.

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T18:30:34Z

On Thu, 15 Aug 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > But it doesn't need to affect anyone, even if it's enabled.  Isn't
> > the lack of an @ just as good as an @ at the end of the username?
>
> No, because there isn't any @ in the incoming connection request in the
> normal-user case: just a user name and a database name, which *we* have
> to assemble into user@database.
>
> We can't really expect the users to do this for us (give user@database
> as their full user name).  There are a number of reasons why I don't
> wanna do that, but the real showstopper is that the username field of
> the connection request packet is only 32 bytes wide, and we cannot
> enlarge it without a protocol breakage.  Fitting "user@database" in 32
> bytes would be awfully restrictive about your user and database names.

Ok, I misunderstood.  I thought it was the user going to have to type
that in based on some of yesterday's comments.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-15T19:30:45Z

Bruce Momjian writes:

> OK, I have another idea.  What if we make global users end with an @, so
> dave@ is a global user.  We can easily check for that in the postmaster
> and not append the dbname.  I know it makes @ a special character, but
> considering the problem of namespace collision, it seems better than
> what we have now.  We could add the install user too if we wish, or just
> tell them to make sure they add a user@ before turning on the feature.

I don't like where this is going.  The original plan was to create a
feature that was simple and transparent.  Now we have a feature that might
be simple to implement, but is neither simple to understand nor
transparent.  Instead it uglifies the common interfaces.

I don't see what the problem is of dumping out the entire content of
pg_shadow into a flat file.  First you look for a non-@ user, then you
look for an @ user that matches the database.

The interface should drive the implementation, not the other way around.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-15T19:51:46Z

Peter Eisentraut <peter_e@gmx.net> writes:
> I don't see what the problem is of dumping out the entire content of
> pg_shadow into a flat file.  First you look for a non-@ user, then you
> look for an @ user that matches the database.

While I'd prefer that approach myself, the way Bruce is proposing does
have a definite advantage: there is no problem with confusion between
global users and database-local users of the same username.  "foo@" is
global, "foo" is not.

My own feeling is that the confusion argument is a weak one, and that
not having to use "@" to log in as a global user would be worth having
to avoid duplicating global and local names.  But I'm not sufficiently
excited about it to volunteer to do the work ;-)

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-15T20:04:24Z

On Thu, 15 Aug 2002, Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > I don't see what the problem is of dumping out the entire content of
> > pg_shadow into a flat file.  First you look for a non-@ user, then you
> > look for an @ user that matches the database.
>
> While I'd prefer that approach myself, the way Bruce is proposing does
> have a definite advantage: there is no problem with confusion between
> global users and database-local users of the same username.  "foo@" is
> global, "foo" is not.
>
> My own feeling is that the confusion argument is a weak one, and that
> not having to use "@" to log in as a global user would be worth having
> to avoid duplicating global and local names.  But I'm not sufficiently
> excited about it to volunteer to do the work ;-)

Here we go again.  I thought you just said that the @ wouldn't be
something a user would have to do.  I understood that to be any user.
It's back to ugly again.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

ngpg@grymmjack.com — 2002-08-15T23:03:23Z

vev@michvhf.com (Vince Vielhaber) wrote
> Here we go again.  I thought you just said that the @ wouldn't be
> something a user would have to do.  I understood that to be any user.
> It's back to ugly again.
> 
> Vince.

If it means anything to you, I agree that it should be a configure/compile 
time option and not a GUC variable -- no, actually this whole thing should 
just be distributed as diff in contrib and if someone wants it they could 
patch it by hand, thats just as asinine as the current implemenation.

What about actually incorporating this into the privileges system?

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T01:12:02Z

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I don't see what the problem is of dumping out the entire content of
> > pg_shadow into a flat file.  First you look for a non-@ user, then you
> > look for an @ user that matches the database.
> 
> While I'd prefer that approach myself, the way Bruce is proposing does
> have a definite advantage: there is no problem with confusion between
> global users and database-local users of the same username.  "foo@" is
> global, "foo" is not.
> 
> My own feeling is that the confusion argument is a weak one, and that
> not having to use "@" to log in as a global user would be worth having
> to avoid duplicating global and local names.  But I'm not sufficiently
> excited about it to volunteer to do the work ;-)

If we don't suffix global users with '@', a global user named 'dave'
could not attach to a database called 'db1' as himself if a user called
'dave@db1' existed. If you have a super-user, who you want to be able to
connect to any database, the creation of that name in any database would
block the superuser from connecting as themselves.  That is the
confusion I want to avoid.  

I have seen some negative reactions to the feature.  I am willing to ask
for a vote, if that is what people want.  If not, I will apply the patch
in the next day or two.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-16T01:14:42Z

> I have seen some negative reactions to the feature.  I am willing to ask
> for a vote, if that is what people want.  If not, I will apply the patch
> in the next day or two.

Please apply.

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T02:20:14Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> If we don't suffix global users with '@', a global user named 'dave'
> could not attach to a database called 'db1' as himself if a user called
> 'dave@db1' existed.

No, it's the other way around (assuming you check user before user@db):
the existence of a global user would prevent similarly-named local users
from connecting.  This does not strike me as too terrible, assuming that
there are not very many global users.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T04:10:54Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > If we don't suffix global users with '@', a global user named 'dave'
> > could not attach to a database called 'db1' as himself if a user called
> > 'dave@db1' existed.
> 
> No, it's the other way around (assuming you check user before user@db):
> the existence of a global user would prevent similarly-named local users
> from connecting.  This does not strike me as too terrible, assuming that
> there are not very many global users.

Yes, something like that.  It could go either way.  I never actually
coded the double checking.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-16T09:21:45Z

On Thu, 15 Aug 2002, Bruce Momjian wrote:

> I have seen some negative reactions to the feature.  I am willing to ask
> for a vote, if that is what people want.  If not, I will apply the patch
> in the next day or two.

So are you calling for a vote or just willing to ask for one?  I vote for
putting it in contrib and letting whoever wants it apply it and use it.
The more we discuss it the worse it looks.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T13:47:44Z

Vince Vielhaber <vev@michvhf.com> writes:
> So are you calling for a vote or just willing to ask for one?  I vote for
> putting it in contrib and letting whoever wants it apply it and use it.

The trouble with putting it in contrib is that that makes it effectively
unavailable to anyone who installs from RPMs, or otherwise doesn't build
from source for themselves.  Putting a patch diff in contrib is a bad
idea anyway since the patch will suffer bit-rot in no time, as the
referenced files change.

Since the patch is small and doesn't change behavior or performance if
you don't enable the feature, I don't think there's a good reason to
push it off to contrib just because it's ugly.

> The more we discuss it the worse it looks.

I still like the other way better --- but I'm still not prepared to do
the legwork to make it happen, so I have to defer to whatever Bruce is
willing to implement.

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-16T14:21:12Z

On Fri, 16 Aug 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > So are you calling for a vote or just willing to ask for one?  I vote for
> > putting it in contrib and letting whoever wants it apply it and use it.
>
> The trouble with putting it in contrib is that that makes it effectively
> unavailable to anyone who installs from RPMs, or otherwise doesn't build
> from source for themselves.  Putting a patch diff in contrib is a bad
> idea anyway since the patch will suffer bit-rot in no time, as the
> referenced files change.

RPMs aren't a good enough reason to put it in.  All features aren't
installed in an RPM, why would this need to?   Besides, anything that
is runtime configurable can end up getting its default changed on a
whim.  Then again as long as 7.2.1 is stable enough for me there's
no reason to upgrade 'cuze I damn sure ain't going back and changing
all sorts of programs and scripts that have global users.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Lee Kindness <lkindness@csl.co.uk> — 2002-08-16T14:40:15Z

Vince Vielhaber writes:
 > [ 'user@' patch ]
 > whim.  Then again as long as 7.2.1 is stable enough for me there's
 > no reason to upgrade 'cuze I damn sure ain't going back and changing
 > all sorts of programs and scripts that have global users.

Having read bits and pieces of this thread, can those in favour
confirm that this would be an effect of this patch? If so I fail to
see the usefulness of this and indeed it would be very harmful to
existing installations! All use of PostgreSQL utilities in scripts for
our product always do a '-U sprint' to use a global user, this aids
our internal development and makes installation notes for clients
easier...

Also what effect would adding significance to '@' in the context of
usernames have, if any, on the current use of it as a database/host
separator (in ECPG, certainly would be useful in the utilities too)?

Thanks, Lee.

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T14:48:42Z

Lee Kindness <lkindness@csl.co.uk> writes:
> Vince Vielhaber writes:
>>> [ 'user@' patch ]
>>> whim.  Then again as long as 7.2.1 is stable enough for me there's
>>> no reason to upgrade 'cuze I damn sure ain't going back and changing
>>> all sorts of programs and scripts that have global users.

> Having read bits and pieces of this thread, can those in favour
> confirm that this would be an effect of this patch?

I think Vince is talking through his hat.  The proposed flag wouldn't
ever be enabled by default.  If someone did turn it on in their
installation "on a whim", they'd soon turn it off again if they didn't
like the effects.  I do not see much difference between the above
argument and arguing "we shouldn't have i18n support, because if I
turned it on on a whim I wouldn't be able to read my error messages".

Once again: *no one* has at any time suggested that any form of this
patch should affect the default behavior in the slightest.

> Also what effect would adding significance to '@' in the context of
> usernames have, if any, on the current use of it as a database/host
> separator (in ECPG, certainly would be useful in the utilities too)?

Well, I don't see any difficulty there, but if you are aware of a
context where it'd be a problem, point it out!

			regards, tom lane

Re: Open 7.3 items

Ross Reedstrom <reedstrm@rice.edu> — 2002-08-16T14:51:15Z

On Fri, Aug 16, 2002 at 10:21:12AM -0400, Vince Vielhaber wrote:

> RPMs aren't a good enough reason to put it in.  All features aren't
> installed in an RPM, why would this need to?   Besides, anything that
> is runtime configurable can end up getting its default changed on a
> whim.  Then again as long as 7.2.1 is stable enough for me there's
> no reason to upgrade 'cuze I damn sure ain't going back and changing
> all sorts of programs and scripts that have global users.

So, Vince, do you have problems with the various GUC based optimizer
hooks getting set to other than the default? I'd think you'd notice 
if suddenly indexscans all went away, or any of these:

#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

My point is that your resistance to a GUC controlled runtime configurable
on the basis of 'it might get changed accidently' makes little sense to
me, given all the other runtime config settings that never do get changed.
What makes you think this one will be more susceptible to accidental
flipping?

I'm not sure who's 'whim' it is that your afraid of: perhaps you have a
paticularly sadistic DBA to deal with? ;-) And of course, this being 
free software and all, noone is forcing an upgrade on you.

Ross

Re: Open 7.3 items

Larry Rosenman <ler@lerctr.org> — 2002-08-16T15:09:46Z

On Fri, 2002-08-16 at 09:51, Ross J. Reedstrom wrote:
> On Fri, Aug 16, 2002 at 10:21:12AM -0400, Vince Vielhaber wrote:
>  
> > RPMs aren't a good enough reason to put it in.  All features aren't
> > installed in an RPM, why would this need to?   Besides, anything that
> > is runtime configurable can end up getting its default changed on a
> > whim.  Then again as long as 7.2.1 is stable enough for me there's
> > no reason to upgrade 'cuze I damn sure ain't going back and changing
> > all sorts of programs and scripts that have global users.
>  
> So, Vince, do you have problems with the various GUC based optimizer
> hooks getting set to other than the default? I'd think you'd notice 
> if suddenly indexscans all went away, or any of these:
> 
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
> 
> My point is that your resistance to a GUC controlled runtime configurable
> on the basis of 'it might get changed accidently' makes little sense to
> me, given all the other runtime config settings that never do get changed.
> What makes you think this one will be more susceptible to accidental
> flipping?
> 
> I'm not sure who's 'whim' it is that your afraid of: perhaps you have a
> paticularly sadistic DBA to deal with? ;-) And of course, this being 
> free software and all, noone is forcing an upgrade on you.
AND, I thought the general consensus was **AWAY** from configure time
directives and to GUC variables whenever **POSSIBLE**. 

LER
-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T15:45:36Z

Vince Vielhaber wrote:
> On Thu, 15 Aug 2002, Bruce Momjian wrote:
> 
> > I have seen some negative reactions to the feature.  I am willing to ask
> > for a vote, if that is what people want.  If not, I will apply the patch
> > in the next day or two.
> 
> So are you calling for a vote or just willing to ask for one?  I vote for
> putting it in contrib and letting whoever wants it apply it and use it.
> The more we discuss it the worse it looks.

I can do a vote.  However, seeing many positive comments about the
patch, and 1-2 negative ones (with no suggestion on how to improve it),
I don't think the negative votes will win.

I usually do a vote when the email comments are coming in kind of close.

Specifically, in the thread, I have Vince and Peter as negative, and >7
positive, I think.

Look at the contraints I am under to implement what is effectively
username schemas:

	small patch, no bloat, because it isn't a core feature
	multiple global users
	no namespace collisions between global/non-global users
	zero performance impact
	32-byte user string coming from the client

Specifically, what is ugly about it?  Is it that global users have an @
at the end of their names?  How do we prevent namespace collisions
_without_ doing this?  I am all ears.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T16:46:44Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Specifically, what is ugly about it?  Is it that global users have an @
> at the end of their names?  How do we prevent namespace collisions
> _without_ doing this?  I am all ears.

The folks who are unhappy about this design basically think that the
namespace collisions issue should not be considered a vital requirement;
whereupon you don't have to have the '@' because a search in the
pg_shadow flat file would work well enough.

It comes down to a judgment call about which is uglier, putting '@' on
global usernames or having to avoid namespace collisions.

At this point I think we've wasted more than enough time on the
argument; I haven't seen any new ideas recently, nor any change in
anyone's position.  Since no one seems to want to do the work to make a
better implementation, I vote we accept the patch we have and move on.

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-16T17:01:21Z

On Fri, 16 Aug 2002, Tom Lane wrote:

> Lee Kindness <lkindness@csl.co.uk> writes:
> > Vince Vielhaber writes:
> >>> [ 'user@' patch ]
> >>> whim.  Then again as long as 7.2.1 is stable enough for me there's
> >>> no reason to upgrade 'cuze I damn sure ain't going back and changing
> >>> all sorts of programs and scripts that have global users.
>
> > Having read bits and pieces of this thread, can those in favour
> > confirm that this would be an effect of this patch?
>
> I think Vince is talking through his hat.  The proposed flag wouldn't
> ever be enabled by default.  If someone did turn it on in their
> installation "on a whim", they'd soon turn it off again if they didn't
> like the effects.  I do not see much difference between the above
> argument and arguing "we shouldn't have i18n support, because if I
> turned it on on a whim I wouldn't be able to read my error messages".
>
> Once again: *no one* has at any time suggested that any form of this
> patch should affect the default behavior in the slightest.

Not yet they haven't.  What happens when it's decided that this
*feature* is a good thing and should be the default?   Maybe not
now, but can you guarantee that that won't happen in say 7.4?  Or
maybe 8.0?  I can hear it now, "Well we're giving you an entire
version to change your scripts".

There's not even a concensus that this is the right way to do it,
you even said you'd prefer it was implemented in another way but
don't have the time to do it.  Since when does this group rush to
stuff features in without agreement even on HOW to implement it?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T17:30:03Z

Vince Vielhaber wrote:
> > Once again: *no one* has at any time suggested that any form of this
> > patch should affect the default behavior in the slightest.
> 
> Not yet they haven't.  What happens when it's decided that this
> *feature* is a good thing and should be the default?   Maybe not
> now, but can you guarantee that that won't happen in say 7.4?  Or
> maybe 8.0?  I can hear it now, "Well we're giving you an entire
> version to change your scripts".

I can't argue hypothetical with you, but if we decided to make this a
default behavior, we would probably push the functionality down into
CREATE USER, create a new column in pg_shadow, lengthen the username
passed from the client, and do it that way.  However, because it is not
on by default _and_ we don't want to add visibility to a functionality
that is off by default, we are doing it this way.

Remember, non-local users already have an @ in their username.  I am
just adding @ to the global users too. This functionality actually
allows you to keep your old users in pg_shadow and once you turn on the
feature, those users become unusable.  When you turn the feature off,
they are back again.

I know the trailing @ is ugly, but it prevents surpises when connecting
to the database.

> There's not even a consensus that this is the right way to do it,
> you even said you'd prefer it was implemented in another way but
> don't have the time to do it.  Since when does this group rush to
> stuff features in without agreement even on HOW to implement it?

This is an argument I don't want to bow to.  How many features have we
left undone, for release after release, because we couldn't find a
perfect way to do it, so we did nothing, and users went elsewhere for
their database needs?   We have had enough discussion to know that there
isn't a perfect solution in this case, so we are going to implement the
best we can, and if we have to revisit it in 8.0, so be it.  I am sure
you will still be around to help craft that solution.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-16T17:34:05Z

On Fri, 16 Aug 2002, Ross J. Reedstrom wrote:

> On Fri, Aug 16, 2002 at 10:21:12AM -0400, Vince Vielhaber wrote:
>
> > RPMs aren't a good enough reason to put it in.  All features aren't
> > installed in an RPM, why would this need to?   Besides, anything that
> > is runtime configurable can end up getting its default changed on a
> > whim.  Then again as long as 7.2.1 is stable enough for me there's
> > no reason to upgrade 'cuze I damn sure ain't going back and changing
> > all sorts of programs and scripts that have global users.
>
> So, Vince, do you have problems with the various GUC based optimizer
> hooks getting set to other than the default? I'd think you'd notice
> if suddenly indexscans all went away, or any of these:
>
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
>
> My point is that your resistance to a GUC controlled runtime configurable
> on the basis of 'it might get changed accidently' makes little sense to
> me, given all the other runtime config settings that never do get changed.
> What makes you think this one will be more susceptible to accidental
> flipping?

My point has nothing to do with resistance to GUC configurables.  Someone
WILL decide that having it as a default is a *Good Thing* because it's
there and is useful to them and in its current implementation there's not
even a concensus that it's the right way to do it.  It's being rushed into
this version unnecessarily.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T18:17:25Z

Vince Vielhaber <vev@michvhf.com> writes:
> My point has nothing to do with resistance to GUC configurables.  Someone
> WILL decide that having it as a default is a *Good Thing* because it's
> there and is useful to them

Which someone would this be?  There's no chance that such a proposal 
would pass a pghackers vote, and certainly no chance that someone
could commit such a change into CVS without everyone noticing.

> and in its current implementation there's not
> even a concensus that it's the right way to do it.  It's being rushed into
> this version unnecessarily.

It's being rushed into this version because we need a stopgap solution.
I don't see it as anything but a stopgap.  The fact that it's a very
small patch is good, because it can be replaced with minimal effort once
someone has the time to design and implement a better mechanism for
multi-database user management.  AFAICT a proper solution will involve
considerable work, and I don't see it happening in time for 7.3.

Also, ugly as this may be, it's still better than the old solution for
people who are trying to support multiple similarly-named users in
different databases.  The old hack required external password files
which mean manual management, admin involvement in any password change,
etc.  With this approach users can set their password normally even if
they're being restricted to one database.  So realistically I think this
does not affect people who aren't using it, and for people who do want
to use it it's a step forward, even if not as far forward as we'd like.

			regards, tom lane

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-16T18:29:23Z

BTW, I just thought of a small improvement to your patch that eliminates
some of the ugliness.  Suppose that when we recognize an attempt to
connect as a global user (ie, feature flag is on and last character of
username is '@'), we strip off the '@' before proceeding.  Then we would
have:
	global users appear in pg_shadow as foo
	local users appear in pg_shadow as foo@db
and what this would mean is that you can flip between feature-enabled
and feature-disabled states without breaking your global logins.  So you
don't need the extra step of creating a "postgres@" before turning on
the feature.  (Which was pretty ugly anyway, since even though postgres@
could be made a superuser, he wouldn't be the same user as postgres ---
this affects table ownership, for example, and would be a serious issue
if you wanted any non-superuser global users.)

I suppose some might argue that having to say postgres@ to log in,
when your username is really just postgres as far as you can see in the
database, is a tad confusing.  But the whole thing is an acknowledged
wart anyway, and I think getting rid of the two problems mentioned above
is worth it.

Also, if we do this then it's important to strip a trailing '@' only
if it's the *only* one in the given username.  Else a local user
'foo@db1' could cheat to log into db2 by saying username = 'foo@db1@'
with requested database db2.  But I can't see any other security hole.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-16T19:03:20Z

Tom Lane wrote:
> BTW, I just thought of a small improvement to your patch that eliminates
> some of the ugliness.  Suppose that when we recognize an attempt to
> connect as a global user (ie, feature flag is on and last character of
> username is '@'), we strip off the '@' before proceeding.  Then we would
> have:
> 	global users appear in pg_shadow as foo
> 	local users appear in pg_shadow as foo@db
> and what this would mean is that you can flip between feature-enabled
> and feature-disabled states without breaking your global logins.  So you
> don't need the extra step of creating a "postgres@" before turning on
> the feature.  (Which was pretty ugly anyway, since even though postgres@
> could be made a superuser, he wouldn't be the same user as postgres ---
> this affects table ownership, for example, and would be a serious issue
> if you wanted any non-superuser global users.)
> 
> I suppose some might argue that having to say postgres@ to log in,
> when your username is really just postgres as far as you can see in the
> database, is a tad confusing.  But the whole thing is an acknowledged
> wart anyway, and I think getting rid of the two problems mentioned above
> is worth it.

Sure. If I can get one more 'yes' I will submit a new patch with the
change.  It does prevent the namespace collision without mucking up
pg_shadow.  We only need to tell people that global users need to supply
their username to the client as user@.  Is that cleaner?

> Also, if we do this then it's important to strip a trailing '@' only
> if it's the *only* one in the given username.  Else a local user
> 'foo@db1' could cheat to log into db2 by saying username = 'foo@db1@'
> with requested database db2.  But I can't see any other security hole.

Ewe, I didn't think of that.  Good point.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Oliver Elphick <olly@lfix.co.uk> — 2002-08-16T19:31:01Z

On Fri, 2002-08-16 at 20:03, Bruce Momjian wrote:
> Sure. If I can get one more 'yes' I will submit a new patch with the
> change.  It does prevent the namespace collision without mucking up
> pg_shadow.  We only need to tell people that global users need to supply
> their username to the client as user@.  Is that cleaner?

I will vote yes for this change.  I think the flexibility this new
system offers will make it much easier for people to offer PostgreSQL
hosting facilities, of which I would like to see many more.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And whatsoever ye shall ask in my name, that will I 
      do, that the Father may be glorified in the Son."     
                                          John 14:13

Re: Open 7.3 items

ngpg@grymmjack.com — 2002-08-17T00:40:32Z

pgman@candle.pha.pa.us (Bruce Momjian) wrote
> 
> I know the trailing @ is ugly, but it prevents surpises when connecting
> to the database.
> 

if you would make the magic character a variable then perhaps you could 
prevent the ugly...  if/when you turn off the feature, you could set the 
PGSQL_STUPID_MAGIC_CHARACTER to '', then you would be appending an empty 
string instead of a @, when you want to turn it back on, set the variable 
back to '@'... and if you change the character, well dont..

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-17T02:13:01Z

ngpg@grymmjack.com wrote:
> pgman@candle.pha.pa.us (Bruce Momjian) wrote
> > 
> > I know the trailing @ is ugly, but it prevents surpises when connecting
> > to the database.
> > 
> 
> if you would make the magic character a variable then perhaps you could 
> prevent the ugly...  if/when you turn off the feature, you could set the 
> PGSQL_STUPID_MAGIC_CHARACTER to '', then you would be appending an empty 
> string instead of a @, when you want to turn it back on, set the variable 
> back to '@'... and if you change the character, well dont..

It already does that.  When it is off, it works just like it does in 7.2.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-17T04:16:14Z

OK, here is the patch with the suggested changes.  I am sending the
patch to hackers because there has been so much interest in this.

---------------------------------------------------------------------------

Tom Lane wrote:
> BTW, I just thought of a small improvement to your patch that eliminates
> some of the ugliness.  Suppose that when we recognize an attempt to
> connect as a global user (ie, feature flag is on and last character of
> username is '@'), we strip off the '@' before proceeding.  Then we would
> have:
> 	global users appear in pg_shadow as foo
> 	local users appear in pg_shadow as foo@db
> and what this would mean is that you can flip between feature-enabled
> and feature-disabled states without breaking your global logins.  So you
> don't need the extra step of creating a "postgres@" before turning on
> the feature.  (Which was pretty ugly anyway, since even though postgres@
> could be made a superuser, he wouldn't be the same user as postgres ---
> this affects table ownership, for example, and would be a serious issue
> if you wanted any non-superuser global users.)
> 
> I suppose some might argue that having to say postgres@ to log in,
> when your username is really just postgres as far as you can see in the
> database, is a tad confusing.  But the whole thing is an acknowledged
> wart anyway, and I think getting rid of the two problems mentioned above
> is worth it.
> 
> Also, if we do this then it's important to strip a trailing '@' only
> if it's the *only* one in the given username.  Else a local user
> 'foo@db1' could cheat to log into db2 by saying username = 'foo@db1@'
> with requested database db2.  But I can't see any other security hole.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-17T04:17:13Z

Sample run:
	
	$ psql -U postgres test
	psql: FATAL:  user "postgres@test" does not exist

	$ psql -U postgres@ test
	Welcome to psql 7.3devel, the PostgreSQL interactive terminal.
	
	Type:  \copyright for distribution terms
	       \h for help with SQL commands
	       \? for help on internal slash commands
	       \g or terminate with semicolon to execute query
	       \q to quit
	
	test=> 

---------------------------------------------------------------------------

Tom Lane wrote:
> BTW, I just thought of a small improvement to your patch that eliminates
> some of the ugliness.  Suppose that when we recognize an attempt to
> connect as a global user (ie, feature flag is on and last character of
> username is '@'), we strip off the '@' before proceeding.  Then we would
> have:
> 	global users appear in pg_shadow as foo
> 	local users appear in pg_shadow as foo@db
> and what this would mean is that you can flip between feature-enabled
> and feature-disabled states without breaking your global logins.  So you
> don't need the extra step of creating a "postgres@" before turning on
> the feature.  (Which was pretty ugly anyway, since even though postgres@
> could be made a superuser, he wouldn't be the same user as postgres ---
> this affects table ownership, for example, and would be a serious issue
> if you wanted any non-superuser global users.)
> 
> I suppose some might argue that having to say postgres@ to log in,
> when your username is really just postgres as far as you can see in the
> database, is a tad confusing.  But the whole thing is an acknowledged
> wart anyway, and I think getting rid of the two problems mentioned above
> is worth it.
> 
> Also, if we do this then it's important to strip a trailing '@' only
> if it's the *only* one in the given username.  Else a local user
> 'foo@db1' could cheat to log into db2 by saying username = 'foo@db1@'
> with requested database db2.  But I can't see any other security hole.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-17T16:26:44Z

OK, I think we are doing this backwards.  Instead of adding '@' to
global users, and then removing it in the backend, why don't we have
local users end with '@', that way, global users continue to connect
just as they have before, and local users connect with @, so dave@db1
connects as 'dave@' and if he has other database access, he can use the
same 'dave@' name.

That removes some of the uglification, I think.

---------------------------------------------------------------------------

Tom Lane wrote:
> BTW, I just thought of a small improvement to your patch that eliminates
> some of the ugliness.  Suppose that when we recognize an attempt to
> connect as a global user (ie, feature flag is on and last character of
> username is '@'), we strip off the '@' before proceeding.  Then we would
> have:
> 	global users appear in pg_shadow as foo
> 	local users appear in pg_shadow as foo@db
> and what this would mean is that you can flip between feature-enabled
> and feature-disabled states without breaking your global logins.  So you
> don't need the extra step of creating a "postgres@" before turning on
> the feature.  (Which was pretty ugly anyway, since even though postgres@
> could be made a superuser, he wouldn't be the same user as postgres ---
> this affects table ownership, for example, and would be a serious issue
> if you wanted any non-superuser global users.)
> 
> I suppose some might argue that having to say postgres@ to log in,
> when your username is really just postgres as far as you can see in the
> database, is a tad confusing.  But the whole thing is an acknowledged
> wart anyway, and I think getting rid of the two problems mentioned above
> is worth it.
> 
> Also, if we do this then it's important to strip a trailing '@' only
> if it's the *only* one in the given username.  Else a local user
> 'foo@db1' could cheat to log into db2 by saying username = 'foo@db1@'
> with requested database db2.  But I can't see any other security hole.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-17T16:47:43Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, I think we are doing this backwards.  Instead of adding '@' to
> global users, and then removing it in the backend, why don't we have
> local users end with '@', that way, global users continue to connect
> just as they have before, and local users connect with @, so dave@db1
> connects as 'dave@' and if he has other database access, he can use the
> same 'dave@' name.

No, *that* would be backwards.  In installations that are using this
feature, the vast majority of the users are going to be local ones.
And the global users will be the presumably-more-sophisticated admins.
Putting the onus of the '@' decoration on the local users instead of
the global ones is exactly the wrong way to go.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-17T23:41:13Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, I think we are doing this backwards.  Instead of adding '@' to
> > global users, and then removing it in the backend, why don't we have
> > local users end with '@', that way, global users continue to connect
> > just as they have before, and local users connect with @, so dave@db1
> > connects as 'dave@' and if he has other database access, he can use the
> > same 'dave@' name.
> 
> No, *that* would be backwards.  In installations that are using this
> feature, the vast majority of the users are going to be local ones.
> And the global users will be the presumably-more-sophisticated admins.
> Putting the onus of the '@' decoration on the local users instead of
> the global ones is exactly the wrong way to go.

OK, but it looks slightly less ugly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-18T02:36:11Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, here is the patch with the suggested changes.  I am sending the
> patch to hackers because there has been so much interest in this.

One minor gripe:

> + 		/* If user@, it is a global user, remove '@' */
> + 		if (strchr(port->user, '@') == port->user + strlen(port->user)-1)

This code is correct, but it tempts someone to replace the strchr()
with a single-character check on the last character of the string.
Which would introduce the security hole we discussed before.  The
code is okay, but *please* improve the comment to point out that you
are also excluding the case where there are @'s to the left of the
last character.

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-18T03:04:02Z

OK, applied, with that change.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, here is the patch with the suggested changes.  I am sending the
> > patch to hackers because there has been so much interest in this.
> 
> One minor gripe:
> 
> > + 		/* If user@, it is a global user, remove '@' */
> > + 		if (strchr(port->user, '@') == port->user + strlen(port->user)-1)
> 
> This code is correct, but it tempts someone to replace the strchr()
> with a single-character check on the last character of the string.
> Which would introduce the security hole we discussed before.  The
> code is okay, but *please* improve the comment to point out that you
> are also excluding the case where there are @'s to the left of the
> last character.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-18T09:37:20Z

Tom Lane writes:

> BTW, I just thought of a small improvement to your patch that eliminates
> some of the ugliness.  Suppose that when we recognize an attempt to
> connect as a global user (ie, feature flag is on and last character of
> username is '@'), we strip off the '@' before proceeding.

I'm missing how hard it is to change "last character of username is @" to
"no @ in username".  This would seem to be a two-line change somewhere.

I'm concerned that we leave essentially no migration path, that is, the
ability to turn the feature on to try it out without immediately breaking
every application.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-18T16:55:41Z

Peter Eisentraut <peter_e@gmx.net> writes:
> I'm concerned that we leave essentially no migration path, that is, the
> ability to turn the feature on to try it out without immediately breaking
> every application.

Uh ... what?  I fail to understand your objection.  AFAICS the only
apps that could be "broken" are scripts that have usernames hardwired
into them ...

			regards, tom lane

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-18T17:10:12Z

On Sat, 17 Aug 2002, Bruce Momjian wrote:

>
> OK, I think we are doing this backwards.  Instead of adding '@' to
> global users, and then removing it in the backend, why don't we have
> local users end with '@', that way, global users continue to connect
> just as they have before, and local users connect with @, so dave@db1
> connects as 'dave@' and if he has other database access, he can use the
> same 'dave@' name.
>
> That removes some of the uglification, I think.

Then why was it when I mentioned global users not having the @ you shot
it down as not possible?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Vince Vielhaber <vev@michvhf.com> — 2002-08-18T17:13:17Z

On Sat, 17 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, I think we are doing this backwards.  Instead of adding '@' to
> > global users, and then removing it in the backend, why don't we have
> > local users end with '@', that way, global users continue to connect
> > just as they have before, and local users connect with @, so dave@db1
> > connects as 'dave@' and if he has other database access, he can use the
> > same 'dave@' name.
>
> No, *that* would be backwards.  In installations that are using this
> feature, the vast majority of the users are going to be local ones.
> And the global users will be the presumably-more-sophisticated admins.
> Putting the onus of the '@' decoration on the local users instead of
> the global ones is exactly the wrong way to go.

Unsophisticated users is hardly a reason.  After all they do have an
@ in their email address.  If they're told the username is foo@ then
their username is foo@.  What's so difficult about that?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
      http://www.camping-usa.com      http://www.cloudninegifts.com
   http://www.meanstreamradio.com       http://www.unknown-artists.com
==========================================================================

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-18T21:36:01Z

Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > I'm concerned that we leave essentially no migration path, that is, the
> > ability to turn the feature on to try it out without immediately breaking
> > every application.
>
> Uh ... what?  I fail to understand your objection.  AFAICS the only
> apps that could be "broken" are scripts that have usernames hardwired
> into them ...

I'm completely lost between all the proposals about where the @ is going
to be specified, added, or removed.  What happens on the client side and
what happens on the server side?

All I would like to see is that I can turn on this feature and nothing
changes as long as I don't add any "local users".  Yes, that includes
hard-wired user names on the client side.  Of course there are various
degrees of hard-wiring, but what if the ISP admin updates to 7.3 and wants
to turn on the feature for new clients?  Does he tell all his existing
clients that they must update their user names?  Possibly, these users got
their database access with a shell account and don't specify the user name
at all because it defaults to the OS user name.  Does that continue to
work?

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-18T22:08:15Z

Peter Eisentraut <peter_e@gmx.net> writes:
> I'm completely lost between all the proposals about where the @ is going
> to be specified, added, or removed.  What happens on the client side and
> what happens on the server side?

Well, the way things stand as of CVS tip is that (assuming you have this
feature turned on in postgresql.conf):

* If a connection request has a username with a trailing '@' (and no
embedded '@'), then the '@' is stripped and connection proceeds.

* Otherwise, '@dbname' is appended to the given username and connection
proceeds.

So a "global" user foo has to say username="foo@" in his connection
request, but he's just "foo" in pg_shadow.  A "local" user foo has to
say "foo" in his connection request, and he's "foo@somedb" in pg_shadow.

> All I would like to see is that I can turn on this feature and nothing
> changes as long as I don't add any "local users".  Yes, that includes
> hard-wired user names on the client side.

Well, we could have that by inverting the use of '@'; but as I commented
before, it makes more sense to me to make the global users say '@' than
to make the local users do so, because I think in an installation that
wants this feature there will be lots more local than global users.
I really don't put that much weight on the compatibility argument you
make --- not that I don't see your point, but that I don't think it
outweighs convenience of day-to-day use after one has gotten the system
set up.  (Also, compatibility cuts both ways: it seems just as likely
to me that the clients with hardwired usernames are going to be ones
you want to connect as local users, as that they are going to be ones
you want to connect as global users.  Maybe more likely, if you grant
the assumption that there will be more local than global users.)

It might be worth recalling the reason that we are going through this
pushup in the first place: Marc wants to be able to assign the same
username to two different users who want to access two different
databases.  If he would be happy with the answer "give them two
different usernames", we'd not be having this discussion at all.
Do you think he will be happy with the answer "you can give them
the same username as long as it ends in '@'"?  I think it's highly
unlikely that he'll be satisfied with that --- he wants to *not*
have constraints on the names he gives out for local users.

> Of course there are various
> degrees of hard-wiring, but what if the ISP admin updates to 7.3 and wants
> to turn on the feature for new clients?  Does he tell all his existing
> clients that they must update their user names?  Possibly, these users got
> their database access with a shell account and don't specify the user name
> at all because it defaults to the OS user name.  Does that continue to
> work?

It works great if the ISP intends to make them all local users, which
seems more likely to me than the other case.

			regards, tom lane

assigning to NULL?

redmonde@purdue.edu — 2002-08-18T22:25:48Z

I'm trying to make postGIS work with pg7.3devel. But a problem is occuring that did not appear in pg7.2. When I execute:

ALTER TABLE geotest ADD CHECK ( geometrytype(geopoint)='POINT'
OR NULL=geopoint);

I get: "ERROR: copyObject: don't know how to copy node type 506"

But when I execute:

ALTER TABLE geotest ADD CHECK ( geometrytype(geopoint)='POINT');

It works fine, which, due to the error message it seems that it is trying to assign rather to NULL, rather than compare (else what object needs to be copied in "NULL=geopoint"?). Is this a bug, a change in NULL, or a change in user defined datatypes?
Thanks;
Eric

Re: assigning to NULL?

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-18T22:46:34Z

redmonde@purdue.edu writes:
> I get: "ERROR: copyObject: don't know how to copy node type 506"

This is a bug in someone's recent patch ... but you don't want to say
"NULL=geopoint" anyway, do you?  Surely it should be "geopoint IS NULL".

			regards, tom lane

Re: Open 7.3 items

ngpg@grymmjack.com — 2002-08-19T00:01:02Z

tgl@sss.pgh.pa.us (Tom Lane) wrote

> * If a connection request has a username with a trailing '@' (and no
> embedded '@'), then the '@' is stripped and connection proceeds.
> 
> * Otherwise, '@dbname' is appended to the given username and
> connection proceeds.
<snip>
> It might be worth recalling the reason that we are going through this
> pushup in the first place: Marc wants to be able to assign the same
> username to two different users who want to access two different
> databases.  If he would be happy with the answer "give them two
> different usernames", we'd not be having this discussion at all.
> Do you think he will be happy with the answer "you can give them
> the same username as long as it ends in '@'"?  I think it's highly
> unlikely that he'll be satisfied with that --- he wants to *not*
> have constraints on the names he gives out for local users.


What about usernames that have trailing or embedded @'s?  I mean you are 
eseentially making the @ a magic character.  I admit I havent looked at 
the source, but doesnt this method effectively put a constraint on the 
use of @?  What if an isp, that could use this feature, already has 
usernames with @'s in them (say a customers email address, etc)?  Will 
they need to assign all new usernames to make this thing function?

What if you want to give one person (one username) access to 2 db's?  
Does that mean, under the current scheme, that the two accounts you 
create can have the same username but have different passwords?  What if 
you want to erase the "one" account (do you have to remember to erase all 
n accounts you created with the same username, or all n except the ones 
that were never mean to be the same person but share the same username)?

Normally a user has a unique name.  Does anyone see a problem if/when the 
whole db access thing becomes part of the privileges system?  If you 
implement the "multiple users same username", then you'll have to 
reassign all but one of the users to new usernames.

Re: Open 7.3 items

Lee Kindness <lkindness@csl.co.uk> — 2002-08-19T08:04:24Z

I'd have thought that if a matching user couldn't be found in the
specified database then it would default to searching through the
global users? Would be more intuitive...

Lee.

Bruce Momjian writes:
 > Sample run:
 > 	$ psql -U postgres test
 > 	psql: FATAL:  user "postgres@test" does not exist
 > 
 > 	$ psql -U postgres@ test
 > 	Welcome to psql 7.3devel, the PostgreSQL interactive terminal.

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-27T19:19:43Z

I think we need to resolve this discussion from a week ago.  The current
code is this:

	global usernames are stored just like before, e.g. postgres
	local users are stored as user@dbname
	when connecting, global users add '@' to their names
	when connecting, local users use just their user name, no @dbname

Tom likes this because it is the fewer global users who have to append
the '@'.

Vince and Peter think that it should be local users adding '@' when
connecting because:

	they have an @ sign in their name anyway
	global users should be able to connect unchanged

I can foresee a time when we will have longer usernames, and local users
will be able to connect with the full user@dbname, and we can allow
user@ as a shortcut.

In summary, I prefer to change the code to have local users append the
'@'.

Comments?  

It is an easy change and prevents what is a very confusing situation
where we add '@' for users who don't have @, and remove '@' for users
who have it.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > I'm concerned that we leave essentially no migration path, that is, the
> > > ability to turn the feature on to try it out without immediately breaking
> > > every application.
> >
> > Uh ... what?  I fail to understand your objection.  AFAICS the only
> > apps that could be "broken" are scripts that have usernames hardwired
> > into them ...
> 
> I'm completely lost between all the proposals about where the @ is going
> to be specified, added, or removed.  What happens on the client side and
> what happens on the server side?
> 
> All I would like to see is that I can turn on this feature and nothing
> changes as long as I don't add any "local users".  Yes, that includes
> hard-wired user names on the client side.  Of course there are various
> degrees of hard-wiring, but what if the ISP admin updates to 7.3 and wants
> to turn on the feature for new clients?  Does he tell all his existing
> clients that they must update their user names?  Possibly, these users got
> their database access with a shell account and don't specify the user name
> at all because it defaults to the OS user name.  Does that continue to
> work?
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-27T19:35:41Z

On Tuesday 27 August 2002 03:19 pm, Bruce Momjian wrote:
> I think we need to resolve this discussion from a week ago.  The current
> code is this:

I thought it WAS resolved, to do:

> 	global usernames are stored just like before, e.g. postgres
> 	local users are stored as user@dbname
> 	when connecting, global users add '@' to their names
> 	when connecting, local users use just their user name, no @dbname

> Tom likes this because it is the fewer global users who have to append
> the '@'.

At least that was my perception of the uneasy consensus reached.

Basically, this tags the @ as magic saying, during the client connect process, 
'I'm GLOBAL, treat me differently'.  Now that I actually understand how this 
is supposed to work, which your four lines above elucidate nicely, I am in 
more agreement than I was that this is the right answer to this issue.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-27T19:43:02Z

Lamar Owen wrote:
> On Tuesday 27 August 2002 03:19 pm, Bruce Momjian wrote:
> > I think we need to resolve this discussion from a week ago.  The current
> > code is this:
> 
> I thought it WAS resolved, to do:
> 
> > 	global usernames are stored just like before, e.g. postgres
> > 	local users are stored as user@dbname
> > 	when connecting, global users add '@' to their names
> > 	when connecting, local users use just their user name, no @dbname
> 
> > Tom likes this because it is the fewer global users who have to append
> > the '@'.
> 
> At least that was my perception of the uneasy consensus reached.
> 
> Basically, this tags the @ as magic saying, during the client connect process, 
> 'I'm GLOBAL, treat me differently'.  Now that I actually understand how this 
> is supposed to work, which your four lines above elucidate nicely, I am in 
> more agreement than I was that this is the right answer to this issue.

OK, you have now split the vote because we have two for the change, and
two against.  Why do you prefer to tag the globals?  Is it Tom's
argument?  I think it is kind of strange to tag the globals when it is
the locals who have @ in their username, and when they do:

	$ psql -U dave test
	Welcome to psql 7.3devel, the PostgreSQL interactive terminal.
	
	Type:  \copyright for distribution terms
	       \h for help with SQL commands
	       \? for help on internal slash commands
	       \g or terminate with semicolon to execute query
	       \q to quit
	
	test=> select current_user;
	 current_user 
	--------------
	 dave@test
	(1 row)

they will see their full username.

I can go either way.  I am just saying we need to hear from more people
to make sure we are doing this properly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T19:47:11Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can go either way.  I am just saying we need to hear from more people
> to make sure we are doing this properly.

Likewise.  In particular I'd like to hear from Marc, who after all
is the one who caused us to consider this hack in the first place.
Does it satisfy his requirement?  Is one way or the other preferable
for his actual usage?

			regards, tom lane

Re: Open 7.3 items

Lamar Owen <lamar.owen@wgcr.org> — 2002-08-27T20:05:36Z

On Tuesday 27 August 2002 03:43 pm, Bruce Momjian wrote:
> Lamar Owen wrote:
> > On Tuesday 27 August 2002 03:19 pm, Bruce Momjian wrote:
> > I thought it WAS resolved, to do:

> > > Tom likes this because it is the fewer global users who have to append
> > > the '@'.

> > At least that was my perception of the uneasy consensus reached.

> OK, you have now split the vote because we have two for the change, and
> two against.  Why do you prefer to tag the globals?  Is it Tom's
> argument?  I think it is kind of strange to tag the globals when it is
> the locals who have @ in their username, and when they do:

I agree with what Tom said, and understand why he said it.  And I thought you 
did, too -- I have apparently misunderstood (again!) the issue.

In the local-enabled scheme, ISTM the majority of users will be local users.  
The goal is transparent virtual databases -- at least that's what I consider 
the goal.  As far as the user is concerned, the other databases might as well 
not even exist -- all they are doing is connecting to their database.  Since 
they have to give the database name as part of the connection, it just makes 
sense that they should have the closest to default behavior.

In the case of a virtual hosting postmaster, global users would likely be 
DBA's, although they might not be.  These users are going to be the 
exception, not the rule -- thus a character to tag their 'exceptional' 
nature.

You may not even want your virtual host local users to realize that there is 
another user by that name.  Thus, the standard notation is the least 
intrusive for the very users that need uninstrusive notation.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Open 7.3 items

Rod Taylor <rbt@zort.ca> — 2002-08-27T20:10:56Z

It should also be noted that it's easy to get the DBAs to change their
username in the future when / if the @ hack goes away BUT it will be
difficult to change the usernames of the hundreds to thousands of
customer accounts.

For an upgrade, we'd end up making a script in the upgrade to keep them
the same (with the @) then have a control panel code in place to suggest
to the user that they may stop using the @ if they wish <click here>
type of thing.


> > > > Tom likes this because it is the fewer global users who have to append
> > > > the '@'.
> 
> > > At least that was my perception of the uneasy consensus reached.
> 
> > OK, you have now split the vote because we have two for the change, and
> > two against.  Why do you prefer to tag the globals?  Is it Tom's
> > argument?  I think it is kind of strange to tag the globals when it is
> > the locals who have @ in their username, and when they do:

> In the case of a virtual hosting postmaster, global users would likely be 
> DBA's, although they might not be.  These users are going to be the 
> exception, not the rule -- thus a character to tag their 'exceptional' 
> nature.

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-27T20:19:47Z

Lamar Owen wrote:
> On Tuesday 27 August 2002 03:43 pm, Bruce Momjian wrote:
> > Lamar Owen wrote:
> > > On Tuesday 27 August 2002 03:19 pm, Bruce Momjian wrote:
> > > I thought it WAS resolved, to do:
> 
> > > > Tom likes this because it is the fewer global users who have to append
> > > > the '@'.
> 
> > > At least that was my perception of the uneasy consensus reached.
> 
> > OK, you have now split the vote because we have two for the change, and
> > two against.  Why do you prefer to tag the globals?  Is it Tom's
> > argument?  I think it is kind of strange to tag the globals when it is
> > the locals who have @ in their username, and when they do:
> 
> I agree with what Tom said, and understand why he said it.  And I thought you 
> did, too -- I have apparently misunderstood (again!) the issue.

I try not to interject my opinions into emails where I am asking for
disucsion so people can more clearly see the options and vote
accordingly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-27T20:20:40Z

OK, we have enough votes to keep the existing behavior, unless Marc
appears and says he doesn't like it.  ;-)

Thanks.

---------------------------------------------------------------------------

Rod Taylor wrote:
> It should also be noted that it's easy to get the DBAs to change their
> username in the future when / if the @ hack goes away BUT it will be
> difficult to change the usernames of the hundreds to thousands of
> customer accounts.
> 
> For an upgrade, we'd end up making a script in the upgrade to keep them
> the same (with the @) then have a control panel code in place to suggest
> to the user that they may stop using the @ if they wish <click here>
> type of thing.
> 
> 
> > > > > Tom likes this because it is the fewer global users who have to append
> > > > > the '@'.
> > 
> > > > At least that was my perception of the uneasy consensus reached.
> > 
> > > OK, you have now split the vote because we have two for the change, and
> > > two against.  Why do you prefer to tag the globals?  Is it Tom's
> > > argument?  I think it is kind of strange to tag the globals when it is
> > > the locals who have @ in their username, and when they do:
> 
> > In the case of a virtual hosting postmaster, global users would likely be 
> > DBA's, although they might not be.  These users are going to be the 
> > exception, not the rule -- thus a character to tag their 'exceptional' 
> > nature.
> 
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Oliver Elphick <olly@lfix.co.uk> — 2002-08-27T21:05:59Z

On Tue, 2002-08-27 at 21:05, Lamar Owen wrote:
> On Tuesday 27 August 2002 03:43 pm, Bruce Momjian wrote:
> > Lamar Owen wrote:
> > > On Tuesday 27 August 2002 03:19 pm, Bruce Momjian wrote:
> > > I thought it WAS resolved, to do:
> 
> > > > Tom likes this because it is the fewer global users who have to append
> > > > the '@'.
> 
> > > At least that was my perception of the uneasy consensus reached.
> 
> > OK, you have now split the vote because we have two for the change, and
> > two against.  Why do you prefer to tag the globals?  Is it Tom's
> > argument?  I think it is kind of strange to tag the globals when it is
> > the locals who have @ in their username, and when they do:
> 
> I agree with what Tom said, and understand why he said it.  And I thought you 
> did, too -- I have apparently misunderstood (again!) the issue.
> 
> In the local-enabled scheme, ISTM the majority of users will be local users.  
> The goal is transparent virtual databases -- at least that's what I consider 
> the goal.  As far as the user is concerned, the other databases might as well 
> not even exist -- all they are doing is connecting to their database.  Since 
> they have to give the database name as part of the connection, it just makes 
> sense that they should have the closest to default behavior.
> 
> In the case of a virtual hosting postmaster, global users would likely be 
> DBA's, although they might not be.  These users are going to be the 
> exception, not the rule -- thus a character to tag their 'exceptional' 
> nature.
> 
> You may not even want your virtual host local users to realize that there is 
> another user by that name.  Thus, the standard notation is the least 
> intrusive for the very users that need uninstrusive notation.

Has this behaviour been carried through into GRANT and REVOKE?  If the
object is transparency for local users, it should be possible in
database "test" to say "GRANT ... TO fred" and have "fred" understood as
"fred@test".

If that is the case, then I will support the current position.


It follows from the objective of transparency that, when reporting a
user name, local users should be reported without the database suffix,
i.e., "fred" not "fred@test".  Global users should be reported with the
trailing "@".  This should cause no problem, because we have no
cross-database communication; it should be impossible for "george@dummy"
to have any connection with database "test".

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But the end of all things is at hand; be ye therefore 
      sober, and watch unto prayer. And above all things 
      have fervent love among yourselves; for love shall  
      cover the multitude of sins."      I Peter 4:7,8

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-27T21:11:21Z

Oliver Elphick wrote:
> > I agree with what Tom said, and understand why he said it.  And I thought you 
> > did, too -- I have apparently misunderstood (again!) the issue.
> > 
> > In the local-enabled scheme, ISTM the majority of users will be local users.  
> > The goal is transparent virtual databases -- at least that's what I consider 
> > the goal.  As far as the user is concerned, the other databases might as well 
> > not even exist -- all they are doing is connecting to their database.  Since 
> > they have to give the database name as part of the connection, it just makes 
> > sense that they should have the closest to default behavior.
> > 
> > In the case of a virtual hosting postmaster, global users would likely be 
> > DBA's, although they might not be.  These users are going to be the 
> > exception, not the rule -- thus a character to tag their 'exceptional' 
> > nature.
> > 
> > You may not even want your virtual host local users to realize that there is 
> > another user by that name.  Thus, the standard notation is the least 
> > intrusive for the very users that need uninstrusive notation.
> 
> Has this behaviour been carried through into GRANT and REVOKE?  If the
> object is transparency for local users, it should be possible in
> database "test" to say "GRANT ... TO fred" and have "fred" understood as
> "fred@test".

No changes have been made anywhere except for the username passed by the
client.  All reporting of user names and all administration go by their
full pg_shadow username, so global user dave@ is dave in pg_shadow, and
dave is dave@db1 in pg_shadow.  One goal of this patch was a small
footprint.

> If that is the case, then I will support the current position.
> 
> 
> It follows from the objective of transparency that, when reporting a
> user name, local users should be reported without the database suffix,
> i.e., "fred" not "fred@test".  Global users should be reported with the
> trailing "@".  This should cause no problem, because we have no
> cross-database communication; it should be impossible for "george@dummy"
> to have any connection with database "test".

Nope, none of this is done and I don't think there is a demand to do it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Oliver Elphick <olly@lfix.co.uk> — 2002-08-27T21:33:35Z

On Tue, 2002-08-27 at 22:11, Bruce Momjian wrote:
> Oliver Elphick wrote:
> > Has this behaviour been carried through into GRANT and REVOKE?  If the
> > object is transparency for local users, it should be possible in
> > database "test" to say "GRANT ... TO fred" and have "fred" understood as
> > "fred@test".
> 
> No changes have been made anywhere except for the username passed by the
> client.  All reporting of user names and all administration go by their
> full pg_shadow username, so global user dave@ is dave in pg_shadow, and
> dave is dave@db1 in pg_shadow.  One goal of this patch was a small
> footprint.

That is understandable, but it means that there is an inconsistency of
usage for _every_ user.

You connect as "postgres@" and "fred", but for all other purposes -
CREATE USER, GRANT, REVOKE, CURRENT_USER, etc. you will be "postgres"
and "fred@database".  This seems likely to cause users confusion, don't
you think?  It also means that any applications which test usernames
will have to be altered to strip off the "@database".

So it seems to me that you have achieved a small footprint within the
code, but potentially at the cost of a larger impact on users.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But the end of all things is at hand; be ye therefore 
      sober, and watch unto prayer. And above all things 
      have fervent love among yourselves; for love shall  
      cover the multitude of sins."      I Peter 4:7,8

Re: Open 7.3 items

Peter Eisentraut <peter_e@gmx.net> — 2002-08-27T21:36:36Z

Bruce Momjian writes:

> 	global usernames are stored just like before, e.g. postgres
> 	local users are stored as user@dbname
> 	when connecting, global users add '@' to their names
> 	when connecting, local users use just their user name, no @dbname

I'm OK with this in principle.  But I must say I was quite confused
because the "@" symbol appears in diametrically opposite contexts:

a) designate local users on the server

b) designate global users in the client

Perhaps I might have been less confused if meaning (b) used a different
character, say "username!".  This might be equally confusing to the next
person -- just my observation.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T21:44:41Z

Oliver Elphick <olly@lfix.co.uk> writes:
> So it seems to me that you have achieved a small footprint within the
> code, but potentially at the cost of a larger impact on users.

I don't think anyone will deny that this is a kluge.  However, we are
not going to resurrect the separate-password-file thing (that was a
worse kluge, especially when used for this purpose), and we are not
going to postpone 7.3 while we think about a nicer solution.  So, simple
is beautiful for now.  If there are enough people actually *using* this
feature to make it worth improving, we can improve it ... later.

			regards, tom lane

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T21:46:38Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Perhaps I might have been less confused if meaning (b) used a different
> character, say "username!".

Well, maybe ... but do we want to create two special characters in
usernames, instead of one?  @ still has to be considered special in
incoming usernames, else you have no security against local users
connecting to other databases.

			regards, tom lane

Re: Open 7.3 items

Oliver Elphick <olly@lfix.co.uk> — 2002-08-27T21:58:30Z

On Tue, 2002-08-27 at 22:44, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > So it seems to me that you have achieved a small footprint within the
> > code, but potentially at the cost of a larger impact on users.
> 
> I don't think anyone will deny that this is a kluge.  However, we are
> not going to resurrect the separate-password-file thing (that was a
> worse kluge, especially when used for this purpose), and we are not
> going to postpone 7.3 while we think about a nicer solution.  So, simple
> is beautiful for now.  If there are enough people actually *using* this
> feature to make it worth improving, we can improve it ... later.

Could we then have a TODO item:

  * Make local and global user representation consistent throughout.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But the end of all things is at hand; be ye therefore 
      sober, and watch unto prayer. And above all things 
      have fervent love among yourselves; for love shall  
      cover the multitude of sins."      I Peter 4:7,8

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T22:10:27Z

Oliver Elphick <olly@lfix.co.uk> writes:
> This should cause no problem, because we have no
> cross-database communication; it should be impossible for "george@dummy"
> to have any connection with database "test".

Not so; you need look no further than the owner column of pg_database
to find a case where people can see usernames that might be local to
other databases.  Group membership lists might well contain users
from multiple databases, too.

			regards, tom lane

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T22:17:03Z

Oliver Elphick <olly@lfix.co.uk> writes:
> Could we then have a TODO item:
>   * Make local and global user representation consistent throughout.

That's hardly an appropriately expansive TODO item.  I prefer

    * Provide a real solution for database-local users

;-)

			regards, tom lane

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-28T01:07:04Z

Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > Could we then have a TODO item:
> >   * Make local and global user representation consistent throughout.
> 
> That's hardly an appropriately expansive TODO item.  I prefer
> 
>     * Provide a real solution for database-local users

I say let's get it out in the field and see what people ask for.  For
all we know, they may be very happy with this, nor not use it at all.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-28T01:08:01Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > 	global usernames are stored just like before, e.g. postgres
> > 	local users are stored as user@dbname
> > 	when connecting, global users add '@' to their names
> > 	when connecting, local users use just their user name, no @dbname
> 
> I'm OK with this in principle.  But I must say I was quite confused
> because the "@" symbol appears in diametrically opposite contexts:
> 
> a) designate local users on the server
> 
> b) designate global users in the client
> 
> Perhaps I might have been less confused if meaning (b) used a different
> character, say "username!".  This might be equally confusing to the next
> person -- just my observation.

There is no question it is 100% confusing.  You are not alone.

What keeps us from unconfusing it is the desire to make local usernames
clean looking, I think.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Open 7.3 items

Oliver Elphick <olly@lfix.co.uk> — 2002-08-28T07:22:53Z

On Tue, 2002-08-27 at 23:10, Tom Lane wrote: 
> Oliver Elphick <olly@lfix.co.uk> writes:
> > This should cause no problem, because we have no
> > cross-database communication; it should be impossible for "george@dummy"
> > to have any connection with database "test".
> 
> Not so; you need look no further than the owner column of pg_database
> to find a case where people can see usernames that might be local to
> other databases.  Group membership lists might well contain users
> from multiple databases, too.

I suspect I have a different view of the ultimate aim of this feature.

If we go to a thorough solution for virtual local databases, local users
of other databases ought to be completely invisible.  I suppose that
means that to a local user, pg_database would be a view showing only
template[01] and the local database. pg_shadow, too, would show only
global users and local users in the same database.

I can't see how a group within a local database could contain users from
other databases.   In the context in which this is being used, each
database belongs to a different customer; each database needs to be
invisible to other customers.  How then should it be possible to have
group lists containing users from different local databases?  Groups
should be local as well as users.

Perhaps I like complicating things too much...

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Use hospitality one to another without grudging."     
                                   I Peter 4:9

Re: Open 7.3 items

Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-28T13:14:43Z

Oliver Elphick <olly@lfix.co.uk> writes:
> If we go to a thorough solution for virtual local databases, local users
> of other databases ought to be completely invisible.

Perhaps.  I'm not convinced of that, but it's a defensible position.

> I can't see how a group within a local database could contain users from
> other databases.

This presupposes that groups become local to databases, which is not
a foregone conclusion in my mind at all.  Perhaps we'll need to invent
the concept of local and global groups, to go along with local and
global users.

Anyway, this is all designing far in advance of available use-cases.
Marc was satisfying his needs (so far as he said, anyway) with a
password-based scheme even klugier than what we're going to put in 7.3.
We don't have other usage examples at all.  And with the availability
of schemas in 7.3, I think that multiple databases per installation
is going to become less common to begin with --- people will more often
use multiple schemas in one big database if they want the option of
data sharing, or completely separate installations if they want airtight
separation.

Accordingly, I'm disinclined to start actually inventing features in
this area until I see more evidence that it's worth the trouble.

			regards, tom lane