\c connects as another user instead I want in psql

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

tir=> \c - 1060
You are now connected as new user 1060.
tir=> select user;
current_user
--------------
1092
(1 row)

Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?

regards, tom lane

On Wed, 2 May 2001, Tom Lane wrote:

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

tir=> \c - 1060
You are now connected as new user 1060.
tir=> select user;
current_user
--------------
1092
(1 row)

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?

Zoltan

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

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.

regards, tom lane

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

Kovacs Zoltan writes:

By the way, could you please define a unique constraint on column
'usesysid' in future in PostgreSQL?

The usesysid column will be removed and the oid column will be used
instead. That one tends to be unique, but an index will still be added.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

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.

TODO item has:

* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid

I overlooked it long ago because there is no cache lookup on that
column.

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

Kovacs Zoltan writes:

By the way, could you please define a unique constraint on column
'usesysid' in future in PostgreSQL?

The usesysid column will be removed and the oid column will be used
instead. That one tends to be unique, but an index will still be added.

Really? We are removing usesysid? Seems the admin will no longer be
able to choose the users id, right?

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

Bruce Momjian writes:

Really? We are removing usesysid? Seems the admin will no longer be
able to choose the users id, right?

Not that this was ever useful.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

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.

Peter Eisentraut wrote:

Bruce Momjian writes:

Really? We are removing usesysid? Seems the admin will no longer be
able to choose the users id, right?

Not that this was ever useful.

Except for re-adding users.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Really? We are removing usesysid? Seems the admin will no longer be
able to choose the users id, right?

Not that this was ever useful.

Except for re-adding users.

Yes. In theory, the correct answer to that is to add referential
integrity checks that prevent you from dropping a user that still
owns any objects. In practice, this is impractical because users
span a whole database installation. We have no reasonable way to
check whether the user owns objects in other databases that cannot
be seen from the DB where we are issuing the DROP USER command.

Therefore, for the foreseeable future it will be important to be
able to reverse a DROP USER command --- ie, recreate a user with
the same user identifier previously used.

After thinking about that for awhile, I am inclined to change my
previous position: we should not switch over to using the OIDs of
pg_shadow rows as user identifiers. usesysid should continue to
exist. Ditto for groups --- grosysid can't go away either.

I think the original motivation for wanting to eliminate these columns
was that we need usesysid and grosysid to be distinct (can't use the
same ID for both a user and a group). Using OIDs as IDs would fix
that, but it's overkill. Wouldn't it be sufficient to use an
installation-wide sequence object to assign new IDs for new users and
groups? We have no such animals at the present, but I see no reason
why we couldn't make one.

regards, tom lane

I think the original motivation for wanting to eliminate these columns
was that we need usesysid and grosysid to be distinct (can't use the
same ID for both a user and a group). Using OIDs as IDs would fix
that, but it's overkill. Wouldn't it be sufficient to use an
installation-wide sequence object to assign new IDs for new users and
groups? We have no such animals at the present, but I see no reason
why we couldn't make one.

Updated TODO to show both options:

* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid or
switch to pg_shadow.oid as user id

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

After thinking about that for awhile, I am inclined to change my
previous position: we should not switch over to using the OIDs of
pg_shadow rows as user identifiers. usesysid should continue to
exist. Ditto for groups --- grosysid can't go away either.

I think the original motivation for wanting to eliminate these columns
was that we need usesysid and grosysid to be distinct (can't use the
same ID for both a user and a group). Using OIDs as IDs would fix
that, but it's overkill. Wouldn't it be sufficient to use an
installation-wide sequence object to assign new IDs for new users and
groups? We have no such animals at the present, but I see no reason
why we couldn't make one.

One thing on the TODO list is to allow people to soecify OID's on
INSERT. There is no reason we should disallow it, and it could come in
handy for fixing deleted rows.

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

In 7.1.1 the following statement doesn't work (backend closes
immediately):

SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;

In 7.1 (final) this problem doesn't occur.

Workaround:

var1 := col1 FROM table WHERE conditions;
var2 := col2 FROM table WHERE conditions;

(Of course I'd better not rewrite my 200K code of PLGSQL... :-)

TIA, Zoltan

--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Kovacs Zoltan wrote:

In 7.1.1 the following statement doesn't work (backend closes
immediately):

SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;

In 7.1 (final) this problem doesn't occur.

It's a known bug.
If you in a hurry, please apply the latest change for
src/pl/plpgsql/src/pl_exec.c by Tom.

regards,
Hiroshi Inoue

There are SELECT INTO statements which work properly. Here is an example
which closes the backend:

CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/usr/local/pgsql-7.1.1/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

create table foo(x int4, y int4);

create function bugtest(int4) returns int4 as '
declare
_x int4;
_y int4;
begin
select into _x,_y
x, y from foo where x = $1 limit 1;
return x;
end;
' language 'plpgsql';

select bugtest(5);

If the WHERE clause doesn't contain any input parameters (i.e. $1), I
don't get into any trouble.

Zoltan

--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

In 7.1.1 the following statement doesn't work (backend closes
immediately):

SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;

Would you mind providing a complete test case, so that we don't waste
time guessing at context?

regards, tom lane

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

If the WHERE clause doesn't contain any input parameters (i.e. $1), I
don't get into any trouble.

Is this the known bug with failure if the SELECT returns zero rows?

regards, tom lane

On Mon, 14 May 2001, Tom Lane wrote:

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

If the WHERE clause doesn't contain any input parameters (i.e. $1), I
don't get into any trouble.

Is this the known bug with failure if the SELECT returns zero rows?

Yes, it is. I haven't known this bug yet, however I read the mailing lists
first. I also tried your patch and it works now greatly. Thanks,

Zoltan