Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump permissions problem

pg_dump permissions problem

From
Christopher Kings-Lynne
Date:
Hi guys,

My live 7.3.4 database dumps data like this:

SET SESSION AUTHORIZATION 'auadmin';

--
-- TOC entry 346 (OID 1012952)
-- Name: log_freeprofile; Type: TABLE; Schema: public; Owner: auadmin
--

CREATE TABLE log_freeprofile (    date date DEFAULT ('now'::text)::date NOT NULL,    "start" integer DEFAULT '0',
completeinteger DEFAULT '0',    gotojoin integer DEFAULT '0',    payment integer DEFAULT '0',    done integer DEFAULT
'0',   joined integer DEFAULT '0'
 
);


--
-- TOC entry 347 (OID 1012952)
-- Name: log_freeprofile; Type: ACL; Schema: public; Owner: auadmin
--

REVOKE ALL ON TABLE log_freeprofile FROM PUBLIC;
GRANT ALL ON TABLE log_freeprofile TO brett;
GRANT INSERT,SELECT,UPDATE ON TABLE log_freeprofile TO "au-php";
GRANT SELECT ON TABLE log_freeprofile TO GROUP readonly;
REVOKE ALL ON TABLE log_freeprofile FROM auadmin;


Now, loading this into 7.4beta3 gives this error:

ERROR:  dependent privileges exist

When executing the REVOKE ALL line.

I think this occurs because we do all the grants as auadmin, not as the 
superuser loading the file.  This means that all the grants that we 
grant are dependent privilegs, and hence that REVOKE line needs a 
CASCADE option.

Actually, why does that line even exist!?!?  Why do we need to REVOKE 
ALL from the table owner??

What should we do about this?

Chris

Re: pg_dump permissions problem

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> My live 7.3.4 database dumps data like this:

> REVOKE ALL ON TABLE log_freeprofile FROM PUBLIC;
> GRANT ALL ON TABLE log_freeprofile TO brett;
> GRANT INSERT,SELECT,UPDATE ON TABLE log_freeprofile TO "au-php";
> GRANT SELECT ON TABLE log_freeprofile TO GROUP readonly;
> REVOKE ALL ON TABLE log_freeprofile FROM auadmin;

> Now, loading this into 7.4beta3 gives this error:
> ERROR:  dependent privileges exist
> When executing the REVOKE ALL line.

Yeah, this is a known issue.  I think Peter was off looking into solutions.
It's clearly a "must fix" item.  Not sure if Bruce's open-items entryFix REVOKE ALL ON FUNCTION error when removing
ownerpermissions
 
describes exactly the same problem or something else.
        regards, tom lane

Re: pg_dump permissions problem

From
Bruce Momjian
Date:
Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > My live 7.3.4 database dumps data like this:
> 
> > REVOKE ALL ON TABLE log_freeprofile FROM PUBLIC;
> > GRANT ALL ON TABLE log_freeprofile TO brett;
> > GRANT INSERT,SELECT,UPDATE ON TABLE log_freeprofile TO "au-php";
> > GRANT SELECT ON TABLE log_freeprofile TO GROUP readonly;
> > REVOKE ALL ON TABLE log_freeprofile FROM auadmin;
> 
> > Now, loading this into 7.4beta3 gives this error:
> > ERROR:  dependent privileges exist
> > When executing the REVOKE ALL line.
> 
> Yeah, this is a known issue.  I think Peter was off looking into solutions.
> It's clearly a "must fix" item.  Not sure if Bruce's open-items entry
>     Fix REVOKE ALL ON FUNCTION error when removing owner permissions
> describes exactly the same problem or something else.

Yep, that is it.  It was reported as a bug by two people recently.

--  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