Обсуждение: Here it is - view permissions
First step done, below is the patch to have views to override the permission checks for the accessed tables. Now we can do the following: CREATE VIEW db_user AS SELECT usename, usesysid, usecreatedb, usetrace, usecatupd, '**********'::text as passwd, valuntil FROM pg_user; REVOKE ALL ON pg_user FROM public; REVOKE ALL ON db_user FROM public; GRANT SELECT ON db_user TO public; And after setting up a non-privileged user account the user does wieck=> select usename, usesysid, passwd from pg_user; ERROR: pg_user: Permission denied. wieck=> select usename, usesysid, passwd from db_user; usename|usesysid|passwd -------+--------+---------- pgsql | 24|********** wieck | 201|********** (2 rows) wieck=> For now and as long as only superusers are permitted to create rules or views due to the pg_rewrite permissions this should be ok. But we should change it soon by adding the flag to pg_class as I said earlier and have only the view owner and superusers permitted to change this flag. This flag is useful anyway. We change the world now that ALL views are overriding the acl checks. If we have this flag we can setup views that behave like before (user cannot see anything through the view he cannot see without) and use the overriding only in cases we need it (like for the pg_user permissions problem). I'll work on it. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) # diff -r -c /usr/local/src/pgsql.orig/src/backend/executor/execMain.c ./src/backend/executor/execMain.c *** /usr/local/src/pgsql.orig/src/backend/executor/execMain.c Fri Feb 13 10:17:15 1998 --- ./src/backend/executor/execMain.c Fri Feb 20 04:43:39 1998 *************** *** 299,304 **** --- 299,315 ---- { RangeTblEntry *rte = lfirst(lp); + if (rte->skipAcl) + { + /* + * This happens if the access to this table is due + * to a view query rewriting - the rewrite handler + * checked the permissions against the view owner, + * so we just skip this entry. + */ + continue; + } + relid = rte->relid; htp = SearchSysCacheTuple(RELOID, ObjectIdGetDatum(relid), diff -r -c /usr/local/src/pgsql.orig/src/backend/nodes/copyfuncs.c ./src/backend/nodes/copyfuncs.c *** /usr/local/src/pgsql.orig/src/backend/nodes/copyfuncs.c Fri Feb 13 10:17:30 1998 --- ./src/backend/nodes/copyfuncs.c Fri Feb 20 02:38:13 1998 *************** *** 1495,1500 **** --- 1495,1501 ---- newnode->relid = from->relid; newnode->inh = from->inh; newnode->inFromCl = from->inFromCl; + newnode->skipAcl = from->skipAcl; return newnode; diff -r -c /usr/local/src/pgsql.orig/src/backend/rewrite/rewriteHandler.c ./src/backend/rewrite/rewriteHandler.c *** /usr/local/src/pgsql.orig/src/backend/rewrite/rewriteHandler.c Wed Feb 4 09:33:56 1998 --- ./src/backend/rewrite/rewriteHandler.c Fri Feb 20 04:39:37 1998 *************** *** 10,15 **** --- 10,16 ---- * *------------------------------------------------------------------------- */ + #include <string.h> #include "postgres.h" #include "miscadmin.h" #include "utils/palloc.h" *************** *** 29,41 **** #include "commands/creatinh.h" #include "access/heapam.h" static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule, ! int rt_index, int relation_level, int *modified); static List *fireRules(Query *parsetree, int rt_index, CmdType event, bool *instead_flag, List *locks, List **qual_products); static void QueryRewriteSubLink(Node *node); static List *QueryRewriteOne(Query *parsetree); static List *deepRewriteQuery(Query *parsetree); /* * gatherRewriteMeta - --- 30,48 ---- #include "commands/creatinh.h" #include "access/heapam.h" + #include "utils/syscache.h" + #include "utils/acl.h" + #include "catalog/pg_user.h" + static void ApplyRetrieveRule(Query *parsetree, RewriteRule *rule, ! int rt_index, int relation_level, ! Relation relation, int *modified); static List *fireRules(Query *parsetree, int rt_index, CmdType event, bool *instead_flag, List *locks, List **qual_products); static void QueryRewriteSubLink(Node *node); static List *QueryRewriteOne(Query *parsetree); static List *deepRewriteQuery(Query *parsetree); + static void CheckViewPerms(Relation view, List *rtable); /* * gatherRewriteMeta - *************** *** 219,225 **** *instead_flag = TRUE; return rule_lock->actions; } ! ApplyRetrieveRule(parsetree, rule_lock, rt_index, relation_level, &modified); if (modified) { --- 226,232 ---- *instead_flag = TRUE; return rule_lock->actions; } ! ApplyRetrieveRule(parsetree, rule_lock, rt_index, relation_level, relation, &modified); if (modified) { *************** *** 247,252 **** --- 254,260 ---- RewriteRule *rule, int rt_index, int relation_level, + Relation relation, int *modified) { Query *rule_action = NULL; *************** *** 256,271 **** int nothing, rt_length; int badsql = FALSE; rule_qual = rule->qual; if (rule->actions) { if (length(rule->actions) > 1) /* ??? because we don't handle ! * rules with more than one ! * action? -ay */ return; rule_action = copyObject(lfirst(rule->actions)); nothing = FALSE; } else { --- 264,304 ---- int nothing, rt_length; int badsql = FALSE; + int viewAclOverride = FALSE; rule_qual = rule->qual; if (rule->actions) { if (length(rule->actions) > 1) /* ??? because we don't handle ! * rules with more than one ! * action? -ay */ ! ! /* WARNING!!! ! * If we sometimes handle ! * rules with more than one ! * action, the view acl checks ! * might get broken. ! * viewAclOverride should only ! * become true (below) if this ! * is a relation_level, instead, ! * select query - Jan ! */ return; rule_action = copyObject(lfirst(rule->actions)); nothing = FALSE; + + /* + * If this rule is on the relation level, the rule action + * is a select and the rule is instead then it must be + * a view. Permissions for views now follow the owner of + * the view, not the current user. + */ + if (relation_level && rule_action->commandType == CMD_SELECT + && rule->isInstead) + { + CheckViewPerms(relation, rule_action->rtable); + viewAclOverride = TRUE; + } } else { *************** *** 284,290 **** rte->inFromCl = false; } rt_length = length(rtable); ! rtable = nconc(rtable, copyObject(rule_action->rtable)); parsetree->rtable = rtable; rule_action->rtable = rtable; --- 317,346 ---- rte->inFromCl = false; } rt_length = length(rtable); ! ! if (viewAclOverride) ! { ! List *rule_rtable, *rule_rt; ! RangeTblEntry *rte; ! ! rule_rtable = copyObject(rule_action->rtable); ! foreach(rule_rt, rule_rtable) ! { ! rte = lfirst(rule_rt); ! ! /* ! * tell the executor that the ACL check on this ! * range table entry is already done ! */ ! rte->skipAcl = true; ! } ! ! rtable = nconc(rtable, rule_rtable); ! } ! else ! { ! rtable = nconc(rtable, copyObject(rule_action->rtable)); ! } parsetree->rtable = rtable; rule_action->rtable = rtable; *************** *** 750,752 **** --- 806,850 ---- return rewritten; } + + + static void + CheckViewPerms(Relation view, List *rtable) + { + HeapTuple utup; + NameData uname; + List *rt; + RangeTblEntry *rte; + int32 aclcheck_res; + + /* + * get the usename of the view's owner + */ + utup = SearchSysCacheTuple(USESYSID, view->rd_rel->relowner, 0, 0, 0); + if (!HeapTupleIsValid(utup)) + { + elog(ERROR, "cache lookup for userid %d failed", + view->rd_rel->relowner); + } + StrNCpy(uname.data, + ((Form_pg_user) GETSTRUCT(utup))->usename.data, + NAMEDATALEN); + + /* + * check that we have read access to all the + * classes in the range table of the view + */ + foreach(rt, rtable) + { + rte = (RangeTblEntry *)lfirst(rt); + + aclcheck_res = pg_aclcheck(rte->relname, uname.data, ACL_RD); + if (aclcheck_res != ACLCHECK_OK) + { + elog(ERROR, "%s: %s", rte->relname, aclcheck_error_strings[aclcheck_res]); + } + } + } + + + diff -r -c /usr/local/src/pgsql.orig/src/include/nodes/parsenodes.h ./src/include/nodes/parsenodes.h *** /usr/local/src/pgsql.orig/src/include/nodes/parsenodes.h Wed Feb 11 15:25:44 1998 --- ./src/include/nodes/parsenodes.h Fri Feb 20 02:21:00 1998 *************** *** 864,869 **** --- 864,870 ---- Oid relid; bool inh; /* inheritance? */ bool inFromCl; /* comes from From Clause */ + bool skipAcl; /* skip ACL check in executor */ } RangeTblEntry; /*
> This flag is useful anyway. We change the world now that ALL > views are overriding the acl checks. If we have this flag we > can setup views that behave like before (user cannot see > anything through the view he cannot see without) and use the > overriding only in cases we need it (like for the pg_user > permissions problem). I'll work on it. OK, but why would anyone want the old behavior? I guess if you have a table that is not select-able by everyone, and you create a view on it, the default permits will allow select to others. You would have to set the permit on that view. Is there more to that pg_class flag you want to add? -- Bruce Momjian maillist@candle.pha.pa.us
> For now and as long as only superusers are permitted to > create rules or views due to the pg_rewrite permissions this > should be ok. But we should change it soon by adding the > flag to pg_class as I said earlier and have only the view > owner and superusers permitted to change this flag. > > This flag is useful anyway. We change the world now that ALL > views are overriding the acl checks. If we have this flag we > can setup views that behave like before (user cannot see > anything through the view he cannot see without) and use the > overriding only in cases we need it (like for the pg_user > permissions problem). I'll work on it. This patch is missing changes to the other node handling functions for rangetableentry. Do you want me to add them? -- Bruce Momjian maillist@candle.pha.pa.us
Bruce wrote: > > > This flag is useful anyway. We change the world now that ALL > > views are overriding the acl checks. If we have this flag we > > can setup views that behave like before (user cannot see > > anything through the view he cannot see without) and use the > > overriding only in cases we need it (like for the pg_user > > permissions problem). I'll work on it. > > OK, but why would anyone want the old behavior? > > I guess if you have a table that is not select-able by everyone, and you > create a view on it, the default permits will allow select to others. > You would have to set the permit on that view. Is there more to that > pg_class flag you want to add? No, there isn't more on that. It's just to be more backward compatible. Users out there might have already views and since it wasn't neccessary to set explicit permissions on a view up to now (views inherited the permissions from all tables they select), it's unlikely that anyone out there set them up. I think it's better to implement something that's a key to open the door instead of opening it by default and telling where's the key to lock it back. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
> > This patch is missing changes to the other node handling functions for > rangetableentry. Do you want me to add them? If you are so kind. It's only a few lines to copy and modify in nodes/outfuncs.c nodes/print.c nodes/readfuncs.c > > -- > Bruce Momjian > maillist@candle.pha.pa.us > Thanks, Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
> > No, there isn't more on that. It's just to be more backward > compatible. Users out there might have already views and > since it wasn't neccessary to set explicit permissions on a > view up to now (views inherited the permissions from all > tables they select), it's unlikely that anyone out there set > them up. > > I think it's better to implement something that's a key to > open the door instead of opening it by default and telling > where's the key to lock it back. This code is big enough without adding system table columns to allow backward compatability. Just tell them as part of the upgrade, they need to set GRANT/REVOKE on views. Put it in the /migration files too. -- Bruce Momjian maillist@candle.pha.pa.us
> > > > > This patch is missing changes to the other node handling functions for > > rangetableentry. Do you want me to add them? > > If you are so kind. > > It's only a few lines to copy and modify in > > nodes/outfuncs.c > nodes/print.c > nodes/readfuncs.c Done. Actually, several of the fields were missing. I don't know how I missed fixing them previously. -- Bruce Momjian maillist@candle.pha.pa.us
Bruce wrote: > > > > > No, there isn't more on that. It's just to be more backward > > compatible. Users out there might have already views and > > since it wasn't neccessary to set explicit permissions on a > > view up to now (views inherited the permissions from all > > tables they select), it's unlikely that anyone out there set > > them up. > > > > I think it's better to implement something that's a key to > > open the door instead of opening it by default and telling > > where's the key to lock it back. > > This code is big enough without adding system table columns to allow > backward compatability. Just tell them as part of the upgrade, they > need to set GRANT/REVOKE on views. Put it in the /migration files too. If that's OK for all of us let's add the information to /migration and the view permissions stuff is done then. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
Bruce Momjian wrote: > > OK, but why would anyone want the old behavior? > > I guess if you have a table that is not select-able by everyone, and you > create a view on it, the default permits will allow select to others. > You would have to set the permit on that view. Is there more to that > pg_class flag you want to add? Why does views default to 'select' permission for 'public'? I think most people will never think of the possibility that others will be able to SELECT their data through views. Should not 'create view' at least print a NOTICE about this? /* m */
> > Bruce Momjian wrote: > > > > OK, but why would anyone want the old behavior? > > > > I guess if you have a table that is not select-able by everyone, and you > > create a view on it, the default permits will allow select to others. > > You would have to set the permit on that view. Is there more to that > > pg_class flag you want to add? > > Why does views default to 'select' permission for 'public'? > I think most people will never think of the possibility that others > will be able to SELECT their data through views. > Should not 'create view' at least print a NOTICE about this? Because the current ACL_WORLD_DEFAULT in include/utils/acl.h is ACL_RD. Anything not revoked explicitly is granted select to public. Not only views. Think of it as a umask of 022. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
On Mon, 23 Feb 1998, Mattias Kregert wrote: > Bruce Momjian wrote: > > > > OK, but why would anyone want the old behavior? > > > > I guess if you have a table that is not select-able by everyone, and you > > create a view on it, the default permits will allow select to others. > > You would have to set the permit on that view. Is there more to that > > pg_class flag you want to add? > > Why does views default to 'select' permission for 'public'? > I think most people will never think of the possibility that others > will be able to SELECT their data through views. > Should not 'create view' at least print a NOTICE about this? Considering how much security we are putting around everything else, is it unreasonably to have both 'create view'/'create table' default to 'revoke all' to public, and 'grant all' to owner?
> > This code is big enough without adding system table columns to allow > > backward compatability. Just tell them as part of the upgrade, they > > need to set GRANT/REVOKE on views. Put it in the /migration files too. > > If that's OK for all of us let's add the information to > /migration and the view permissions stuff is done then. Ok, the following has been added to migration 6.2.1_6.3, and a note has been added to the item on the TODO/HISTORY list, though you can only see it now on the www page. --------------------------------------------------------------------------- In addition, 6.3 has separate permissions for views, rather than relying on the permissions set on the underlying tables. For this reason, you will have to set permissions on your views if you want anything but the default permissions. -- Bruce Momjian maillist@candle.pha.pa.us
> > Bruce Momjian wrote: > > > > OK, but why would anyone want the old behavior? > > > > I guess if you have a table that is not select-able by everyone, and you > > create a view on it, the default permits will allow select to others. > > You would have to set the permit on that view. Is there more to that > > pg_class flag you want to add? > > Why does views default to 'select' permission for 'public'? > I think most people will never think of the possibility that others > will be able to SELECT their data through views. > Should not 'create view' at least print a NOTICE about this? All tables are created with default permissions for SELECT to PUBLIC, so views are no different. -- Bruce Momjian maillist@candle.pha.pa.us
> > Why does views default to 'select' permission for 'public'? > > I think most people will never think of the possibility that others > > will be able to SELECT their data through views. > > Should not 'create view' at least print a NOTICE about this? > > Considering how much security we are putting around everything > else, is it unreasonably to have both 'create view'/'create table' default > to 'revoke all' to public, and 'grant all' to owner? Most commercial databases don't do this. -- Bruce Momjian maillist@candle.pha.pa.us
> > On Mon, 23 Feb 1998, Mattias Kregert wrote: > > > Bruce Momjian wrote: > > > > > > OK, but why would anyone want the old behavior? > > > > > > I guess if you have a table that is not select-able by everyone, and you > > > create a view on it, the default permits will allow select to others. > > > You would have to set the permit on that view. Is there more to that > > > pg_class flag you want to add? > > > > Why does views default to 'select' permission for 'public'? > > I think most people will never think of the possibility that others > > will be able to SELECT their data through views. > > Should not 'create view' at least print a NOTICE about this? > > Considering how much security we are putting around everything > else, is it unreasonably to have both 'create view'/'create table' default > to 'revoke all' to public, and 'grant all' to owner? include/utils/acl.h line 65 set ACL_WORLD_DEFAULT to ACL_NO Then tables and views default to what you wanted. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
On Mon, 23 Feb 1998, Bruce Momjian wrote: > > > Why does views default to 'select' permission for 'public'? > > > I think most people will never think of the possibility that others > > > will be able to SELECT their data through views. > > > Should not 'create view' at least print a NOTICE about this? > > > > Considering how much security we are putting around everything > > else, is it unreasonably to have both 'create view'/'create table' default > > to 'revoke all' to public, and 'grant all' to owner? > > Most commercial databases don't do this. Well, just checked with Wayne (My Oracle Guru) and in Oracle, everything is private by default, and you open it up as required/desired to other ppl...
Re: [HACKERS] Here it is - view permissions]
От
orion.SAPserv.Hamburg.dsh.de!wieck@sapserv.debis.de
Дата:
> > On Mon, 23 Feb 1998, Bruce Momjian wrote: > > > > > Why does views default to 'select' permission for 'public'? > > > > I think most people will never think of the possibility that others > > > > will be able to SELECT their data through views. > > > > Should not 'create view' at least print a NOTICE about this? > > > > > > Considering how much security we are putting around everything > > > else, is it unreasonably to have both 'create view'/'create table' default > > > to 'revoke all' to public, and 'grant all' to owner? > > > > Most commercial databases don't do this. > > Well, just checked with Wayne (My Oracle Guru) and in Oracle, > everything is private by default, and you open it up as required/desired > to other ppl... Microsoft SQL server too defaults to private and requires explicit GRANT for public. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
On Mon, 23 Feb 1998, Jan Wieck wrote: > > > > On Mon, 23 Feb 1998, Mattias Kregert wrote: > > > > > Bruce Momjian wrote: > > > > > > > > OK, but why would anyone want the old behavior? > > > > > > > > I guess if you have a table that is not select-able by everyone, and you > > > > create a view on it, the default permits will allow select to others. > > > > You would have to set the permit on that view. Is there more to that > > > > pg_class flag you want to add? > > > > > > Why does views default to 'select' permission for 'public'? > > > I think most people will never think of the possibility that others > > > will be able to SELECT their data through views. > > > Should not 'create view' at least print a NOTICE about this? > > > > Considering how much security we are putting around everything > > else, is it unreasonably to have both 'create view'/'create table' default > > to 'revoke all' to public, and 'grant all' to owner? > > include/utils/acl.h line 65 > > set ACL_WORLD_DEFAULT to ACL_NO > > Then tables and views default to what you wanted. Have you actually tried this? :) Does it break anything?
> > On Mon, 23 Feb 1998, Jan Wieck wrote: > > > > > > > On Mon, 23 Feb 1998, Mattias Kregert wrote: > > > > > > > Bruce Momjian wrote: > > > > > > > > > > OK, but why would anyone want the old behavior? > > > > > > > > > > I guess if you have a table that is not select-able by everyone, and you > > > > > create a view on it, the default permits will allow select to others. > > > > > You would have to set the permit on that view. Is there more to that > > > > > pg_class flag you want to add? > > > > > > > > Why does views default to 'select' permission for 'public'? > > > > I think most people will never think of the possibility that others > > > > will be able to SELECT their data through views. > > > > Should not 'create view' at least print a NOTICE about this? > > > > > > Considering how much security we are putting around everything > > > else, is it unreasonably to have both 'create view'/'create table' default > > > to 'revoke all' to public, and 'grant all' to owner? > > > > include/utils/acl.h line 65 > > > > set ACL_WORLD_DEFAULT to ACL_NO > > > > Then tables and views default to what you wanted. > > Have you actually tried this? :) Does it break anything? > > > No I didn't - but if I read your smiley correct it does - right? I'm close to fixing the backend crashes on REVOKE ALL ON pg_user and so I didn't wanted to loose any minute and check if the above works properly. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
On Mon, 23 Feb 1998, Jan Wieck wrote: > > > include/utils/acl.h line 65 > > > > > > set ACL_WORLD_DEFAULT to ACL_NO > > > > > > Then tables and views default to what you wanted. > > > > Have you actually tried this? :) Does it break anything? > > > > > > > > No I didn't - but if I read your smiley correct it does - right? Just rebuilding on my Solaris/i386 machine with this enabled...will let you know after I'm done :)
On Mon, 23 Feb 1998, Jan Wieck wrote: > > > include/utils/acl.h line 65 > > > > > > set ACL_WORLD_DEFAULT to ACL_NO > > > > > > Then tables and views default to what you wanted. > > > > Have you actually tried this? :) Does it break anything? > > > > > > > > No I didn't - but if I read your smiley correct it does - right? Only too well :) If I enable this, a \d no longer works as anyone but superuser, with the first error being: ERROR: pg_class: Permission denied. So it hits all the system tables too... Which wouldn't be too bad, just a matter of doing a 'GRANT SELECT' from inside of initdb to get around this... ...except, doing 'GRANT SELECT on pg_class to public' causes a 'PQexec() -- Request was sent to backend, but...' error message, and a 'Failed Assertion' on file inval.c... I'm not going to get a chance to investigate this during the day today, so if anyone else wants to look into it? If not, I'll look at it from home tonight and see if I can figure it out...
On Fri, 20 Feb 1998, Jan Wieck wrote: > First step done, > > below is the patch to have views to override the permission > checks for the accessed tables. Now we can do the following: > > CREATE VIEW db_user AS SELECT > usename, > usesysid, > usecreatedb, > usetrace, > usecatupd, > '**********'::text as passwd, > valuntil > FROM pg_user; I used the rewrite RULE here, instead of the above VIEW, with the VIEW being merely a 'SELECT * FROM pg_user'...by using the RULE (unless I'm mistaken?), if we change pg_user (ie. add another field at some later date), the view itself shouldn't require a change...
> > On Fri, 20 Feb 1998, Jan Wieck wrote: > > > First step done, > > > > below is the patch to have views to override the permission > > checks for the accessed tables. Now we can do the following: > > > > CREATE VIEW db_user AS SELECT > > usename, > > usesysid, > > usecreatedb, > > usetrace, > > usecatupd, > > '**********'::text as passwd, > > valuntil > > FROM pg_user; > > I used the rewrite RULE here, instead of the above VIEW, with the > VIEW being merely a 'SELECT * FROM pg_user'...by using the RULE (unless > I'm mistaken?), if we change pg_user (ie. add another field at some later > date), the view itself shouldn't require a change... I thought we couldn't use rules because COPY will dump out the password, no? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)