Hello,
You can add another JOIN in your function to avoid the test to return
either true or false.
Here's an example:
CREATE OR REPLACE FUNCTION f_contact_acl(integer, integer)
RETURNS BOOL AS $$
WITH contact (id_contact) AS (VALUES (1),(2),(3),(7),(8)),
customer (id_customer,id_org) AS (VALUES (4,1),(5,2),(6,3)),
customer_contact (id_contact,id_customer) AS (VALUES
(1,4),(2,5),(3,6)),
util (id_user,id_org) AS (VALUES (1,1),(2,2))
SELECT COUNT(*)!=0 FROM contact JOIN customer_contact ON
contact.id_contact=customer_contact.id_contact
JOIN customer ON
customer.id_customer=customer_contact.id_customer
JOIN util ON customer.id_org=util.id_org
WHERE contact.id_contact=$2 AND util.id_user=$1;
$$ LANGUAGE SQL;
The WITH clause and VALUES was supposed to give a test case. You simply
have to remove them and keep the query. So the function becomes a simple
SQL function.
Hope this helps :-)
Regards
Jean-Michel Souchard and Thomas Reiss
-------- Message original --------
Sujet : Re: [SQL] Question on COUNT performance
De : Anders Østergaard Jensen <a...@itersys.dk>
Pour : pgsql-sql@postgresql.org
Date : 15/07/2010 02:14
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
<http://contacts.id>
JOIN customers cust ON cust.id <http://cust.id> = cc.customer_id
WHERE contacts.id <http://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';
- Hide quoted text -
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
<http://projects.id>
JOIN project_documents ON projects.id <http://projects.id> =
project_documents.project_id
JOIN documents ON project_documents.document_id = documents.id
<http://documents.id>
WHERE documents.id <http://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!
