On Friday I asked.

> Has something changed recently?  I created a new database and it won't
> let me insert unless I specifically grant myself privledges.  It
> shows as being owned by me.  Also, I can't create a view.  It says
> "pg_rewrite: Permission denied." and doesn't create the view.

I have since found out that it was changed so that no one, not even the
owner, had full privs on the tables.  I went in and changed my code
to grant ALL on each database to the db owner.  Everything is fine 
except that I still can't create a view.  I still  get that "pg_rewrite:
Permission denied." error.  Any ideas?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 12 Aug 1998 15:44:53 -0400 (EDT)
darcy@druid.net (D'Arcy J.M. Cain)
Table permissions problem
> On Friday I asked.
> 
> > Has something changed recently?  I created a new database and it won't
> > let me insert unless I specifically grant myself privledges.  It
> > shows as being owned by me.  Also, I can't create a view.  It says
> > "pg_rewrite: Permission denied." and doesn't create the view.
> 
> I have since found out that it was changed so that no one, not even the
> owner, had full privs on the tables.  I went in and changed my code
> to grant ALL on each database to the db owner.  Everything is fine 
> except that I still can't create a view.  I still  get that "pg_rewrite:
> Permission denied." error.  Any ideas?
> 

Only superuser.  From TODO:

	CREATE VIEW requires super-user priviledge

This is because someone who can modify pg_rewrite can create their own
view to modify any table.


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

Wed, 12 Aug 1998 16:07:54 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Table permissions problem
Thus spake Bruce Momjian
> > I have since found out that it was changed so that no one, not even the
> > owner, had full privs on the tables.  I went in and changed my code
> > to grant ALL on each database to the db owner.  Everything is fine 
> > except that I still can't create a view.  I still  get that "pg_rewrite:
> > Permission denied." error.  Any ideas?
> > 
> 
> Only superuser.  From TODO:
> 
> 	CREATE VIEW requires super-user priviledge
> 
> This is because someone who can modify pg_rewrite can create their own
> view to modify any table.

Hmm.  That makes it difficult.  I would like to build databases from
scripts.  I guess I can build them as the super user.

Shouldn't the database owner have more privs then someone using the
database?  Is that what that TODO item is actually suggesting?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 12 Aug 1998 23:00:19 -0400 (EDT)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] Table permissions problem
> Thus spake Bruce Momjian
> > > I have since found out that it was changed so that no one, not even the
> > > owner, had full privs on the tables.  I went in and changed my code
> > > to grant ALL on each database to the db owner.  Everything is fine 
> > > except that I still can't create a view.  I still  get that "pg_rewrite:
> > > Permission denied." error.  Any ideas?
> > > 
> > 
> > Only superuser.  From TODO:
> > 
> > 	CREATE VIEW requires super-user priviledge
> > 
> > This is because someone who can modify pg_rewrite can create their own
> > view to modify any table.
> 
> Hmm.  That makes it difficult.  I would like to build databases from
> scripts.  I guess I can build them as the super user.
> 
> Shouldn't the database owner have more privs then someone using the
> database?  Is that what that TODO item is actually suggesting?

Remember some tables are shared with all databases.  Makes things more
difficult.



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

Thu, 13 Aug 1998 01:00:40 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Table permissions problem
>
> > Thus spake Bruce Momjian
> > > > I have since found out that it was changed so that no one, not even the
> > > > owner, had full privs on the tables.  I went in and changed my code
> > > > to grant ALL on each database to the db owner.  Everything is fine
> > > > except that I still can't create a view.  I still  get that "pg_rewrite:
> > > > Permission denied." error.  Any ideas?
> > > >
> > >
> > > Only superuser.  From TODO:
> > >
> > >     CREATE VIEW requires super-user priviledge
> > >
> > > This is because someone who can modify pg_rewrite can create their own
> > > view to modify any table.
> >
> > Hmm.  That makes it difficult.  I would like to build databases from
> > scripts.  I guess I can build them as the super user.
> >
> > Shouldn't the database owner have more privs then someone using the
> > database?  Is that what that TODO item is actually suggesting?
>
> Remember some tables are shared with all databases.  Makes things more
> difficult.

    And  that's  why I suggested a uid/euid model over functions,
    triggers and views, where  the  permission  checks  are  done
    against  the function/view owner instead of the current user.
    If nobody reverted things, this is  already  done  for  views
    (pg_user  vs  pg_shadow).  The ACL checks are done during the
    rewrite.

    So CREATE VIEW or CREATE RULE could eventually use some trick
    to  get around the restrictions on pg_rewrite but ensure that
    the rule owner is the one creating it. Pg_rewrite itself must
    be  protected, otherwise a user could change the ownership of
    a view and get around access restrictions.


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



