Thread: New buildin function

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.

-- 
Olleg Samoylov

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
referencesrole
 
}

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.