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