Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump and REVOKE on function

pg_dump and REVOKE on function

From
Rod Taylor
Date:
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.

Re: pg_dump and REVOKE on function

From
Tom Lane
Date:
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

Re: pg_dump and REVOKE on function

From
Bruce Momjian
Date:
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,
Pennsylvania19073

Re: pg_dump and REVOKE on function

From
Tom Lane
Date:
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

Re: pg_dump and REVOKE on function

From
Peter Eisentraut
Date:
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

Re: pg_dump and REVOKE on function

From
Bruce Momjian
Date:
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,
Pennsylvania19073

Re: pg_dump and REVOKE on function

From
Peter Eisentraut
Date:
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