Re: Paypal and "going root"

Richard Huxton wrote:
> Kenneth Downs wrote:
>> Richard Huxton wrote:
>>> Kenneth Downs wrote:
>>>> The last one left that I have is the sticky issue of a paypal IPN
>>>> transaction coming in.  I believe it applies generally to financial
>>>> transactions.  The user is sent by our application to the Paypal
>>>> site.  When they pay, paypal sends a POST with various information
>>>> that we need.  The user does not see this, it is behind the
>>>> scenes.  The POST request must run as an anonymous user because I
>>>> have no state whatsoever.  But the request must also commit
>>>> financial data.  This creates a vulnerability, at least in theory.
>>>
>>> Well, your POST will be authenticating as some sort of PG user,
>>> presumably. Give it its own account and make sure the only
>>> permissions it has is to insert into the paypal_rcpt table (or call
>>> a function that does it for you). Obviously it will only connect
>>> from the webserver(s) and only from the apache user account (or
>>> IIS/whatever). So, you can use the ~/.pgpass password file to keep
>>> that password protected.
>>>
>> I think this is the answer that I need.  This goes to the heart of
>> how the user connects to PG.  The key concept that I'm taking away
>> from your answer is that instead of connecting as a powerful user,
>> connect as a severely limited user who can do only one thing: make
>> that insert.  The rest should be conducted from there.
>
> Ah, that's exactly what I was trying to say. Apologies if I phrased it
> badly, but you seem to have the gist anyway.
>
>> I can put some rules on the receipts table that require the row to
>> contain various hashes and verification codes obtained from the
>> invoice table, and the user who inserts to this table must have no
>> ability to read any other table in the system, so they cannot obtain
>> the codes by any means.  In converse, I believe normal users should
>> not be able to read or write this table, it would be completely
>> invisible to your average Joe.
>
> You might want to allow inserts and have a "validated" flag that you
> can check. Failing that, make sure you log the values on a failed
> insert - always useful to have an audit trail if there are problems.
>

If I can go off on a tangent, my Andromeda framework handles cases like
this well, though I'm a bit embarrassed I did not think of it myself :)

The receipts table can be specified to fetch values from the invoice
table, compare them to the inserted values, and reject the insert if
they do not match.  Assuming they match, the invoices table has a count
of receipts, when that number hits 1, it sets its valid flag, and can
push the flag to the various items purchases.  None of this requires any
code, which is why I'm mentioning it, it all is done as part of the
database table definitions.

The logging of failures would actually be accomplished if I allowed
failed inserts to proceed, but simply did not mark them as valid.  We
could then "debug the data" as it were by just looking at what was inserted.


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010