# Postgres and rake **URL:** \ **Category:** Rails\ **Created:** [February 13, 2006, 3:43am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372 "2006-02-13T03:43:17Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 3:43am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/1 "2006-02-13T03:43:17Z")
am in a bit of a quandry… I have asked postgresql-users list and haven’t gotten anything to work with… > > How do I change the owner of a schema? > > > > ALTER SCHEMA “public” OWNER to “some\_user”; #? > **[ALTER SCHEMA](https://www.postgresql.org/docs/current/sql-alterschema.html)** > > ALTER SCHEMA ALTER SCHEMA — change the definition of a schema Synopsis ALTER SCHEMA name RENAME TO new\_name ALTER SCHEMA … > The docs explain this very situation. HTML documentation ships with > the PostgreSQL distribution and can also be found online. * * * seeing as how the above line seems to me to be exactly like the page that you just referred me to, I have included some clips of my terminal transactions because quite clearly I am too stupid to understand this… th-db\_test=\> ALTER SCHEMA “public” OWNER TO tobyhouse; ERROR: syntax error at or near “OWNER” at character 23 th-db\_test=\> ALTER SCHEMA public OWNER TO tobyhouse; ERROR: syntax error at or near “OWNER” at character 21 # rpm -q postgresql-server postgresql-server-7.4.8-1.RHEL4.1 My problem is with rake… $ rake clone\_structure\_to\_test --trace (in /home/craig/ruby-db/th-db) \*\* Invoke clone\_structure\_to\_test (first\_time) \*\* Invoke db\_structure\_dump (first\_time) \*\* Invoke environment (first\_time) \*\* Execute environment \*\* Execute db\_structure\_dump \*\* Invoke purge\_test\_database (first\_time) \*\* Invoke environment \*\* Execute purge\_test\_database \*\* Execute clone\_structure\_to\_test psql:db/development\_structure.sql:28: NOTICE: CREATE TABLE will create implicit sequence “case\_managers\_id\_seq” for “serial” column “case\_managers.id” psql:db/development\_structure.sql:57: NOTICE: CREATE TABLE will create implicit sequence “placements\_id\_seq” for “serial” column “placements.id” psql:db/development\_structure.sql:70: NOTICE: CREATE TABLE will create implicit sequence “referral\_notes\_id\_seq” for “serial” column “referral\_notes.id” psql:db/development\_structure.sql:86: NOTICE: CREATE TABLE will create implicit sequence “clients\_id\_seq” for “serial” column “clients.id” psql:db/development\_structure.sql:103: NOTICE: CREATE TABLE will create implicit sequence “facilities\_id\_seq” for “serial” column “facilities.id” psql:db/development\_structure.sql:122: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “case\_managers\_pkey” for table “case\_managers” psql:db/development\_structure.sql:131: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “placements\_pkey” for table “placements” psql:db/development\_structure.sql:140: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “referral\_notes\_pkey” for table “referral\_notes” psql:db/development\_structure.sql:149: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “clients\_pkey” for table “clients” psql:db/development\_structure.sql:158: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index “facilities\_pkey” for table “facilities” psql:db/development\_structure.sql:211: ERROR: must be owner of schema public Apparently, I can’t set the owner of the schema to the user that I am using and actually owns the db and the tables in postgresql 7.4 How does one do this? Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 3:53am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/2 "2006-02-13T03:53:19Z")
On Feb 12, 2006, at 6:42 PM, Craig W. wrote: > alterschema.html > > > The docs explain this very situation. HTML documentation ships with > > the PostgreSQL distribution and can also be found online. The docs don’t show the name of the schema in quotes, nor the user for that matter. – – Tom M. ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 3:55am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/3 "2006-02-13T03:55:32Z")
On Sun, 2006-02-12 at 18:52 -0800, Tom M. wrote: > > > > > > > [http://www.postgresql.org/docs/current/interactive/sql-](http://www.postgresql.org/docs/current/interactive/sql-) > > alterschema.html > > > > > The docs explain this very situation. HTML documentation ships with > > > the PostgreSQL distribution and can also be found online. > > The docs don’t show the name of the schema in quotes, nor the user for > that matter. * * * 1 - the docs are for 8.1 2 - the part you clipped out from my post showed me doing it both ways - with and without quotes 3 - that command isn’t in the docs for 7.4 Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 4:57am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/4 "2006-02-13T04:57:31Z")
On Sun, 2006-02-12 at 19:42 -0700, Craig W. wrote: > [PostgreSQL: Documentation: 15: ALTER SCHEMA](http://www.postgresql.org/docs/current/interactive/sql-alterschema.html) > > \*\* Invoke clone\_structure\_to\_test (first\_time) > “case\_managers.id” > “facilities.id” > PRIMARY KEY will create implicit index “clients\_pkey” for table > How does one do this? > Within psql, can you try: \h GRANT 7.4 interactive docs: > **[GRANT](https://www.postgresql.org/docs/7.4/sql-grant.html)** > > GRANT GRANT — define access privileges Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | … GRANT ALL ON SCHEMA public TO tobyhouse; ? Ping me on IRC if you need help… robbyonrails (freenode). Cheers, -Robby – /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* - Robby R., Founder & Executive Director \* - PLANET ARGON, LLC | [www.planetargon.com](http://www.planetargon.com) \* - Ruby on Rails Development, Consulting, and Hosting \* - Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 \* - blog: [www.robbyonrails.com](http://www.robbyonrails.com) | book: [www.programmingrails.com](http://www.programmingrails.com) \* \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 6:00am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/5 "2006-02-13T06:00:20Z")
On Sun, 2006-02-12 at 20:57 -0700, Pat M. wrote: > I’m not sure why you’re having problems changing the schema owner. > However, are you having issues with rake? I’ve always gotten that > error, even if I set ‘public’ owned as my user, in both the dev and > test database. Rake tests all run perfectly fine still though. Is > that error killing your rake process or something? * * * yeah - I was happy as a pig in slop until I tried fooling with migrations and running into this road block (rake clearly didn’t do anything) was to create a new schema in all three (devel, test & prod) that my user owned and then I ended up dumping the data from my production and importing it back into the new schema instead of schema public. Clearly this is a better setup for long term usage anyway. What I did notice when I was playing around was that if the db didn’t exist at all, it was created - naturally before I created the new schema so it just tossed it into the public schema and I was on my way to fixing things anyway. Thanks for the follow… It does intrigue me that while I can work in development while others are using production, if I am working on controllers/models/views etc. I make make them crazy - but I guess that is something I will ultimately figure out. Thanks for the follow ups everyone Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 4:58am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/6 "2006-02-13T04:58:10Z")
I’m not sure why you’re having problems changing the schema owner. However, are you having issues with rake? I’ve always gotten that error, even if I set ‘public’ owned as my user, in both the dev and test database. Rake tests all run perfectly fine still though. Is that error killing your rake process or something? Pat ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 14, 2006, 11:39pm UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/7 "2006-02-14T23:39:24Z")
Craig W. wrote: > > > > th-db\_test=\> ALTER SCHEMA “public” OWNER TO tobyhouse; > $ rake clone\_structure\_to\_test --trace > psql:db/development\_structure.sql:28: NOTICE: CREATE TABLE will create > implicit sequence “case\_managers\_id\_seq” for “serial” column ---- cut ---- > psql:db/development\_structure.sql:211: ERROR: must be owner of schema > public > > Apparently, I can’t set the owner of the schema to the user that I am > using and actually owns the db and the tables in postgresql 7.4 > > How does one do this? > > Craig You have to log into the server as the super-user ‘postgres’ to do this. But I wouldn’t change the owner of the public schema. It probably will cause trouble latter. It probably would be better to create a new user for RoR to use, create a schema under that name, then all tables etc. created by RoR will be in that schema. Regards Neil. ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 13, 2006, 6:02am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/8 "2006-02-13T06:02:29Z")
On Sun, 2006-02-12 at 19:57 -0800, Robby R. wrote: > > > > > > > ERROR: syntax error at or near “OWNER” at character 23 > > (in /home/craig/ruby-db/th-db) > > implicit sequence “case\_managers\_id\_seq” for “serial” column > > implicit sequence “facilities\_id\_seq” for “serial” column > > psql:db/development\_structure.sql:149: NOTICE: ALTER TABLE / ADD > > Ping me on IRC if you need help… robbyonrails (freenode). * * * I granted the yin and the yang but rake is pretty adamant about the user ‘owning’ the schema - so as you might notice from the other post, I created a new schema for all environments and dumped the development db, did a find/replace on the public schema and imported it back in…life seems to be fine. Thanks Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [February 15, 2006, 12:09am UTC](https://www.ruby-forum.com/t/postgres-and-rake/66372/9 "2006-02-15T00:09:37Z")
On Wed, 2006-02-15 at 09:36 +1100, Neil D. wrote: > > > > > > > \*\* Execute clone\_structure\_to\_test > > a schema under that name, then all tables etc. created by RoR will be in > that schema. * * * that’s what I ended up doing - I thought I might have posted that but perhaps I slipped up Thanks Craig