Horton
Menu

Thread

  1. Paypal and "going root"

    Kenneth Downs <ken@secdat.com> — 2007-05-17T13:45:18Z

    I am seeking to have a system in which it is never necessary for 
application code to "go root" w/respect to the database server, where 
all commands issued to a server are as a regular logged in user with 
their privileges.

There are two holes I know of here.  Thanks to Tom I've got the answer 
to the first one: which is creating users.  We will implement stored 
procedures that create users and grant privileges, and then grant 
execute privileges to these stored procedures.  This means we don't have 
to "go root" to grant membership in groups.

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.  There are fields contained 
in the transaction meant to allow confirmation and prevent fraud, but I 
just don't like that idea of running anonymously and committing 
financial data.

In this case it seems creating a stored procedure will not automatically 
help, as then we just execute the SP anonymously, and it strikes me as 
no different.

Has anybody pondered this and come up with anything?

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

  2. Re: Paypal and "going root"

    John Gateley <gateley@jriver.com> — 2007-05-17T14:14:37Z

    On Thu, 17 May 2007 09:45:18 -0400
Kenneth Downs <ken@secdat.com> 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.  There are fields contained 
> in the transaction meant to allow confirmation and prevent fraud, but I 
> just don't like that idea of running anonymously and committing 
> financial data.

This really isn't postgresql, but anyways: The IPN has fraud detection
built into it, if you follow PayPal's instructions on this. Do
not just accept the IPN, you have to contact PayPal's site with
the info from the IPN so that they can verify it.

Second, it's not an "anonymous user", it's the user running the
webserver that received the IPN. You could set this webserver
up to run as a special user, say "paypalipn", and no-one else
can run as that user, then it's no longer anonymous, and you
can grant privileges as you see fit.

j

  3. Re: Paypal and "going root"

    Richard Huxton <dev@archonet.com> — 2007-05-17T14:23:28Z

    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.

Most of these payment systems pass back a reference number you give 
them. So if you say this is ref# 123456789 that should come back in the 
paypal POST. You'll be checking that this ref and amount match your 
order, no?

So - the only danger is that I can post a fake paypal response to your 
database. Provided your reference numbers are big enough and not 
predictable, it should be straightforward enough to weed out any fakes.


Of course, if you were processing credit-card details yourself, you'd 
want to think about it a bit harder, but with PayPal handling the cash 
side of things, your customers can't be compromised via your site, only 
you can.

-- 
   Richard Huxton
   Archonet Ltd

  4. Re: Paypal and "going root"

    Dave Page <dpage@postgresql.org> — 2007-05-17T14:32:33Z

    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.  There are fields contained
> in the transaction meant to allow confirmation and prevent fraud, but I
> just don't like that idea of running anonymously and committing
> financial data.
> 
> In this case it seems creating a stored procedure will not automatically
> help, as then we just execute the SP anonymously, and it strikes me as
> no different.
> 
> Has anybody pondered this and come up with anything?
> 

In response to the incoming IPN you can create a connection back to the
paypal server to validate it. Iirc, you basically just send the entire
request back again and it returns 'VERIFIED'.

Only then do you act upon the IPN data (remember to double check all the
prices etc, to catch any instance in which a user might have faked the
handover from your site to Paypal).

Regards, Dave.

  5. Re: Paypal and "going root"

    PFC <lists@peufeu.com> — 2007-05-17T15:36:02Z

    >> Has anybody pondered this and come up with anything?

	Well, I've done e-commerce stuff although it was with banks, not paypal.  
There are a few levels of safety :

	- Level 0 : Total stupidity (osCommerce)
	Bank redirects to your site. Order is marked as paid with "paid=1" in the  
URL.
	Solution : fire the programmer

	- Level 1 : callback
	Bank hits URL on your site with data you provided (order_id) so you may  
mark the order as paid.
	Technical documentation says to verify the transaction by checking the  
bank server's IP address.
	Solution : choose another bank

	- Level 3 : crypto
	Bank hits URL on your site with data you provided (order_id) so you may  
mark the order as paid.
	Bank encrypts everything and securely signs the data (HMAC,  
certificate...)
	This works well.

	Additionally, you can hit a URL on the bank to check validity. However,  
if this is not encrypted (SSL...) can you be sure who you are talking to ?

	My advice is to store, in your table, the signed and encrypted data the  
bank sends you.
	Suppose a hacker breaks your verification code and marks his order as  
paid in the database. Even so, he will never be able to use the bank's  
certificate to generate a fake signed payment confirmation to insert in  
your log table. And, usually, the data the bank signs includes the user  
name, id, order id, amount, and user-specified fields, that you use to  
store a SHA1 hash of the list of things the user ordered, so he can't  
steal the payment confirmation from another user, or from  a past order.
	So, if later you suspect something, you can re-check the signatures on  
the payment confirmation data, and the fraud will be apparent.

  6. Re: Paypal and "going root"

    Martijn van Oosterhout <kleptog@svana.org> — 2007-05-17T15:44:16Z

    On Thu, May 17, 2007 at 09:45:18AM -0400, 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.  There are fields contained 
> in the transaction meant to allow confirmation and prevent fraud, but I 
> just don't like that idea of running anonymously and committing 
> financial data.

Just an additional comment to what others have said: have the incoming
connection from paypal just dump all the relevent data into an
unpriviledged table and have it send a NOTIFY. Then have a completely
seperate daemon, with the right priviledges, do any necessary
verification and update the real data. This at the very least gets you
out of handling transient connection failures, and seems more stable
all round...

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

  7. Re: Paypal and "going root"

    Kenneth Downs <ken@secdat.com> — 2007-05-18T12:23:07Z

    Dave Page 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.  There are fields contained
>> in the transaction meant to allow confirmation and prevent fraud, but I
>> just don't like that idea of running anonymously and committing
>> financial data.
>>
>> In this case it seems creating a stored procedure will not automatically
>> help, as then we just execute the SP anonymously, and it strikes me as
>> no different.
>>
>> Has anybody pondered this and come up with anything?
>>
>>     
>
> In response to the incoming IPN you can create a connection back to the
> paypal server to validate it. Iirc, you basically just send the entire
> request back again and it returns 'VERIFIED'.
>   

Ah yes, that's true, thanks for the wake-up on that one.

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

  8. Re: Paypal and "going root"

    Kenneth Downs <ken@secdat.com> — 2007-05-18T12:30:08Z

    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.

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.


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

  9. Re: Paypal and "going root"

    Richard Huxton <dev@archonet.com> — 2007-05-18T12:40:15Z

    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.

-- 
   Richard Huxton
   Archonet Ltd

  10. Re: Paypal and "going root"

    Kenneth Downs <ken@secdat.com> — 2007-05-18T12:48:38Z

    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