Thread

1. Problem with function aclcontains, features or bug?

   Vadim I. Passynkov <pvi@axxent.ca> — 2001-02-19T15:23:59Z

   Hi All,
   
   I have some problem with get permission for table/view before make real
   SELECT/UPDATE/INSERT/DELETE operations.
   
   spidermon=# \d objects_view
             View "objects_view"
   ...
   
   spidermon=# \z objects_view
      Access permissions for database "spidermon"
      Relation   |        Access permissions        
   --------------+----------------------------------
    objects_view | {"=","pvi=r","group netadmin=r"}
   (1 row)
   
   spidermon=# SELECT aclcontains ( ( SELECT relacl FROM pg_class where
   relname = 'objects_view' ), 'user pvi=r' );
    aclcontains 
   -------------
    t
   (1 row)
   
   It's result OK.
   But, next result is wrong.
   
   spidermon=# SELECT aclcontains ( ( SELECT relacl FROM pg_class where
   relname = 'objects_view' ), 'user pvi=w' );
    aclcontains 
   -------------
    t
   (1 row)
   
   Same problem with permission for group.
   
   How I can know permission for user/group before make real operations?
   
   FreeBSD 4.2,  postgresql 7.0.3.
   
   Thanks
   
   -- 
   
    Vadim I. Passynkov, Axxent Corp.
    mailto:pvi@axxent.ca
   
   

2. Re: Problem with function aclcontains, features or bug?

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-19T16:36:15Z

   "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
   
   

3. Re: Problem with function aclcontains, features or bug?

   Vadim I. Passynkov <pvi@axxent.ca> — 2001-02-21T00:11:12Z

   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