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(at)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(at)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(dot)klaver(at)gmail(dot)com