Thu, 13 Aug 1998 11:19:14 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Table permissions problem
> > Remember some tables are shared with all databases.  Makes things more
> > difficult.
> 
>     And  that's  why I suggested a uid/euid model over functions,
>     triggers and views, where  the  permission  checks  are  done
>     against  the function/view owner instead of the current user.
>     If nobody reverted things, this is  already  done  for  views
>     (pg_user  vs  pg_shadow).  The ACL checks are done during the
>     rewrite.
> 
>     So CREATE VIEW or CREATE RULE could eventually use some trick
>     to  get around the restrictions on pg_rewrite but ensure that
>     the rule owner is the one creating it. Pg_rewrite itself must
>     be  protected, otherwise a user could change the ownership of
>     a view and get around access restrictions.

Couldn't we do some permission checks on tables at runtime from the
rewrite system ONLY when a table is being added or UPDATE is added to
SELECT, etc?

Would that solve the problem?  Maybe not because you would have to rip
apart the plan, wouldn't you?

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

Thu, 13 Aug 1998 07:36:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Table permissions problem
Thus spake Bruce Momjian
> > Hmm.  That makes it difficult.  I would like to build databases from
> > scripts.  I guess I can build them as the super user.
> > 
> > Shouldn't the database owner have more privs then someone using the
> > database?  Is that what that TODO item is actually suggesting?
> 
> Remember some tables are shared with all databases.  Makes things more
> difficult.

I didn't realize that.  I assume that you don't mean some system tables
as they could, I am sure, be special cased.  Can you expand?  I once
had a situation where a table in one database was trashed by being filled
with data from another table of the same name in another database.  Was
I bitten by this feature somehow?  At the same time I like the idea of
sharing tables.  It would be perfect for things like time zone and
province/state tables.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 13 Aug 1998 08:35:35 -0400 (EDT)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] Table permissions problem
>
> Thus spake Bruce Momjian
> > > Hmm.  That makes it difficult.  I would like to build databases from
> > > scripts.  I guess I can build them as the super user.
> > >
> > > Shouldn't the database owner have more privs then someone using the
> > > database?  Is that what that TODO item is actually suggesting?
> >
> > Remember some tables are shared with all databases.  Makes things more
> > difficult.
>
> I didn't realize that.  I assume that you don't mean some system tables
> as they could, I am sure, be special cased.  Can you expand?  I once
> had a situation where a table in one database was trashed by being filled
> with data from another table of the same name in another database.  Was
> I bitten by this feature somehow?  At the same time I like the idea of
> sharing tables.  It would be perfect for things like time zone and
> province/state tables.

    Especially system tables (pg_database, pg_shadow!, ...).


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



