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:
Show quoted text
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 momjian Exp $
*
*-------------------------------------------------------------------------
*/
--- 8,14 ----
*
*
* IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.62 2001/05/03 21:16:48 tgl Exp $
*
*-------------------------------------------------------------------------
*/
***************
*** 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
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 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?
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:
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
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!
Show quoted text
regards, tom lane