Re: [GENERAL] Drawbacks of using BYTEA for PK?

[D. Dante Lorenso]

they can try to look up information on other customers by doing:

   http://domain.com/application/load_record.html?customer_id=12346
   http://domain.com/application/load_record.html?customer_id=12344
...basically walking the sequence.  Sure, you will protect against this
       

to happen.  NOW, if you use a GUID:
       

Security != obscurity.

While using GUIDs may make it harder to get hacked, it in no way actually 
increases security.  Real security comes from secure code, period.
     

Well, uh, you're both wrong.
On the one hand if your GUIDs are just an MD5 of a sequence then they're
just as guessable as the sequence.
   

Its not a question of right or wrong.  Its the method.  One thing I see here is
a failing to use several security methods at different layers....why are you not using HTTPS (i.e. authentication)?
What about using a crytographic cookies to identify your session and link that
to you userid (after authorization)?  
 

Ok, my point is not one of security as much as the obscurity.  I have the
security aspect already covered whereby I only select the customer 
record from
the database where the logged in account has access to the record.  So, if
you are not the admin or the actual customer, the select will return a code
indicating that you do not have permission to view the given record.

Maybe a better example of my problem is with records throughout the system
like invoices, customer data, etc...  If any of these items use a sequence
and that sequence is global to the table in the database and the number is
exposed externally, then it is possible to infer the success of the company
underneath, is it not?
For instance, if I generate sequential numbers for invoice ids and the 
customer
sees #123 as an invoice number one month and sees #128 the next month, 
it might
imply that there are only 4 customers getting invoiced each month.

Another example ... let's say customers can create 'Widgets' in their 
account.
There might be a page that lists all their 'widgets'.  If you click on the
widget, you can edit it.  A link to do this might look as follows:

   http://.../account/widget_list.html
   http://.../account/widget_edit.html?widget_id=12345
Well, if the widget_id is a sequence (global to the widget table), then 
by creating
one widget, customer would get widget id (WIDG_1) and another widget 
(WIDG_2),
the customer could see that the widget_id increased by only an amount of
  
   N = WIDG_2 - WIDG_1

and would therefore provide the assumption that the number of customers 
creating
widgets in total does not exceed N.  I don't see this as much of a 
problem about
'security' in the respect of who can access the data as much as who can make
conclusions about the company beind the data. 

See what I mean?  What do you propose as the best solution for this?  
Not expose
the sequences to the user and use user-enumerated ids?  Then a trigger 
on the
table would assign ids like:

   SELECT (MAX(widget_id)+1) INTO NEW.widget_id
   WHERE cust_id = NEW.cust_id;
But I think after several hundred customer records, this trigger would start
getting slow.  I don't know really, any ideas?
Dante















---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match