

> > CREATE USER sql command updates the file, but an UPDATE on pg_shadow
> > does not.
> 
> How about INSERT INTO pg_shadow? Or how do you judge the 
> following excerpt
> from the createuser script:
> 
> QUERY="insert into pg_shadow \
>         (usename, usesysid, usecreatedb, usetrace, usesuper, 
> usecatupd) \
>        values \
>          ('$NEWUSER', $SYSID, '$CANCREATE', 'f', '$CANADDUSER','f')"
> 
> Fortunately (perhaps), I am getting rid of this as we're 
> speaking. The one
> feature the createuser script has over the CREATE USER "SQL" 
> command is
> that you can pick your system ID. Ignoring the question whether or not
> this has any real purpose, it seems this is almost like 
> rolling dice since

The sysid is essential for one of the authentication methods available in
PostgreSQL
(was it ident, I forgot) where the unix system password was used.

Andreas

Mon, 8 Nov 1999 23:00:29 +0100 
Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at>
AW: [HACKERS] Re: [GENERAL] users in Postgresql
On Mon, 8 Nov 1999, Zeugswetter Andreas SEV wrote:

> > speaking. The one
> > feature the createuser script has over the CREATE USER "SQL" 
> > command is
> > that you can pick your system ID. Ignoring the question whether or not
> > this has any real purpose, it seems this is almost like 
> > rolling dice since
> 
> The sysid is essential for one of the authentication methods available in
> PostgreSQL
> (was it ident, I forgot) where the unix system password was used.

Can't be ident, since I am running it with differing user ids. Perhaps
some odd usage of password authentication, but I don't use that too much.

Hence, can anyone comment on this?

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 9 Nov 1999 10:18:27 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
>> The sysid is essential for one of the authentication methods available in
>> PostgreSQL
>> (was it ident, I forgot) where the unix system password was used.

> Can't be ident, since I am running it with differing user ids. Perhaps
> some odd usage of password authentication, but I don't use that too much.
> Hence, can anyone comment on this?

AFAIK it's not *essential* to make Postgres and Unix UIDs the same
... but I think it is convenient to do so from an admin standpoint.
(One less set of numbers to keep track of, and one fewer way to get
confused about who is who.)  I would not like to see you remove a
feature that makes it easy to do that.

Of course there's no value in it if you are running a setup in which
not all the Postgres users have Unix-system accounts.  But that doesn't
mean there is no value in it for installations where there is such a
correspondence.

			regards, tom lane

Wed, 10 Nov 1999 21:40:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql 
On 1999-11-10, Tom Lane mentioned:

> AFAIK it's not *essential* to make Postgres and Unix UIDs the same
> ... but I think it is convenient to do so from an admin standpoint.
> (One less set of numbers to keep track of, and one fewer way to get
> confused about who is who.)  I would not like to see you remove a
> feature that makes it easy to do that.
> 
> Of course there's no value in it if you are running a setup in which
> not all the Postgres users have Unix-system accounts.  But that doesn't
> mean there is no value in it for installations where there is such a
> correspondence.

Excuse my ignorance once again, but

1) Why bother about those sysids at all? To the end user/administrator they
have about the same informational value as the oid of the float4 type. As
long as you always write "float4" or "username" you don't have to bother.

2) The mere fact of mentioning or even prompting for these ids confuses users.

3) If you really "keep track" of user ids (Unix or PostgreSQL) you really don't
have enough users or a really superior brain.

4) The purpose of the wrapper scripts was to provide "wrappers" around the
various SQL commands. If you do something in the scripts that you can't do in
"SQL" then we'll never stop having these confused users that at one point
almost caused us to remove the scripts altogether.

5) How exactly are you supposed to set the uid? The behaviour of
INSERT INTO pg_shadow VALUES (...);
and
UPDATE pg_shadow SET usesysid = ...;
is non-deterministic at best, unfortunately. The proper fix (ignoring the first
4 points above) would be to provide an extension to CREATE/ALTER USER, and
*then* we can extend the scripts that way.

