Problem with function aclcontains, features or bug?

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(at)axxent(dot)ca

"Vadim I. Passynkov" <pvi(at)axxent(dot)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 Lane wrote:
>
> "Vadim I. Passynkov" <pvi(at)axxent(dot)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(at)axxent(dot)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(at)axxent(dot)ca