Thread: AW: \c connects as another user instead I want in psql

> >> Is it possible that 1060 and 1092 have the same usesysid 
> in pg_shadow?
> 
> > Hmmm. That was the problem. Thanks! By the way, could you 
> please define a
> > unique constraint on column 'usesysid' in future in PostgreSQL?
> 
> Yup, there should be one (and one on usename, too).  Not sure why it's
> been overlooked so far.

The usesysid was originally intended to map pg users to unix accounts.
I do not see why it should not be possible to map different pg users
to a single unix account. The above imho stems from an improper use of this 
column which needs to be fixed, not the column made unique.

Andreas

Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at> writes:
> The usesysid was originally intended to map pg users to unix accounts.
> I do not see why it should not be possible to map different pg users
> to a single unix account. The above imho stems from an improper use of this 
> column which needs to be fixed, not the column made unique.

No.  I'm not sure whether or not I believe the comment about Unix
accounts; Postgres does not care about Unix accounts, and never has
to my knowledge.  But it has always used the usesysid as owner
identification for database objects (tables etc).  If two different
users have the same usesysid then they are both the owner of these
objects; moreover they are interchangeable for permissions checks, too.
This is not a situation that has any practical use AFAICS.

There has been some talk of eliminating usesysid entirely in favor of
using the OID of the pg_shadow entry as the user's ID for ownership
identification.  If that happens, we'd want a unique index on OID
instead.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> No.  I'm not sure whether or not I believe the comment about Unix
> accounts; Postgres does not care about Unix accounts, and never has
> to my knowledge.  But it has always used the usesysid as owner
> identification for database objects (tables etc).  If two different
> users have the same usesysid then they are both the owner of these
> objects; moreover they are interchangeable for permissions checks, too.
> This is not a situation that has any practical use AFAICS.

On Unix it is reasonable to have multiple users with the same user ID.
You do this when they play the same role, but it is useful to
distinguish them for logging purposes.  They have different passwords,
of course, and logging code uses getlogin() to get the login name they
used.

I can imagine something similar within Postgres, using triggers to
record log information when changes are made.

Whether this is a feature worth having, I don't know.  But there is at
least one practical use.

Ian

---------------------------(end of broadcast)---------------------------
TIP 924: Good news from afar can bring you a welcome visitor.