pg_dump and REVOKE on function
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.
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
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 19073Bruce 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
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
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 19073Fixed.
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