I seems to me that the scripts were written before there even was a CREATE USER
command and then the functionality was just carried over without much
contemplation.

Well, okay, everyone that wants to set their PostgreSQL user id
explicitly, send me a note and I'll put it back in, which ever way.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Thu, 11 Nov 1999 22:33:08 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql 
Peter Eisentraut <peter_e@gmx.net> writes:
> 5) How exactly are you supposed to set the uid? The behaviour of
> INSERT INTO pg_shadow VALUES (...);
> and
> UPDATE pg_shadow SET usesysid = ...;
> is non-deterministic at best, unfortunately.

INSERT seems to have worked fine in the old version of createuser...
but I agree CREATE/ALTER USER ought to have the same functionality.

			regards, tom lane

Thu, 11 Nov 1999 16:36:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql 
> Well, okay, everyone that wants to set their PostgreSQL user id
> explicitly, send me a note and I'll put it back in, which ever way.

I thought that Tom Lane was representing me just fine, so was keeping
quiet ;)

An aside on procedures: on a change like this, I might have expected a
discussion on functionality *before* the patch was developed, since it
changes a seemingly fundamental feature. Though I haven't thought of a
strong, or even weak, argument for why id matching is necessary, it is
a topic about which there has been no discussion in the past, so I
didn't realize I needed an opinion until now.

Another aside: I'd like to think that most good ideas which stand the
test of an extended discussion will get a consensus to form. So if you
really think this is a step forward then keep talking about it; don't
give up too soon...

Back on topic: If there is currently no apparent need for a link
between Postgres user ids and external system ids, it is the case that
this is an obvious mechanism to make that link. So if someday a user
or a system feature needs it, it is already there and has been so from
day 1. afaik other DBs have a similar attribute for users.

                         - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 12 Nov 1999 07:39:55 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql
On Fri, 12 Nov 1999, Thomas Lockhart wrote:

> > Well, okay, everyone that wants to set their PostgreSQL user id
> > explicitly, send me a note and I'll put it back in, which ever way.
> 
> I thought that Tom Lane was representing me just fine, so was keeping
> quiet ;)

Okay, vote noted.

> An aside on procedures: on a change like this, I might have expected a
> discussion on functionality *before* the patch was developed, since it
> changes a seemingly fundamental feature. Though I haven't thought of a
> strong, or even weak, argument for why id matching is necessary, it is
> a topic about which there has been no discussion in the past, so I
> didn't realize I needed an opinion until now.

It seems to me that especially in the code I'm digging around now, there
are a lot of way old things lying around (think Postgres95). When I ask if
someone actually uses them I usually get responses like "No, we can't yank
it, someone might be using it", which doesn't answer my question at all.

Thus I found it more effective to threaten removal first and then see if
someone speaks up.

> Another aside: I'd like to think that most good ideas which stand the
> test of an extended discussion will get a consensus to form. So if you
> really think this is a step forward then keep talking about it; don't
> give up too soon...

I just remember the heart-breaking discussion about the pg_ prefix ;)

Well, I outlined my points: 1) It confuses users, 2) It doesn't match the
SQL, 3) user IDs are internal representations that you should not be able
to mess with with user-level tools, 4) If you can pick it, you should also
be able to change it later. But you can't, really.

> Back on topic: If there is currently no apparent need for a link
> between Postgres user ids and external system ids, it is the case that
> this is an obvious mechanism to make that link. So if someday a user
> or a system feature needs it, it is already there and has been so from
> day 1. afaik other DBs have a similar attribute for users.

This is based on the premise that it would somehow be useful to link Unix
and PostgreSQL users. In that case this would certainly be needed.

However, this would be a significant step backwards, since database users
are in general not equal to system users, most importantly since clients
might run on completely different systems than the server.

	-Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Fri, 12 Nov 1999 12:16:23 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: AW: [HACKERS] Re: [GENERAL] users in Postgresql