Tom Lane <tgl@xxxxxxxxxxxxx> writes:
> Greg Stark <gsstark@xxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
