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;
  
  /*

Fri, 20 Feb 1998 21:50:38 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Here it is - view permissions
>     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

Fri, 20 Feb 1998 17:11:44 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Here it is - view permissions
>     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

Fri, 20 Feb 1998 22:19:08 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [PATCHES] Here it is - view permissions
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) #



Sat, 21 Feb 1998 12:46:10 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Here it is - view permissions
> 
> 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) #


Sat, 21 Feb 1998 12:53:02 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [PATCHES] Here it is - view permissions
> 
>     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

Sat, 21 Feb 1998 11:49:55 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Here it is - view permissions
> 
> > 
> > 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

Sat, 21 Feb 1998 13:26:54 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [PATCHES] Here it is - view permissions

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) #



Mon, 23 Feb 1998 09:28:29 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Here it is - view permissions
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 */

Mon, 23 Feb 1998 11:32:04 +0100
Mattias Kregert <matti@algonet.se>
Re: [HACKERS] Here it is - view permissions
> 
> 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) #


Mon, 23 Feb 1998 13:14:39 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Here it is - view permissions
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?



Mon, 23 Feb 1998 08:06:33 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions
> > 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

Mon, 23 Feb 1998 08:07:26 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Here it is - view permissions
> 
> 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

Mon, 23 Feb 1998 08:10:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Here it is - view permissions
> > 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

Mon, 23 Feb 1998 08:15:17 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Here it is - view permissions]
> 
> 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) #


Mon, 23 Feb 1998 14:27:12 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Here it is - view permissions
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...



Mon, 23 Feb 1998 08:39:39 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions]
> 
> 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) #



Mon, 23 Feb 1998 15:12:41 +0100 (MET)
orion.SAPserv.Hamburg.dsh.de!wieck@sapserv.debis.de
Re: [HACKERS] Here it is - view permissions]
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?



Mon, 23 Feb 1998 09:33:09 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions
> 
> 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) #


Mon, 23 Feb 1998 15:42:30 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Here it is - view permissions
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 :)



Mon, 23 Feb 1998 09:43:51 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions
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...



Mon, 23 Feb 1998 10:30:19 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions
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...



Mon, 23 Feb 1998 23:18:39 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Here it is - view permissions
> 
> 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)

Mon, 23 Feb 1998 22:50:12 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [PATCHES] Re: [HACKERS] Here it is - view permissions