New buildin function

Olleg Samoylov writes:

What about adding new function:
pg_uid()
pg_session_uid()

as reference to internal function GetUserId() and GetSessionUserId().

CURRENT_USER, SESSION_USER

--
Peter Eisentraut peter_e@gmx.net

On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:

Hi!

What about adding new function:
pg_uid()
pg_session_uid()

as reference to internal function GetUserId() and GetSessionUserId().

These can help useful, for instance in row based securety.

Do CURRENT_USER and SESSION_USER not give those values?

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On 23 Jan 2003, Rod Taylor wrote:

RT>On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
RT>> Hi!
RT>>
RT>> What about adding new function:
RT>> pg_uid()
RT>> pg_session_uid()
RT>>
RT>> as reference to internal function GetUserId() and GetSessionUserId().
RT>>
RT>> These can help useful, for instance in row based securety.
RT>
RT>Do CURRENT_USER and SESSION_USER not give those values?

Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
it's key usesysid in table pg_shadow, for instance, for row based
permissions. Explain in example:

create table role {
role smallinteger, -- analog group of users
name text
}

create table permission { -- link role with pg_user
uid integer references pg_user(usesysid),
role smallint references role
}

create table protected_table {
-- payload fields
access smallint references role,
author_of_last_changes integer references pg_user(usesysid) default
PG_SESSION_UID(), -- proposed function
time_of_last_changes timestamp not null default current_timestamp
}

create function update_trigger_function() returns opaque as '
begin -- PG_UID() proposed function
if (select role from role where uid=PG_UID())=old.access then
new.time_of_last_changes=current_timestamp;
new.author_of_last_changes=PG_SESSION_UID(); -- proposed function
return new;
else
return null;
end if;
end;
' language 'plpgsql';

create trigger update_trigger before update on protected table for each row
execute procedure update_trigger_function();

О©╫ertainly, I can create such function in my own project as:

create function pg_uid() returns integer as '
select usesysid from pg_user where usename=current_user;
' language 'sql';

Or as C function:

long pg_uid()
{
return GetUserId();
}

But, IMHO, such fuction must be common.

--
Olleg Samoylov

On Fri, 24 Jan 2003, Olleg Samoylov wrote:

On 23 Jan 2003, Rod Taylor wrote:

Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
it's key usesysid in table pg_shadow, for instance, for row based
permissions. Explain in example:

create table role {
role smallinteger, -- analog group of users
name text
}

create table permission { -- link role with pg_user
uid integer references pg_user(usesysid),
role smallint references role
}

create table protected_table {
-- payload fields
access smallint references role,
author_of_last_changes integer references pg_user(usesysid) default
PG_SESSION_UID(), -- proposed function
time_of_last_changes timestamp not null default current_timestamp
}

As a side note, the above table definitions are pretty invalid.
Referencing system tables is not guaranteed to work, and referencing
system views even less so.