Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6372F632E45 for ; Thu, 15 Jul 2010 06:24:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 78964-09 for ; Thu, 15 Jul 2010 09:24:44 +0000 (UTC) X-Greylist: delayed 00:08:37.274829 by SQLgrey-1.7.6 Received: from tigre.interieur.gouv.fr (tigre.interieur.gouv.fr [212.234.218.76]) by mail.postgresql.org (Postfix) with ESMTP id C7B35632AD9 for ; Thu, 15 Jul 2010 06:24:44 -0300 (ADT) Received: from puma.interieur.gouv.fr by tigre3-26.interieur.gouv.fr with ESMTP id o6F9G4Ub022014 for ; Thu, 15 Jul 2010 11:16:04 +0200 Received: from smtp-hub02.messagerie.si.mi (SMTP-HUB02 [10.253.19.214]) by puma.interieur.gouv.fr with ESMTP; Thu, 15 Jul 2010 11:15:58 +0200 Received: from icasso2.mel75.si.mi (unknown [10.237.24.245]) by smtp-hub02.messagerie.si.mi (Postfix) with ESMTPS id 05F611F0002; Thu, 15 Jul 2010 11:15:58 +0200 (CEST) Received: from icasso2 (unknown [127.0.0.1]) by icasso2.mel75.si.mi (Postfix) with ESMTP id 6DA8A5CC0E2; Thu, 15 Jul 2010 11:15:57 +0200 (CEST) Received: from [10.237.6.16] (unknown [10.237.6.16]) (Authenticated sender: thomas.reiss@interieur.gouv.fr) by icasso2.mel75.si.mi (Postfix) with ESMTP id 235615CC0D3; Thu, 15 Jul 2010 11:15:57 +0200 (CEST) Message-Id: <4C3ED1D3.3010203@interieur.gouv.fr> Date: Thu, 15 Jul 2010 11:16:03 +0200 From: REISS Thomas DSIC BIP User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= CC: pgsql-sql@postgresql.org Subject: Re: Question on COUNT performance References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------090703030504040305040106" X-Anti-Virus: Kaspersky Anti-Virus for Sendmail with Milter API 5.6.20, bases: 20100714 #3966972, check: 20100715 clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.509 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/74 X-Sequence-Number: 34387 This is a multi-part message in MIME format. --------------090703030504040305040106 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Hello, You can add another JOIN in your function to avoid the test to return=20 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=20 (1,4),(2,5),(3,6)), util (id_user,id_org) AS (VALUES (1,1),(2,2)) SELECT COUNT(*)!=3D0 FROM contact JOIN customer_contact ON=20 contact.id_contact=3Dcustomer_contact.id_contact JOIN customer ON=20 customer.id_customer=3Dcustomer_contact.id_customer JOIN util ON customer.id_org=3Dutil.id_or= g WHERE contact.id_contact=3D$2 AND util.id_user=3D$1; $$ LANGUAGE SQL; =20 The WITH clause and VALUES was supposed to give a test case. You simply=20 have to remove them and keep the query. So the function becomes a simple=20 SQL function. Hope this helps :-) Regards Jean-Michel Souchard and Thomas Reiss -------- Message original -------- Sujet : Re: [SQL] Question on COUNT performance De : Anders =D8stergaard Jensen Pour : pgsql-sql@postgresql.org Date : 15/07/2010 02:14 > Hi all,=20 > > Thank you so much for your kind replies. It has all been a great help.=20 > > I tried the SELECT COUNT(1) but that didn't yield any improvement, sorr= y.=20 > > Doing the index on f_plan_event_acl( ... ) wont work, as the=20 > parameters are frequently shifted (the second parameter denotes the id=20 > of a user in another table).=20 > > As Mr. Leeuwen rightfully points out, there might be some performance=20 > problems in my acl functions (these are basic functions that determine=20 > wether or not a user has got access to a certain row in a table or=20 > not---fx f_customer_acl(customer_id, user_id) will return true if the=20 > user has access to the customer with ID customer_id etc, the same for=20 > f_project_acl on projects etc).. I am not great at optimising=20 > PL/pgSQL, though I have the assumption that the speed of the=20 > procedural language might have a great impact here.=20 > > Before I start changing the content of the function that Mr. Leeuwen=20 > kindly provided above, can I pleas ask for help on how to optimise the=20 > other acl functions first?=20 > > 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 =3D contacts.id=20 > > JOIN customers cust ON cust.id =3D cc.customer_id > WHERE contacts.id =3D cid; > > SELECT INTO user * FROM users WHERE id=3Duid; > > if (customer.org_id !=3D 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 =3D $1; > customer_id =3D $2; > SELECT INTO user * FROM users WHERE id=3Duser_id; > SELECT INTO customer * FROM customers WHERE id=3Dcustomer_id; > > -- Assert that org_id matches:=20 > if (customer.org_id !=3D 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 :=3D $1; > doc_id :=3D $2; > SELECT INTO user * FROM users WHERE id=3Duser_id; > SELECT INTO doc * FROM documents WHERE id=3Ddoc_id; > > -- Check that org_id matches > if (doc.org_id !=3D user.org_id) then > return false; > end if; > > -- If document was created by user, accept it > if (doc.user_id_created =3D 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=20 > -- user member of a project that allows access to the document?=20 > SELECT INTO proj_rel COUNT(*) AS acl_count FROM project_users > JOIN projects ON project_users.project_id =3D projects.id=20 > > JOIN project_documents ON projects.id =3D=20 > project_documents.project_id > JOIN documents ON project_documents.document_id =3D documents.id=20 > > WHERE documents.id =3D doc_id > AND project_users.user_id =3D $1; > > -- acl_count returns the number of allowed relationships to exactly=20 > -- this document =20 > return proj_rel.acl_count > 0; > END; > $$ LANGUAGE 'plpgsql'; > > Would it be more beneficial to drop the functions and rewrite my basic=20 > queries first? However, it is a nice feature having all security=20 > checks wrapped into a three-four basic functions.=20 > If my design is completely flawed, I am also open to other design=20 > 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=20 > speed this up would be really great. Thanks!=20 --------------090703030504040305040106 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 <aj@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
    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';
- 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
    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! 

--------------090703030504040305040106--