laravel mysql php ldap lumen
I have 3 tables:
Ad_users
Ad_groups
Ad_usersxad_groups
Ad_usersxad_groups is the junction table for the other two.
Now, I'm working in a lumen/laravel project and we are using eloquent model.
I have the following models for ad_users table and ad_groups table:
ad_user.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Ad_user extends Model
{
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'common_name',
'location',
'description',
'postalcode',
'physical_delivery_office_name',
'telephone_number',
'initials',
'street_address'
];
/**
* Hides pivot in return queries.
*
* @var array
*/
protected $hidden = [
'pivot'
];
/**
* Many-To-Many relationship.
*/
public function ad_groups()
{
return $this->belongsToMany('App\Ad_group', 'Ad_usersxad_groups');
}
}
ad_group.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Ad_group extends Model
{
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name'
];
/**
* Hides pivot from return queries.
*
* @var array
*/
protected $hidden = [
'pivot'
];
/**
* Many-To-Many relationshipl.
*/
public function ad_users()
{
return $this->belongsToMany('App\Ad_user', 'Ad_usersxad_groups');
}
public function extensiontables()
{
return $this->belongsToMany('App\extensiontables_registry', 'extensiontables_registryxad_groups');
}
}
The data inside ad_groups and ad_user basically comes from our companys LDAP server.
So when a user logs in, a middleware comes in establishing a connection to the ldap and retrieving the users data. The data is then synced to our local database, meaning its either updated or created.
Just for completeness, here is the code being executed when a user access the login route:
AuthController.php
public function login(Request $request)
{
$ldap = new LDAP;
$jwt = new JWTAuth;
$response = new Response;
$this->validate($request, [
'username' => 'string|required',
'password' => 'string|required'
]);
if(!$ldap->authenticateUser($request->username, $request->password))
{
return response()->json([
'error' => 'User could not be authenticated'
], 401);
}
/**
* Synchronizes the user's data from ldap with the local db.
* Used for better performance and mitigating network overhead.
*/
$ldap->syncData($request->username);
$response->header('Authorization', 'Bearer '.$jwt->generateJWT($request->username));
return $response;
}
Here, the important part is the $ldap->syncData($request->username);.
It calls to LDAP.php, where this code is executed:
LDAP.php
public function syncData($username)
{
try
{
if(!$this->connect())
{
$this->disconnect();
return false;
}
$userData = $this->getUser($username, [
'cn',
'l',
'description',
'postalcode',
'physicaldeliveryofficename',
'telephonenumber',
'initials',
'memberof',
'streetaddress'
]);
$user = Ad_user::updateOrCreate(
['common_name' => $userData['cn']],
[
'common_name' => $userData['cn'],
'location' => $userData['l'],
'description' => $userData['description'],
'postalcode' => $userData['postalcode'],
'physical_delivery_office_name' => $userData['physicaldeliveryofficename'],
'telephone_number' => $userData['telephonenumber'],
'initials' => $userData['initials'],
'street_address' => $userData['streetaddress']
]
);
// Remove everything but the user roles
foreach($userData['memberof'] as $key=>$role)
{
preg_match("/^CN=.+?,/", $role, $role);
$userData['memberof'][$key] = substr($role[0], 3, -1);
}
// Loop through every role because updateOrCreate cant handle arrays
foreach($userData['memberof'] as $value)
{
$roles[] = Ad_group::updateOrCreate(
['name' => $value],
['name' => $value]
)->id;
}
// Syncs current roles received from ldap with the local db
$user->ad_groups()->sync($roles);
}
catch(\Exception $e)
{
$error = array("exception_code" => $e->getCode(), "error_message" => $e->getMessage());
Log::error($error);
return false;
}
finally
{
$this->disconnect();
}
}
I discovered that whenever a user logs into the system, records are inserted into the junction table.
EVERY time, meaning theres a whole lot of redundant data being created.
I guess the important part of the code causing this problem is this:
$user->ad_groups()->sync($roles)
After 7 logins by the same user for example, the junction table looks like this:
select * from ad_usersxad_groups;
+------------+-------------+------------+------------+
| Ad_user_id | Ad_group_id | created_at | updated_at |
+------------+-------------+------------+------------+
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
| 1 | 1 | NULL | NULL |
| 1 | 2 | NULL | NULL |
| 1 | 3 | NULL | NULL |
| 1 | 4 | NULL | NULL |
| 1 | 5 | NULL | NULL |
| 1 | 6 | NULL | NULL |
| 1 | 7 | NULL | NULL |
| 1 | 8 | NULL | NULL |
| 1 | 9 | NULL | NULL |
| 1 | 10 | NULL | NULL |
| 1 | 11 | NULL | NULL |
| 1 | 12 | NULL | NULL |
+------------+-------------+------------+------------+
This is neither desired nor acceptable, but since I'm pretty new to lumen/laravel and also the php LDAP API in use here, I have really little to no idea what might cause this behavior Oo At least I'm pretty sure that MySQL/MariaDB does not effect this behavior, since the FOREIGN KEYs have no CASCADEs attached to them.
I wonder if I did something wrong in the definition of the models, but I wouldn't know what xD
Some help from you would be really appreciated ^^
EDIT:
The log has shown this for the roles array:
[2020-01-31 08:57:27] local.INFO: array (
0 => 1,
1 => 2,
2 => 3,
3 => 4,
4 => 5,
5 => 6,
6 => 7,
7 => 8,
8 => 9,
9 => 10,
10 => 11,
11 => 12,
)
Just for reference, here are the foreign keys of the DB, of course including the ones relevant to this problem. I'm pretty sure I configured them correctly, but maybe I'm just blind so here they are:
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
| ad_usersxad_groups | Ad_user_id | fk_ad_groupxad_user | ad_users | id |
| ad_usersxad_groups | Ad_group_id | fk_ad_userxad_group | ad_groups | id |
| extensiontables_registryxad_groups | ad_group_id | fk_ad_groupxextensiontables_registry | ad_groups | id |
| extensiontables_registryxad_groups | extensiontables_registry_id | fk_extensiontables_registryxad_group | extensiontables_registry | id |
| extensiontable_itc | coretable_id | fk_extensiontable_itc_coretable | coretable | id |
| inaccessibletable | coretable_id | fk_inaccessibletable_coretable | coretable | id |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
If this doesn't solve your problem, please dump the
$rolesarray and post it here.Didn't solve the problem, but still thanks for the advice! :D I'll dump the $roles array immediately, just give me a sec
Was worth a shot, yes please do. I'll delete or update my answer.
Dumped the log here, I must admit Im pretty confused by it xD
Updated answer now, not sure this will help, but again worth a try.