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
Show quoted text
On 6/26/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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
"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?
Show quoted text
On 6/26/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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