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”; #?
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
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.
On Sun, 2006-02-12 at 18:52 -0800, Tom M. wrote:
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
On Sun, 2006-02-12 at 19:42 -0700, Craig W. wrote:
PostgreSQL: Documentation: 15: ALTER SCHEMA
** 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 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 *
- Ruby on Rails Development, Consulting, and Hosting *
- Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 *
- blog: www.robbyonrails.com | book: www.programmingrails.com *
***************************************************************/
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
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
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.
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
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