Re: Need help extripating plpgsql

On 02/21/2013 03:18 PM, James B. Byrne wrote:
>
> On Thu, February 21, 2013 16:02, Adrian Klaver wrote:
>> On 02/21/2013 12:14 PM, James B. Byrne wrote:
>
>>>
>>> The current arrangement is not really satisfactory as it requires
>>> either separate template databases for each userid granted the
>>> DBCREATE role or the superuser role has to be granted in place of
>>> the DBCREATE role.
>>>
>>>
>>
>> That is sort of the point of the template system, different templates
>> for different situations.
>>
>
> Creating a new database from the system provided standard templates is
> not what I would consider a different situation requiring a
> specialized template for each and every user granted the DBCREATE
> role.  Requiring that seems to me to be busywork and a complete waste
> of DBA resources.
>
> If all the elements contained in the standard templates had their
> ownerships changed to that of the owner of the new database then my
> problem would never have arisen.  I do not understand why this is not
> the case.  Is there a reason why this is so?

Hmm, you might be on to something:

I changed owner in template1 to me:

p_test=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dL
                        List of languages
    Name    |  Owner   | Trusted |         Description
-----------+----------+---------+------------------------------
  plpgsql   | aklaver  | t       | PL/pgSQL procedural language
  plpythonu | postgres | f       |
(2 rows)

Create new database as me:

template1=# \c - aklaver
You are now connected to database "template1" as user "aklaver".
template1=> create database p_test template=template1 owner=aklaver;
CREATE DATABASE
template1=> \c p_test
You are now connected to database "p_test" as user "aklaver".

In new database language is owned by me.

p_test=> \dL
                        List of languages
    Name    |  Owner   | Trusted |         Description
-----------+----------+---------+------------------------------
  plpgsql   | aklaver  | t       | PL/pgSQL procedural language
  plpythonu | postgres | f       |
(2 rows)


Dump the database:

aklaver@panda:~> /usr/local/pgsql92/bin/pg_dump p_test -C -U aklaver -p
5442 -f p_test.sql


Dropped the database:

postgres=> drop database p_test ;
DROP DATABASE


Restored it:

aklaver@panda:~> /usr/local/pgsql92/bin/psql -d postgres -U aklaver -p
5442 -f p_test.sql
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "p_test" as user "aklaver".
SET
SET
SET
SET
SET
CREATE EXTENSION
psql:p_test.sql:39: ERROR:  must be owner of extension plpgsql



Now plpgsql is back to being owned by postgres:


postgres=> \c p_test
You are now connected to database "p_test" as user "aklaver".
p_test=> \dL
                       List of languages
   Name   |  Owner   | Trusted |         Description
---------+----------+---------+------------------------------
  plpgsql | postgres | t       | PL/pgSQL procedural language
(1 row)


The issue seems to be, from the p_test.sql file:

CREATE DATABASE p_test WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


It is creating the database using template0 instead for the template
specified in the CREATE DATABASE  run from psql.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com