Thread: pg_dump 7.4 bug
If you do this sequence of events, you get a failure to restore: 1. As superuser, do this: test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2-# LANGUAGE c; CREATE FUNCTION 2. Drop privs. test2=# alter user chriskl with nocreateuser; So, now we're a regular joe user. 3. pg_dump now gives this: SET SESSION AUTHORIZATION 'chriskl'; SET search_path = public, pg_catalog; -- -- TOC entry 37 (OID 853309) -- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: public; Owner: chriskl -- CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql.so', 'plpgsql_call_handler' LANGUAGEc; 4. Now, trying to restore this as the joe user gives: test2=> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler test2-> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2-> LANGUAGE c; ERROR: permission denied for language c This caused me pain in the 7.4 upgrade I just performed... Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > If you do this sequence of events, you get a failure to restore: This is not a pg_dump bug. Possibly ALTER USER should refuse to drop someone's superuserness if there is content in the database that depends on his superuserness, but I don't see how to enforce that. regards, tom lane
>>If you do this sequence of events, you get a failure to restore: > > This is not a pg_dump bug. > > Possibly ALTER USER should refuse to drop someone's superuserness if > there is content in the database that depends on his superuserness, > but I don't see how to enforce that. How about we allow changing owner of lanugages so I can fix this problem? Is it safe for me to just update the catalogs? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > How about we allow changing owner of lanugages so I can fix this problem? > Is it safe for me to just update the catalogs? Sure. regards, tom lane
Is this fixed in 7.5? --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > If you do this sequence of events, you get a failure to restore: > > 1. As superuser, do this: > > test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler > test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > test2-# LANGUAGE c; > CREATE FUNCTION > > 2. Drop privs. > > test2=# alter user chriskl with nocreateuser; > > So, now we're a regular joe user. > > 3. pg_dump now gives this: > > SET SESSION AUTHORIZATION 'chriskl'; > > SET search_path = public, pg_catalog; > > -- > -- TOC entry 37 (OID 853309) > -- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: > public; Owner: chriskl > -- > > CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > LANGUAGE c; > > 4. Now, trying to restore this as the joe user gives: > > test2=> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > test2-> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > test2-> LANGUAGE c; > ERROR: permission denied for language c > > This caused me pain in the 7.4 upgrade I just performed... > > Chris > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- 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
Only if you commit my pg_dump patch that's in the queue. Chris Bruce Momjian wrote: > Is this fixed in 7.5? > > --------------------------------------------------------------------------- > > Christopher Kings-Lynne wrote: > >>If you do this sequence of events, you get a failure to restore: >> >>1. As superuser, do this: >> >>test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler >>test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' >>test2-# LANGUAGE c; >>CREATE FUNCTION >> >>2. Drop privs. >> >>test2=# alter user chriskl with nocreateuser; >> >>So, now we're a regular joe user. >> >>3. pg_dump now gives this: >> >>SET SESSION AUTHORIZATION 'chriskl'; >> >>SET search_path = public, pg_catalog; >> >>-- >>-- TOC entry 37 (OID 853309) >>-- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: >>public; Owner: chriskl >>-- >> >>CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' >> LANGUAGE c; >> >>4. Now, trying to restore this as the joe user gives: >> >>test2=> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >>test2-> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' >>test2-> LANGUAGE c; >>ERROR: permission denied for language c >> >>This caused me pain in the 7.4 upgrade I just performed... >> >>Chris >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> > >
OK. --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > Only if you commit my pg_dump patch that's in the queue. > > Chris > > Bruce Momjian wrote: > > > Is this fixed in 7.5? > > > > --------------------------------------------------------------------------- > > > > Christopher Kings-Lynne wrote: > > > >>If you do this sequence of events, you get a failure to restore: > >> > >>1. As superuser, do this: > >> > >>test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler > >>test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > >>test2-# LANGUAGE c; > >>CREATE FUNCTION > >> > >>2. Drop privs. > >> > >>test2=# alter user chriskl with nocreateuser; > >> > >>So, now we're a regular joe user. > >> > >>3. pg_dump now gives this: > >> > >>SET SESSION AUTHORIZATION 'chriskl'; > >> > >>SET search_path = public, pg_catalog; > >> > >>-- > >>-- TOC entry 37 (OID 853309) > >>-- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: > >>public; Owner: chriskl > >>-- > >> > >>CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > >> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > >> LANGUAGE c; > >> > >>4. Now, trying to restore this as the joe user gives: > >> > >>test2=> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > >>test2-> AS '$libdir/plpgsql.so', 'plpgsql_call_handler' > >>test2-> LANGUAGE c; > >>ERROR: permission denied for language c > >> > >>This caused me pain in the 7.4 upgrade I just performed... > >> > >>Chris > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Don't 'kill -9' the postmaster > >> > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- 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