Обсуждение: AW: [HACKERS] Re: [GENERAL] users in Postgresql
> > 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
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
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
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
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
> 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
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