{"action":"created","comment":{"url":"https://api.github.com/repos/xtuple/xtuple/pulls/comments/211673082","pull_request_review_id":148145003,"id":211673082,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIxMTY3MzA4Mg==","diff_hunk":"@@ -8,18 +8,68 @@ DECLARE\n BEGIN\n   -- This query will give us the most permissive privilege the user has been granted\n   SELECT privilege, granted INTO _priv, _grant\n-  FROM privgranted \n+  FROM privgranted\n   WHERE privilege IN ('MaintainAllCRMAccounts','ViewAllCRMAccounts','MaintainPersonalCRMAccounts','ViewPersonalCRMAccounts')\n   ORDER BY granted DESC, sequence\n   LIMIT 1;\n \n   -- If have an 'All' privilege return all results\n   IF (_priv ~ 'All' AND _grant) THEN\n-    RETURN QUERY SELECT * FROM crmacct;\n+    RETURN QUERY SELECT * FROM crmacct\n+                 WHERE (CASE WHEN checkprivilege('MaintainVendorAccounts ViewVendorAccounts')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{vendor}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainCustomerMasters ViewCustomerMasters')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{customer}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainSalesReps ViewSalesReps')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{salesrep}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainTaxAuthorities ViewTaxAuthorities')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{taxauth}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainProspectMasters ViewProspectMasters')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{prospect}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainEmployees ViewEmployees')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{employee}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainUsers')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{user}' IS NOT NULL\n+                                  ELSE false END\n+                         -- plus handle the 'other' types of CRM account\n+                         OR CASE WHEN crmaccttypes(crmacct_id)::TEXT ='{\"customer\":null,\"vendor\":null,\"salesrep\":null,\"taxauth\":null,\"prospect\":null,\"employee\":null,\"user\":null}'\n+                                  THEN true ELSE false END\n+                        );\n   -- Otherwise if have any other grant, must be personal privilege.\n   ELSIF (_grant) THEN\n-    RETURN QUERY SELECT * FROM crmacct \n-                  WHERE crmacct_owner_username = getEffectiveXtUser();\n+    RETURN QUERY SELECT * FROM crmacct\n+                  WHERE crmacct_owner_username = getEffectiveXtUser()\n+                    AND (CASE WHEN checkprivilege('MaintainVendorAccounts ViewVendorAccounts')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{vendor}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainCustomerMasters ViewCustomerMasters')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{customer}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainSalesReps ViewSalesReps')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{salesrep}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainTaxAuthorities ViewTaxAuthorities')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{taxauth}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainProspectMasters ViewProspectMasters')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{prospect}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainEmployees ViewEmployees')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{employee}' IS NOT NULL\n+                                  ELSE false END\n+                         OR  CASE WHEN checkprivilege('MaintainUsers')\n+                                  THEN crmaccttypes(crmacct_id)#>>'{user}' IS NOT NULL\n+                                  ELSE false END\n+                         -- plus handle the 'other' types of CRM account\n+                         OR CASE WHEN crmaccttypes(crmacct_id)::TEXT ='{\"customer\":null,\"vendor\":null,\"salesrep\":null,\"taxauth\":null,\"prospect\":null,\"employee\":null,\"user\":null}'\n+                                  THEN true ELSE false END\n+                        );","path":"foundation-database/public/functions/crmacct.sql","position":45,"original_position":76,"commit_id":"385185f24fbc8df9d80474bf0cf62e731fa093e1","original_commit_id":"385185f24fbc8df9d80474bf0cf62e731fa093e1","user":{"login":"gilmoskowitz","id":704111,"node_id":"MDQ6VXNlcjcwNDExMQ==","avatar_url":"https://avatars1.githubusercontent.com/u/704111?v=4","gravatar_id":"","url":"https://api.github.com/users/gilmoskowitz","html_url":"https://github.com/gilmoskowitz","followers_url":"https://api.github.com/users/gilmoskowitz/followers","following_url":"https://api.github.com/users/gilmoskowitz/following{/other_user}","gists_url":"https://api.github.com/users/gilmoskowitz/gists{/gist_id}","starred_url":"https://api.github.com/users/gilmoskowitz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gilmoskowitz/subscriptions","organizations_url":"https://api.github.com/users/gilmoskowitz/orgs","repos_url":"https://api.github.com/users/gilmoskowitz/repos","events_url":"https://api.github.com/users/gilmoskowitz/events{/privacy}","received_events_url":"https://api.github.com/users/gilmoskowitz/received_events","type":"User","site_admin":false},"body":"This query is functional but quite slow. In a customer database with ~11500 CRM accounts:\r\n\r\nquery | run time\r\n----- | -------\r\n`select * from crmacct limit 1000;` | ~0.8 ms\r\nadd `where crmacct_owner_username = getEffectiveXtUser()` |~ 14 ms\r\nadd both the username check and this case structure | ~ 63 ms\r\nadd just the case structure | ~3000 ms\r\n\r\n","created_at":"2018-08-21T16:29:58Z","updated_at":"2018-08-21T16:29:58Z","html_url":"https://github.com/xtuple/xtuple/pull/3400#discussion_r211673082","pull_request_url":"https://api.github.com/repos/xtuple/xtuple/pulls/3400","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/comments/211673082"},"html":{"href":"https://github.com/xtuple/xtuple/pull/3400#discussion_r211673082"},"pull_request":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/3400"}}},"pull_request":{"url":"https://api.github.com/repos/xtuple/xtuple/pulls/3400","id":208459664,"node_id":"MDExOlB1bGxSZXF1ZXN0MjA4NDU5NjY0","html_url":"https://github.com/xtuple/xtuple/pull/3400","diff_url":"https://github.com/xtuple/xtuple/pull/3400.diff","patch_url":"https://github.com/xtuple/xtuple/pull/3400.patch","issue_url":"https://api.github.com/repos/xtuple/xtuple/issues/3400","number":3400,"state":"open","locked":false,"title":"[5798] Restrict CRM Account List based on account type privileges","user":{"login":"andersdd","id":4682742,"node_id":"MDQ6VXNlcjQ2ODI3NDI=","avatar_url":"https://avatars3.githubusercontent.com/u/4682742?v=4","gravatar_id":"","url":"https://api.github.com/users/andersdd","html_url":"https://github.com/andersdd","followers_url":"https://api.github.com/users/andersdd/followers","following_url":"https://api.github.com/users/andersdd/following{/other_user}","gists_url":"https://api.github.com/users/andersdd/gists{/gist_id}","starred_url":"https://api.github.com/users/andersdd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andersdd/subscriptions","organizations_url":"https://api.github.com/users/andersdd/orgs","repos_url":"https://api.github.com/users/andersdd/repos","events_url":"https://api.github.com/users/andersdd/events{/privacy}","received_events_url":"https://api.github.com/users/andersdd/received_events","type":"User","site_admin":false},"body":"requires xtuple/xtuple#3399","created_at":"2018-08-15T02:42:38Z","updated_at":"2018-08-21T16:29:58Z","closed_at":null,"merged_at":null,"merge_commit_sha":"4e882f588837aa123d6111dcbe54bb77416f3040","assignee":null,"assignees":[],"requested_reviewers":[],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/xtuple/xtuple/pulls/3400/commits","review_comments_url":"https://api.github.com/repos/xtuple/xtuple/pulls/3400/comments","review_comment_url":"https://api.github.com/repos/xtuple/xtuple/pulls/comments{/number}","comments_url":"https://api.github.com/repos/xtuple/xtuple/issues/3400/comments","statuses_url":"https://api.github.com/repos/xtuple/xtuple/statuses/385185f24fbc8df9d80474bf0cf62e731fa093e1","head":{"label":"andersdd:5798_restrict_crm_account_list","ref":"5798_restrict_crm_account_list","sha":"385185f24fbc8df9d80474bf0cf62e731fa093e1","user":{"login":"andersdd","id":4682742,"node_id":"MDQ6VXNlcjQ2ODI3NDI=","avatar_url":"https://avatars3.githubusercontent.com/u/4682742?v=4","gravatar_id":"","url":"https://api.github.com/users/andersdd","html_url":"https://github.com/andersdd","followers_url":"https://api.github.com/users/andersdd/followers","following_url":"https://api.github.com/users/andersdd/following{/other_user}","gists_url":"https://api.github.com/users/andersdd/gists{/gist_id}","starred_url":"https://api.github.com/users/andersdd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andersdd/subscriptions","organizations_url":"https://api.github.com/users/andersdd/orgs","repos_url":"https://api.github.com/users/andersdd/repos","events_url":"https://api.github.com/users/andersdd/events{/privacy}","received_events_url":"https://api.github.com/users/andersdd/received_events","type":"User","site_admin":false},"repo":{"id":10651573,"node_id":"MDEwOlJlcG9zaXRvcnkxMDY1MTU3Mw==","name":"xtuple","full_name":"andersdd/xtuple","owner":{"login":"andersdd","id":4682742,"node_id":"MDQ6VXNlcjQ2ODI3NDI=","avatar_url":"https://avatars3.githubusercontent.com/u/4682742?v=4","gravatar_id":"","url":"https://api.github.com/users/andersdd","html_url":"https://github.com/andersdd","followers_url":"https://api.github.com/users/andersdd/followers","following_url":"https://api.github.com/users/andersdd/following{/other_user}","gists_url":"https://api.github.com/users/andersdd/gists{/gist_id}","starred_url":"https://api.github.com/users/andersdd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andersdd/subscriptions","organizations_url":"https://api.github.com/users/andersdd/orgs","repos_url":"https://api.github.com/users/andersdd/repos","events_url":"https://api.github.com/users/andersdd/events{/privacy}","received_events_url":"https://api.github.com/users/andersdd/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/andersdd/xtuple","description":"xTuple Enterprise Resource Planning","fork":true,"url":"https://api.github.com/repos/andersdd/xtuple","forks_url":"https://api.github.com/repos/andersdd/xtuple/forks","keys_url":"https://api.github.com/repos/andersdd/xtuple/keys{/key_id}","collaborators_url":"https://api.github.com/repos/andersdd/xtuple/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/andersdd/xtuple/teams","hooks_url":"https://api.github.com/repos/andersdd/xtuple/hooks","issue_events_url":"https://api.github.com/repos/andersdd/xtuple/issues/events{/number}","events_url":"https://api.github.com/repos/andersdd/xtuple/events","assignees_url":"https://api.github.com/repos/andersdd/xtuple/assignees{/user}","branches_url":"https://api.github.com/repos/andersdd/xtuple/branches{/branch}","tags_url":"https://api.github.com/repos/andersdd/xtuple/tags","blobs_url":"https://api.github.com/repos/andersdd/xtuple/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/andersdd/xtuple/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/andersdd/xtuple/git/refs{/sha}","trees_url":"https://api.github.com/repos/andersdd/xtuple/git/trees{/sha}","statuses_url":"https://api.github.com/repos/andersdd/xtuple/statuses/{sha}","languages_url":"https://api.github.com/repos/andersdd/xtuple/languages","stargazers_url":"https://api.github.com/repos/andersdd/xtuple/stargazers","contributors_url":"https://api.github.com/repos/andersdd/xtuple/contributors","subscribers_url":"https://api.github.com/repos/andersdd/xtuple/subscribers","subscription_url":"https://api.github.com/repos/andersdd/xtuple/subscription","commits_url":"https://api.github.com/repos/andersdd/xtuple/commits{/sha}","git_commits_url":"https://api.github.com/repos/andersdd/xtuple/git/commits{/sha}","comments_url":"https://api.github.com/repos/andersdd/xtuple/comments{/number}","issue_comment_url":"https://api.github.com/repos/andersdd/xtuple/issues/comments{/number}","contents_url":"https://api.github.com/repos/andersdd/xtuple/contents/{+path}","compare_url":"https://api.github.com/repos/andersdd/xtuple/compare/{base}...{head}","merges_url":"https://api.github.com/repos/andersdd/xtuple/merges","archive_url":"https://api.github.com/repos/andersdd/xtuple/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/andersdd/xtuple/downloads","issues_url":"https://api.github.com/repos/andersdd/xtuple/issues{/number}","pulls_url":"https://api.github.com/repos/andersdd/xtuple/pulls{/number}","milestones_url":"https://api.github.com/repos/andersdd/xtuple/milestones{/number}","notifications_url":"https://api.github.com/repos/andersdd/xtuple/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/andersdd/xtuple/labels{/name}","releases_url":"https://api.github.com/repos/andersdd/xtuple/releases{/id}","deployments_url":"https://api.github.com/repos/andersdd/xtuple/deployments","created_at":"2013-06-12T20:36:45Z","updated_at":"2014-07-06T21:58:43Z","pushed_at":"2018-08-16T02:07:22Z","git_url":"git://github.com/andersdd/xtuple.git","ssh_url":"git@github.com:andersdd/xtuple.git","clone_url":"https://github.com/andersdd/xtuple.git","svn_url":"https://github.com/andersdd/xtuple","homepage":null,"size":74158,"stargazers_count":0,"watchers_count":0,"language":"JavaScript","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"other","name":"Other","spdx_id":null,"url":null,"node_id":"MDc6TGljZW5zZTA="},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"xtuple:5_0_x","ref":"5_0_x","sha":"dd59c2a97b7b9132fc5c17928357ff34f4845163","user":{"login":"xtuple","id":703922,"node_id":"MDEyOk9yZ2FuaXphdGlvbjcwMzkyMg==","avatar_url":"https://avatars1.githubusercontent.com/u/703922?v=4","gravatar_id":"","url":"https://api.github.com/users/xtuple","html_url":"https://github.com/xtuple","followers_url":"https://api.github.com/users/xtuple/followers","following_url":"https://api.github.com/users/xtuple/following{/other_user}","gists_url":"https://api.github.com/users/xtuple/gists{/gist_id}","starred_url":"https://api.github.com/users/xtuple/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xtuple/subscriptions","organizations_url":"https://api.github.com/users/xtuple/orgs","repos_url":"https://api.github.com/users/xtuple/repos","events_url":"https://api.github.com/users/xtuple/events{/privacy}","received_events_url":"https://api.github.com/users/xtuple/received_events","type":"Organization","site_admin":false},"repo":{"id":8138568,"node_id":"MDEwOlJlcG9zaXRvcnk4MTM4NTY4","name":"xtuple","full_name":"xtuple/xtuple","owner":{"login":"xtuple","id":703922,"node_id":"MDEyOk9yZ2FuaXphdGlvbjcwMzkyMg==","avatar_url":"https://avatars1.githubusercontent.com/u/703922?v=4","gravatar_id":"","url":"https://api.github.com/users/xtuple","html_url":"https://github.com/xtuple","followers_url":"https://api.github.com/users/xtuple/followers","following_url":"https://api.github.com/users/xtuple/following{/other_user}","gists_url":"https://api.github.com/users/xtuple/gists{/gist_id}","starred_url":"https://api.github.com/users/xtuple/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xtuple/subscriptions","organizations_url":"https://api.github.com/users/xtuple/orgs","repos_url":"https://api.github.com/users/xtuple/repos","events_url":"https://api.github.com/users/xtuple/events{/privacy}","received_events_url":"https://api.github.com/users/xtuple/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/xtuple/xtuple","description":"This repository contains the source code for the database schema for the PostBooks edition of xTuple ERP, xTuple's REST API core, and xTuple's web application core. The web app core is written in JavaScript, using the Enyo framework for the client side and Node.js for the datasource layer. The database schema for PostBooks runs on PostgreSQL.","fork":false,"url":"https://api.github.com/repos/xtuple/xtuple","forks_url":"https://api.github.com/repos/xtuple/xtuple/forks","keys_url":"https://api.github.com/repos/xtuple/xtuple/keys{/key_id}","collaborators_url":"https://api.github.com/repos/xtuple/xtuple/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/xtuple/xtuple/teams","hooks_url":"https://api.github.com/repos/xtuple/xtuple/hooks","issue_events_url":"https://api.github.com/repos/xtuple/xtuple/issues/events{/number}","events_url":"https://api.github.com/repos/xtuple/xtuple/events","assignees_url":"https://api.github.com/repos/xtuple/xtuple/assignees{/user}","branches_url":"https://api.github.com/repos/xtuple/xtuple/branches{/branch}","tags_url":"https://api.github.com/repos/xtuple/xtuple/tags","blobs_url":"https://api.github.com/repos/xtuple/xtuple/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/xtuple/xtuple/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/xtuple/xtuple/git/refs{/sha}","trees_url":"https://api.github.com/repos/xtuple/xtuple/git/trees{/sha}","statuses_url":"https://api.github.com/repos/xtuple/xtuple/statuses/{sha}","languages_url":"https://api.github.com/repos/xtuple/xtuple/languages","stargazers_url":"https://api.github.com/repos/xtuple/xtuple/stargazers","contributors_url":"https://api.github.com/repos/xtuple/xtuple/contributors","subscribers_url":"https://api.github.com/repos/xtuple/xtuple/subscribers","subscription_url":"https://api.github.com/repos/xtuple/xtuple/subscription","commits_url":"https://api.github.com/repos/xtuple/xtuple/commits{/sha}","git_commits_url":"https://api.github.com/repos/xtuple/xtuple/git/commits{/sha}","comments_url":"https://api.github.com/repos/xtuple/xtuple/comments{/number}","issue_comment_url":"https://api.github.com/repos/xtuple/xtuple/issues/comments{/number}","contents_url":"https://api.github.com/repos/xtuple/xtuple/contents/{+path}","compare_url":"https://api.github.com/repos/xtuple/xtuple/compare/{base}...{head}","merges_url":"https://api.github.com/repos/xtuple/xtuple/merges","archive_url":"https://api.github.com/repos/xtuple/xtuple/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/xtuple/xtuple/downloads","issues_url":"https://api.github.com/repos/xtuple/xtuple/issues{/number}","pulls_url":"https://api.github.com/repos/xtuple/xtuple/pulls{/number}","milestones_url":"https://api.github.com/repos/xtuple/xtuple/milestones{/number}","notifications_url":"https://api.github.com/repos/xtuple/xtuple/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/xtuple/xtuple/labels{/name}","releases_url":"https://api.github.com/repos/xtuple/xtuple/releases{/id}","deployments_url":"https://api.github.com/repos/xtuple/xtuple/deployments","created_at":"2013-02-11T13:33:14Z","updated_at":"2018-08-21T13:17:37Z","pushed_at":"2018-08-21T13:56:37Z","git_url":"git://github.com/xtuple/xtuple.git","ssh_url":"git@github.com:xtuple/xtuple.git","clone_url":"https://github.com/xtuple/xtuple.git","svn_url":"https://github.com/xtuple/xtuple","homepage":"http://www.xtuple.com","size":74626,"stargazers_count":210,"watchers_count":210,"language":"PLpgSQL","has_issues":false,"has_projects":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":327,"mirror_url":null,"archived":false,"open_issues_count":4,"license":{"key":"other","name":"Other","spdx_id":null,"url":null,"node_id":"MDc6TGljZW5zZTA="},"forks":327,"open_issues":4,"watchers":210,"default_branch":"4_12_x"}},"_links":{"self":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/3400"},"html":{"href":"https://github.com/xtuple/xtuple/pull/3400"},"issue":{"href":"https://api.github.com/repos/xtuple/xtuple/issues/3400"},"comments":{"href":"https://api.github.com/repos/xtuple/xtuple/issues/3400/comments"},"review_comments":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/3400/comments"},"review_comment":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/xtuple/xtuple/pulls/3400/commits"},"statuses":{"href":"https://api.github.com/repos/xtuple/xtuple/statuses/385185f24fbc8df9d80474bf0cf62e731fa093e1"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"gilmoskowitz"}}