Thread: Bug in pg_dump in 7.4.6?
I regularly do a pg_dump -s and store the file in CVS. Normally I briefly look over the diff before committing so I can describe the changes. This latest time I observed a strange behaviour. 7.4.6 pg_dump seems to be inserting a lot more SET SESSION AUTHORIZATION lines than previously. Before almost every object even when the owner is the same as before, whereas previously pg_dump only emitted them when the owner changed. I'm not exactly sure which version I had generated the previous dump with, but it was definitely a 7.4.x version. I don't see any changes related to this in the release notes since the 7.4 release notes. It's not causing me any problems aside from an overly chatty CVS diff. But I thought I would mention it in case it was an unintended change. -- greg
Greg Stark <gsstark@mit.edu> writes:
> 7.4.6 pg_dump seems to be inserting a lot more SET SESSION
> AUTHORIZATION lines than previously.
By "previously" do you mean 7.4.5 or thereabouts? I can't recall any
late-7.4.* changes that might affect this. pg_dump is supposed to
optimize away redundant SET SESSION AUTH commands. Can you give a
specific example of what you are seeing?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > 7.4.6 pg_dump seems to be inserting a lot more SET SESSION > > AUTHORIZATION lines than previously. > > By "previously" do you mean 7.4.5 or thereabouts? I think I went from 7.4.2 to 7.4.6. But I'm having trouble finding any records to back this up. postgres.log doesn't include the version when it starts up, and debian's installer tool doesn't leave an audit trail of upgrades (something that I've often been annoyed at). > I can't recall any late-7.4.* changes that might affect this. pg_dump is > supposed to optimize away redundant SET SESSION AUTH commands. Can you give > a specific example of what you are seeing? I'm sorry, I have to remove the actual identifiers from the schema excerpt before posting it. I replaced everything interesting by ellipses below. It seems the spurious SET SESSION AUTHORIZATION commands appear after any REVOKE/GRANT pair. SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: TABLE; Schema: public; Owner: dbo CREATE TABLE ... ( ... ); -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: TABLE; Schema: public; Owner: dbo CREATE TABLE ... ( ... ); -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: TABLE; Schema: public; Owner: dbo CREATE TABLE ... ( ... ); -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: TABLE; Schema: public; Owner: dbo CREATE TABLE ... ( ... ); -- Name: ...; Type: ACL; Schema: public; Owner: dbo REVOKE ALL ON TABLE ... FROM PUBLIC; GRANT SELECT,UPDATE ON TABLE ... TO dbuser; SET SESSION AUTHORIZATION 'dbo'; -- Name: ...; Type: SEQUENCE; Schema: public; Owner: dbo CREATE SEQUENCE ... ... -- greg
On Tue, 11 Jan 2005, Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
>> 7.4.6 pg_dump seems to be inserting a lot more SET SESSION
>> AUTHORIZATION lines than previously.
>
> By "previously" do you mean 7.4.5 or thereabouts? I can't recall any
> late-7.4.* changes that might affect this. pg_dump is supposed to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Maybe this one, from 7.4.4 relnotes?
- pg_dump handled ACLs with grant options incorrectly
> optimize away redundant SET SESSION AUTH commands. Can you give a
> specific example of what you are seeing?
>
> regards, tom lane
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
Greg Stark <gsstark@mit.edu> writes:
> It seems the spurious SET SESSION AUTHORIZATION commands appear after any
> REVOKE/GRANT pair.
Oh, right. In order to handle grants with GRANT OPTION, the dump data
may need to include SET SESSION AUTHORIZATION commands; so the code
assumes that it doesn't know the authorization any more after emitting
an ACL entry. Not a bug. It could possibly be smarter (eg grep the
text for "SET SESSION AUTHORIZATION" before deciding this) but since
that's not the default mode anymore anyway, I'm not very concerned.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > It seems the spurious SET SESSION AUTHORIZATION commands appear after any > > REVOKE/GRANT pair. > > Oh, right. In order to handle grants with GRANT OPTION, the dump data > may need to include SET SESSION AUTHORIZATION commands; so the code > assumes that it doesn't know the authorization any more after emitting > an ACL entry. Not a bug. It could possibly be smarter (eg grep the > text for "SET SESSION AUTHORIZATION" before deciding this) Wouldn't it make more sense to have a global state variable that held the current user and anyone invoking SET SESSION AUTHORIZATION has to set that state variable? Or have a function responsible for emitting SET SESSION AUTHORIZATION and bar other functions from doing it manually. Then have a local static variable in that function responsible for keeping state. > but since that's not the default mode anymore anyway, I'm not very > concerned. What's not the default mode? I'm just running "pg_dump -U postgresql -s db" -- greg
Greg Stark <gsstark@mit.edu> writes:
>> but since that's not the default mode anymore anyway, I'm not very
>> concerned.
> What's not the default mode? I'm just running "pg_dump -U postgresql -s db"
As of 8.0, I meant.
regards, tom lane