Hi all,
Thank you so much for your kind replies. It has all been a great help.
I tried the SELECT COUNT(1) but that didn't yield any improvement, sorry.
Doing the index on f_plan_event_acl( ... ) wont work, as the parameters are
frequently shifted (the second parameter denotes the id of a user in another
table).
As Mr. Leeuwen rightfully points out, there might be some performance
problems in my acl functions (these are basic functions that determine
wether or not a user has got access to a certain row in a table or not---fx
f_customer_acl(customer_id, user_id) will return true if the user has access
to the customer with ID customer_id etc, the same for f_project_acl on
projects etc).. I am not great at optimising PL/pgSQL, though I have the
assumption that the speed of the procedural language might have a great
impact here.
Before I start changing the content of the function that Mr. Leeuwen kindly
provided above, can I pleas ask for help on how to optimise the other acl
functions first?
CREATE OR REPLACE FUNCTION f_contact_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
user record;
contact record;
customer record;
begin
SELECT INTO customer cust.* FROM contacts
JOIN customer_contacts cc ON cc.contact_id = contacts.id
JOIN customers cust ON cust.id = cc.customer_id
WHERE contacts.id = cid;
SELECT INTO user * FROM users WHERE id=uid;
if (customer.org_id != user.org_id) then
return false;
end if;
return true;
end
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f_customer_acl(uid integer, cid integer)
RETURNS BOOL AS $$
declare
user_id integer;
customer_id integer;
user record;
customer record;
begin
user_id = $1;
customer_id = $2;
SELECT INTO user * FROM users WHERE id=user_id;
SELECT INTO customer * FROM customers WHERE id=customer_id;
-- Assert that org_id matches:
if (customer.org_id != user.org_id) then
return false;
end if;
-- Nothing more to check for:
return true;
end;
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f_doc_acl(uid integer, did integer)
RETURNS BOOL AS $$
DECLARE
user_id integer;
doc_id integer;
user record;
doc record;
proj_rel record;
BEGIN
user_id := $1;
doc_id := $2;
SELECT INTO user * FROM users WHERE id=user_id;
SELECT INTO doc * FROM documents WHERE id=doc_id;
-- Check that org_id matches
if (doc.org_id != user.org_id) then
return false;
end if;
-- If document was created by user, accept it
if (doc.user_id_created = user_id) then
return true;
end if; -- if document is public, accept it
if (doc.is_public) then
return true;
end if;
-- else, check the project-document relations -- is the
-- user member of a project that allows access to the document?
SELECT INTO proj_rel COUNT(*) AS acl_count FROM project_users
JOIN projects ON project_users.project_id = projects.id
JOIN project_documents ON projects.id = project_documents.project_id
JOIN documents ON project_documents.document_id = documents.id
WHERE documents.id = doc_id
AND project_users.user_id = $1;
-- acl_count returns the number of allowed relationships to exactly
-- this document
return proj_rel.acl_count > 0;
END;
$$ LANGUAGE 'plpgsql';
Would it be more beneficial to drop the functions and rewrite my basic
queries first? However, it is a nice feature having all security checks
wrapped into a three-four basic functions.
If my design is completely flawed, I am also open to other design
suggestions on how to do proper row-based access control.
I am not asking for the complete solution but a few pointers on how to speed
this up would be really great. Thanks!
