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

[Adrian Klaver <adrian.klaver@xxxxxxxxx>]

On Mon, Feb 27, 2012 at 6:22 PM, James B. Byrne <byrnejb@xxxxxxxxxxxxx> 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@xxxxxxxxx