Re: Problem with function aclcontains, features or bug?

Tom Lane wrote:
>
> "Vadim I. Passynkov" <pvi@axxent.ca> writes:
> > But, next result is wrong.
>
> > spidermon=# SELECT aclcontains ( ( SELECT relacl FROM pg_class where
> > relname = 'objects_view' ), 'user pvi=w' );
> >  aclcontains
> > -------------
> >  t
> > (1 row)
>
> aclcontains() is defined in a bizarre and useless fashion in pre-7.1
> releases --- IIRC, it returns T in this example if there is an entry
> mentioning user pvi in the ACL list, regardless of whether it grants
> w access or not.  This is changed for 7.1, but it still doesn't tell
> you what you really want to know, which is whether pvi has w access
> (possibly via a group) or not.
>
> > How I can know permission for user/group before make real operations?
>
> There's no good way at the moment.  Sorry.
>
>                         regards, tom lane

Tom I found some solution

/*
 * written by Vadim Passynkov (pvi@axxent.ca)
 * check_acl ( <relation name>, <mode flag> );
 * <mode flag> should be single letter 'w', 'r', 'a' or 'R'
 */
CREATE FUNCTION check_acl ( text, char ) RETURNS bool AS '
DECLARE
  acl text;
  username text := getpgusername();
  user_id integer;
  rec record;
BEGIN
  IF ( $2 NOT IN ( ''w'',''r'',''a'',''R'' ) ) THEN
    RAISE EXCEPTION ''mode flags must use single letter from "arwR"'';
  END IF;
  SELECT INTO rec relacl, relowner, usesuper, usesysid FROM
    pg_class, pg_user WHERE relname = $1 AND usename = username;
  IF NOT FOUND THEN
    RAISE EXCEPTION ''Did not find any relation named "%".'', $1;
  END IF;
  user_id = rec.usesysid;
  IF rec.relowner = user_id OR rec.usesuper THEN
    RETURN ''t'';
  END IF;
  acl := rec.relacl;
  IF acl IS NULL THEN
    RETURN ''f'';
  END IF;
  IF acl ~ ( ''\"=[rwaR]*'' || $2 || ''[rwaR]*\"'' ) OR /* public */
    acl ~ ( ''\"'' || username || ''=[rwaR]*'' || $2 || ''[rwaR]*\"'' )
/* user */
  THEN
    RETURN ''t'';
  END IF;
  FOR rec IN SELECT pg_group.groname WHERE pg_group.grolist *= user_id
LOOP
    IF acl ~ ( ''\"group '' || rec.groname || ''=[rwaR]*'' || $2 ||
''[rwaR]*\"'' ) THEN
      RETURN ''t'';
    END IF;
  END LOOP;
  RETURN ''f'';
END;
' LANGUAGE 'plpgsql';


--

 Vadim I. Passynkov, Axxent Corp.
 mailto:pvi@axxent.ca