Problem with function aclcontains, features or bug?

"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 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