Thread: Having a problem with RoR-3.1.1 and Pg-9.1
On: Fri, 24 Feb 2012 07:33:01 -0800, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Friday, February 24, 2012 7:16:47 am James B. Byrne > wrote: >> CentOS-5.7 >> RoR-3.1.1 >> Pg-9.1 >> >> I am trying to run a test suite against Pg-9.1 for a >> RoR-3.1.1 based application. When I run the test DB >> setup task it fails: >> >> $ rake db:test:prepare >> Using AdapterExtensions >> psql:/...rails3/db/development_structure.sql:22: ERROR: >> must be owner of extension plpgsql >> >> >> The offending line in development_structure.sql is: >> >> COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural >> language'; >> >> The error does not happen if the target DBMS is >> Pg-8.4. I recall that with 8.4 that we would see >> warnings relating to trying to load/create plpgsql >> when it already existed in the test database but >> now we are getting an error which stops further >> processing. Is there a way around this? See here for similar report: http://archives.postgresql.org/pgsql-general/2012-02/msg00504.php The options seem to be run the script as the owner of the plpgsql EXTENSION or do not include the comment. How does one instruct pg_dump not to include the COMMENT for the plpgsql extension? The case in question is the automated creation of an sql script to create a test database from a development database. The owners of the development and test databases are the same but this user id is not a superuser and it certainly does not own the plpgsql extension. -- *** 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
On 02/27/2012 08:51 AM, James B. Byrne wrote: > > The options seem to be run the script as the owner of the > plpgsql EXTENSION or do not include the comment. > > How does one instruct pg_dump not to include the COMMENT > for the plpgsql extension? I am not sure pg_dump is including the COMMENT. From your previous post the source of the COMMENT would seem to be development_structure.sql, which I assume is coming from RoR. > > The case in question is the automated creation of an sql > script to create a test database from a development > database. The owners of the development and test > databases are the same but this user id is not a superuser > and it certainly does not own the plpgsql extension. Run the script as a superuser? If the purpose is create a database from scratch it would seem that would have to happen via a user with sufficient privileges on all the objects in the database. > -- Adrian Klaver adrian.klaver@gmail.com
On 2/27/12 9:51 AM, James B. Byrne wrote: > The options seem to be run the script as the owner of the > plpgsql EXTENSION or do not include the comment. > > How does one instruct pg_dump not to include the COMMENT > for the plpgsql extension? > > The case in question is the automated creation of an sql > script to create a test database from a development > database. The owners of the development and test > databases are the same but this user id is not a superuser > and it certainly does not own the plpgsql extension. > My development_structure.sql file has the exact same line at the same place and narry a problem. I don't believe the pg_dump scripts are used at all in creation of development_structure.sql. I assume the user for running db:test:prepare, is the database.yml test: user. It will need super user privs for your test DB. -ds
On 02/27/2012 08:51 AM, James B. Byrne wrote: > > The options seem to be run the script as the owner of the > plpgsql EXTENSION or do not include the comment. > > How does one instruct pg_dump not to include the COMMENT > for the plpgsql extension? Did some testing. So when you use 9.1 pg_dump to dump from versions prior to 9.1 it pulls a language in using CREATE OR REPLACE langname with no COMMENT on the language. If you use 9.1 pg_dump to dump a 9.1 database is uses the CREATE EXTENSION langname and adds the COMMENT you are seeing. As I know of no way to disable COMMENTs in a dump you are left with my suggestion from previous post, of running script as superuser. -- Adrian Klaver adrian.klaver@gmail.com
On Mon, February 27, 2012 13:54, Adrian Klaver wrote: > On 02/27/2012 08:51 AM, James B. Byrne wrote: >> >> The options seem to be run the script as the owner of >> the >> plpgsql EXTENSION or do not include the comment. >> >> How does one instruct pg_dump not to include the COMMENT >> for the plpgsql extension? > > I am not sure pg_dump is including the COMMENT. From your > previous post the source of the COMMENT would seem to be > development_structure.sql, which I assume is coming from > RoR. > RoR is using pg_dump to create development_structure.sql. A direct invocation of pg_dump -s from the command line gives this output. . . . COMMENT ON SCHEMA public IS 'standard public schema'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; . . . So, RoR is simply trying to deal with what Pg is providing it. The questions are: 1. Can the comments be suppressed? If so then how are the COMMENT statements suppressed within Pg itself or pg_dump?. The development_structure.sql is regenerated automatically so editing that file is futile. 2. Why is this an error in the first place? Surely a warning is sufficient if a comment cannot be applied. 3. Why are these dependencies not owned by the database owner to begin with? Surely this code: CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION "1.0"; ALTER EXTENSION plpgsql OWNER TO postgres; could just as easily specify the actual database owner instead? -- *** 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
On Mon, February 27, 2012 14:30, Adrian Klaver wrote: > On 02/27/2012 08:51 AM, James B. Byrne wrote: > >> >> The options seem to be run the script as the owner of >> the >> plpgsql EXTENSION or do not include the comment. >> >> How does one instruct pg_dump not to include the COMMENT >> for the plpgsql extension? > > Did some testing. So when you use 9.1 pg_dump to dump from > versions > prior to 9.1 it pulls a language in using CREATE OR > REPLACE langname > with no COMMENT on the language. If you use 9.1 pg_dump to > dump a 9.1 > database is uses the CREATE EXTENSION langname and adds > the COMMENT you > are seeing. As I know of no way to disable COMMENTs in a > dump you are left with my suggestion from previous post, > of running script as superuser. > Then what is the value of the GRANT CREATE DATABASE TO userid ability if a user cannot create a database without superuser privileges? -- *** 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
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> 1. Can the comments be suppressed?
No.
> 2. Why is this an error in the first place?
Because you're not running the script as superuser.
regards, tom lane
On Monday, February 27, 2012 11:44:09 am James B. Byrne wrote: > > 3. Why are these dependencies not owned by the database > owner to begin with? Surely this code: > > CREATE EXTENSION plpgsql > SCHEMA pg_catalog > VERSION "1.0"; > ALTER EXTENSION plpgsql > OWNER TO postgres; > > could just as easily specify the actual database owner > instead? I have not spent enough time with 9.1, exploring the change over to EXTENSIONs to manage a language to give you a definitive answer. However it would seem you can peg a LANGUAGE to a database owner: http://www.postgresql.org/docs/9.1/interactive/sql-createlanguage.html " Ordinarily, the user must have the PostgreSQL superuser privilege to register a new language. However, the owner of a database can register a new language within that database if the language is listed in the pg_pltemplate catalog and is marked as allowed to be created by database owners (tmpldbacreate is true). The default is that trusted languages can be created by database owners, but this can be adjusted by superusers by modifying the contents of pg_pltemplate. The creator of a language becomes its owner and can later drop it, rename it, or assign it to a new owner. " Not sure how that interacts with the EXTENSION mechanism though:( -- Adrian Klaver adrian.klaver@gmail.com
On Mon, February 27, 2012 15:44, Tom Lane wrote: > "James B. Byrne" <byrnejb@harte-lyne.ca> writes: >> 1. Can the comments be suppressed? > > No. > >> 2. Why is this an error in the first place? > > Because you're not running the script as superuser. > > regards, tom lane > Why is it necessary to run a script that creates a database by the superuser when the CREATE DATABASE privilege has been granted to the use running the script? This was not previously the case. -- *** 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
Here is an interesting situation. In PGAdmin3-1.14.2 when
I display the extension properties then I see this:
CREATE EXTENSION plpgsql
SCHEMA pg_catalog
VERSION "1.0";
ALTER EXTENSION plpgsql
OWNER TO postgres;
However, if I do this exact statement in the SQL pane
while connected as the postgres superuser then I see this:
ALTER EXTENSION plpgsql OWNER TO postgres;
ERROR: syntax error at or near "OWNER"
LINE 1: ALTER EXTENSION plpgsql OWNER TO postgres;
^
********** Error **********
ERROR: syntax error at or near "OWNER"
SQL state: 42601
Character: 25
So, as I am unfamiliar with the EXTENSION syntax, what is
wrong with the preceding statement? What is the correct
syntax?
--
*** 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
Obviously, I am missing something important here. The
database in question is created thusly:
CREATE DATABASE test
WITH OWNER = devl
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
The manual says this:
Loading an extension requires the same privileges that
would be required to create its component objects. For
most extensions this means superuser or database owner
privileges are needed. The user who runs CREATE EXTENSION
becomes the owner of the extension for purposes of later
privilege checks, as well as the owner of any objects
created by the extension's script.
However, looking at the extension properties shows this:
-- Extension: plpgsql
-- DROP EXTENSION plpgsql;
CREATE EXTENSION plpgsql
SCHEMA pg_catalog
VERSION "1.0";
ALTER EXTENSION plpgsql
OWNER TO postgres;
So, why is the plpgsql extension in test owned by postgres
and not by devl to begin with?
--
*** 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
On Monday, February 27, 2012 1:23:22 pm James B. Byrne wrote: > Obviously, I am missing something important here. The > database in question is created thusly: > > CREATE DATABASE test > WITH OWNER = devl > ENCODING = 'UTF8' > TABLESPACE = pg_default > LC_COLLATE = 'en_US.UTF-8' > LC_CTYPE = 'en_US.UTF-8' > CONNECTION LIMIT = -1; When you run the above are you doing it as user dev1 or the postgres user? > > > The manual says this: > > Loading an extension requires the same privileges that > would be required to create its component objects. For > most extensions this means superuser or database owner > privileges are needed. The user who runs CREATE EXTENSION > becomes the owner of the extension for purposes of later > privilege checks, as well as the owner of any objects > created by the extension's script. > > However, looking at the extension properties shows this: > > -- Extension: plpgsql > > -- DROP EXTENSION plpgsql; > > CREATE EXTENSION plpgsql > SCHEMA pg_catalog > VERSION "1.0"; > ALTER EXTENSION plpgsql > OWNER TO postgres; > > So, why is the plpgsql extension in test owned by postgres > and not by devl to begin with? Is plpgsql in template1 and you are running the CREATE DATABASE script as postgres? -- Adrian Klaver adrian.klaver@gmail.com
On Mon, February 27, 2012 16:37, Adrian Klaver wrote: > On Monday, February 27, 2012 1:23:22 pm James B. Byrne > wrote: >> Obviously, I am missing something important here. The >> database in question is created thusly: >> >> CREATE DATABASE test >> WITH OWNER = devl >> ENCODING = 'UTF8' >> TABLESPACE = pg_default >> LC_COLLATE = 'en_US.UTF-8' >> LC_CTYPE = 'en_US.UTF-8' >> CONNECTION LIMIT = -1; > > When you run the above are you doing it as user dev1 or > the postgres user? > >> >> >> The manual says this: >> >> Loading an extension requires the same privileges that >> would be required to create its component objects. For >> most extensions this means superuser or database owner >> privileges are needed. The user who runs CREATE >> EXTENSION >> becomes the owner of the extension for purposes of later >> privilege checks, as well as the owner of any objects >> created by the extension's script. >> >> However, looking at the extension properties shows this: >> >> -- Extension: plpgsql >> >> -- DROP EXTENSION plpgsql; >> >> CREATE EXTENSION plpgsql >> SCHEMA pg_catalog >> VERSION "1.0"; >> ALTER EXTENSION plpgsql >> OWNER TO postgres; >> >> So, why is the plpgsql extension in test owned by >> postgres and not by devl to begin with? > > Is plpgsql in template1 and you are running the CREATE > DATABASE script as postgres? If template1 has plpgsql as a language extension by default then I have not removed it. If it does not then I have not added it either. It is likely that I created the database initially in PGAdmin3 while connected to the server as the postgres user. Why would creating a database with a specified owner result in critical bits of it not being owned by the specified owner? That strikes me as bizarre. -- *** 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
On Monday, February 27, 2012 1:45:13 pm James B. Byrne wrote: > On Mon, February 27, 2012 16:37, Adrian Klaver wrote: > It is likely that I created the database initially in > PGAdmin3 while connected to the server as the postgres > user. Why would creating a database with a specified > owner result in critical bits of it not being owned by the > specified owner? That strikes me as bizarre. From psql do \l and see who actually owns the database. -- Adrian Klaver adrian.klaver@gmail.com
On Mon, February 27, 2012 17:16, Adrian Klaver wrote:
>
>
> From psql do \l and see who actually owns the database.
>
List of databases
Name | Owner | Encoding |
-------------------+----------------------+----------+--
devl | devl | UTF8 |
test | devl | UTF8 |
postgres | postgres | UTF8 |
template0 | postgres | UTF8 |
| | |
template1 | postgres | UTF8 |
| | |
(5 rows)
Collation, C-Type and Access Privileges columns removed.
--
*** 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
On Mon, Feb 27, 2012 at 6:22 PM, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
List of databases
On Mon, February 27, 2012 17:16, Adrian Klaver wrote:
>
>
> From psql do \l and see who actually owns the database.
>
Name | Owner | Encoding |
-------------------+----------------------+----------+--
devl | devl | UTF8 |
test | devl | UTF8 |
postgres | postgres | UTF8 |
template0 | postgres | UTF8 |
| | |
template1 | postgres | UTF8 |
| | |
(5 rows)
Collation, C-Type and Access Privileges columns removed.
template1=# \dL
List of languages
Name | Owner | Trusted
---------+----------+---------
plpgsql | postgres | t
template1=# CREATE DATABASE pl_test with owner=aklaver;
CREATE DATABASE
template1=# \c pl_test aklaver -
You are now connected to database "pl_test" as user "aklaver".
pl_test=>
pl_test=> \dL
List of languages
Name | Owner | Trusted
---------+----------+---------
plpgsql | postgres | t
(1 row)
pl_test=> \c - postgres
You are now connected to database "pl_test" as user "postgres".
pl_test=# DROP EXTENSION plpgsql ;
DROP EXTENSION
pl_test=# \c - aklaver
You are now connected to database "pl_test" as user "aklaver".
pl_test=> CREATE EXTENSION plpgsql ;
CREATE EXTENSION
pl_test=> \dL
List of languages
Name | Owner | Trusted
---------+---------+---------
plpgsql | aklaver | t
(1 row)
I guess the options are either do as I did above or create a new template database as the owner you want and use that as the template for your CREATE DATABASE.
On Tue, February 28, 2012 12:17, Adrian Klaver wrote: > Alright here is what I found: > > template1=# \dL > List of languages > Name | Owner | Trusted > ---------+----------+--------- > plpgsql | postgres | t > > template1=# CREATE DATABASE pl_test with owner=aklaver; > CREATE DATABASE > > template1=# \c pl_test aklaver - > You are now connected to database "pl_test" as user > "aklaver". > pl_test=> > > pl_test=> \dL > List of languages > Name | Owner | Trusted > ---------+----------+--------- > plpgsql | postgres | t > (1 row) > > pl_test=> \c - postgres > You are now connected to database "pl_test" as user > "postgres". > pl_test=# DROP EXTENSION plpgsql ; > DROP EXTENSION > > pl_test=# \c - aklaver > You are now connected to database "pl_test" as user > "aklaver". > pl_test=> CREATE EXTENSION plpgsql ; > CREATE EXTENSION > pl_test=> \dL > List of languages > Name | Owner | Trusted > ---------+---------+--------- > plpgsql | aklaver | t > (1 row) > > So when you do the CREATE DATABASE it goes to template1 > and grabs the PL with the permissions present in > template1. If you want to have the language > run with the permissions of the database owner you need > to drop the superuser version(as the superuser) and then > load it as the database owner. > As to why: > > http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html > > I guess the options are either do as I did above or > create a new template database as the owner you want > and use that as the template for your CREATE > DATABASE. > Sigh. I will have to think on this before changing anything. To my mind, the most straight-forward way of dealing with this is to remove the language from template1 altogether. Thereafter, the db owner must explicitly add it back in where required. So, the default would be no extension. -- *** 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
On 02/28/2012 09:50 AM, James B. Byrne wrote: > > > Sigh. I will have to think on this before changing anything. > > To my mind, the most straight-forward way of dealing with > this is to remove the language from template1 altogether. > Thereafter, the db owner must explicitly add it back in > where required. So, the default would be no extension. Well that is how it was prior to 9.0. -- Adrian Klaver adrian.klaver@gmail.com
On Tue, February 28, 2012 12:17, Adrian Klaver wrote: > > I guess the options are either do as I did above or > create a new template database as the owner you want > and use that as the template for your CREATE > DATABASE. 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; -- *** 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
On 02/28/2012 10:23 AM, James B. Byrne wrote: > > On Tue, February 28, 2012 12:17, Adrian Klaver wrote: > >> >> I guess the options are either do as I did above or >> create a new template database as the owner you want >> and use that as the template for your CREATE >> DATABASE. > > > 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 -- Adrian Klaver adrian.klaver@gmail.com
On Tue, February 28, 2012 12:52, Adrian Klaver wrote:
> On 02/28/2012 09:50 AM, James B. Byrne wrote:
>>
>>
>> Sigh. I will have to think on this before changing
>> anything.
>>
>> To my mind, the most straight-forward way of dealing
>> with
>> this is to remove the language from template1
>> altogether.
>> Thereafter, the db owner must explicitly add it back in
>> where required. So, the default would be no extension.
>
> Well that is how it was prior to 9.0.
>
It does not now appear to be the case. When I drop the
test database and attempt to recreate it the plpgsql
extension is automatically added back in with postgres as
the owner. This was a new installation of 9.1 and I have
made no modifications to the templates whatsoever.
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
language
--
*** 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
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
On 02/28/2012 10:37 AM, James B. Byrne wrote: > > On Tue, February 28, 2012 12:52, Adrian Klaver wrote: >> On 02/28/2012 09:50 AM, James B. Byrne wrote: >>> >>> >>> Sigh. I will have to think on this before changing >>> anything. >>> >>> To my mind, the most straight-forward way of dealing >>> with >>> this is to remove the language from template1 >>> altogether. >>> Thereafter, the db owner must explicitly add it back in >>> where required. So, the default would be no extension. >> >> Well that is how it was prior to 9.0. >> > > It does not now appear to be the case. When I drop the > test database and attempt to recreate it the plpgsql > extension is automatically added back in with postgres as > the owner. This was a new installation of 9.1 and I have > made no modifications to the templates whatsoever. http://www.postgresql.org/docs/9.0/interactive/release-9-0.html " E.8.3.7.1. PL/pgSQL Server-Side Language Install PL/pgSQL by default (Bruce Momjian) The language can still be removed from a particular database if the administrator has security or performance concerns about making it available. " Prior to version 9.0 plpgsql was included with the database but not installed until the DBA requested it. Starting with 9.0 it was installed by default. Basically went from opt-in to opt-out. You do not have to touch template1, the language is already loaded. If you do not want that behavior you have to remove it from template1 or any 'template' database you choose to CREATE a new database from. In your case, the new database you created inherited the language from template1 with the super user privileges. To undo that, in that particular database, you need to DROP the superuser version of the language(as the superuser) and then recreate it as the database owner user to get it to run as that user. For future use, assuming you do not want new databases to inherit the superuser version of plpgsl, you need to DROP it from template1. -- Adrian Klaver adrian.klaver@gmail.com
On 02/28/2012 10:52 AM, James B. Byrne wrote: > > 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? > The PgAdmin folks would be better able to help you with the exact reason for the above, but I suspect they really meant: http://www.postgresql.org/docs/9.1/interactive/sql-alterlanguage.html ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner So in the category I have learned something new: template1=# \dL List of languages Name | Owner | Trusted ---------+----------+--------- plpgsql | postgres | t (1 row) template1=# ALTER LANGUAGE plpgsql owner to aklaver; ALTER LANGUAGE template1=# \dL List of languages Name | Owner | Trusted ---------+---------+--------- plpgsql | aklaver | t (1 row) -- Adrian Klaver adrian.klaver@gmail.com
On 02/28/2012 10:52 AM, James B. Byrne wrote:
>
> 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.
No, you just did not run into the issue, probably because your template1
was just a straight clone of template0 with no added features
template1=# SELECT version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 32-bit
template1=# SELECT current_user;
current_user
--------------
postgres
template1=# CREATE TABLE perm_test(id integer, fld_1 text);
CREATE TABLE
template1=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | perm_test | table | postgres
template1=# \du
List of roles
Role name | Attributes | Member of
------------+-------------+--------------
aklaver | Create DB | {production}
template1=# \c - aklaver
psql (8.4.9)
You are now connected to database "template1" as user "aklaver".
template1=> CREATE DATABASE new_database;
CREATE DATABASE
template1=> \l
List of databases
Name | Owner | Encoding | Collation | Ctype |
Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
new_database | aklaver | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template1=> \c new_database
psql (8.4.9)
You are now connected to database "new_database".
new_database=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | perm_test | table | postgres
(1 row)
The privileges inherit from the template.
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Tue, February 28, 2012 14:03, Adrian Klaver wrote: >> > > The PgAdmin folks would be better able to help you with > the exact reason > for the above, but I suspect they really meant: > > http://www.postgresql.org/docs/9.1/interactive/sql-alterlanguage.html > > ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner > > So in the category I have learned something new: > > template1=# \dL > List of languages > Name | Owner | Trusted > ---------+----------+--------- > plpgsql | postgres | t > (1 row) > > template1=# ALTER LANGUAGE plpgsql owner to aklaver; > ALTER LANGUAGE > template1=# \dL > List of languages > Name | Owner | Trusted > ---------+---------+--------- > plpgsql | aklaver | t > (1 row) > I encountered a strange inconsistency with PGAdmin3-1.14.2 relating to this. After executing "ALTER LANGUAGE plpgsql owner to devl;" in the SQL query pane inside PGAdmin3 the extension ownership change is never reflected in the object properties pane. Even when disconnecting and reconnecting to the server after the change the properties pane of the extension in that database continues to show the plpgsql owner as postgres. psql on the other hand shows the new language owner as expected. By the way. Thank you very much for your assistance with this. It is greatly appreciated. Regards, -- *** 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
On Tue, February 28, 2012 14:17, Adrian Klaver wrote: > No, you just did not run into the issue, probably > because your template1 was just a straight clone of > template0 with no added features > You are correct. It was the inability to change the comment on the extension as required by the pg_dump file because of the ownership of the extension which triggered this entire discussion. I have removed plpgsql from template1 on all the systems affected. The tests now run against 9.1 fine. Regards, -- *** 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
On Tuesday, February 28, 2012 11:44:09 am James B. Byrne wrote: > > I encountered a strange inconsistency with PGAdmin3-1.14.2 > relating to this. After executing "ALTER LANGUAGE plpgsql > owner to devl;" in the SQL query pane inside PGAdmin3 the > extension ownership change is never reflected in the > object properties pane. Even when disconnecting and > reconnecting to the server after the change the properties > pane of the extension in that database continues to show > the plpgsql owner as postgres. psql on the other hand > shows the new language owner as expected. pgAdmin bug reports can be submitted here: http://www.pgadmin.org/support/list.php I am sure they would appreciate knowing about the above issue. > > By the way. Thank you very much for your assistance with > this. It is greatly appreciated. No problem. I learned some new things along the way also. > > Regards, -- Adrian Klaver adrian.klaver@gmail.com