I use this module in most of my data driven CGI Apps, it is obviously most useful in mod_perl and FastCGI, but I tend to use it in plain CGI too. Especially useful are the use of instance parameters, set outside the CGI::App classes, to instantiate the DBI object.
Again I like this module. You can keep the password data (and its location) a long way away from the code by setting that as fixed parameters. By fixed parameters I mean using the features of CGI::Application::Dispatch to pass the parameters to the CGI::Application object. I cannot see any security downsides to this.