Re: Open 7.3 items

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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? :)

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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?

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)gmx(dot)net

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)gmx(dot)net

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(at)gmx(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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

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

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database ...

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database ...
>

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

Joe

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

Rod Taylor <rbt(at)zort(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

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

Neil Conway <nconway(at)klamath(dot)dyndns(dot)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(at)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

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Neil Conway <nconway(at)klamath(dot)dyndns(dot)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

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(at)candle(dot)pha(dot)pa(dot)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(at)database ...
> >
> > Yes, the format was going to be my second vote, dbname.username or
> > username(at)dbname(dot) 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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database ...

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

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

On Wed, 7 Aug 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database ...
>
> Yes, the format was going to be my second vote, dbname.username or
> username(at)dbname(dot) 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)

> 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

Hannu Krosing <hannu(at)tm(dot)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

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

Greg

On Wed, 2002-08-07 at 21:31, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)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

On Thu, 2002-08-08 at 07:31, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)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

Hannu Krosing <hannu(at)tm(dot)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(at)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

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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

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

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

> Some mentioned using user(at)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(dot)doe(at)foo(dot)com

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

--
Sean Chittenden

Sean Chittenden wrote:
> > Some mentioned using user(at)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(dot)doe(at)foo(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> > > Some mentioned using user(at)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(dot)doe(at)foo(dot)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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database, so I think we're roughly at a
tie. It would be good to get more votes ...

regards, tom lane

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database, so I think we're roughly at a
> tie. It would be good to get more votes ...
>

I'm in favor of username(at)database too.

Joe

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)domain(dot)com','@'), split_after('user(at)domain(dot)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(at)somewhere','@'),
split_after('me(at)somewhere','@');
split_before | split_after
--------------+-------------
me | somewhere
(1 row)

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

> > > > 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(at)domain(dot)com','@'), split_after('user(at)domain(dot)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

> > > > > > 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(at)domain(dot)com','@'), split_after('user(at)domain(dot)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(at)somewhere','@'),
> split_after('me(at)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

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

On Wed, 2002-08-14 at 00:11, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)database, so I think we're roughly at a
> tie. It would be good to get more votes ...

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

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(at)domain(dot)com','@'), split_after('user(at)domain(dot)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

On Wed, Aug 14, 2002 at 12:11:10AM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)database, too.

A

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

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

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(at)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

ngpg(at)grymmjack(dot)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)

We are clearly going for user(at)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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)gmx(dot)net

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(at)gmx(dot)net
>
>

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

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(at)gmx(dot)net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)db(dot) 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

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.

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)db(dot) 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)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(at)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2002-08-14 at 14:34, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)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(at)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(at)database'
should be rejected shouldn't it? Otherwise we wind up with two
potential 'user(at)database' users (globals users are really user@<each
database>) but with a single ID.

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(at)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(at)template1' -- but lowen(at)pari wouldn't have
access to template1, right? Or am I misunderstanding the feature?
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar(dot)owen(at)wgcr(dot)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(at)template1' -- but lowen(at)pari wouldn't have
> access to template1, right?

If not, standard things like "psql -l" won't work for lowen(at)pari(dot) 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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

On Wed, 14 Aug 2002, Tom Lane wrote:

> Lamar Owen <lamar(dot)owen(at)wgcr(dot)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(at)template1' -- but lowen(at)pari wouldn't have
> > access to template1, right?
>
> If not, standard things like "psql -l" won't work for lowen(at)pari(dot) 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(at)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(at)somedb later, will
it be allowed?

One possible simplification would be to make the username the full
username "lowen(at)somedb", "lowen", ... Right now we can create a
"lowen(at)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(at)michvhf(dot)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
==========================================================================

On Wednesday 14 August 2002 03:04 pm, Tom Lane wrote:
> Lamar Owen <lamar(dot)owen(at)wgcr(dot)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(at)template1' -- but lowen(at)pari
> > wouldn't have access to template1, right?

> If not, standard things like "psql -l" won't work for lowen(at)pari(dot) 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(at)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(at)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(at)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(at)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(at)template1 has access across the install (subject to GRANT/REVOKE, of
course). lowen(at)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(at)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(at)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)db1, then when dave tries to
connect to db1, he comes in as dave(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)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(at)somedb later, will
> it be allowed?

