ERROR: must be owner of language plpgsql

Question

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 futile 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've considered that I should drop the `pl/pgsql` language, and install it manually each time. — Lee Hambley, Dec 06 '10 at 19:56

score 21 · Answer 1 · answered Dec 10 '13 at 21:28

21

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.

answered Dec 10 '13 at 21:28

Frank

461
3
4

1

Your comments are spot on - and the rake file provided in the gist works a peach - Thanks! – Ivar Jul 10 '14 at 18:18
I agree, this should really be the preferred answer to this question. – tobias.mcnulty Oct 09 '15 at 15:11
Thanks for providing up-to-date info, this should be the accepted answer – Alex from Jitbit Jun 20 '19 at 11:28

score 21 · Answer 2 · answered Sep 25 '11 at 07:42

21

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

answered Sep 25 '11 at 07:42

David Dehghan

22,159
10
107
95

2

This works for me; but what does `alter role my_user_name with superuser;` actually do? – Calvin Cheng Aug 06 '12 at 07:15
It makes your my_user_name , SuperUser of postgres. – Rahul garg Jun 29 '14 at 13:15
2

This is not a proper solution. I do not want to give this user superuser rights. – George Daramouskas Jan 30 '17 at 11:53

score 8 · Accepted Answer · answered Jan 04 '11 at 10:07

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)

It doesn't take the highest numbered template, it takes `template1` unless you specify something else. — Peter Eisentraut, Jan 04 '11 at 11:32
Thanks for the clarification Peter, mistake on my part. In the case that you would delete `template1` would `template0` not be the default? (thus supporting my assumption) — Lee Hambley, Jan 04 '11 at 13:46

score 2 · Answer 4 · answered May 30 '16 at 09:52

2

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.

answered May 30 '16 at 09:52

Phil Loden

1,394
1
15
15

bonkydog · Answer 5 · 2015-09-19T01:25:10.847

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

ERROR: must be owner of language plpgsql

5 Answers5

Linked