Thu, 13 Aug 1998 15:42:49 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Table permissions problem
>
> > > Remember some tables are shared with all databases.  Makes things more
> > > difficult.
> >
> >     And  that's  why I suggested a uid/euid model over functions,
> >     triggers and views, where  the  permission  checks  are  done
> >     against  the function/view owner instead of the current user.
> >     If nobody reverted things, this is  already  done  for  views
> >     (pg_user  vs  pg_shadow).  The ACL checks are done during the
> >     rewrite.
> >
> >     So CREATE VIEW or CREATE RULE could eventually use some trick
> >     to  get around the restrictions on pg_rewrite but ensure that
> >     the rule owner is the one creating it. Pg_rewrite itself must
> >     be  protected, otherwise a user could change the ownership of
> >     a view and get around access restrictions.
>
> Couldn't we do some permission checks on tables at runtime from the
> rewrite system ONLY when a table is being added or UPDATE is added to
> SELECT, etc?
>
> Would that solve the problem?  Maybe not because you would have to rip
> apart the plan, wouldn't you?
>
> --
> Bruce Momjian                          |  830 Blythe Avenue

    I  checked  it.   It's all already there. The rewrite handler
    checks for all the range table entries he adds to the  query,
    if   the   rule   owner   has   the  appropriate  permissions
    (read/write). And it sets the skipAcl field for  these  rte's
    so the executor later will ignore them.

    The  only  thing  missing is that CREATE VIEW and CREATE RULE
    require write access to pg_rewrite (create view does a create
    table  and create rule internally). But we cannot grant write
    access on pg_rewrite to looser users. So  I  think  a  global
    variable,  turning  off the whole ACL system temporary, would
    do.


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



Thu, 13 Aug 1998 15:54:32 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Table permissions problem
> Thus spake Bruce Momjian
> > > Hmm.  That makes it difficult.  I would like to build databases from
> > > scripts.  I guess I can build them as the super user.
> > > 
> > > Shouldn't the database owner have more privs then someone using the
> > > database?  Is that what that TODO item is actually suggesting?
> > 
> > Remember some tables are shared with all databases.  Makes things more
> > difficult.
> 
> I didn't realize that.  I assume that you don't mean some system tables
> as they could, I am sure, be special cased.  Can you expand?  I once
> had a situation where a table in one database was trashed by being filled
> with data from another table of the same name in another database.  Was
> I bitten by this feature somehow?  At the same time I like the idea of
> sharing tables.  It would be perfect for things like time zone and
> province/state tables.

See pgsql/data/ and you will see the tables that are shared:

	pg_database
	pg_group
	pg_shadow
	pg_variable

Makes sense they have to be shared.

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

Thu, 13 Aug 1998 10:32:50 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Table permissions problem
> > Would that solve the problem?  Maybe not because you would have to rip
> > apart the plan, wouldn't you?
> >
> > --
> > Bruce Momjian                          |  830 Blythe Avenue
> 
>     I  checked  it.   It's all already there. The rewrite handler
>     checks for all the range table entries he adds to the  query,
>     if   the   rule   owner   has   the  appropriate  permissions
>     (read/write). And it sets the skipAcl field for  these  rte's
>     so the executor later will ignore them.
> 
>     The  only  thing  missing is that CREATE VIEW and CREATE RULE
>     require write access to pg_rewrite (create view does a create
>     table  and create rule internally). But we cannot grant write
>     access on pg_rewrite to looser users. So  I  think  a  global
>     variable,  turning  off the whole ACL system temporary, would
>     do.

I understand.  We don't have APPEND rights separate from MODIFY rights.
Actually, I think they are defined in the system, but not used.  We have
a similar problem with pg_database.  People who can create databases can
remove other database entries from pg_database.

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

Thu, 13 Aug 1998 11:26:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Table permissions problem
Thus spake Bruce Momjian
> > > Remember some tables are shared with all databases.  Makes things more
> > > difficult.
> > 
> > I didn't realize that.  I assume that you don't mean some system tables
> > as they could, I am sure, be special cased.  Can you expand?  I once
> > had a situation where a table in one database was trashed by being filled
> > with data from another table of the same name in another database.  Was
> > I bitten by this feature somehow?  At the same time I like the idea of
> > sharing tables.  It would be perfect for things like time zone and
> > province/state tables.
> 
> See pgsql/data/ and you will see the tables that are shared:
> 
> 	pg_database
> 	pg_group
> 	pg_shadow
> 	pg_variable
> 
> Makes sense they have to be shared.

Sure.  I guess that had nothing to do with my problem then.  I guess it
also means that I can't share tables myself like I mentioned.  Oh well.

So could we special case these tables then and let the database owner
create views on the rest?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 13 Aug 1998 12:13:15 -0400 (EDT)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] Table permissions problem