Tom, On Tue, Jul 25, 2017 at 16:43 Tom Lane <t...@sss.pgh.pa.us> wrote:
> AFAICT, pg_dump has no notion that it needs to be careful about the order > in which permissions are granted. I did > > regression=# create user joe; > CREATE ROLE > regression=# create user bob; > CREATE ROLE > regression=# create user alice; > CREATE ROLE > regression=# \c - joe > You are now connected to database "regression" as user "joe". > regression=> create table joestable(f1 int); > CREATE TABLE > regression=> grant select on joestable to alice with grant option; > GRANT > regression=> \c - alice > You are now connected to database "regression" as user "alice". > regression=> grant select on joestable to bob; > GRANT > > and then pg_dump'd that. The ACL entry for joestable looks like this: > > -- > -- TOC entry 5642 (class 0 OID 0) > -- Dependencies: 606 > -- Name: joestable; Type: ACL; Schema: public; Owner: joe > -- > > SET SESSION AUTHORIZATION alice; > GRANT SELECT ON TABLE joestable TO bob; > RESET SESSION AUTHORIZATION; > GRANT SELECT ON TABLE joestable TO alice WITH GRANT OPTION; > > Unsurprisingly, that fails to restore: > > db2=# SET SESSION AUTHORIZATION alice; > SET > db2=> GRANT SELECT ON TABLE joestable TO bob; > ERROR: permission denied for relation joestable > > > I am not certain whether this is a newly introduced bug or not. > However, I tried it in 9.2-9.6, and all of them produce the > GRANTs in the right order: > > GRANT SELECT ON TABLE joestable TO alice WITH GRANT OPTION; > SET SESSION AUTHORIZATION alice; > GRANT SELECT ON TABLE joestable TO bob; > RESET SESSION AUTHORIZATION; > > That might be just chance, but my current bet is that Stephen > broke it sometime in the v10 cycle --- especially since we > haven't heard any complaints like this from the field. Hmmm. I'll certainly take a look when I get back to a laptop, but I can't recall offhand any specific code for handling that, nor what change I might have made in the v10 cycle which would have broken it (if anything, I would have expected an issue from the rework in 9.6...). I should be able to look at this tomorrow though. Thanks! Stephen