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

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

Only if you commit my pg_dump patch that's in the queue.

Chris

Bruce Momjian wrote:

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