[Catalyst] Session::Store::DBIC and session table (postgres)

[Fernan Aguero]

On Wed, Nov 14, 2012 at 11:36 AM, Alejandro Imass <alejandro.imass at gmail.com
> wrote:

> On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero <fernan.aguero at gmail.com>w=
rote:
>
>> Hi,
>>
>> I'm having this issue with my catalyst app where the session table is
>> not fully qualified in the generated SQL statement:
>>
>
> [...]
>
>
>> The sessions table lives in my PostgreSQL database in a separate
>> schema 'webapp'.
>>
>> So I would expect the statement to be:
>> DELETE FROM webapp.sessions WHERE ( id =3D ? )
>>
>> I also have the table name fully qualified in the corresponding DBIC
>> schema class (GUS/Webapp/Sessions.pm):
>>
>>
> [...]
>
> DBIC works well with schemas.
>

I know. The app works so far OK with all schemas in the DB. The only one
giving problems is the sessions table.


> The first obvious question is permissions. Have you tried manually
> connecting as the app user using pgsql and then selecting the table using
> the fq name?
>

Of course, I can reproduce succesfully what DBIC is trying to do on the Pg
terminal. E.g. for the following error:

[error] Scheduler: Error executing /cron/remove_sessions:
DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute
failed: ERROR:  relation "sessions" does not exist [for Statement "DELETE
FROM sessions WHERE ( id =3D ? )" with ParamValues:
1=3D'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384']

I can fix the query and run it (using the same userid/credentials of the
catalyst app) without issues:

tcsnp3=3D> DELETE FROM webapp.sessions WHERE ( id =3D
'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384' );
DELETE 1


Pg requires grants on an object by object basis not like MySQL where you
> can do something like grant all on *.db. You have to grant one by one to
> the Catalyst DB user to the objects in the particular schema, unless of
> course the user is the owner of the schema and in that case you don't even
> need to fq the object names.
>

The owner of all schemas is 'dba'.

The catalyst user has 'arwdxt' privileges on the webapp schema (this schema
contains the sessions and users tables). Essentially it has all privileges
except TRUNCATE:
http://www.postgresql.org/docs/8.4/static/sql-grant.html

Also, if you are creating your Result classes with loader have you tried
> "{loader_options=3D>{db_schema=3D>'foo'}}
>

I am creating the Result classes in this way for larger schemas. However,
for this particular schema that isolates the catalyst-specific tables, I've
manually created the classes (there are only two tables), and I've checked
that it follows the same convention as those created by loader, e.g.

__PACKAGE__->table("schemaname.tablename");


Best,
>
> --
> Alejandro Imass
>

Thanks Alejandro for the quick response. I still think that this is
exposing a bug in Session::Store::DBIC or Session::Store::Delegate. They
should use whatever qualified name is given in the DBIC class ... but I'm
not sure where to go next ...


-- =

fernan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20121114/7adb4=
0b7/attachment.htm