External auth causes problems with assets (listing)

Okay...so I've added more than 1 through the asset functionality (either when editing a region or in the asset manager) but they aren't showing when I go back to the list. Right after adding, the image thumbnail shows on the edit (when using the asset manager), but when I got back to the assets list there's nothing there.

I'm assuming that if my URL is "/perch/core/apps/assets/" then I'm not (not filter query params). If filters are remembered, then I'm clicking the Assets link on the left and I'm not seeing anything.

I've just checked the DB and the resources table has 49 records in it.

Actually...may have found the issue. Maybe you can direct to where I might find the solution.

Turned on debug and found this query and error:

SELECT roleSlug FROM perch3_YOUR_USER_TABLE WHERE userID='<username>'
Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_YOUR_USER_TABLE' doesn't exist

So, it looks like a problem with my custom auth plugin...but I'm not sure how.

It looks like I need to specify a table name in the auth provider...? But, looking at the perch3_users table, there's no column roleSlug – did it not get updated during the migration (I think from 3 to 3.1) that I did today?

Disabling the auth plugin does make the assets work.

So, I copied exactly what's on the Authentication plugins help page but it still doesn't work.

Here's the debug and errors at the bottom of the page:

0.0045  0   [1] SELECT * FROM perch3_user_roles WHERE roleSlug='admin' LIMIT 1
0.0078  0.0032  [41] SELECT p.privKey FROM perch3_user_privileges p
0.0089  0.0012  [34] SELECT settingID, settingValue, userID FROM perch3_settings WHERE userID=0 OR userID=0 ORDER BY userID ASC
0.0111  0.0022  [8] SELECT itemValue FROM perch3_menu_items WHERE itemType='app'
0.012   0.0009  Looking up missing property roleSlug
0.0121  0.0001  SELECT roleSlug FROM perch3_YOUR_USER_TABLE WHERE userID='admin@domain.com'
0.0125  0.0004  [0] Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_YOUR_USER_TABLE' doesn't exist
0.0126  0   [1] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.0131  0.0005  Looking up missing property can_use_bucket
0.0132  0.0001  SELECT can_use_bucket FROM perch3_YOUR_USER_TABLE WHERE userID='admin@domain.com'
0.0134  0.0003  [0] Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_YOUR_USER_TABLE' doesn't exist
0.0135  0.0001  SELECT SQL_CALC_FOUND_ROWS DISTINCT r1.* FROM perch3_resources r1 WHERE r1.resourceKey='orig' AND r1.resourceAWOL=0 AND r1.resourceBucket IN () ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30
0.0137  0.0002  Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30' at line 3
0.0137  0   [1] SELECT FOUND_ROWS() AS `count`
0.015   0.0013  [1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
0.0155  0.0005  [7] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
0.0168  0.0014  [1] SELECT itemTitle FROM perch3_menu_items WHERE itemType='app' AND itemValue='assets' LIMIT 1
0.0176  0.0007  [1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
0.0179  0.0004  [2] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
0.0189  0.001   File: /core/apps/assets/modes/list.post.php
0.0191  0.0002  [3] SELECT DISTINCT resourceType FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !="" ORDER BY resourceType ASC
0.0196  0.0005  [1] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.0201  0.0005  [40] SELECT t.tagTitle, t.tagSlug, t.tagCount FROM perch3_resource_tags t WHERE tagCount > 0 GROUP BY t.tagID ORDER BY t.tagCount DESC LIMIT 40

If I add the following lines within the PerchAPI_AuthPlugin::recover() method and this does get it to work:

$details['roleSlug']    = 'admin';
$details['can_use_bucket'] = true;

But that's definitely not a solution.

How do I set that? The custom auth page doesn't mention this. Also, what will Perch be looking for in that table? Seems weird to me that it'd be querying a table it doesn't manage. (What happens if it's in a different database?)

Changed the error, but not by much (my table name is user):

Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_user' doesn't exist

It's adding the Perch table name prefix. Even if this does work, my table doesn't have roleSlug

Full debug output:

0.0215  0   [1] SELECT * FROM perch3_user_roles WHERE roleSlug='admin' LIMIT 1
0.0255  0.004   [41] SELECT p.privKey FROM perch3_user_privileges p
0.032   0.0064  [34] SELECT settingID, settingValue, userID FROM perch3_settings WHERE userID=0 OR userID=0 ORDER BY userID ASC
0.0444  0.0125  [8] SELECT itemValue FROM perch3_menu_items WHERE itemType='app'
0.063   0.0186  Looking up missing property roleSlug
0.0631  0.0001  SELECT roleSlug FROM perch3_user WHERE userID='admin@domain.com'
0.064   0.0009  [0] Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_user' doesn't exist
0.0641  0.0001  [1] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.0671  0.003   Looking up missing property can_use_bucket
0.0672  0.0001  SELECT can_use_bucket FROM perch3_user WHERE userID='admin@domain.com'
0.0676  0.0004  [0] Invalid query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.perch3_user' doesn't exist
0.0677  0.0001  SELECT SQL_CALC_FOUND_ROWS DISTINCT r1.* FROM perch3_resources r1 WHERE r1.resourceKey='orig' AND r1.resourceAWOL=0 AND r1.resourceBucket IN () ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30
0.068   0.0003  Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY r1.resourceUpdated DESC, r1.resourceID DESC LIMIT 0, 30' at line 3
0.068   0.0001  [1] SELECT FOUND_ROWS() AS `count`
0.0895  0.0214  [1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
0.0903  0.0008  [7] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
0.0933  0.003   [1] SELECT itemTitle FROM perch3_menu_items WHERE itemType='app' AND itemValue='assets' LIMIT 1
0.0945  0.0012  [1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
0.095   0.0005  [2] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
0.0975  0.0025  File: /core/apps/assets/modes/list.post.php
0.0993  0.0018  [3] SELECT DISTINCT resourceType FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !="" ORDER BY resourceType ASC
0.0999  0.0006  [1] SELECT DISTINCT resourceBucket FROM perch3_resources WHERE resourceAWOL=0 AND resourceType !=""
0.1013  0.0013  [40] SELECT t.tagTitle, t.tagSlug, t.tagCount FROM perch3_resource_tags t WHERE tagCount > 0 GROUP BY t.tagID ORDER BY t.tagCount DESC LIMIT 40

I'm not. As far as I can tell, your code is looking for roleSlug and then running a query (as per Looking up missing property roleSlug in the debug).

I've setup a plain Perch Runway install. Without the auth plugin, the assets work perfectly. Once I add the auth plugin (no other changes), the assets no longer show and the errors show up.

<?php

class External_auth_plugin extends PerchAPI_AuthPlugin
{
    protected $table = 'user';

    public function log_user_in($username, $password)
    {
        //make request to check username and password
        return [
            'email' => 'admin@example.com',
            'role'  => 'admin',
        ];
    }

    public function resume_session()
    {
        //make request to check username and password
        return [
            'email' => 'admin@example.com',
            'role'  => 'admin',
        ];
    }

    public function log_user_out()
    {
        return true;
    }
}

This file is located at perch/addons/plugins/auth/symfony/auth.php as per the docs.

The file is a near copy of your doc page plus the addition of protected $table = 'user'; as per.

If needed, I can set you a copy the code/database or access to the server if needed (privately of course).

Any updates on this?

Thanks!

Hi Drew,

I'm not even sure where to start. Let try to explain this.

1. I am doing actual authentication. I am only providing a simple example to show where the issue is. As I've said, I've copied what's on your documentation page to accomplish this.

2. Your documentation for providing your external authentication is incomplete (https://docs.grabaperch.com/api/auth/) as per requiring the addition of protected $table = 'user'; in the auth class (as per previous messages in this conversation).

3. I am not querying your user table – your code is. In my real code, I'm querying my own table and you should not be querying my table (though you are telling me the opposite).

4. Your code is querying the "user" table looking for roleSlug – I don't care, nor do I or my code know anything about roleSlugs. According to the docs, you only say I need to return the "role" which I am.

Can you please try following your instructions on setting up external authentication with the example you provide in the docs (https://docs.grabaperch.com/api/auth/) in an empty Runway site? This is all I've done and I'm receiving this error.

I've put my code on github: https://github.com/xmmedia/perch_auth_bug The auth file as above, is here: https://github.com/xmmedia/perch_auth_bug/blob/master/perch/addons/plugins/auth/external/auth.php

Again, that's not my real auth file – I'm only using this to reproduce the issue and show that there are no other plugins/etc.

Looking forward to resolving this. Thanks Darryl

Hi Drew,

Any thoughts on where the issue lies?

Hi Drew,

Are there any updates on this? I can send you a copy of the exact code I'm using for you to try along with the DB so it only takes a quick setup to try it. I don't want to have to learn all of the Perch auth and asset code to figure out what's going on.