Thread: By Passed Domain Constraints

     I have a database in which I used domains with check constraints  
to keep all the constraints nice and uniform for like columns across  
tables.  My users access and update data primarily through function  
calls that have parameters that of DOMAIN types.
    These constraints work when I call the functions via psql or  
from a PHP client.  But, my pqlib users seem to be able to get bad  
data in.  I have not done any real testing yet to track this down,  
but have looks through the archives and have not been able to spot  
anything.  Anybody else seen something like this?  Am I missing  
something fundamental.
    I have noticed that I can cast the bad data to the domain type,  
but not to a varchar and then the domain type.

i.e.

Select passcode::D_PASSCODE from  
employee_passcode;                      -- will work
Select passcode::varchar::D_PASSCODE from employee_passcode;      --  
properly complains --> ERROR:  value for domain d_passcode violates  
check constraint "d_passcode_check"

    It looks like the data is not checked when passed to the  
function and from their on out since it is already the correct type  
it is not checked again. Has anyone else seen something like this?
    I am using 8.0.1.  On the pqlib side we are calling PQexecParams  
and using NULL for param types for the function calls in question.

Robert Perry <rlperry@lodestonetechnologies.com> writes:
>      It looks like the data is not checked when passed to the  
> function and from their on out since it is already the correct type  
> it is not checked again. Has anyone else seen something like this?

IIRC, plpgsql does not know anything about domains and does not enforce
domain constraints when assigning to local variables or the function
result.  The same is true of the other PLs, though I think it could only
matter for the function result in those cases (internal variables aren't
of SQL types anyway for them).  You could probably work around this by
writing explicit casts to the domain inside the function, eg
"RETURN x::domain" not just "RETURN x".

I thought the unfinished work for domains was mentioned on the TODO list
but I don't see anything about it right now.
        regards, tom lane

     I'm sorry Tom, but I am not certain that I understand how this  
known problem applies to my problem.

select * from employee_passcode_update('system',100000, 'DOGGY');
ERROR:  value for domain d_passcode violates check constraint  
"d_passcode_check"
    I call the same function from pqlib using PQexecParams and the  
next thing I know.

select * from employee_passcode where id = 100000;   id   | passcode | mod_user |           timestamp
--------+----------+----------+-------------------------------
100000 | DOGGY    | system   | 2005-07-06 11:37:09.926907-04

Where

\df employee_passcode_update                                       List of functions
Schema |           Name           | Result data type |           
Argument data types
--------+--------------------------+------------------ 
+----------------------------------------
public | employee_passcode_update | d_employee_id    | d_user_name,  
d_employee_id, d_passcode

and

\d employee_passcode   Table "public.employee_passcode"  Column   |     Type      | Modifiers
-----------+---------------+-----------
id        | d_employee_id | not null
passcode  | d_passcode    |
mod_user  | d_user_name   |
timestamp | d_timestamp   |
Indexes:    "employee_passcode_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:    "employee_passcode_mod_user_fkey" FOREIGN KEY (mod_user)  
REFERENCES employee(user_name)
    The same plpgsql function is being called each time.   
Additionally, I believe that the same checks work from php.
    However, if I cast the D_PASSCODE to a varchar and back in the  
function implementation I do get the expected exception when called  
from PQexecParams.  Which is good.  It means I have a work around,  
though I really do not want to updated hundreds of functions to do  
this.  (I suspect that this will be my short term solution) Just  
casting to a D_PASSCODE does nothing.  I am guessing this is because  
p_passcode, my function parameter in question, is already a D_PASSCODE.
    I have also been bitten by the problem you are describing. But,  
that one is a problem even when called from psql if I am not  
mistaken.  Does psql not use pqlib?  Perhaps it is something about  
PQexecParams that is the problem.  I will test in a little while.

Thanks for you help
Robert Perry

On Jul 6, 2005, at 10:35 AM, Tom Lane wrote:

