Rails - Mar 2005 - permission error in rake''s clone_structure_to_test

Hello,

When I try to run "rake clone_structure_to_test" on my project, I get
the following errors:

$ rake clone_structure_to_test
(in /home/www/cedscreening)
psql:db/development_structure.sql:8: ERROR:  permission denied to set
session authorization
psql:db/development_structure.sql:16: ERROR:  permission denied for
schema public
psql:db/development_structure.sql:34: NOTICE:  CREATE TABLE will
create implicit sequence "pages_id_seq" for "serial" column
"pages.id"
psql:db/development_structure.sql:46: NOTICE:  CREATE TABLE will
create implicit sequence "users_id_seq" for "serial" column
"users.id"
psql:db/development_structure.sql:55: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index "pages_pkey" for table
"pages"
psql:db/development_structure.sql:64: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index "users_pkey" for table
"users"
psql:db/development_structure.sql:73: NOTICE:  ALTER TABLE / ADD
UNIQUE will create implicit index "pages_title_key" for table
"pages"
psql:db/development_structure.sql:76: ERROR:  permission denied to set
session authorization
psql:db/development_structure.sql:83: ERROR:  must be owner of schema public

I''m using postgres 7.4, rails 0.10, ruby 1.8.2, I''ve applied
the patch
mentioned at
http://weblog.rubyonrails.com/archives/2005/01/15/having-problems-running-tests-under-182
and I''m using the stock Rakefile that rails generated.

I''m a total db noob, what do i have to change to fix this permissions
issue? Using psql, this is what users exist:

=> \du
              List of database users
 User name | User ID |         Attributes
-----------+---------+----------------------------
 postgres  |       1 | superuser, create database
 rbpark    |     100 | create database
(2 rows)

(I''m trying to connect with "rbpark" user). 

Any help is appreciated.

-- 
One Guy With A Camera
http://rbpark.ath.cx

On Sat, 5 Mar 2005 22:25:55 -0700, Rob Park
<rbpark-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> psql:db/development_structure.sql:83: ERROR:  must be owner of schema
public
Eugh, postgres''s error messages are _WORTHLESS_.

The solution to this problem is to make the user you connect as into
the superuser. My user already had createdb privs, just had to add
createuser privs:

    alter user rbpark createuser;

And then magically everything started working. The error message said
I had to be the owner of the "public" schema, not at all, I just had
to make myself the superuser.

The good news is, my first webapp is quickly nearing completion...
barring minor tweaks to the views, all that''s left is to get FastCGI
working and move the rails app over from webrick to lighttpd, then
I''ll be done.

Although nobody replied to me in this thread, I got lots of great
responses from people in other threads, and I''d just like to thank
everybody that helped, and everybody that contributed to the various
wikis, wrote tutorials, etc. You''ve all been instrumental in helping
me learn Ruby on Rails ;)

-- 
One Guy With A Camera
http://rbpark.ath.cx

On Mar 6, 2005, at 19:00, Rob Park wrote:
> On Sat, 5 Mar 2005 22:25:55 -0700, Rob Park
<rbpark-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> psql:db/development_structure.sql:83: ERROR:  must be owner of schema  
>> public
>
> Eugh, postgres''s error messages are _WORTHLESS_.
>
> The solution to this problem is to make the user you connect as into
> the superuser. My user already had createdb privs, just had to add
> createuser privs:
>
>     alter user rbpark createuser;
>
> And then magically everything started working. The error message said
> I had to be the owner of the "public" schema, not at all, I just
had
> to make myself the superuser.
Sorry I didn''t see your original email earlier.

In some ways you can think of a superuser in PostgreSQL as "root". By
connecting as a superuser, you''re pretty much allowed to do anything  
you want. Just as root doesn''t need to be the explicit owner of a  
filesystem object to modify something, a superuser doesn''t need to be  
the explicit owner of a database object to be able to modify it. In  
this case, the error returned by PostgreSQL is--afaict--exactly right.  
Running around as the superuser gives you a lot more than just being  
able to modify a schema.

In psql, the \l and \dn commands will show you the databases and  
schemas along with their owners. (\? will give you a list of additional  
\ commands).

Looking back at the original post, I see basically two errors, one with  
SET AUTHORIZATION, and the other related to ownership of a schema. The  
PostgreSQL documentation for SET AUTHORIZATION states

<http://www.postgresql.org/docs/7.4/interactive/sql-set-session- 
authorization.html>

"The session user identifier may be changed only if the initial session  
user (the authenticated user) had the superuser privilege."

Without seeing your development_structure.sql script, it''s hard to know
for sure what was causing these errors, but I hope this sheds some  
light on the situation.

I''m glad to hear it''s working for you now. You mention that
you''re "a
total db noob". The PostgreSQL documentation is quite good. The folks  
at #postgresql on freenode are often very friendly as well.

Good luck!

Sincerely,

Michael Glaesemann
grzm myrealbox com

Rob Park wrote:
> Hello,
> 
> When I try to run "rake clone_structure_to_test" on my project, I
get
> the following errors:
> 
> Any help is appreciated.
> 
I had the same problem, in mySql.

Rails uses two envirmnments, development and test in that rake command.
But it looks to me like the development env tries to create the test db.

I grant my development user to access both development and test 
databases, to get around this.
But I think the rake code should be able to keep them distinct.
--- Tim