ERROR: must be owner of language plpgsql
Solution 1
The solution was as follows:
On my installation, there are standard templates template0
and template1
- at least as I understand it postgres will look for the highest numbered templateN
when creating a new database, unless the template is specified.
In this instance, as template0
included plpgsql
, so did template1
… the idea being that you will customise template1
to suite your site specific default needs, and in the case that you blow everything up, you would restore template1
from template0
.
As my site specific requirement was to install plpgsql
as part of the automated build of my web application (a step we had to keep to maintain 8.4 compatibility) - the solution was easy: remove plpgsql
from template1
and the warning/error went away.
In the case that the site-specific defaults would change, and we should need to go back to the default behaviour, we would simply remove template1
and recreate it (which would use template0
)
Solution 2
I had the same problem. I fixed my template with the commands below
psql template1
template1=# alter role my_user_name with superuser;
read more at http://gilesbowkett.blogspot.com/2011/07/error-must-be-owner-of-language-plpgsql.html
Solution 3
For new readers, I read this older post after having run into this error in one of my own projects. I strongly feel that giving the app's PostgreSQL a superuser role is a terrible idea and changing the template is not ideal either. Since the referenced PSQL commands that are added by db:structure:dump
are not needed by the Rails app's database, I have written a custom rake task that comments out the problematic lines in structure.sql. I have shared that code publicly on Github as a Gist at https://gist.github.com/rietta/7898366.
Solution 4
I encountered this error while attempting to do RAILS_ENV=development bundle exec rake db:reset
. I was able to accomplish the same thing (for my purposes) by doing RAILS_ENV=development bundle exec rake db:drop db:create db:migrate
instead.
Lee Hambley
Updated on July 30, 2022Comments
-
Lee Hambley almost 2 years
I'm using
PostgreSQL v9.0.1
withRails
(and it's deps) @v2.3.8
, owing to the use of the fulltext capability of postgres, I have a table which is defined as:CREATE TABLE affiliate_products ( id integer NOT NULL, name character varying(255), model character varying(255), description text, price numeric(9,2), created_at timestamp without time zone, updated_at timestamp without time zone, textsearch_vector tsvector, );
Note the last line, this ensures that active record isn't able to process it with the standard schema dumper, so I have to set
config.active_record.schema_format = :sql
in./config/environment.rb
; and userake db:test:clone_structure
instead ofrake db:test:clone
.None of this is too remarkable, only inconvenient - however
rake db:test:clone_structure
fails with the error:ERROR: must be owner of language plpgsql
Because of line
#16
in my resulting./db/development_schema.sql
:CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
Under
PostgreSQL v9.0+
the languageplpsql
is installed by the superuser, to the initial template, which is then available to the newly created schema.I cannot run tests on this project without resolving this, and even editing
./db/development_schema.sql
manually is futile as it is regenerated every time I runrake db:test:clone_structure
(and ignored byrake db:test:clone
).I hope someone can shed some light on this?
Note: I have used both the
pg 0.9.0
adapter gem, and thepostgres
gem at version0.7.9.2008.01.28
- both display identical behaviour.My teammates run
PostgreSQL v8.4
where the language installation is a manual step.