If the user 'lowen' is then expanded to 'lowen(at)template1' it would be stored
that way -- and lowen(at)template1 is different from lowen(at)pari, for instance.
The lowen(at)template1 user could be a superuser and lowen(at)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

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(at)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(at)somedb later, will
> > it be allowed?
>
> If the user 'lowen' is then expanded to 'lowen(at)template1' it would be stored
> that way -- and lowen(at)template1 is different from lowen(at)pari, for instance.
> The lowen(at)template1 user could be a superuser and lowen(at)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(at)templatge1' vs 'I am dave(at)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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)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(at)somedb later, will
> > it be allowed?
>
> If the user 'lowen' is then expanded to 'lowen(at)template1' it would be stored
> that way -- and lowen(at)template1 is different from lowen(at)pari, for instance.
> The lowen(at)template1 user could be a superuser and lowen(at)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(at)template1?

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)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
==========================================================================

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(at)template1' it would be
> > stored that way -- and lowen(at)template1 is different from lowen(at)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(at)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(at)template1 -- no namespace collision, and the special case is that
anyone(at)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

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(at)template1(dot)

> 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(at)templatge1' vs 'I am dave(at)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(at)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(at)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(at)pari for the syntax, the create user trips the
trigger, which checks for lowen(at)template1 and aborts if so. CREATE USER
lowen(at)template1 does the same, checking for ANY user lowen. Namespace
collision averted? CREATE USER lowen would be the same as CREATE USER
lowen(at)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(at)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(at)pari
and lowen(at)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(at)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

Lamar Owen <lamar(dot)owen(at)wgcr(dot)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(at)template1 -- no namespace collision, and the special case is that
> anyone(at)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(at)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(at)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(at)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

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(at)gmx(dot)net

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(at)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(dot)owen(at)wgcr(dot)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(at)template1 -- no namespace collision, and the special case is that
> > anyone(at)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(at)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(at)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(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> How about if we just document that they have to create a
> postgres(at)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(at)db1, postgres(at)db2,
postgres(at)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

On Wed, 14 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)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(at)db',
is there a third choice for us confused folks to go for? A
compromise: database(at)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > How about if we just document that they have to create a
> > postgres(at)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(at)db1, postgres(at)db2,
> postgres(at)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

On Wed, 14 Aug 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > How about if we just document that they have to create a
> > > postgres(at)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(at)db1, postgres(at)db2,
> > postgres(at)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(at)michvhf(dot)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
==========================================================================

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian) wrote:
> Tom Lane wrote:
>> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)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(at)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(at)yourdb instead of
jsmith(at)yourdb? If they cant remember it or something maybe they can
write it down? I dunno...

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

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(at)mascari(dot)com

>
> Joe

Mike Mascari <mascarm(at)mascari(dot)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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)michvhf(dot)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
==========================================================================

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(at)sss(dot)pgh(dot)pa(dot)us>
Date: Wed, 14 Aug 2002 20:30:47 -0400 (EDT)
cc: Lamar Owen <lamar(dot)owen(at)wgcr(dot)org>, Vince Vielhaber <vev(at)michvhf(dot)com>,
PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-hackers-owner(at)postgresql(dot)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(at)michvhf(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)michvhf(dot)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
==========================================================================

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(at)michvhf(dot)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
==========================================================================

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)michvhf(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)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

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(at)michvhf(dot)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
==========================================================================

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(at)template1, I am of course OK with it.
:-) Easier to type than user(at)template1, too.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Vince Vielhaber <vev(at)michvhf(dot)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

On Thu, 15 Aug 2002, Tom Lane wrote:

> Vince Vielhaber <vev(at)michvhf(dot)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(at)michvhf(dot)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
==========================================================================

Vince Vielhaber <vev(at)michvhf(dot)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(at)database(dot)

We can't really expect the users to do this for us (give user(at)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(at)database" in 32
bytes would be awfully restrictive about your user and database names.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

On Thu, 15 Aug 2002, Tom Lane wrote:

> Vince Vielhaber <vev(at)michvhf(dot)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(at)database(dot)
>
> We can't really expect the users to do this for us (give user(at)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(at)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(at)michvhf(dot)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 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(at)gmx(dot)net