On Tue, February 28, 2012 13:28, Adrian Klaver wrote:
>>
>> Why does this not work?
>>
>> => \c test
>> You are now connected to database "test" as user "devl".
>> ALTER EXTENSION plpgsql OWNER TO devl;
>> ERROR: syntax error at or near "OWNER"
>> LINE 1: ALTER EXTENSION plpgsql OWNER TO devl;
>> => \c - postgres
>> You are now connected to database "test" as user
>> "postgres".
>> ALTER EXTENSION plpgsql OWNER TO devl;
>> ERROR: syntax error at or near "OWNER"
>> LINE 1: ALTER EXTENSION plpgsql OWNER TO devl;
>>
>
> There is no OWNER clause in ALTER EXTENSION.
> If you are trying to do what I showed, you need to as
> the'postgres' user
> DROP EXTENSION plpgsl in database 'test' and then become
> owner 'dev1'
> and CREATE EXTENSION plpgsl
>
And yet PGAdmin3-1.14.2 shows this in the sql pane when
the plpgsql object is selected:
- Extension: plpgsql
-- DROP EXTENSION plpgsql;
CREATE EXTENSION plpgsql
SCHEMA pg_catalog
VERSION "1.0";
ALTER EXTENSION plpgsql
OWNER TO postgres;
It seems to me that this arrangement of including
programming languages in a database is far more awkward
than it needs be. Since ALTER EXTENSION is a PostgreSQL
extension to SQL could not the ability to alter the owner
of an extension be provided as well?
As it presently stands, newly created databases
automatically get plpgsql with its comment. The comment
then causes any pg_restore file created with pg_dump to
choke unless the script is run by the owner of the
extension. Even if otherwise the user has CREATE DATABASE
privileges.
This behaviour effectively means that only the superuser
can restore databases in 9.1 or build them from scripts;
unless the default template is altered. Is this desired?
What then does GRANT CREATE DATABASE mean in 9.1 then? It
is certainly at odds with the behaviour in 8.x releases.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3