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(at)lodestonetechnologies(dot)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(at)lodestonetechnologies(dot)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(at)postgresql(dot)org)
>

Robert Perry <rlperry(at)lodestonetechnologies(dot)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(at)lodestonetechnologies(dot)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(at)postgresql(dot)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