Dear General,
I have stolen some code from information_schema.applicable_roles, so that i can query the roles for a user, without having to become that user (a superuser executes this).
The problem is that pg_has_role does not recognize the usernames when they are escaped by quote_literal or quote_ident.
I allow a period "." as a character in usernames in the front-end, so escaping is necessary in most cases. Also, it´s a principle that all user-typed text is escaped to prevent SQL inserts, even through user names.
I think that the authorization of PostgreSQL has been designed with great care, so i´m not sure if this might be called a "bug".
But it seems that i can´t use this function.
Does anyone have the surrogate SQL statement lying around? (from before pg_has_role was born)
here´s my code:
-------------------------------------
CREATE OR REPLACE FUNCTION contacts.user_roles(p_role name)
RETURNS SETOF text
AS
$body$
DECLARE
--non-existant roles will result in an error.
arecord record;
t_role name;
BEGIN
t_role := quote_ident(trim(both '\'' from trim(both '\"' from p_role)));--'"--quotes might allready have been added by a calling function
--RAISE NOTICE 'getting roles for role: %', t_role;
FOR arecord IN
(SELECT b.rolname::information_schema.sql_identifier AS role_name
FROM pg_auth_members m
JOIN pg_authid a ON m.member =
a.oid
JOIN pg_authid b ON m.roleid = b.oid
WHERE pg_has_role(t_role, a.oid, 'MEMBER'::text))
LOOP
RETURN NEXT arecord.role_name;
END LOOP;
END
$body$
LANGUAGE plpgsql STRICT STABLE;
-------------------------------------
WBL
