Thread: View permissions in 7.1
Hi, I'm setting up a system to allow certain users to see only certain records via a views. Thus, I revoked all permissions on the original tables, I set up some views that handle the filtering on the records using an account key, and these views I used to produce user views on which permissions are granted to select, update , ... In short, there are three layers of relations: 1. fysical tables: only access by sysadmin ARE USED BY 2. administrative views: only access by sysadmin ARE USED BY 3. user views: access to users depending on function The problem is, that e.g. when I try to do a select on a user view, on which a certain user has the permissions to select, I get an exception that tells me the user has no rights to access to underlying administrative views! So from this behaviour, either I must have completely misunderstood the authorization system, or their must be a bug in the system? The following link drove me into the direction of seting the system up like this. http://www.archonet.com/pgdocs/chap-access.html#RESTRICT-USERS Any comments will be greatly appreciated, Lieven A short example: /* table to link uid to administrative accounts */ CREATE TABLE adm_user (login char(20), admin char(20)); /* sample base table */ CREATE TABLE base (admin char(20), data text); /* sample administrative view */ CREATE VIEW adm_base AS SELECT b.data FROM base b, adm_user u WHERE (b.admin = u.admin) AND (u.login = bpchar(current_user)) ; /* rules to manipulate adm_base - omitted */ /* sample user view */ CREATE VIEW usr_base AS SELECT * FROM adm_base; After setting up the adm_user table, granting permissions on usr_base to a user, and connecting to the DB as that user, I get SELECT * FROM usr_base; Error: adm_base: permission denied. Of course, loosing the permissions on the adm_base view or base table could solve this issue, but the point was security in implementing this system!
Lieven Van Acker <lieven@elisa.be> writes:
> After setting up the adm_user table, granting permissions on usr_base
> to a user, and connecting to the DB as that user, I get
> SELECT * FROM usr_base;
> Error: adm_base: permission denied.
This is a bug in 7.1 --- I'll see if I can fix it for 7.1.1.
regards, tom lane
Lieven Van Acker <lieven@elisa.be> writes:
> [ permission checking doesn't work correctly for nested views in 7.1 ]
I think the attached patch fixes your problem; at least it fixes the
example you gave. Do you have time to try it out more heavily before
Friday? I'd like to commit it for 7.1.1 if it's right ...
regards, tom lane
*** src/backend/rewrite/rewriteHandler.c.orig Mon Apr 16 20:32:58 2001
--- src/backend/rewrite/rewriteHandler.c Wed May 2 22:06:16 2001
***************
*** 309,317 ****
--- 309,319 ----
Assert(subrte->relid == relation->rd_id);
subrte->checkForRead = rte->checkForRead;
subrte->checkForWrite = rte->checkForWrite;
+ subrte->checkAsUser = rte->checkAsUser;
rte->checkForRead = false; /* no permission check on subquery itself */
rte->checkForWrite = false;
+ rte->checkAsUser = InvalidOid;
/*
* FOR UPDATE of view?
Hi Tom, I guess I was a bit to optimistic about the patch. It seems like the select permissions are solved, but update (inc. insert / delete) operations still fail with permission denied on the nested views. Regards, Lieven Tom Lane wrote: > Lieven Van Acker <lieven@elisa.be> writes: > > [ permission checking doesn't work correctly for nested views in 7.1 ] > > I think the attached patch fixes your problem; at least it fixes the > example you gave. Do you have time to try it out more heavily before > Friday? I'd like to commit it for 7.1.1 if it's right ... > > regards, tom lane > > *** src/backend/rewrite/rewriteHandler.c.orig Mon Apr 16 20:32:58 2001 > --- src/backend/rewrite/rewriteHandler.c Wed May 2 22:06:16 2001 > *************** > *** 309,317 **** > --- 309,319 ---- > Assert(subrte->relid == relation->rd_id); > subrte->checkForRead = rte->checkForRead; > subrte->checkForWrite = rte->checkForWrite; > + subrte->checkAsUser = rte->checkAsUser; > > rte->checkForRead = false; /* no permission check on subquery itself */ > rte->checkForWrite = false; > + rte->checkAsUser = InvalidOid; > > /* > * FOR UPDATE of view? > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Lieven Van Acker <lieven@elisa.be> writes:
> I guess I was a bit to optimistic about the patch. It seems like the
> select permissions are solved, but update (inc. insert / delete)
> operations still fail with permission denied on the nested views.
That's no help; I need an example.
regards, tom lane
Lieven Van Acker <lieven@elisa.be> writes:
> I guess I was a bit to optimistic about the patch. It seems like the
> select permissions are solved, but update (inc. insert / delete)
> operations still fail with permission denied on the nested views.
Okay, the example you sent me off-list turns out to exhibit one bug
and one not-yet-implemented feature. There is a bug in permissions
checking for insert/update/delete rules (any references therein to
NEW or OLD should be checked against the rule owner, not the calling
user). A patch for that is attached. However, you were also expecting
that an SQL function call would provide "setuid" behavior, and it
doesn't. (I believe changing that is on the TODO list.) In the
meantime, you'd need to replace the current_adm() function call in your
adm_base view rules with explicit subselects, so that the accesses to
the "users" table are checked against the rule owner rather than the
calling user.
BTW, this patch changes the stored form of a rule, so you'd need to
drop and recreate the rule to make it take effect.
regards, tom lane
*** src/backend/rewrite/rewriteDefine.c.orig Fri Mar 23 13:45:24 2001
--- src/backend/rewrite/rewriteDefine.c Thu May 3 17:16:48 2001
***************
*** 8,14 ****
*
*
* IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.61 2001/03/23 04:49:54
momjianExp $
*
*-------------------------------------------------------------------------
*/
--- 8,14 ----
*
*
* IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.62 2001/05/03 21:16:48
tglExp $
*
*-------------------------------------------------------------------------
*/
***************
*** 377,383 ****
* We want the rule's table references to be checked as though by the
* rule owner, not the user referencing the rule. Therefore, scan
* through the rule's rtables and set the checkAsUser field on all
! * rtable entries (except *OLD* and *NEW*).
*/
foreach(l, action)
{
--- 377,383 ----
* We want the rule's table references to be checked as though by the
* rule owner, not the user referencing the rule. Therefore, scan
* through the rule's rtables and set the checkAsUser field on all
! * rtable entries.
*/
foreach(l, action)
{
***************
*** 426,454 ****
/*
* setRuleCheckAsUser
* Recursively scan a query and set the checkAsUser field to the
! * given userid in all rtable entries except *OLD* and *NEW*.
*/
static void
setRuleCheckAsUser(Query *qry, Oid userid)
{
List *l;
! /* Set all the RTEs in this query node, except OLD and NEW */
foreach(l, qry->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
- if (strcmp(rte->eref->relname, "*NEW*") == 0)
- continue;
- if (strcmp(rte->eref->relname, "*OLD*") == 0)
- continue;
-
if (rte->subquery)
{
!
! /*
! * Recurse into subquery in FROM
! */
setRuleCheckAsUser(rte->subquery, userid);
}
else
--- 426,453 ----
/*
* setRuleCheckAsUser
* Recursively scan a query and set the checkAsUser field to the
! * given userid in all rtable entries.
! *
! * Note: for a view (ON SELECT rule), the checkAsUser field of the *OLD*
! * RTE entry will be overridden when the view rule is expanded, and the
! * checkAsUser field of the *NEW* entry is irrelevant because that entry's
! * checkFor bits will never be set. However, for other types of rules it's
! * important to set these fields to match the rule owner. So we just set
! * them always.
*/
static void
setRuleCheckAsUser(Query *qry, Oid userid)
{
List *l;
! /* Set all the RTEs in this query node */
foreach(l, qry->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
if (rte->subquery)
{
! /* Recurse into subquery in FROM */
setRuleCheckAsUser(rte->subquery, userid);
}
else
Lieven Van Acker <lieven@elisa.be> writes:
> do I have to restore the original rewriteHandler.c? (before the first patch)
No, that patch is correct as far as it goes, and indeed necessary for
the second patch.
regards, tom lane
> Okay, the example you sent me off-list turns out to exhibit one bug > and one not-yet-implemented feature. There is a bug in permissions > checking for insert/update/delete rules (any references therein to > NEW or OLD should be checked against the rule owner, not the calling > user). A patch for that is attached. Thanks, I'll apply it. > However, you were also expecting > that an SQL function call would provide "setuid" behavior, and it > doesn't. (I believe changing that is on the TODO list.) In the > meantime, you'd need to replace the current_adm() function call in your > adm_base view rules with explicit subselects, so that the accesses to > the "users" table are checked against the rule owner rather than the > calling user. Well, in fact, -at this point - I don't need setuid, because the function current_adm() has to lookup the effective uid ofthe calling user. The point is I want to filter the records depending on the uid of the user calling the top-level view. So as I canunderstand, views that are called by other views run still within the same session - thus returning the effective uid, right? Kind Regards, Lieven.
Tom, do I have to restore the original rewriteHandler.c? (before the first patch) Lieven
Lieven Van Acker <lieven@elisa.be> writes:
> Well, in fact, -at this point - I don't need setuid, because the
> function current_adm() has to lookup the effective uid of the calling
> user. The point is I want to filter the records depending on the uid
> of the user calling the top-level view. So as I can understand, views
> that are called by other views run still within the same session -
> thus returning the effective uid, right?
The problem is that current_adm() fails for lack of read access on the
users table, when it's invoked on behalf of the unprivileged user.
I think that what you really want to be using for the lookup is
SESSION_USER not CURRENT_USER. There's no difference at the moment,
but there will be once we have setuid functions ...
regards, tom lane
Tom Lane wrote: > Lieven Van Acker <lieven@elisa.be> writes: > > Well, in fact, -at this point - I don't need setuid, because the > > function current_adm() has to lookup the effective uid of the calling > > user. The point is I want to filter the records depending on the uid > > of the user calling the top-level view. So as I can understand, views > > that are called by other views run still within the same session - > > thus returning the effective uid, right? > > The problem is that current_adm() fails for lack of read access on the > users table, when it's invoked on behalf of the unprivileged user. > You're right. I forgot to grant select priv's to public! > > I think that what you really want to be using for the lookup is > SESSION_USER not CURRENT_USER. There's no difference at the moment, > but there will be once we have setuid functions ... > Thanks for pointing this out. I'll have to change this to use the session_user! > > regards, tom lane