Redmine 的 Repository Controls plugin（續）

在正式把手邊的專案移上 redmine + svn 之前，
我還是花了點時間把這個 plugin 做改善，
資料庫也從 sqlite 換到了 mysql 上，
也把 redmine 從 ports 的版本直接換成官方 svn trunk 裡的版本。

換上 svn trunk 的版本後我馬上發現到一件事，
就是新版的 redmine 有所謂的 multi-role 機制，
舉例來說一個 project 的 member 可以同時是 Manager 和 Developer，
這讓我想通了為什麼這個 plugin 的作者會以 role 的 position 來做優先順序的判斷，
舉例來說 Manager 的 position 是 3 而 Developer 的 position 是 4，
那麼檔案庫存取權限就是看 Manager 的為準；
雖然我也思考過是否有更聰明更複雜的判斷方法，
不過還是以單一 role 來判斷最不容易導致混亂，
也是唯一最貼近 svn 原本 authz 運作模式的實現方法，
更何況這是每 checkout 一個檔案就要跑一次的動作，
SQL 的查詢次數和複雜度也不便設計得太誇張，
不然光是 checkout 和 update 就可以慢到會等死人的程度。

不過利用 mod_perl2 掛 access/auth/authz 的 handler 有一個問題，
那就是跑到 auth 這個 step 的時候必須先發送一個 AUTH_REQUIRED 給 browser (或 svn client)，
這樣 browser 才會跳出輸入帳號密碼的 dialog (svn client 也才會要求輸入密碼)，
加上 HTTP 協定並不是 keep alive 的東西，
我很難去判斷這是第幾次發送 AUTH_REQUIRED 目前這個 client，
所以如果 user 希望用帳號密碼完全空白的 anonymous 模式存取檔案庫，
那就只會不斷的重複被詢問帳號密碼而不是被直接放行；
關於這點我最後的解決方式就是把存取的 URL 拆成 http:// 和 https:// 兩種協定，
perl module 也是一分為二，
走 http:// 的就是完全不需要打帳號密碼一律視為 anonymous 的存取，
走 https:// 的就視為一定要輸入帳號密碼的 members，
這樣做想想其實也還蠻合理的，
畢竟吃 LDAP 帳號密碼的這種架構下允許 members 走 http:// 實在太恐怖。

下面是分離出來給 anonymous 存取用的 perl module：

package Apache::Authn::RedminePublicRepoControl;

=head1 Apache::Authn::RedminePublicRepoControl

Module for repository access and control to interface with Redmine

=head1 SYNOPSIS

=head1 INSTALLATION

=head1 CONFIGURATION

=cut

use strict;
use warnings FATAL => 'all', NONFATAL => 'redefine';

use DBI;
use Digest::SHA1;

use Apache2::Module;
use Apache2::Access;
use Apache2::ServerRec qw();
use Apache2::RequestRec qw();
use Apache2::RequestUtil qw();
use Apache2::Const qw(:common :override :cmd_how);

my @directives = (
    {
        name         => 'RedmineDSN',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
        errmsg       => 'DSN in format used by Perl DBI. eg: "DBI:Pg:dbname=databasename;host=my.db.server"',
    },
    {
        name         => 'RedmineDbUser',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
    },
    { 
        name         => 'RedmineDbPass',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
    },
);

sub RedmineDSN {
    my ($self, $parms, $arg) = @_;
    $self->{RedmineDSN} = $arg;
    my $query = "SELECT 
    hashed_password, auth_source_id, permissions
    FROM members, projects, users, roles
    WHERE 
    projects.id=members.project_id 
    AND users.id=members.user_id 
    AND roles.id=members.role_id
    AND users.status=1 
    AND login=? 
    AND identifier=? ";
    $self->{RedmineQuery} = trim($query);
}

sub RedmineDbUser { set_val('RedmineDbUser', @_); }
sub RedmineDbPass { set_val('RedmineDbPass', @_); }
sub RedmineDbWhereClause { 
    my ($self, $parms, $arg) = @_;
    $self->{RedmineQuery} = trim($self->{RedmineQuery}.($arg ? $arg : "")." ");
}

sub trim {
    my $string = shift;
    $string =~ s/\s{2,}/ /g;
    return $string;
}

sub set_val {
    my ($key, $self, $parms, $arg) = @_;
    $self->{$key} = $arg;
}

Apache2::Module::add(__PACKAGE__, \@directives);

# This is the list of all apache request methods that we are treating as "read-only" methods
my %read_only_methods = map { $_ => 1 } qw/GET PROPFIND REPORT OPTIONS/;

#
# Access is the first step in the AAA model. This funciton decides whether or not to even ask
# the user to log in. Basically, this is going to always be yes, however, if an anonymous
# person is trying to connect, and we've set that anonymous people can browse public projects
# then we will OK it, and tell apache that the authenicatin handler doesn't need to be called
# or, that the anonymous person doesn't need to log in.
#
sub access_handler {
    my $r = shift;

    if( !defined($read_only_methods{$r->method}) ) {
        return FORBIDDEN;
    }

    my $project_id = get_project_identifier($r);
    my $req_path = get_requested_path($r);

    if( is_anonymous_readable($project_id, $req_path, $r) ) {
        return OK;
    }
    
    return FORBIDDEN;
}

