I am trying to create a view that will allow an application user to
effectively log in to an application, so that subsequent views can check
that user's permissions (I'm trying to implement a Virtual Private
Database - just to prove that PostgreSQL can do it).
It seems that code directly implemented in rules runs with the
permissions of the rule owner but that code in a function called from a
rule does not. This seems very strange to me. Is it a bug, something
that has not yet been implemented, or is there a fundamental reason why
this would be bad?
If it is a bug or missing feature I will look at fixing/implementing
it. If not, I'm back to the drawing board.
My view is:
create view logon as
select ctx_getperson() as name,
'########'::varchar(10) as password,
'####'::varchar(4) as role;
I want the on insert rule to check the users credentials
(username/password) against a table, t_person, that the user cannot see.
If I create my insert rule like this:
create rule logon_insert as
on insert to logon do instead
select ctx_logon(new.name, new.password, new.role);
Where ctx_logon checks against the t_person table. This gives me:
vpd=> insert into logon (name, password, role) values ('a', 'b', 'C');
ERROR: t_person: Permission denied.
It looks like the function is being run with the effective userid of the
caller and not the owner of the rule. This is true for both plpgsql and
C functions.
If instead the rule looks like:
create rule logon_insert as
on insert to logon do instead
select oid
from t_person
where name = new.name and
password = new.password;
Then the access to t_person is allowed:
vpd=> insert into logon (name, password, role) values ('a', 'b', 'C');
oid
-------
28407
(1 row)
All responses will be appreciated, insightful or humerous ones,
particularly.
--
Marc marc@bloodnok.com