> Robert Perry <rlperry@lodestonetechnologies.com> writes:
>
>>      It looks like the data is not checked when passed to the
>> function and from their on out since it is already the correct type
>> it is not checked again. Has anyone else seen something like this?
>>
>
> IIRC, plpgsql does not know anything about domains and does not  
> enforce
> domain constraints when assigning to local variables or the function
> result.  The same is true of the other PLs, though I think it could  
> only
> matter for the function result in those cases (internal variables  
> aren't
> of SQL types anyway for them).  You could probably work around this by
> writing explicit casts to the domain inside the function, eg
> "RETURN x::domain" not just "RETURN x".
>
> I thought the unfinished work for domains was mentioned on the TODO  
> list
> but I don't see anything about it right now.
>
>             regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to  
> majordomo@postgresql.org)
>

Robert Perry <rlperry@lodestonetechnologies.com> writes:
>      I have also been bitten by the problem you are describing. But,  
> that one is a problem even when called from psql if I am not  
> mistaken.  Does psql not use pqlib?  Perhaps it is something about  
> PQexecParams that is the problem.  I will test in a little while.

[ thinks about it... ]  If you've declared the function input parameter
as a domain type and then write a parameterized query like... function($1) ...
and don't specify any particular datatype for the parameter symbol,
I think the backend will infer the domain type as the parameter type.
Which would also allow bypassing the domain checks.

You could work around this by explicitly specifying the parameter
type as text or varchar or whatever the domain's base type is.
I wonder though if we oughtn't change the backend so that the inferred
type of a parameter symbol is never a domain, but the domain's base
type.  That would force the proper application of CoerceToDomain inside
the constructed query parsetree.
        regards, tom lane

Tom
    Thank you very much.  This sounds like my problem exactly.   I  
personally, feel that the change you have described is the right way  
to go for PostgreSQL. But, since the thing that I expected to work  
does not and would with your suggested change I guess that my opinion  
is pretty predictable.
    B.T.W.  Using PQexec instead of PQexecParams also solves my  
problem.  But, that is not a surprise either given your assessment of  
the problem.  Since all of the C++ code in my project ends up calling  
a single function that calls PQexecParams (this was done to  
centralize the conversion of PostgreSQL exceptions to out own  
internal exception classes) I think it is going to be easier for us  
to make this function dynamically build a non parameterized query.   
But, I still appreciate your advice on a work around and I am holding  
it as my plan B.

Thanks again
Robert Perry

On Jul 6, 2005, at 12:05 PM, Tom Lane wrote:

> Robert Perry <rlperry@lodestonetechnologies.com> writes:
>
>>      I have also been bitten by the problem you are describing. But,
>> that one is a problem even when called from psql if I am not
>> mistaken.  Does psql not use pqlib?  Perhaps it is something about
>> PQexecParams that is the problem.  I will test in a little while.
>>
>
> [ thinks about it... ]  If you've declared the function input  
> parameter
> as a domain type and then write a parameterized query like
>     ... function($1) ...
> and don't specify any particular datatype for the parameter symbol,
> I think the backend will infer the domain type as the parameter type.
> Which would also allow bypassing the domain checks.
>
> You could work around this by explicitly specifying the parameter
> type as text or varchar or whatever the domain's base type is.
> I wonder though if we oughtn't change the backend so that the inferred
> type of a parameter symbol is never a domain, but the domain's base
> type.  That would force the proper application of CoerceToDomain  
> inside
> the constructed query parsetree.
>
>             regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that  
> your
>        message can get through to the mailing list cleanly
>

> You could work around this by explicitly specifying the parameter
> type as text or varchar or whatever the domain's base type is.
> I wonder though if we oughtn't change the backend so that the inferred
> type of a parameter symbol is never a domain, but the domain's base
> type.  That would force the proper application of CoerceToDomain inside
> the constructed query parsetree.

Remember we have similar weirdness when returning domain types from 
stored procs :(

Chris