Rails Error Must Be Owner Of Language Plpgsql
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 error: must be owner of extension plpgsql rds About Us Learn more about Stack Overflow the company Business Learn more about
Pg_restore Must Be Owner Of Extension Plpgsql
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join heroku error: must be owner of extension plpgsql the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR: must be owner of
Plpgsql Extension
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 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 create extension if not exists plpgsql with schema pg_catalog 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. ruby-on-rails ruby postgresql activerecord plpgsql share|improve this question edited Oct 9 '11 at 5:06 Erwin Brandstetter
Sign in Pricing Blog Support Search GitHub This repository Watch 1,020 Star 18,591 Fork 5,080 gitlabhq/gitlabhq Code Issues 1 Pull requests 0 Projects
Error: Must Be Owner Of Function
0 Pulse Graphs New issue During restore of gitlab-7.4.3-omnibus: "ERROR: must
Postgres Extension Plpgsql
be owner of extension plpgsql" #8292 Closed dandric-activevideo opened this Issue Nov 12, 2014 · 3 comments comment on extension plpgsql is 'pl/pgsql procedural language' Labels None yet Milestone No milestone Assignees No one assigned 3 participants dandric-activevideo commented Nov 12, 2014 I'm attempting to migrate an Omnibus GitLab 7.4.3 hosted on http://stackoverflow.com/questions/4368789/error-must-be-owner-of-language-plpgsql CentOS6 to another CentOS6 machine, using backup and restore. The source instance runs "gitlab-rake gitlab:backup:create" every night, and I copied the resulting tarball to the freshly installed destination instance, where I then tried: gitlab-rake gitlab:backup:restore BACKUP=1415689405 This results in one error, and several warnings. The error comes right at the start: Unpacking backup ... done Restoring database ... Restoring https://github.com/gitlabhq/gitlabhq/issues/8292 PostgreSQL database gitlabhq_production ... SET SET SET SET SET CREATE EXTENSION psql:/var/opt/gitlab/backups/db/database.sql:22: ERROR: must be owner of extension plpgsql but this does not seem immediately fatal, it continues with printing database statements (why are these echo'd anyway?): SET CREATE SEQUENCE ALTER TABLE [...] This goes on for some time, and after a while it prints these warnings: [...] ALTER TABLE psql:/var/opt/gitlab/backups/db/database.sql:15761: WARNING: no privileges could be revoked for "public" REVOKE psql:/var/opt/gitlab/backups/db/database.sql:15762: WARNING: no privileges could be revoked for "public" REVOKE psql:/var/opt/gitlab/backups/db/database.sql:15763: WARNING: no privileges were granted for "public" GRANT psql:/var/opt/gitlab/backups/db/database.sql:15764: WARNING: no privileges were granted for "public" GRANT [DONE] I saw something that looked like the "must be owner" error in #6067, but that issue appears to have been closed due to a timeout, without a real resolution. Any idea whether the restored instance will now have a correct database? The line 22 about which the error is reported just has: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; while lines 15761 through 15764 are: REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE AL
Rails application, I followed simple Amazon RDS Setup Guide , but still why I ended up banging my head for long https://pramodbshinde.wordpress.com/2014/03/31/do-not-bang-your-head-while-setting-up-amazon-rds-instance-like-i-did/ time before success. So I proceed by following steps, Before that I want to share my Rails application stack which is Rails 3.2.14 Ruby 1.9.3 PostgreSQL 9.1 Step 1: Launch DB instances This http://blog.endpoint.com/2012/10/postgres-system-triggers-error.html is the simplest step and pretty straight forward Go to http://aws.amazon.com/rds and click Sign Up for Amazon RDS. Follow the on-screen instructions. Here Point (2) is important => on-screen instruction , includes following key points 2. must be a) Selecting Database for a DB instance like MySQL, PostgreSQL, Oracle etc - I selected PostgreSQL 2. b) Selecting DB version, Amazon offers only PostgreSQL 9.3 as of date - as obvious I selected PostgreSQL 9.3 2. c) Selecting Instance types from various types according to our need. - I selected db.m1.medium 2. d) Setting up master user this is the most important, Amazon RDS setup wizard lets must be owner us to create a master user with inherited permissions from rds_admin user which is the default superuser for RDS - I created a master user as MyDBUser 2. e) Choosing DB name - should be same as the DB name which is going to be imported into RDS instance Apart from this selecting DB identifier, Allocated Storage, Backup setting and Backup Retention Period etc can be configured as per our choice and need or leave them to default. Step 2: Most Important - Importing Data to Amazon RDS Before that I want to share my config/database.yml and local DB user and roles details which is adapter: postgresql encoding: unicode database: blog_production port: 5432 pool: 5 username: MyDBUser password: ******** min_messages: WARNING host: localhost **Note: here I am connecting to ‘blog_production' as MyDBUser and I have created master user MyDBUser Also I imported some tables as ‘postgres' user form third party DB which was the need, with a simple table import commands pg_dump -U postgres -t TABLE_NAME DATABASE_NAME > FILE.sql What this command does?, This imports TABLE_NAME DATABASE_NAME in FILE.sql with ‘postgres' as a owner. Note: I had local database with multiple users as
2012 Tags: database, postgres, Ruby on Rails This mystifying Postgres error popped up for one of my coworkers lately while using Ruby on Rails: ERROR: permission denied: "RI_ConstraintTrigger_16410" is a system trigger On PostgreSQL version 9.2 and newer, the error may look like this: ERROR: permission denied: "RI_ConstraintTrigger_a_32778" is a system trigger ERROR: permission denied: "RI_ConstraintTrigger_c_32780" is a system trigger I labelled this as mystifying because, while Postgres' error system is generally well designed and gives clear messages, this one stinks. A better one would be something similar to: ERROR: Cannot disable triggers on a table containing foreign keys unless superuser As you can now guess, this error is caused by a non-superuser trying to disable triggers on a table that is used in a foreign key relationship, via the SQL command: ALTER TABLE foobar DISABLE TRIGGERS ALL; Because Postgres enforces foreign keys through the use of triggers, and because data integrity is very important to Postgres, one must be a superuser to perform such an action and bypass the foreign keys. (A superuser is a Postgres role that has "do anything" privileges). We'll look at an example of this in action, and then discuss solutions and workarounds. Note that if you are not a superuser *and* you are not the owner of the table, you will get a much better error message when you try to disable all the triggers: ERROR: must be owner of relation foobar To reproduce the original error, we will create two tables, and then link them together via a foreign key: postgres=# create user alice; CREATE ROLE postgres=# \c postgres alice You are now connected to database "postgres" as user "alice". -- Verify that we are not a superuser postgres=> select usename, usesuper from pg_user where usename = (select current_user); usename | usesuper ---------+---------- alice | f postgres=> create table foo(a int unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for table "foo" CREATE TABLE postgres=> create table bar(b int)