Thread: Having a problem with RoR-3.1.1 and Pg-9.1

Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
David Salisbury
Date:

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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Tom Lane
Date:
"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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:


On Mon, Feb 27, 2012 at 6:22 PM, James B. Byrne <byrnejb@harte-lyne.ca> wrote:

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.


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:


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.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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

Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
"James B. Byrne"
Date:
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


Re: Having a problem with RoR-3.1.1 and Pg-9.1

From
Adrian Klaver
Date:
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