#
# Returns the path requested in the repository
#
sub get_requested_path {
    my $r = shift;

    my $location = $r->location;
    my $path;
    
    if($location ne "/") {
        ($path) = $r->uri =~ m{$location/*[^/]+(/.*)};
    }
    else {
         my @path_items = split /\//, $r->uri;

         if(@path_items <= 2) {
             $path = "/";
         }
         else {
             if($path_items[2] eq "!svn") {
                 if(@path_items <= 5) {
                     return "/!svn";
                 }

                 @path_items = @path_items[5..$#path_items];
             }
             else {
                 @path_items = @path_items[2..$#path_items];
             }

             $path = "/" . join "/", @path_items;
         }
   }

   $path;
}

# 
# Returns the project identifier for a given request
#
sub get_project_identifier {
    my $r = shift;

    my $location = $r->location;
    my $identifier;

    if($location ne "/") {
        ($identifier) = $r->uri =~ m{$location/*([^/]+)};
    }
    else {
        my @path_items = split /\//, $r->uri;
        $identifier = $path_items[1];
    }

    $identifier;
}

#
# Returns a connection to the database
#
sub connect_database {
    my $r = shift;

    my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );
    return DBI->connect( $cfg->{RedmineDSN}, $cfg->{RedmineDbUser},$cfg->{RedmineDbPass} );
}

sub get_role_permission {
    my ($role_id, $project_id, $req_path, $r) = @_;

    if(!defined($project_id)) {
        return '';
    }

    my $dbh = connect_database($r);
    my $sth = $dbh->prepare("SELECT path, permissions FROM repository_controls, projects
                             WHERE repository_controls.role_id='$role_id'
                             AND projects.identifier='$project_id'
                             AND repository_controls.project_id=projects.id");
    $sth->execute;
    
    my $longest_path = 0;
    my $role_perm;
    
    while(my ($path, $perm) = $sth->fetchrow_array()) {
        if($req_path =~ m{^($path).*}) {
            my $path_length = length $path;
            
            if($path_length > $longest_path) {
                $longest_path = $path_length;
                $role_perm = $perm;
            }
        }
    }
    
    $sth->finish();
    $dbh->disconnect();

    return $role_perm;
}

sub is_anonymous_readable {
    my ($project_id, $req_path, $r) = @_;
    my $perm;
    
    if($req_path =~ m{!svn}) {
        return 1;
    }
    else {
        $perm = get_role_permission('2', $project_id, $req_path, $r);
    }

    if($perm and $perm =~ /:browse_repository/) {
        return 1;
    }
    else {
        return 0;
    }
}

1;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

package Apache::Authn::RedminePublicRepoControl;

=head1 Apache::Authn::RedminePublicRepoControl

Module for repository access and control to interface with Redmine

=head1 SYNOPSIS

=head1 INSTALLATION

=head1 CONFIGURATION

=cut

use strict;

use warnings FATAL => 'all', NONFATAL => 'redefine';

use DBI;

use Digest::SHA1;

use Apache2::Module;

use Apache2::Access;

use Apache2::ServerRec qw();

use Apache2::RequestRec qw();

use Apache2::RequestUtil qw();

use Apache2::Const qw(:common :override :cmd_how);

my @directives = (

{

name => 'RedmineDSN',

req_override => OR_AUTHCFG,

args_how => TAKE1,

errmsg => 'DSN in format used by Perl DBI. eg: "DBI:Pg:dbname=databasename;host=my.db.server"',

{

name => 'RedmineDbUser',

req_override => OR_AUTHCFG,

args_how => TAKE1,

{

name => 'RedmineDbPass',

req_override => OR_AUTHCFG,

args_how => TAKE1,

);

sub RedmineDSN {

my ($self, $parms, $arg) = @_;

$self->{RedmineDSN} = $arg;

my $query = "SELECT

hashed_password, auth_source_id, permissions

FROM members, projects, users, roles

WHERE

projects.id=members.project_id

AND users.id=members.user_id

AND roles.id=members.role_id

AND users.status=1

AND login=?

AND identifier=? ";

$self->{RedmineQuery} = trim($query);

}

sub RedmineDbUser { set_val('RedmineDbUser', @_); }

sub RedmineDbPass { set_val('RedmineDbPass', @_); }

sub RedmineDbWhereClause {

my ($self, $parms, $arg) = @_;

$self->{RedmineQuery} = trim($self->{RedmineQuery}.($arg ? $arg : "")." ");

}

sub trim {

my $string = shift;

$string =~ s/\s{2,}/ /g;

return $string;

}

sub set_val {

my ($key, $self, $parms, $arg) = @_;

$self->{$key} = $arg;

}

Apache2::Module::add(__PACKAGE__, \@directives);

# This is the list of all apache request methods that we are treating as "read-only" methods

my %read_only_methods = map { $_ => 1 } qw/GET PROPFIND REPORT OPTIONS/;

# Access is the first step in the AAA model. This funciton decides whether or not to even ask

# the user to log in. Basically, this is going to always be yes, however, if an anonymous

# person is trying to connect, and we've set that anonymous people can browse public projects

# then we will OK it, and tell apache that the authenicatin handler doesn't need to be called

# or, that the anonymous person doesn't need to log in.

sub access_handler {

my $r = shift;

if( !defined($read_only_methods{$r->method}) ) {

return FORBIDDEN;

}

my $project_id = get_project_identifier($r);

my $req_path = get_requested_path($r);

if( is_anonymous_readable($project_id, $req_path, $r) ) {

return OK;

}

return FORBIDDEN;

}

# Returns the path requested in the repository

sub get_requested_path {

my $r = shift;

my $location = $r->location;

my $path;

if($location ne "/") {

($path) = $r->uri =~ m{$location/*[^/]+(/.*)};

}

else {

my @path_items = split /\//, $r->uri;

if(@path_items <= 2) {

$path = "/";

}

else {

if($path_items[2] eq "!svn") {

if(@path_items <= 5) {

return "/!svn";

}

@path_items = @path_items[5..$#path_items];

}

else {

@path_items = @path_items[2..$#path_items];

}

$path = "/" . join "/", @path_items;

}

$path;

}

# Returns the project identifier for a given request

sub get_project_identifier {

my $r = shift;

my $location = $r->location;

my $identifier;

if($location ne "/") {

($identifier) = $r->uri =~ m{$location/*([^/]+)};

}

else {

my @path_items = split /\//, $r->uri;

$identifier = $path_items[1];

}

$identifier;

}

# Returns a connection to the database

sub connect_database {

my $r = shift;

my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );

return DBI->connect( $cfg->{RedmineDSN}, $cfg->{RedmineDbUser},$cfg->{RedmineDbPass} );

}

sub get_role_permission {

my ($role_id, $project_id, $req_path, $r) = @_;

if(!defined($project_id)) {

return '';

}

my $dbh = connect_database($r);

my $sth = $dbh->prepare("SELECT path, permissions FROM repository_controls, projects

WHERE repository_controls.role_id='$role_id'

AND projects.identifier='$project_id'

AND repository_controls.project_id=projects.id");

$sth->execute;

my $longest_path = 0;

my $role_perm;

while(my ($path, $perm) = $sth->fetchrow_array()) {

if($req_path =~ m{^($path).*}) {

my $path_length = length $path;

if($path_length > $longest_path) {

$longest_path = $path_length;

$role_perm = $perm;

}

$sth->finish();

$dbh->disconnect();

return $role_perm;

}

sub is_anonymous_readable {

my ($project_id, $req_path, $r) = @_;

my $perm;

if($req_path =~ m{!svn}) {

return 1;

}

else {

$perm = get_role_permission('2', $project_id, $req_path, $r);

}

if($perm and $perm =~ /:browse_repository/) {

return 1;

}

else {

return 0;

}

下面這個是給 members 登入後才能存取用的 perl module：

package Apache::Authn::RedmineRepoControl;

=head1 Apache::Authn::RedmineRepoControl

Module for repository access and control to interface with Redmine

=head1 SYNOPSIS

=head1 INSTALLATION

=head1 CONFIGURATION

=cut

use strict;
use warnings FATAL => 'all', NONFATAL => 'redefine';

use DBI;
use Digest::SHA1;

# optional module for LDAP authentication
my $CanUseLDAPAuth = eval("use Authen::Simple::LDAP; 1");

use Apache2::Module;
use Apache2::Access;
use Apache2::ServerRec qw();
use Apache2::RequestRec qw();
use Apache2::RequestUtil qw();
use Apache2::Const qw(:common :override :cmd_how);
use APR::Pool();
use APR::Table();

my @directives = (
    {
        name         => 'RedmineDSN',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
        errmsg       => 'DSN in format used by Perl DBI. eg: "DBI:Pg:dbname=databasename;host=my.db.server"',
    },
    {
        name         => 'RedmineDbUser',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
    },
    { 
        name         => 'RedmineDbPass',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
    },
    {
        name         => 'RedmineCacheCredsMax',
        req_override => OR_AUTHCFG,
        args_how     => TAKE1,
        errmsg => 'RedmineCacheCredsMax must be a decimal number',
    },
);

sub RedmineDSN {
    my ($self, $parms, $arg) = @_;
    $self->{RedmineDSN} = $arg;
    my $query = "SELECT 
    hashed_password, auth_source_id, permissions
    FROM members, projects, users, roles
    WHERE 
    projects.id=members.project_id 
    AND users.id=members.user_id 
    AND roles.id=members.role_id
    AND users.status=1 
    AND login=? 
    AND identifier=? ";
    $self->{RedmineQuery} = trim($query);
}

sub RedmineDbUser { set_val('RedmineDbUser', @_); }
sub RedmineDbPass { set_val('RedmineDbPass', @_); }
sub RedmineDbWhereClause { 
    my ($self, $parms, $arg) = @_;
    $self->{RedmineQuery} = trim($self->{RedmineQuery}.($arg ? $arg : "")." ");
}

sub RedmineCacheCredsMax { 
    my ($self, $parms, $arg) = @_;

    if ($arg) {
        $self->{RedmineCacheCredsCount} = 0;
        $self->{RedmineCacheCredsMax} = $arg;
    }
}

sub trim {
    my $string = shift;
    $string =~ s/\s{2,}/ /g;
    return $string;
}

sub set_val {
    my ($key, $self, $parms, $arg) = @_;
    $self->{$key} = $arg;
}

Apache2::Module::add(__PACKAGE__, \@directives);

# This is the list of all apache request methods that we are treating as "read-only" methods
my %read_only_methods = map { $_ => 1 } qw/GET PROPFIND REPORT OPTIONS/;

#
# Access is the first step in the AAA model. This funciton decides whether or not to even ask
# the user to log in. Basically, this is going to always be yes, however, if an anonymous
# person is trying to connect, and we've set that anonymous people can browse public projects
# then we will OK it, and tell apache that the authenicatin handler doesn't need to be called
# or, that the anonymous person doesn't need to log in.
#
sub access_handler {
    my $r = shift;

    my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );

    unless ( $r->some_auth_required ) {
        $r->log_reason("No authentication has been configured");
        return FORBIDDEN;
    }

    return OK;
}

# 
# This is the second step in the AAA model, Authentication. If we have gotten here, we need
# to make sure the user can authenticate against redmine. 
# 
sub authen_handler {
    my $r = shift;

    my $ret = AUTH_REQUIRED;

    my ($res, $redmine_pass) = $r->get_basic_auth_pw();
    return $res unless $res == OK;

    my $redmine_user = $r->user;
    my $project_id   = get_project_identifier($r);

    if(!defined($project_id) or !defined($redmine_user)) {
        return FORBIDDEN;
    }

    my $cfg = Apache2::Module::get_config(__PACKAGE__, $r->server, $r->per_dir_config);

    #1. Check the chache for the user's credentials
    my $usrprojpass;
    my $pass_digest = Digest::SHA1::sha1_hex($redmine_pass);
    if ($cfg->{RedmineCacheCredsMax}) {
        if(!defined($cfg->{RedmineCacheCreds})) {
            $cfg->{RedmineCachePool} = APR::Pool->new;
            $cfg->{RedmineCacheCreds} = APR::Table::make($cfg->{RedmineCachePool}, $cfg->{RedmineCacheCredsMax});
        }

        $usrprojpass = $cfg->{RedmineCacheCreds}->get($redmine_user.":".$project_id);
        return OK if ( defined $usrprojpass and ( $usrprojpass eq $pass_digest ));
    }

    #2. Otherwise, authenticate the user

    # Pull the hashed password for the user from the DB
    my $dbh          = connect_database($r);
    my $sth = $dbh->prepare("SELECT hashed_password, auth_source_id FROM users WHERE users.status=1 AND login=? ");
    $sth->execute($redmine_user);

    # check the result from the DB query to try and authenticate the user
    while ( my($hashed_password, $auth_source_id) = $sth->fetchrow_array ) {
        # if there is an auth_source_id set, then skip this first part and authenticate using the auth_source
        unless($auth_source_id) {
            # otherwise, authenticate using the hashed password
            if ( $hashed_password eq $pass_digest ) {
                $ret = OK;
            }
        } elsif ($CanUseLDAPAuth) {
            # pull the auth_source configuration from the database
            my $sthldap = $dbh->prepare("SELECT host,port,tls,account,account_password,base_dn,attr_login from auth_sources WHERE id = ?;");
            $sthldap->execute($auth_source_id);
            while (my @rowldap = $sthldap->fetchrow_array) {
                # add ldap authenticate as user
                my $bind_as = $rowldap[3];
                my $bind_pw = $rowldap[4] ? $rowldap[4] : "";
                if ($bind_as =~ m/\$login/) {
                    # if we have $login in the bind user, replace it with the user
                    # trying to log in, and use their password as well
                    $bind_as =~ s/\$login/$redmine_user/g;
                    $bind_pw = $redmine_pass;
                }
                
                my $ldap = Authen::Simple::LDAP->new(
                    host   => ($rowldap[2] == 1) ? "ldaps://$rowldap[0]" : $rowldap[0],
                    port   => $rowldap[1],
                    basedn => $rowldap[5],
                    binddn => $bind_as,
                    bindpw => $bind_pw,
                    filter => "(".$rowldap[6]."=%s)"
                );

                $ret = OK if ($ldap->authenticate($redmine_user, $redmine_pass));
            }

            $sthldap->finish();
        } else {
            #there is an auth_source, but we can't use it because we don't have the LDAP module installed,
            #so error and let the user know
            $r->log_error("Cannot load the Authen::Simple::LDAP module to authenticate the user, please check
               your installation");
            $ret = SERVER_ERROR;
        }
    } 

    $sth->finish();
    $dbh->disconnect();

    #
    # If the login was successful, add it to the cache
    #
    if ($cfg->{RedmineCacheCredsMax} and $ret == OK) {
        if (defined $usrprojpass) {
            $cfg->{RedmineCacheCreds}->set($redmine_user.":".$project_id, $pass_digest);
        } else {
            if ($cfg->{RedmineCacheCredsCount} < $cfg->{RedmineCacheCredsMax}) {
                $cfg->{RedmineCacheCreds}->set($redmine_user.":".$project_id, $pass_digest);
                $cfg->{RedmineCacheCredsCount}++;
            } else {
                $cfg->{RedmineCacheCreds}->clear();
                $cfg->{RedmineCacheCredsCount} = 0;
            }
        }
    }

    $ret;
}

#
# This is the final stage in the AAA model, Authorization. This function decides
# whether or not the user is actually allowed to access this particular path.
sub authz_handler {
    my $r = shift;
    my $user = $r->user;
    my $project_id = get_project_identifier($r);
    my $req_path = get_requested_path($r);

    if($req_path =~ m{!svn}) {
        return OK;
    }

    my $perm = get_user_permission($user, $project_id, $req_path, $r);
    
    if(exists $read_only_methods{$r->method}) {
        return OK if($perm =~ /:browse_repository/);
    }
    else {
        return OK if($perm =~ /:commit_access/);
    }
    
    return FORBIDDEN;
}

#
# Returns the path requested in the repository
#
sub get_requested_path {
    my $r = shift;

    my $location = $r->location;
    my $path;
    
    if($location ne "/") {
        ($path) = $r->uri =~ m{$location/*[^/]+(/.*)};
    }
    else {
         my @path_items = split /\//, $r->uri;

         if(@path_items <= 2) {
             $path = "/";
         }
         else {
             if($path_items[2] eq "!svn") {
                 if(@path_items <= 5) {
                     return "/!svn";
                 }

                 @path_items = @path_items[5..$#path_items];
             }
             else {
                 @path_items = @path_items[2..$#path_items];
             }

             $path = "/" . join "/", @path_items;
         }
   }

   $path;
}

# 
# Returns the project identifier for a given request
#
sub get_project_identifier {
    my $r = shift;

    my $location = $r->location;
    my $identifier;

    if($location ne "/") {
        ($identifier) = $r->uri =~ m{$location/*([^/]+)};
    }
    else {
        my @path_items = split /\//, $r->uri;
        $identifier = $path_items[1];
    }

    $identifier;
}

#
# Returns a connection to the database
#
sub connect_database {
    my $r = shift;

    my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );
    return DBI->connect( $cfg->{RedmineDSN}, $cfg->{RedmineDbUser},$cfg->{RedmineDbPass} );
}

sub get_role_permission {
    my ($role_id, $project_id, $req_path, $r) = @_;

    if(!defined($project_id)) {
        return '';
    }

    my $dbh = connect_database($r);
    my $sth = $dbh->prepare("SELECT path, permissions FROM repository_controls, projects
                             WHERE repository_controls.role_id='$role_id'
                             AND projects.identifier='$project_id'
                             AND repository_controls.project_id=projects.id");
    $sth->execute;
    
    my $longest_path = 0;
    my $role_perm;
    
    while(my ($path, $perm) = $sth->fetchrow_array()) {
        if($req_path =~ m{^($path).*}) {
            my $path_length = length $path;
            
            if($path_length > $longest_path) {
                $longest_path = $path_length;
                $role_perm = $perm;
            }
        }
    }
    
    $sth->finish();
    $dbh->disconnect();

    return $role_perm;
}

sub get_user_permission {
    my ($user, $project_id, $req_path, $r) = @_;
    my $user_perm;
    
    if(!defined($project_id)) {
        return '';
    }

    if($user) {
        my $dbh = connect_database($r);
        my $sth;
        my $role_id;

        $sth = $dbh->prepare("SELECT role_id FROM member_roles 
                                INNER JOIN users 
                                  ON users.login='$user' 
                                INNER JOIN projects 
                                  ON projects.identifier='$project_id' 
                                INNER JOIN members 
                                  ON members.user_id=users.id 
                                  AND members.project_id=projects.id 
                                INNER JOIN roles 
                                  ON roles.id=member_roles.role_id 
                              WHERE member_roles.member_id=members.id 
                              ORDER BY position 
                              LIMIT 1");
        $sth->execute;
        ($role_id) = $sth->fetchrow_array();
        $sth->finish();
        
        if(defined $role_id) {
            my $longest_path = 0;
        
            $sth = $dbh->prepare("SELECT path, permissions FROM repository_controls 
                                    INNER JOIN projects 
                                      ON projects.identifier='$project_id' 
                                    WHERE repository_controls.project_id=projects.id 
                                    AND repository_controls.role_id=$role_id");
            $sth->execute;
            
            while(my ($path, $perm) = $sth->fetchrow_array()) {
                if($req_path =~ m{^($path).*}) {
                    my $path_length = length $path;
                    
                    if($path_length > $longest_path) {
                        $longest_path = $path_length;
                        $user_perm = $perm;
                    }
                }
            }

            $sth->finish();
        }
        else {
            # non-member access
            $user_perm = get_role_permission('1', $project_id, $req_path, $r);
        }
        $dbh->disconnect();
    }
    else {
        # anonymous access
        $user_perm = '';
    }

    if(!defined($user_perm)) {
        $user_perm = '';
    }

    return $user_perm;
}

1;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

package Apache::Authn::RedmineRepoControl;

=head1 Apache::Authn::RedmineRepoControl

Module for repository access and control to interface with Redmine

=head1 SYNOPSIS

=head1 INSTALLATION

=head1 CONFIGURATION

=cut

use strict;

use warnings FATAL => 'all', NONFATAL => 'redefine';

use DBI;

use Digest::SHA1;

# optional module for LDAP authentication

my $CanUseLDAPAuth = eval("use Authen::Simple::LDAP; 1");

use Apache2::Module;

use Apache2::Access;

use Apache2::ServerRec qw();

use Apache2::RequestRec qw();

use Apache2::RequestUtil qw();

use Apache2::Const qw(:common :override :cmd_how);

use APR::Pool();

use APR::Table();

my @directives = (

{

name => 'RedmineDSN',

req_override => OR_AUTHCFG,

args_how => TAKE1,

errmsg => 'DSN in format used by Perl DBI. eg: "DBI:Pg:dbname=databasename;host=my.db.server"',

{

name => 'RedmineDbUser',

req_override => OR_AUTHCFG,

args_how => TAKE1,

{

name => 'RedmineDbPass',

req_override => OR_AUTHCFG,

args_how => TAKE1,

{

name => 'RedmineCacheCredsMax',

req_override => OR_AUTHCFG,

args_how => TAKE1,

errmsg => 'RedmineCacheCredsMax must be a decimal number',

);

sub RedmineDSN {

my ($self, $parms, $arg) = @_;

$self->{RedmineDSN} = $arg;

my $query = "SELECT

hashed_password, auth_source_id, permissions

FROM members, projects, users, roles

WHERE

projects.id=members.project_id

AND users.id=members.user_id

AND roles.id=members.role_id

AND users.status=1

AND login=?

AND identifier=? ";

$self->{RedmineQuery} = trim($query);

}

sub RedmineDbUser { set_val('RedmineDbUser', @_); }

sub RedmineDbPass { set_val('RedmineDbPass', @_); }

sub RedmineDbWhereClause {

my ($self, $parms, $arg) = @_;

$self->{RedmineQuery} = trim($self->{RedmineQuery}.($arg ? $arg : "")." ");

}

sub RedmineCacheCredsMax {

my ($self, $parms, $arg) = @_;

if ($arg) {

$self->{RedmineCacheCredsCount} = 0;

$self->{RedmineCacheCredsMax} = $arg;

}

sub trim {

my $string = shift;

$string =~ s/\s{2,}/ /g;

return $string;

}

sub set_val {

my ($key, $self, $parms, $arg) = @_;

$self->{$key} = $arg;

}

Apache2::Module::add(__PACKAGE__, \@directives);

# This is the list of all apache request methods that we are treating as "read-only" methods

my %read_only_methods = map { $_ => 1 } qw/GET PROPFIND REPORT OPTIONS/;

# Access is the first step in the AAA model. This funciton decides whether or not to even ask

# the user to log in. Basically, this is going to always be yes, however, if an anonymous

# person is trying to connect, and we've set that anonymous people can browse public projects

# then we will OK it, and tell apache that the authenicatin handler doesn't need to be called

# or, that the anonymous person doesn't need to log in.

sub access_handler {

my $r = shift;

my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );

unless ( $r->some_auth_required ) {

$r->log_reason("No authentication has been configured");

return FORBIDDEN;

}

return OK;

}

# This is the second step in the AAA model, Authentication. If we have gotten here, we need

# to make sure the user can authenticate against redmine.

sub authen_handler {

my $r = shift;

my $ret = AUTH_REQUIRED;

my ($res, $redmine_pass) = $r->get_basic_auth_pw();

return $res unless $res == OK;

my $redmine_user = $r->user;

my $project_id = get_project_identifier($r);

if(!defined($project_id) or !defined($redmine_user)) {

return FORBIDDEN;

}

my $cfg = Apache2::Module::get_config(__PACKAGE__, $r->server, $r->per_dir_config);

#1. Check the chache for the user's credentials

my $usrprojpass;

my $pass_digest = Digest::SHA1::sha1_hex($redmine_pass);

if ($cfg->{RedmineCacheCredsMax}) {

if(!defined($cfg->{RedmineCacheCreds})) {

$cfg->{RedmineCachePool} = APR::Pool->new;

$cfg->{RedmineCacheCreds} = APR::Table::make($cfg->{RedmineCachePool}, $cfg->{RedmineCacheCredsMax});

}

$usrprojpass = $cfg->{RedmineCacheCreds}->get($redmine_user.":".$project_id);

return OK if ( defined $usrprojpass and ( $usrprojpass eq $pass_digest ));

}

#2. Otherwise, authenticate the user

# Pull the hashed password for the user from the DB

my $dbh = connect_database($r);

my $sth = $dbh->prepare("SELECT hashed_password, auth_source_id FROM users WHERE users.status=1 AND login=? ");

$sth->execute($redmine_user);

# check the result from the DB query to try and authenticate the user

while ( my($hashed_password, $auth_source_id) = $sth->fetchrow_array ) {

# if there is an auth_source_id set, then skip this first part and authenticate using the auth_source

unless($auth_source_id) {

# otherwise, authenticate using the hashed password

if ( $hashed_password eq $pass_digest ) {

$ret = OK;

}

} elsif ($CanUseLDAPAuth) {

# pull the auth_source configuration from the database

my $sthldap = $dbh->prepare("SELECT host,port,tls,account,account_password,base_dn,attr_login from auth_sources WHERE id = ?;");

$sthldap->execute($auth_source_id);

while (my @rowldap = $sthldap->fetchrow_array) {

# add ldap authenticate as user

my $bind_as = $rowldap[3];

my $bind_pw = $rowldap[4] ? $rowldap[4] : "";

if ($bind_as =~ m/\$login/) {

# if we have $login in the bind user, replace it with the user

# trying to log in, and use their password as well

$bind_as =~ s/\$login/$redmine_user/g;

$bind_pw = $redmine_pass;

}

my $ldap = Authen::Simple::LDAP->new(

host => ($rowldap[2] == 1) ? "ldaps://$rowldap[0]" : $rowldap[0],

port => $rowldap[1],

basedn => $rowldap[5],

binddn => $bind_as,

bindpw => $bind_pw,

filter => "(".$rowldap[6]."=%s)"

);

$ret = OK if ($ldap->authenticate($redmine_user, $redmine_pass));

}

$sthldap->finish();

} else {

#there is an auth_source, but we can't use it because we don't have the LDAP module installed,

#so error and let the user know

$r->log_error("Cannot load the Authen::Simple::LDAP module to authenticate the user, please check

your installation");

$ret = SERVER_ERROR;

}

$sth->finish();

$dbh->disconnect();

# If the login was successful, add it to the cache

if ($cfg->{RedmineCacheCredsMax} and $ret == OK) {

if (defined $usrprojpass) {

$cfg->{RedmineCacheCreds}->set($redmine_user.":".$project_id, $pass_digest);

} else {

if ($cfg->{RedmineCacheCredsCount} < $cfg->{RedmineCacheCredsMax}) {

$cfg->{RedmineCacheCreds}->set($redmine_user.":".$project_id, $pass_digest);

$cfg->{RedmineCacheCredsCount}++;

} else {

$cfg->{RedmineCacheCreds}->clear();

$cfg->{RedmineCacheCredsCount} = 0;

}

$ret;

}

# This is the final stage in the AAA model, Authorization. This function decides

# whether or not the user is actually allowed to access this particular path.

sub authz_handler {

my $r = shift;

my $user = $r->user;

my $project_id = get_project_identifier($r);

my $req_path = get_requested_path($r);

if($req_path =~ m{!svn}) {

return OK;

}

my $perm = get_user_permission($user, $project_id, $req_path, $r);

if(exists $read_only_methods{$r->method}) {

return OK if($perm =~ /:browse_repository/);

}

else {

return OK if($perm =~ /:commit_access/);

}

return FORBIDDEN;

}

# Returns the path requested in the repository

sub get_requested_path {

my $r = shift;

my $location = $r->location;

my $path;

if($location ne "/") {

($path) = $r->uri =~ m{$location/*[^/]+(/.*)};

}

else {

my @path_items = split /\//, $r->uri;

if(@path_items <= 2) {

$path = "/";

}

else {

if($path_items[2] eq "!svn") {

if(@path_items <= 5) {

return "/!svn";

}

@path_items = @path_items[5..$#path_items];

}

else {

@path_items = @path_items[2..$#path_items];

}

$path = "/" . join "/", @path_items;

}

$path;

}

# Returns the project identifier for a given request

sub get_project_identifier {

my $r = shift;

my $location = $r->location;

my $identifier;

if($location ne "/") {

($identifier) = $r->uri =~ m{$location/*([^/]+)};

}

else {

my @path_items = split /\//, $r->uri;

$identifier = $path_items[1];

}

$identifier;

}

# Returns a connection to the database

sub connect_database {

my $r = shift;

my $cfg = Apache2::Module::get_config( __PACKAGE__, $r->server, $r->per_dir_config );

return DBI->connect( $cfg->{RedmineDSN}, $cfg->{RedmineDbUser},$cfg->{RedmineDbPass} );

}

sub get_role_permission {

my ($role_id, $project_id, $req_path, $r) = @_;

if(!defined($project_id)) {

return '';

}

my $dbh = connect_database($r);

my $sth = $dbh->prepare("SELECT path, permissions FROM repository_controls, projects

WHERE repository_controls.role_id='$role_id'

AND projects.identifier='$project_id'

AND repository_controls.project_id=projects.id");

$sth->execute;

my $longest_path = 0;

my $role_perm;

while(my ($path, $perm) = $sth->fetchrow_array()) {

if($req_path =~ m{^($path).*}) {

my $path_length = length $path;

if($path_length > $longest_path) {

$longest_path = $path_length;

$role_perm = $perm;

}

$sth->finish();

$dbh->disconnect();

return $role_perm;

}

sub get_user_permission {

my ($user, $project_id, $req_path, $r) = @_;

my $user_perm;

if(!defined($project_id)) {

return '';

}

if($user) {

my $dbh = connect_database($r);

my $sth;

my $role_id;

$sth = $dbh->prepare("SELECT role_id FROM member_roles

INNER JOIN users

ON users.login='$user'

INNER JOIN projects

ON projects.identifier='$project_id'

INNER JOIN members

ON members.user_id=users.id

AND members.project_id=projects.id

INNER JOIN roles

ON roles.id=member_roles.role_id

WHERE member_roles.member_id=members.id

ORDER BY position

LIMIT 1");

$sth->execute;

($role_id) = $sth->fetchrow_array();

$sth->finish();

if(defined $role_id) {

my $longest_path = 0;

$sth = $dbh->prepare("SELECT path, permissions FROM repository_controls

INNER JOIN projects

ON projects.identifier='$project_id'

WHERE repository_controls.project_id=projects.id

AND repository_controls.role_id=$role_id");

$sth->execute;

while(my ($path, $perm) = $sth->fetchrow_array()) {

if($req_path =~ m{^($path).*}) {

my $path_length = length $path;

if($path_length > $longest_path) {

$longest_path = $path_length;

$user_perm = $perm;

}

$sth->finish();

}

else {

# non-member access

$user_perm = get_role_permission('1', $project_id, $req_path, $r);

}

$dbh->disconnect();

}

else {

# anonymous access

$user_perm = '';

}

if(!defined($user_perm)) {

$user_perm = '';

}

return $user_perm;

}

註解方面我就沒有時間去慢慢改慢慢加了，
反正目前稍微測試了一下是可以在 virtual host 的 / 為起點的設定下正常運作，
而原作者的設計是假設檔案庫一定不是在 / 下面而是在某層目錄之下，
這個部分我也保留了原作者用 regular expression 的處理方式，
所以如果不是掛在 / 的話能不能正常運作我也不知道。

如果你的 svn 位址不是在 / 開始 (如 http://svn.xxx.xxx/repos 而不是 http://svn.xxx.xxx/)，
請自行修改 get_project_identifier() 及 get_requested_path() 中 if($location ne "/") { ... } 的部分。
可以仿照 else { ... } 內的邏輯實作一份。
因為我的檔案庫掛在根目錄，
所以對於不是掛在根目錄的 case 我是直接使用原作者有問題的實作。

NOTE: (for users who don't understand Tranditional Chinese)
If your SVN repositories aren't located on the root directory of your website, you MUST re-implement some codes in get_project_identifier() and get_requested_path().

if($location ne "/") {
    # You have to re-implement this block.
}
else {
    # This block is implemented by me. You can follow the same logic for implementing the previous block.
}

if($location ne "/") {

# You have to re-implement this block.

}

else {

# This block is implemented by me. You can follow the same logic for implementing the previous block.

}

Since my SVN repositories are located on the root directory, I can only implement and test this case.
I know that I can generalize these codes in order to handle all cases, but I don't have enough time and environments.
Thus, I only publish my modifications in my blog rather than Redmine's forum.

至於那個 SQL 的寫法，
就我讀過的書來說如果寫 SELECT xxx FROM a, b, c 是所謂的 CROSS JOIN，
而且我記得 CROSS JOIN 的 performace 會比較差 (但實際測試似乎都會被 MySQL 的 optimizer 處理到沒差多少)，
所以我還是直接寫成 SELECT xxx FROM a INNER JOIN b INNER JOIN c 的形式，
原作者用的是前者的形式而且條件式的部分一律使用 WHERE 不使用 ON (那種寫法確實也沒地方能擺 ON)，
我並不是 SQL 的專家所以也沒有在研究這個的，
不過之前讀過一本 O'Reilly 的 SQL 之美學似乎作者是比較支持前者的寫法，
到底哪種好哪種不好可能就要問資管畢業的夭壽仔了 (他好像跑去當兵都找不到人)，
我是覺得可讀性來說用 INNER JOIN + ON 的方式可以一層一層看出設計者的目的，
會比較容易理解就是了；
原作者寫的 SQL 我是沒有動它，
所以 code 裡會同時看到兩種寫法。

另外還發現一個小 bug，
就是如果編輯已經設定好的 role 權限，
然後把 read / write 的權限通通都取消的話，
儲存以後會發現權限會保持原狀而不會變成 None (新增的時候兩個都不勾倒是會正常)，
這個部分要修改一下 controller 裡的 edit method：

--- repository_controls_controller.rb   (revision 2)
+++ repository_controls_controller.rb   (working copy)
@@ -21,6 +21,8 @@
 
     def edit
         if request.post?
+            params[:repository_control][:permissions] = [] if !params[:repository_control][:permissions]
+           
             @control.update_attributes(params[:repository_control])
             flash[:notice] = l(:notice_successful_update)
             redirect_to :controller => 'projects', :action => 'settings', :tab => 'repository_controls', :id => @control.project_id

--- repository_controls_controller.rb (revision 2)

+++ repository_controls_controller.rb (working copy)

@@ -21,6 +21,8 @@

def edit

if request.post?

+ params[:repository_control][:permissions] = [] if !params[:repository_control][:permissions]

@control.update_attributes(params[:repository_control])

flash[:notice] = l(:notice_successful_update)

redirect_to :controller => 'projects', :action => 'settings', :tab => 'repository_controls', :id => @control.project_id

2009-12-23 Update
根據機八林餅幹的說法，
SELECT xxx FROM a, b, c 這種寫法 MySQL 會做 cache，
單次跑出來是數據會差不多，
但是跑多幾次就會發現這種寫法會比較快。

2010-01-27 Update
今天發現就算用這個改寫過的 RedmineRepoControl.pm，
在 MPM=worker 的模式下用 svn copy 還是會爆炸。
gdb 也攔不到原因只會看到直接 program exited with code 02 跳掉，
所以 svn 會說 connection truncated。
目前懶得追了，
只確定是某次 DBI->connect 呼叫下去就爛掉。
跟以前遇到的狀況一樣只要把 apache 改回 MPM=prefork 就會正常。
Redmine 官方的 Redmine.pm 也有遇到一樣的問題：
http://www.redmine.org/boards/2/topics/7593
最新的一篇是 4 個月前了，
大概是沒救了。

2009 年 12 月
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31