Below is output from 7.3 pg_dump that is being loaded into 7.4 beta1.

It would seem that revoking the permissions of the owner doesn't work
out so well.


r=# CREATE FUNCTION weekdate (date) RETURNS timestamp with time zone
r-#     AS '
r'# SELECT cast(to_date(''01 01 ''|| extract(''year'' FROM $1), ''DD MM
YYYY'') +
r'# (cast(extract(''week'' FROM $1) AS numeric) *7-8) * interval ''1
day'' as timestamp with time zone);'
r-#     LANGUAGE sql;
CREATE FUNCTION
r=#
r=#
r=# --
r=# -- TOC entry 752 (OID 18968885)
r=# -- Name: weekdate (date); Type: ACL; Schema: public; Owner: rbt
r=# --
r=#
r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
REVOKE
r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
GRANT
r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
Tue, 12 Aug 2003 11:28:50 -0400
Rod Taylor <rbt@rbt.ca>
pg_dump and REVOKE on function
Rod Taylor <rbt@rbt.ca> writes:
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
> REVOKE
> r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
> GRANT
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
> ERROR:  dependent privileges exist
> HINT:  Use CASCADE to revoke them too.

Ugh.  We could fix pg_dump to output the commands in a better order,
but that won't help for dumps from existing releases.

Given that rbt is the owner of the object, I'm not sure that it is
sensible to interpret the above as revoking his ability to grant
privileges to others.  Seems to me that his ability to GRANT is inherent
in being the owner, and as such his "grant option" bits are irrelevant.
So maybe the commands are okay and the backend's interpretation is
bogus.

Peter, any thoughts?

			regards, tom lane

Tue, 12 Aug 2003 17:15:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump and REVOKE on function 
Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
> > REVOKE
> > r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
> > GRANT
> > r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
> > ERROR:  dependent privileges exist
> > HINT:  Use CASCADE to revoke them too.
> 
> Ugh.  We could fix pg_dump to output the commands in a better order,
> but that won't help for dumps from existing releases.
> 
> Given that rbt is the owner of the object, I'm not sure that it is
> sensible to interpret the above as revoking his ability to grant
> privileges to others.  Seems to me that his ability to GRANT is inherent
> in being the owner, and as such his "grant option" bits are irrelevant.
> So maybe the commands are okay and the backend's interpretation is
> bogus.
> 
> Peter, any thoughts?

Has this been resolved?


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 31 Aug 2003 00:26:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump and REVOKE on function
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Given that rbt is the owner of the object, I'm not sure that it is
>> sensible to interpret the above as revoking his ability to grant
>> privileges to others.
>>
>> Peter, any thoughts?

> Has this been resolved?

No.  I was hoping Peter would comment before we decide what to do.

			regards, tom lane

Sun, 31 Aug 2003 00:44:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump and REVOKE on function 
Tom Lane writes:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Given that rbt is the owner of the object, I'm not sure that it is
> >> sensible to interpret the above as revoking his ability to grant
> >> privileges to others.
> >>
> >> Peter, any thoughts?
>
> > Has this been resolved?
>
> No.  I was hoping Peter would comment before we decide what to do.

It's on my list, but it's tricky.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 1 Sep 2003 21:21:10 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: pg_dump and REVOKE on function 

This item has been added to the 7.4 open items list:

	ftp://momjian.postgresql.org/pub/postgresql/open_items

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.
> Below is output from 7.3 pg_dump that is being loaded into 7.4 beta1.
> 
> It would seem that revoking the permissions of the owner doesn't work
> out so well.
> 
> 
> r=# CREATE FUNCTION weekdate (date) RETURNS timestamp with time zone
> r-#     AS '
> r'# SELECT cast(to_date(''01 01 ''|| extract(''year'' FROM $1), ''DD MM
> YYYY'') +
> r'# (cast(extract(''week'' FROM $1) AS numeric) *7-8) * interval ''1
> day'' as timestamp with time zone);'
> r-#     LANGUAGE sql;
> CREATE FUNCTION
> r=#
> r=#
> r=# --
> r=# -- TOC entry 752 (OID 18968885)
> r=# -- Name: weekdate (date); Type: ACL; Schema: public; Owner: rbt
> r=# --
> r=#
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
> REVOKE
> r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
> GRANT
> r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
> ERROR:  dependent privileges exist
> HINT:  Use CASCADE to revoke them too.
> 
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 26 Sep 2003 12:26:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump and REVOKE on function
Fixed.

Bruce Momjian writes:

>
> This item has been added to the 7.4 open items list:
>
> 	ftp://momjian.postgresql.org/pub/postgresql/open_items
>
> ---------------------------------------------------------------------------
>
> Rod Taylor wrote:
> -- Start of PGP signed section.
> > Below is output from 7.3 pg_dump that is being loaded into 7.4 beta1.
> >
> > It would seem that revoking the permissions of the owner doesn't work
> > out so well.
> >
> >
> > r=# CREATE FUNCTION weekdate (date) RETURNS timestamp with time zone
> > r-#     AS '
> > r'# SELECT cast(to_date(''01 01 ''|| extract(''year'' FROM $1), ''DD MM
> > YYYY'') +
> > r'# (cast(extract(''week'' FROM $1) AS numeric) *7-8) * interval ''1
> > day'' as timestamp with time zone);'
> > r-#     LANGUAGE sql;
> > CREATE FUNCTION
> > r=#
> > r=#
> > r=# --
> > r=# -- TOC entry 752 (OID 18968885)
> > r=# -- Name: weekdate (date); Type: ACL; Schema: public; Owner: rbt
> > r=# --
> > r=#
> > r=# REVOKE ALL ON FUNCTION weekdate (date) FROM PUBLIC;
> > REVOKE
> > r=# GRANT ALL ON FUNCTION weekdate (date) TO PUBLIC;
> > GRANT
> > r=# REVOKE ALL ON FUNCTION weekdate (date) FROM rbt;
> > ERROR:  dependent privileges exist
> > HINT:  Use CASCADE to revoke them too.
> >
> -- End of PGP section, PGP failed!
>
>

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 5 Oct 2003 23:49:32 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: pg_dump and REVOKE on function