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"; #? > >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.---- 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 White wrote:> 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"; #? >> > 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. -- -- Tom Mornini
On Sun, 2006-02-12 at 18:52 -0800, Tom Mornini wrote:> On Feb 12, 2006, at 6:42 PM, Craig White wrote: > > > 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"; #? > >> > > 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 White wrote:> 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"; #? > > > > > 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. > ---- > 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?Within psql, can you try: \h GRANT 7.4 interactive docs: http://www.postgresql.org/docs/7.4/interactive/sql-grant.html GRANT ALL ON SCHEMA public TO tobyhouse; ? Ping me on IRC if you need help... robbyonrails (freenode). Cheers, -Robby -- /************************************************************** * Robby Russell, 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 * ***************************************************************/
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 On 2/12/06, Craig White <craigwhite@azapple.com> wrote:> 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"; #? > > > > > 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. > ---- > 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 > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Sun, 2006-02-12 at 20:57 -0700, Pat Maddox 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
On Sun, 2006-02-12 at 19:57 -0800, Robby Russell wrote:> On Sun, 2006-02-12 at 19:42 -0700, Craig White wrote: > > 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"; #? > > > > > > > > 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. > > ---- > > 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? > > Within psql, can you try: \h GRANT > > 7.4 interactive docs: > > http://www.postgresql.org/docs/7.4/interactive/sql-grant.html > > GRANT ALL ON SCHEMA public TO tobyhouse; ? > > 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
Craig White wrote:> 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"; #? >> >> > 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. > > ---- > 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---- 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 Wed, 2006-02-15 at 09:36 +1100, Neil Dugan wrote:> Craig White wrote: > > 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"; #? > >> > >> > > 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. > > > > ---- > > 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 > > ---- 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.---- that''s what I ended up doing - I thought I might have posted that but perhaps I slipped up Thanks Craig