Create Language Plpgsql Error Must Be Owner Of Database
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn error must be owner of extension plpgsql more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags error must be owner of extension plpgsql rds Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you,
Plpgsql Extension
helping each other. Join them; it only takes a minute: Sign up ERROR: must be owner of language plpgsql up vote 20 down vote favorite 1 I'm using PostgreSQL v9.0.1 with Rails (and it's deps) @ v2.3.8, owing
Create Extension If Not Exists Plpgsql With Schema Pg_catalog;
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 error must be owner of database postgres ./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. ruby-on-rails ruby postgresql activerecord plpgsql share|improve this question edited Oct 9 '11 at 5:06 Erwin Brandstetter 217k29353462 asked Dec 6 '10 at 16:44 Lee Hambley 3,96332661 1 I've considered that I should drop the pl/pgsql language, and install it manually each time. –Lee Hambley Dec 6 '10 at 19:56 add a comment| 5 Answers 5 active oldest votes up vote 21 down vote I had the same problem. I fixed my template with the commands below psql template1 template1=# alte
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users
Comment On Extension Plpgsql Is 'pl/pgsql Procedural Language';
International Sites Propaganda Resources Weekly News Re: 9.0: plpgsql eror when restoring could not execute query: error: must be owner of extension plpgsql a database as a non superuser From: Adrian Klaver
gist now Instantly share code, notes, and snippets. Star 8 Fork 2 rietta/plpgsql.rake Last active https://gist.github.com/rietta/7898366 Mar 27, 2016 Embed What would you like to do? Embed Embed this gist in your website. Embed Share Copy sharable URL for this gist. Share Clone http://alvinalexander.com/blog/post/postgresql/postgresql-error-must-be-owner-relation via HTTPS Clone with Git or checkout with SVN using the repository's web address. HTTPS Learn more about clone URLs Download ZIP Code Revisions 4 Stars 8 Forks must be 2 Are you using PostgreSQL and don't want to make your app run as PostgreSQL super user, then add this custom rake task to your `lib/tasks` folder and be happy. Raw plpgsql.rake # # PostgreSQL writes two optional commands to the database schema # file, called db/structure.sql, that can only be run as a root must be owner # database user. These are not needed actually, so comment them # out automatically # # CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; # COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; # namespace :db do namespace :structure do desc 'Comment out the plpgsql lines from structure.sql so that a non-root user can create the test database' task :fix_plpgsql do lines_to_strike = [ 'CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;', "COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';", ] schema_file = File.join(File.dirname(__FILE__), '..', '..', 'db', 'structure.sql') if File.exist?(schema_file) and (input = File.open(schema_file)) # Create a temp file, read through the original, commenting out the target lines. lines = Array.new line_count = 0 input.each_line do |line| if line line_count += 1 if lines_to_strike.include?(line.strip) lines << "-- The following was commented out by rake db:structure:fix_plpgsql\n" lines << '-- ' + line else lines << line end end end # each input.close if lines.count > line_count # Lines were commented out, s
latex (26) linux/unix (289) mac os x (315) mysql (54) ooa/ood (11) perl (156) php (97) postgresql (17) programming (43) ruby (56) scala (640) sencha (23) servlets (10) technology (84) testing (13) uml (24) zen (47) Postgresql error - must be owner of relation By Alvin Alexander. Last updated: June 3 2016 If you get a Postgresql error message like this: ERROR: must be owner of relation [your_table_here] don't worry too much, it probably means what it says: You don't own the table (or relation) you're trying to modify. Well, I guess you can worry if you can't get someone to fix the permissions, but if you can it's no big deal. I just had the problem trying to run some ALTER TABLE statements, and it turns out the Postgres user I logged in as didn't own the tables I was trying to modify. I sent an email to the database administrator, told him what the problem was, and he fixed the permissions. After that my ALTER TABLE commands worked just fine. If it helps you can look at the tables in your database by running this command from the Postgresql command line: \d That's the Postgresql describe command, and it will show you something like this: List of relations Schema | Name | Type | Owner --------+-------------------------+----------+-------- public | blogs | table | al public | users | table | postgres In my case, I got this Postgres error message when I was logged in as the Postgres user "al", and I was trying to modify the table named "users", which was owned by the Postgres user named "postgres". Once the database administrator changed that table to be owned by "al" my ALTER TABLE statement worked fine. postgresql table relation problem postgresql postgres error alter How to show Postgres table information Postgresql index - What to do when a Postgresql index count is wrong Tomcat DBCP error: Cannot create JDBC driver of class ... for connect URL null Postgresql backup - how to backup a Postgres database Postgres case insensitive searching Postgresql regular expressions (syntax, operators) books i’ve written Mast cell activation disease vs histamine intolerance (differences) Matt Cassel talking about Bill Belichick It’s a dif