eZ Community » Forums » eZ Publish 5 Platform » [Guide] How to configure database...
expandshrink

[Guide] How to configure database sessions in 5.x

[Guide] How to configure database sessions in 5.x

Friday 05 July 2013 3:33:14 pm - 2 replies

Some of you may have noticed that "ezpSessionHandlerDB" was quietly removed from 5.x series. (I must have missed it in the changelog :P ).

If you're in a clustered environment, with loadbalancers doing inexplicable things, database sessions might be useful. An alternative would be using a shared filesystem, but this can be hard to configure if you don't manage your own servers.

Enabling database sessions is simple - in Symfony, PDO Sessions are installed as standard.

#config.yml
 
framework:
    session:
        # ...
        handler_id:     session.handler.pdo
        # value in seconds
        cookie_lifetime: 0
 
parameters:
    pdo.db_options:
        db_table:    sessions
        db_id_col:   sess_id
        db_data_col: sess_value
        db_time_col: sess_time
 
services:
    pdo:
        class: PDO
        arguments:
            dsn:      "mysql:dbname=<mysql_database>"
            user:     <mysql_user>
            password: <mysql_password>

    session.handler.pdo:
        class:     Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler
        arguments: ["@pdo", "%pdo.db_options%"]

You'll need to create a database table with the given columns. Here's some mysql for that:

 CREATE TABLE `sessions` (
    `sess_id` varchar(255) NOT NULL,
    `sess_value` text NOT NULL,
    `sess_time` int(11) NOT NULL,
    PRIMARY KEY (`sess_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf

The behavior is as follows:

1) User signs in, session starts
2) Every pageview updates sess_time to the current microtime
2) Timeout is validated against "cookie_lifetime"
3) Value of "0" means session ends when browser is closed
4) Setting cookie_lifetime to "60" means the user will have to sign in every 60 seconds.
5) Maybe you could set cookie_lifetime to 1yr, if the security risk is low.

If this behavior is good for you, then you can stop here. Not even one line of code happy.gif Emoticon
For me, my site has a high security risk, so ending a session on browser close is not enough. I need an inactivity timeout.

Now we introduce our garbage collector. A garbage collector hunts through the database and wipes out all sessions which have exceeded a specific timeout.
We're going to generate a bundle:

 php ezpublish/console generate:bundle

I wont go in to detail about bundles. This should get you started: https://confluence.ez.no/display/EZP/1.+Getting+started

You'll need to add a new folder "Command", and a new file, which can be called anything so long as it ends with "Command.php". I'll use "SessionGCCronCommand.php"

Here's a link to my file: https://github.com/gazofnaz/ezpub...dle/Command/SessionGCCronCommand.php

And the code in case github is down:

 <?php
 
namespace ACME\SessionsBundle;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use eZ\Publish\Core\MVC\ConfigResolverInterface;
use eZ\Publish\Core\MVC\Exception\ParameterNotFoundException;
use eZINI;
 
class SessionGCCronCommand extends ContainerAwareCommand
{
    public function __construct(){
        parent::__construct();
    }
 
    protected function configure(){
        $this
            ->setName('session:garbage_collector')
            ->setDescription('Clear Expired Database Sessions');
    }
 
    protected function execute(InputInterface $input, OutputInterface $output){
        $configResolver = $this->getContainer()->get( 'ezpublish.config.resolver' );
        $databaseSettings = $configResolver->getParameter( 'database' );
 
        $databaseTableOptions = $this->getContainer()->getParameter( 'pdo.db_options' );
        $sessionIdleTimeout = $this->_getIdleTimeoutValue( );
 
        try{
            $connectionString = sprintf( "%s:host=%s;dbname=%s", $databaseSettings['type'], $databaseSettings['server'], $databaseSettings['database_name'] );
            $pdo = new \PDO( $connectionString, $databaseSettings['user'], $databaseSettings['password'] );
 
            $session = new PdoSessionHandler( $pdo, $databaseTableOptions );
            $session->gc( $sessionIdleTimeout );
        }
        catch( PDOException $e ){
            printf( "Session GC PDO Connection Error: %s", $e->getMessage() );
        }
    }
 
    private function _getIdleTimeoutValue(){
        $legacyResolver = $this->getContainer()->get( 'ezpublish_legacy.config.resolver' );
        $sessionIdleTimeout = $legacyResolver->getParameter( 'Session.SessionTimeout' );
        return $sessionIdleTimeout;
    }
}

We are doing a few things here.

1) Configuring the command to appear in the console with configure()
2) Pulling the database settings with 'ezpublish.config.resolver' -> 'database'
3) Getting the table details from 'pdo.db_options'
4) Getting the Session Timeout from the legacy ini setting, "Session.SessionTimeout"
5) Running the PDO sessions garbage collector to remove all sessions which have been idle for longer than "Session.SessionTimeout"

To test the garbage collector; you can sign in to your site, wait until your timeout, and run php ezpublish/console session:garbage_collector from the console. You should be logged out.
A cronjob on Debian to run every minute will look something like this:

* * * * * cd /var/www/www.mysite.com && php ezpublish/console session:garbage_collector >/dev/null

Now your users will be logged out after a set period of inactivity, and also when their browser is closed.

There are a few things I am not completely happy with in this solution. First is that the database connection details are duplicated from ezpublish.yml to config.yml. Now I have to change them in two places instead of one. Second, is using the Session.SessionTimeout legacy setting. Perhaps bringing this setting into the bundle would be better.

 

Good luck!

Modified on Friday 05 July 2013 3:34:25 pm by Gareth Arnott

Sunday 07 July 2013 8:52:07 pm

Hi,

This is a very valuable post, thanks for sharing your experience with the community.

Regards Robin

Sunday 07 July 2013 9:01:11 pm

Hi Gareth

Thanks for this post. You should do a blog post instead of a simple forum topic happy.gif Emoticon.

This is definitely a good candidate for the cookbook on http://confluence.ez.no

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from