Thread: Re: row based security ... was Different views with same name for
Harald wants to be able to show different users different subsets of
data, and also give them different update permissions.
This is also what I want to do with a PostrgeSQL implementation of
Virtual Private Databases. Harald, you might want to check out some of
Oracle's documentation on this to help soldify your own ideas. Google
should be able to help. If not contact me and I'll see what I can find
for you.
Here is a quick view of what I want to achieve:
Consider a table "t_x" to which we wish to control access according to
the value of its "name" field, and the caller's access rights.
We create a view "x" on this table as follows:
create view x as
select *
from t_x
where i_can_see(name);
The access control is now placed firmly in the hands of the i_can_see()
function which we can make as simple or complex as we like. And we can
play similar tricks with update, insert and delete rules.
The problem we face is that the i_can_see function probably needs
greater levels of access than we want to grant to our everyday user.
For this, we need to be able to have functions which run with the
permissions of the rule owner rather than the caller (please see my
response, in plsql-general, to depesz@depesz.pl, Re: IDEA: "suid"
function).
There will necessarily be a performance penalty to pay for this but with
good design I believe that this can be minimsed.
Any thoughts anyone?
> Date: Sun, 20 Jan 2002 22:26:34 +0100
> From: "Harald Massa" <HaraldMassa@ghum.de>
> To: "Postgres Mailing List" <pgsql-general@postgresql.org>
> Subject: row based security ... was Different views with same name for
different users
> Message-ID: <015a01c1a1f9$2dc1aa80$0100a8c0@tog2>
>
> Hello,
>
> I have other words for my question, maybe they are more helpfull.
>
> What I'm looking for is ROW BASED SECURITY.
>
> That means:
>
> User A is allowed to look and update some rows
> User B is allowed to look and update some other rows
>
> Example: User A is only allowed to edit customers from the USA, User B
is
> allowed to edit customers from Europa.
>
> My solution is:
> one field (beraterid) in the table, and a view like
>
> create view pers as select * from totaldatabase
> where case currentuser="userA" then beraterid in
(1256,2523,2521,623,124)
> else beraterid in (9123, 12312,12313) end
>
>
> some pain is connected. BUT I strongly beliefe, there must be a
simpler,
> more elegant solution.
>
> What is it?
>
> Thanl you very much in advance
>
> HArald
--
Marc marc@bloodnok.com
Marc Munro wrote: > Harald wants to be able to show different users different subsets of > data, and also give them different update permissions. > > This is also what I want to do with a PostrgeSQL implementation of > Virtual Private Databases. Harald, you might want to check out some of > Oracle's documentation on this to help soldify your own ideas. Google > should be able to help. If not contact me and I'll see what I can find > for you. > > Here is a quick view of what I want to achieve: > > Consider a table "t_x" to which we wish to control access according to > the value of its "name" field, and the caller's access rights. > > We create a view "x" on this table as follows: > > create view x as > select * > from t_x > where i_can_see(name); > > The access control is now placed firmly in the hands of the i_can_see() > function which we can make as simple or complex as we like. And we can > play similar tricks with update, insert and delete rules. That is a fantastic trick. -- 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
Marc Munro <marc@bloodnok.com> writes:
> For this, we need to be able to have functions which run with the
> permissions of the rule owner rather than the caller (please see my
> response, in plsql-general, to depesz@depesz.pl, Re: IDEA: "suid"
> function).
I believe "suid" functions are a more practical solution than expecting
the rule mechanism to handle this for you. I don't want to put access
checking/id switching overhead into the basic expression evaluation
engine; but it's hard to see how we could make functions-invoked-in-rules
be treated specially without that. The problem is that expressions
coming out of the rewriter might be arbitrary combinations of clauses
that appeared in the rule and clauses that appeared in the user's
original query.
"Suid" functions have been on the TODO list for awhile. Peter E. has
been making noises recently suggesting that he's actually planning to
make them happen for 7.3.
regards, tom lane
Tom, Does this mean that all querytree permission checking is done at query build time? (I'm still trying to grok the source code in this area). I was hoping to let the rule system do all the hard permission checking work since it already does that, and the security implications of allowing rules to execute with greater privilege than their callers has already been considered at some length. True suid functions are probably a better idea but then we need to be able to limit who can execute such functions. This looks like a much more extensive change than I was anticipating. I don't fully understand the issue with rewritten expressions being arbitrary combinations of clauses from the rule and the users original query. Isn't each clause its own node (allowing us to determine whether the function should be called in the user's or rule-owner's context) or do I have to go and read the source some more ;-) Anyway, thanks for the response. I'll have to do some more hard thinking now. On Mon, 2002-01-21 at 12:19, Tom Lane wrote: > Marc Munro <marc@bloodnok.com> writes: > > For this, we need to be able to have functions which run with the > > permissions of the rule owner rather than the caller (please see my > > response, in plsql-general, to depesz@depesz.pl, Re: IDEA: "suid" > > function). > > I believe "suid" functions are a more practical solution than expecting > the rule mechanism to handle this for you. I don't want to put access > checking/id switching overhead into the basic expression evaluation > engine; but it's hard to see how we could make functions-invoked-in-rules > be treated specially without that. The problem is that expressions > coming out of the rewriter might be arbitrary combinations of clauses > that appeared in the rule and clauses that appeared in the user's > original query. > > "Suid" functions have been on the TODO list for awhile. Peter E. has > been making noises recently suggesting that he's actually planning to > make them happen for 7.3. > > regards, tom lane > -- Marc marc@bloodnok.com
Marc,
thank you very much for this idea.
> create view x as
> select *
> from t_x
> where i_can_see(name);
i had this function "can_see(bid)", and the can_see function checked for the
name.
It works.
BUT: this method kills the query-planner. seq_scan is chosen no matter what.
Today I used a simplified idea out of the earlier responses
create table useracc (uname text, bid int4);
insert into useracc values ('ham', 234);
insert into useracc values ('ham', 548);
(234 and 548 and... are the ids of a special field (like areacode) in the
database)
and the view
create view pers as select persganz join useracc on
persganz.berater=useracc.bid
and useracc.uname=current_user;
an
explain select persganz join useracc on persganz.berater=useracc.bid
and useracc.uname=current_user;
shows, that the index on persganz.berater is used... and useracc is
sequencially scanned (THAT is fine, because it is very very short)
but...
update
and
insert
are still painfull...
Thank you for your thoughts!
STILL I believe, that this kind of abstraction SHOULD exist on database
level... because it is a VERY common problem. Or isn't it?
By
HArald
Marc Munro <marc@bloodnok.com> writes:
> Does this mean that all querytree permission checking is done at query
> build time? (I'm still trying to grok the source code in this area).
No, it's done at executor startup (look in execMain.c, IIRC). The rule
rewriter does mark rangetable entries to tell the executor how to do the
checking --- so, in essence, we can tell whether a rangetable entry came
from the rule text or from the original query text. But we don't make
such distinctions for elements of expressions.
> True suid functions are probably a better idea but then we need to be
> able to limit who can execute such functions.
IIRC, call-permissions for functions are also part of the change Peter
has been muttering about.
> I don't fully understand the issue with rewritten expressions being
> arbitrary combinations of clauses from the rule and the users original
> query. Isn't each clause its own node (allowing us to determine whether
> the function should be called in the user's or rule-owner's context) or
> do I have to go and read the source some more ;-)
Yes, but (a) where did the node come from; (b) do you really want
ExecEvalExpr doing such checks? It's a hot spot already.
regards, tom lane
Harald,
Yes, this solution can be a performance nightmare. I know of two ways
to deal with this, neither of them perfect. You should definitely mark
the function as iscachable no matter how you proceed.
1) Make your function return an indexed column, eg:
create view x as
select *
from t_x
where name = name_I_can_see();
This doesn't work too well though if you can see more than one row!
Naturally there are variants of this approach but they all tend to
make optimising the query that much harder than it should really be.
2) Write all your queries as though they were against the original table
"t_x", rather than the view "x".
By this I mean that your application should provide all of the key
columns and specify all of the joins, as though you could see the
full set of data. The i_can_see() function then becomes a final
filter only after all other query and join criteria have been applied
by the optimiser. This kinda sucks too, as your user now has to know
details that you would probably like to be able to abstract away.
Whatever you choose to do, you are going to have to check your
performance very carefully as a badly written ad-hoc query will give you
all sorts of headaches.
FWIW, Oracle's VPD (Virtual Private Database) implementation has its
access checking functions return a text string predicate (eg "name =
'Marc' and x = y") which is added to the query plan at rewrite time.
They seem to believe that this offers the query optimiser more scope for
getting it right, which I suppose is true, but it seems like unnecessary
complexity to me. And I can't see how to do this in PostgreSQL.
On Mon, 2002-01-21 at 13:47, Harald Massa wrote:
> Marc,
>
> thank you very much for this idea.
>
> > create view x as
> > select *
> > from t_x
> > where i_can_see(name);
>
> i had this function "can_see(bid)", and the can_see function checked for the
> name.
> It works.
>
> BUT: this method kills the query-planner. seq_scan is chosen no matter what.
>
[ . . . ]
>
> HArald
>
>
--
Marc marc@bloodnok.com