ERROR: must be owner of language plpgsql

I'm using PostgreSQL v9.0.1 with Rails (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 use rake db:test:clone_structure instead of rake 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 language plpsql 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 f开发者_运维百科utile as it is regenerated every time I run rake db:test:clone_structure (and ignored by rake 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 the postgres gem at version 0.7.9.2008.01.28 - both display identical behaviour.

My teammates run PostgreSQL v8.4 where the language installation is a manual step.

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

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.

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)

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.

I just filter the plpgsql extension statements from the structure.sql file post-dump:

# lib/tasks/db.rake

namespace :db do
  desc "Fix 'ERROR:  must be owner of extension plpgsql' complaints from Postgresql"
  task :fix_psql_dump do |task|
    filename = ENV['DB_STRUCTURE'] || File.join(ActiveRecord::Tasks::DatabaseTasks.db_dir, "structure.sql")
    sql = File.read(filename)
    sql.sub!(/(CREATE EXTENSION IF NOT EXISTS plpgsql)/, '-- \1')
    sql.sub!(/(COMMENT ON EXTENSION plpgsql)/, '-- \1')
    File.open(filename, 'w') do |f|
      f.write(sql)
    end
    task.reenable
  end
end

Rake::Task["db:structure:dump"].enhance do
  Rake::Task["db:fix_psql_dump"].invoke
end