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@xxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
