Very slow updating category permissions and "access denied" on all pages until done

I should add that all roles except admin are blocked during category permissions updates, not just anonymous users.

I tried it and the "access denied" message is no longer there while updating permissions, but it still runs really slow when updating permissions. In one case 20 min. for one role. I'll have a look at the indexes and tables and make sure they got properly upgraded.

The 5.x-2.x-dev version does almost the same thing, except that the "access denied" messages seem to appear arbitrarily during the update. It's still very slow on a large taxonomy structure (several minutes).

Unfortunately, the one page that I've noticed consistently reading, "access denied" during the 5 min. update is the front page.

Actually, I'm wrong. It's still happening all over the place, and seems to be arbitrary.

No difference in behavior whatsoever. Even though I now have a fraction of the categories in my taxonomy access control lists for each role, it is still incredibly slow, and my entire site says, "Access denied" while updating permissions. I've followed the instructions in Troubleshooting in the docs provided, and rebuilt the permissions on the administer->posts page, and it does fix some permission problems but it doesn't improve performance or help with the "access denied" messages.

After each permission change, TAC calls node_access_rebuild() which, according to what I've read, has been known to cause timeouts, exhaust memory, etc. in the past. I think this is why the updates take such a long time because when I go to admin->posts and run "Rebuild permissions" it takes several minutes to complete.

Wow, I've been reading up on this node_access_rebuild() function, and here's what it appears to do: First it deletes the entire contents of its database table, so that access is denied to all nodes on your site. Then it loads every node in the system as though it were being accessed by a user on the site, checks which access permissions are granted/denied to each role, and by what module or "realm," and stores the results of this check in the node_access table. During this process, the nodes that have not been re-evaluated continue to display "access denied" messages to anyone but the admin. With a large site this function is very slow, and is definitely not the type of thing you call at the end of practically every function in your module. In fact, it's intended to be a safety net in case your site's access is screwed up because of orphaned access control permissions that got left behind when an access control module was improperly un-installed. I just had a look at the code, and in the 5.x version of Taxonomy Access control, it's called in seven places, only one of which is when the module is un-installed. In fact, it gets called every time the admin form is submitted.

As downloaded, TAC takes several minutes (15-30) to update any permission changes to a single role on my site, regardless of how minor. I removed all calls to node_access_rebuild() from the taxonomy_access_admin.inc file and the same update was so fast that I hardly noticed the page refresh! Deleting and re-inserting rows instead of keeping track of stuff and doing selective updates may be easier, but it's definitely not efficient. Nice to be a guinea pig, though. I'm not sure why the node_access table has to be used, since I assume node access attempts can be intercepted by TAC via hooks even if all access is granted to all roles in node_access, but I could be wrong. Might be a failsafe in case TAC fails, too.

The function node_access_acquire_grants() is "smart" enough to delete node_access entries before re-inserting them (man, has anybody ever heard of UPDATE?), so you don't even have to search for entries in node_access before selectively rebuilding the permissions for individual nodes. Just get the nids of the nodes that are associated with the categories that you update in TAC, then call node_access_acquire_grants() to revise only the permissions of the affected nodes.

I've managed to replace most of the node_access_rebuild() calls with functions that selectively update node permissions, and it works very well. It isn't the cleanest thing ever, because it's the result of a mad scramble to fix my sites, but logically it's very sound. The updates are faster, no memory overruns, no page timeouts, and no "access denied" messages.

This is a proof of concept patch to propose a solution to the scalability problem with this module, i.e. the more nodes there are on a site, the longer it takes and the more memory is gobbled up by the node_access_rebuild() function. It greatly reduces the amount of time updates take on larger sites, and eliminates the "access denied" messages on pages during the update process. This is accomplished by individually evaluating node permissions instead of rebuilding them for the entire site, on each form submission. Note: This was done in a hurry so the added functions could probably be replaced with Drupal core API functions, but I've tested it extensively and haven't found any problems with it yet, at least on my sites.

http://drupal.org/node/153256

Okay, here's an updated patch with the sub-selects replaced by LEFT JOINs. This patch fixes the following problems:

- Access denied messages displayed on pages while updating TAC permissions
- Extremely long update times
- Memory limit overruns

Keep in mind that given the cost of node_access functions, TAC would be far more efficient if it compared the data it used to build the admin form, with the submitted values, and acted only on those terms that had been modified. Then it would be slow only if several hundred (or more) nodes were associated with a single taxonomy term that was being updated for a particular role. DELETEs and INSERTs should be replaced by UPDATEs, to take advantage of indexes, and terms should only be deleted if no longer controlled by TAC, and inserted if newly-controlled by TAC.

Here's the patch for taxonomy_access.module. It has a couple of node_access_rebuild() calls when categories are added/removed from Drupal, that I converted to use the new functions in taxonomy_access_admin.inc.

I just noticed something I have to fix in the second patch. It looks like instead of loading the taxonomy_access_admin.inc file globally in taxonomy_access.module, it is selectively loaded based on a URL condition:

Lines 176-181:

  // Only include administrative callbacks and css if we are viewing an admin page.
  else if (arg(0) == 'admin' AND arg(1) == 'user' AND arg(2) == 'taxonomy_access') {
    $path = drupal_get_path('module', 'taxonomy_access');
    include_once($path . '/taxonomy_access_admin.inc');
    drupal_add_css($path . '/admin.css');
  }

In order for it to load the admin functions I added when adding and removing categories, this condition will have to be modified to include the path to this section of Drupal's admininstration interface, like so:

  // Only include administrative callbacks and css if we are viewing an admin page.
  else if (arg(0) == 'admin' AND ((arg(1) == 'user' AND arg(2) == 'taxonomy_access') OR (arg(1) == 'content' AND arg(2) == 'taxonomy'))) {
    $path = drupal_get_path('module', 'taxonomy_access');
    include_once($path . '/taxonomy_access_admin.inc');
    drupal_add_css($path . '/admin.css');
  }

I'll update the patch and post it again.

Hmm... looks like term_node.tid and term_access.rid aren't making it to the query. I guess it makes sense since anonymous users don't belong to a role, but then I wonder why it's been working on my site fine so far. It doesn't look like a pgsql/mysql compatibility error. I'll have a look and try a bunch of more tests and see if I can get it to do that. BTW I'm glad to see that somebody else is having the same problem, because as you can see I've been talking to myself in here for months!

Here's an updated patch to test. As it turns out, I made a syntactical error and put a "(" instead of a "{" in two places. Sorry about that!

I have 559 terms on one site and 310 on another, and large updates take a few seconds. My admin is happy with it, and he uses it several times daily.

Here's an updated patch for each of the affected taxonomy_access files, that:

a) fix an SQL error that occurred with the last one, while updating anonymous users' privileges
b) contain better inline documentation of where the patches have been applied, for the benefit of the developers, and
c) also apply the patch for issue #171675 that causes your logs to fill up with SQL errors

Note: Please apply these patches from the main drupal directory, and make sure your modules/taxonomy_access directory contains a clean copy of 5.x-2.x-dev.