Having a problem with RoR-3.1.1 and Pg-9.1

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:

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:

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.

--
Adrian Klaver
adrian.klaver@gmail.com

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