Обсуждение: dumb question
How do I change the owner of a schema? ALTER SCHEMA "public" OWNER to "some_user"; #? Craig
On Feb 13, 2006, at 10:29 , Craig White wrote: > 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. Hope this helps. Michael Glaesemann grzm myrealbox com
On Mon, 2006-02-13 at 11:07 +0900, Michael Glaesemann wrote: > On Feb 13, 2006, at 10:29 , Craig White wrote: > > > 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 Craig
On Feb 13, 2006, at 11:27 , Craig White wrote: > 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 The docs I referred you to are for the current release. For earlier releases, such as 7.4, you need to refer to the appropriate docs, such as: http://www.postgresql.org/docs/7.4/interactive/sql-alterschema.html It appears that the ability to assign a new owner to a schema via ALTER SCHEMA was introduced some time after 7.4. You may be able to munge the system tables, but you may want to consider upgrading your PostgreSQL server installation. Michael Glaesemann grzm myrealbox com
On Mon, 2006-02-13 at 11:39 +0900, Michael Glaesemann wrote: > On Feb 13, 2006, at 11:27 , Craig White wrote: > > > 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 > > The docs I referred you to are for the current release. For earlier > releases, such as 7.4, you need to refer to the appropriate docs, > such as: > > http://www.postgresql.org/docs/7.4/interactive/sql-alterschema.html > > It appears that the ability to assign a new owner to a schema via > ALTER SCHEMA was introduced some time after 7.4. > > You may be able to munge the system tables, but you may want to > consider upgrading your PostgreSQL server installation. ---- seems to be a lot of work just for this...the problem is trying to use rubyonrails and their 'migrations' which cause this... $ 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 So how can I just 'munge' the system tables? I have granted all privileges to this user. Thanks Craig
On Feb 13, 2006, at 11:47 , Craig White wrote: > seems to be a lot of work just for this...the problem is trying to use > rubyonrails and their 'migrations' which cause this... <snip /> > psql:db/development_structure.sql:211: ERROR: must be owner of schema > public > > So how can I just 'munge' the system tables? I have granted all > privileges to this user. In all honesty, they're not meant for general use because it is quite easy to irreparably screw up your database. The fact that you have to ask how to do this makes me think that it's not a good idea in your case. Documentation on the system tables (also known as the system catalog) is included in the PostgreSQL documentation. And before you do anything, I highly recommend backing up. I do think it would be easier for you to upgrade. 7.4 is now two releases behind. There's a lot of good stuff in 8.1. As for the issue with Ruby on Rails you mention above, you'll need a database user with superuser privileges. Newly created databases are based on templates, and the public schema in the templates is most likely owned by someone other than the user you're connecting as for your tests. To be able to change the owner of a schema, the user running the command needs to be a superuser. Michael Glaesemann grzm myrealbox com