Re: dunction issue - Mailing list pgsql-general

On Fri, Mar 28, 2008 at 06:43:00PM +0900, Craig Ringer wrote:
> Alain Roger wrote:
> > I do not agree with you Sam.
> >
> > Stored procedure are safe from hacking (from external access).
>
> In that a stored procedure encapsulates a series of data operations,
> meaning that the client doesn't have to know the details or even have
> privileges to run the individual operations ? Yes, that can be really
> useful, but it's hardly the full story.

Indeed.  And in my experience, it's the program's own developers you've
got to be most cautious about.  "Hackers" would have very little trouble
breaking most software these days---almost everything is far too big
and complicated, ignoring rules like keeping it simple, respecting the
principle of least authority and other time tested rules.  Attackers
also tend to go around the barriers you put in their way, not through
them, the most general attack would be the physical one, i.e. paying a
cleaner to remove something important.  Another way of looking at it is
to witness the types of bugs being fixed in software, almost all of them
have no security implications and are straight human fallibility.

> Proper use of things like foreign keys, unique constraints, CHECK
> constraints, etc adds another level of protection. I'd use those tools
> before I restored to using a stored procedure. Like stored procedures,
> users with appropriately limited priveleges are unable to bypass, drop,
> or modify constraints.

Indeed, use the simplest possible tool to get the job done.  And if
possible reuse an existing one (i.e. all the work that has gone into
getting the constraint handling working correctly in all the known
cases).

> -- Really basic valiation of email addresses. It's not worth doing much
> -- more than this sort of thing IMO because of performance issues and
> -- transcient errors (MX lookup fail etc) when doing proper email
> -- validation. At least now you don't have to revalidate in every
> -- procedure.
> ALTER TABLE tmp_newsletterreg ADD CONSTRAINT simplistic_email_check
> CHECK lower(trim(both ' ' from email)) LIKE '%_@_%';

Just out of interest, what's the lower() function call doing?

I'd almost be tempted to do something like:

  CREATE DOMAIN emailaddr AS text
    CHECK (VALUE ~ '^[^ ]+@[^ ]+$');

and then use this instead of text/varchar types.

> ... or preferably throw informative exceptions.

This would be my preference.  It'll probably do the "right" thing if the
code is called from other stored procedures then.

> However, I do find it
> frustrating that I can't attach a value or list of values to a
> PostgreSQL exception in a way that is easy for the client app to extract
> - I have to resort to text parsing (mega-ugly and unsafe) if I need to
> do it.

Yup, why is this so often ignored when people write database drivers.  I
used the "pyPgSQL" python module (I think) for a bit, before realising
that it even went as far as to "helpfully" automatically start a new
transaction when the last one aborted.  The resulting semantics meant my
code did the most horrible things.


And I'd agree with the remainder of your comments!


  Sam