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(at)debis(dot)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;

/*