escaped rolenames in pg_has_role

From: "Willy-Bas Loos" <willybas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: escaped rolenames in pg_has_role
Date: 2007-06-26 14:44:36
Message-ID: 1dd6057e0706260744t59d3741eje082d07fcd7f38a8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-06-26 15:05:53 Re: escaped rolenames in pg_has_role
Previous Message Bart Degryse 2007-06-26 14:36:59 Re: NO DATA FOUND Exception