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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
