Обсуждение: escaped rolenames in pg_has_role

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

"Willy-Bas Loos" <willybas@gmail.com> writes:
> The problem is that pg_has_role does not recognize the usernames when they
> are escaped by quote_literal or quote_ident.

Don't use quote_ident here.  In fact, I'd say that you appear to have
completely misunderstood the appropriate rules for quoting at all.
If you are passed data that you don't know if it's quoted or not, what
the heck are you supposed to do with it?  A quote in the data might be
real data or quoting, and you have no way to tell the difference.

You need to redesign your function stack to eliminate that ambiguity.
I'd recommend that data items never have quoting, except at the instant
that they get embedded into SQL strings (which this function isn't doing
at all, and so it does not need to quote the name).

            regards, tom lane

> A quote in the data might be real data or quoting, and you have no way to tell the difference.
It´s not so bad, because a parameter of type name cannot start with a quote.

But you´re right, i have been having a hard time with the use of quoting.
The rule i used until now was: "every string that has been entered by a user must be escaped, so that there can never be sql inserts".
Every function has all string input escaped. When you call another function from inside your function, don´t pass any un-escaped strings, you can´t allways be sure that others escape their strings properly.
Just for safety´s sake.
This hasn´t been verfy practical in it´s use.

Do I understand correctly that you mean to say:
"User-input strings do not need to be escaped, except when you build an SQL string with it, i.e. when using EXECUTE. When calling functions from inside your functions, it is the responability of the receiving function to handle escaping of its input."
Or in other words,i suppose, are EXECUTE and PERFORM really the only ways that sql in the form of input string parameters could be executed inside my function?

WBL

"Willy-Bas Loos" <willybas@gmail.com> writes:
>> A quote in the data might be real data or quoting, and you have no way to
>> tell the difference.

> It's not so bad, because a parameter of type name cannot start with a quote.

Sure it can.

regression=# select '"Fooey"'::name;
  name
---------
 "Fooey"
(1 row)

regression=# create table foo ( """Fooey""" int);
CREATE TABLE
regression=# \d foo
      Table "public.foo"
 Column  |  Type   | Modifiers
---------+---------+-----------
 "Fooey" | integer |

regression=#

            regards, tom lane

er.. i guess i was misinformed. (by Korry & Susan Douglas´ second edition, maybe this is relatively new?)
However, could you please answer my question?