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...
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 :)
