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

Mon, 29 Mar 2004 23:39:07 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
pg_dump 7.4 bug
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

Mon, 29 Mar 2004 11:01:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump 7.4 bug 
>>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


Tue, 30 Mar 2004 10:08:50 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump 7.4 bug
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

Mon, 29 Mar 2004 22:13:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump 7.4 bug 

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, Pennsylvania 19073

Fri, 9 Jul 2004 11:08:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump 7.4 bug
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
>>
> 
> 

Sat, 10 Jul 2004 02:40:26 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump 7.4 bug

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, Pennsylvania 19073

Fri, 9 Jul 2004 15:38:30 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump 7.4 bug