Re: [GENERAL] PG in cash till machines
Hi Carlos,
On 5/10/2013 6:43 AM, Carlos Henrique Reimer wrote:
We are developing a solution which will run in thousands of small cash till
machines running Linux and we would like to use PostgreSQL but there is a
insecurity feeling regarding the solution basically because these boxes
would be exposed to an insecure environment and insecure procedures like:
non controlled power outages, untrusted hardware, no appropriate
environment for hardware (dusty, hot) etc...
Welcome to my world! :>
To clarify your requirements:
- the devices contain hard currency which they either *accept* from
the user, dispense *to* the user, or both.
- the contents of the DB are critical to the proper operation
of the device (e.g., perhaps they maintain "account balances"
that, if corruptible, represent an exploit)
- presumably, the environment is secure enough that an adversary
can't just remove the entire device to abscound with the monies therein
(i.e., while it may not be "as secure as a bank lobby", neither is it
sitting in the middle of an open field! Perhaps sitting *in* a
storefront establishment?)
- the device has some physical safeguards to prevent an adversary
vandalizing it GIVEN THE LEVEL OF SUPERVISION IN ITS ENVIRONMENT
(e.g., the coinbox of a US "pay phone" is pretty hard to break into)
- yet, a subtle, clever hacker could "tease" the device into a
state that is more "generous" to his wishes (e.g., a high voltage
discharge at a key point along the case/wiring to crash the processor
at a critical point in it's transaction processing)
- a single device may not be a significant "value" target -- but,
an exploit that can be reapplied to *each* target (possibly
repeatedly, over time) makes this a sufficiently large honey pot
- you're very confident in the robustness of the underlying
OS, application and PostgreSQL itself that you don't fear problems
from any of those in "normal operation" (e.g., the kernel isn't
likely to panic due to some unforseen out-of-memory condition -- nor
the processor hiccup from operating in an overtemperature condition)
Our major concern is related to the write-back issues we can face in this
environment. Is there a way to prevent it or a way PG can be configured to
detect write-back configurations? Don't think so, but...
I'm not sure what specific threat you are anticipating, here.
Could you posit a potential attack scenario against which you
would like to defend?
Basically, the question is: is there a way to guaranty a reliable PG
database in an untrusted and insecure environment? Maybe some kind of file
system could give this guaranty, not sure..
Absent *physical* security, you can't do *anything*! (especially
given your reliance on FOSS components!). E.g., an adversary can
disembowel the device and massage the contents of the raw media,
etc.)
Having designed (regulated) gaming and "grey area" devices (each
handling hard currency), I can tell you that you have to have already
performed a pretty exhaustive threat analysis (e.g., red team, blue\
team) *before* you start the product's design. If you can't imagine
*all* of the ways you can be targeted, then you can't determine
how/if you will be "secure" in each of those scenarios (e.g.,
I've incorporated features into the hardware designs to counter
certain types of physical attacks).
Always consider how you can design the system to *screw* the
attacker instead of *reward* him, given the option. E.g.,
given the (fictitious) example of allowing the user to
"withdraw" funds from his "account" (possibly), then opt
for:
decrease_balance(amount)
dispense_currency(amount)
instead of:
dispense_currency(amount)
decrease_balance(amount)
as the latter case could allow an attacker to interrupt the
atomic operation *after* receiving his "hard currency" yet
before a record of this has been made. In the former case,
an attack results in his account *losing* money without ever
receiving the hard currency!
Attackers are shy about complaining that "the machine cheated
me as that brings attention to their activities! ("Oh? Let's
review the video surveillance to see what happened..." :>)
Of course, the above *should* be atomic but you can't roll-back
a *physical* transaction (i.e., reach out and grab the currency
*from* him if the transaction is aborted).
Perhaps if you can highlight specific actions that you imagine
as being vulnerabilities, a compromise solution might be
available?
[BTW, I find these the most delightful projects as you *really*
have to think outside the box -- unlike a desktop/server environment
where all you have to worry about is a disk crash, DoS attack, etc.]
G'luck!
--don
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG in cash till machines
Hi John, On 5/10/2013 2:55 PM, John R Pierce wrote: On 5/10/2013 2:11 PM, Bexley Hall wrote: Having designed (regulated) gaming and "grey area" devices (each handling hard currency), I can tell you that you have to have already performed a pretty exhaustive threat analysis (e.g., red team, blue\ team) *before* you start the product's design. If you can't imagine *all* of the ways you can be targeted, then you can't determine how/if you will be "secure" in each of those scenarios (e.g., I've incorporated features into the hardware designs to counter certain types of physical attacks). indeed, and there's always threat models that no one could foresee, witness the recent story of coordinated ATM withdrawals of $45,000,000 enabled by some back door hacking of the bank databases. All (?) software and security flaws can be traced to "improper assumptions". Someone, somewhere, involved in the design of the "system" (which includes more than just hardware and software) made some BASIC assumption that was flawed. I.e., assumed "/* CAN'T HAPPEN */" actually *couldn't* happen! The trick to designing robust software (and robust systems) is to identify those assumptions and then seriously question whether they *really* are valid -- or, just valid in your particular outlook on Life, The Universe and Everything. - This instruction got executed so the next one will, as well. - The only way for this instruction to be executed is if the preceeding one is, also. - There can't be two credit cards with the same account number. - People can't be in geographically different locations at the same time (so they can't possibly make withdrawals on the same account from those different locations, concurrently) - Social security numbers can't begin with '0'. - System power won't fail (or be intentionally interrupted) before I get a chance to do X... (e.g., flush buffers to permanent storage) - All first names are less than BUFLEN characters. - When turning *right*, I can't hit anything on the *left*. - No one will unplug (or cut!) this cable. - Users will choose "good" passwords. - malloc() will never FAIL. - This contact will close each time a coin is dispensed. - Coins can't pass this sensor faster than once every N seconds. etc. Conversely, the way to find faults in those systems/software is to "do the unexpected". Developers tend to be *so* focused on "just trying to get it to work" that they often don't attend to "getting it to work *well*". Carlos has to figure out which of these assumptions he's made that aren't *guaranteed* (by some agency/mechanism) to be true and figure out how to *make* them true (or, detect when they are not). --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query caching absent "query caching"
Hi, In the absence of query caching AND NOT WANTING TO FORCE THE APPLICATION TO DO SO EXPLICITLY, I'm looking for ideas as to how I should "future-safe" the design of some custom user base types and functions thereon. Specifically, I have several computationally expensive functions that derive their results from specific values of these base types. *Solely*. (For example, area() when applied to a given "circle" always yields the same result... though this is a trivial/inexpensive function, by comparison). I can define the base types to set aside space to store these results and cache them *in* the base type. Then, serve up these cached results when they are needed, again. With plan caching, this should (?) reduce the cost of repeated queries significantly without the need/benefit for caching the actual query results. (Is that true?) To guard against future enhancements to the server (e.g., if query caching is ever implemented, etc.), I assume that all such functions should declare themselves as IMMUTABLE? Or, does my update of the internal representation of the data values (i.e., to include the cached results of each of these functions) conflict with this declaration? [I am working in a fixed, constrained resource environment so the idea of explicitly building a temporary table to hold these results in the absence of a smart query caching strategy is not possible -- the builder(s) of such tables would have to know too much about each other to not exhaust the resources available!] Thanks! --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
Hi Pavel, On 11/24/2012 9:47 PM, Pavel Stehule wrote: Hello you can try use plperl as cache http://okbob.blogspot.cz/2007/12/using-shared-as-table-cache-in-plperl.html But how is this any different than just creating a named/shared table manually? And, how do further/additional accesses (by other clients or the same client) *augment* the shared table? In terms of my "application": - Assume client A does a query that evaluates expensive_function() for rows 1, 5 and 93 - Client B does a query that evaluates expensive_function() for rows 3, 5 and 97 - Client C does a query that evaluates expensive_function() for rows 93, 95 and 97 (no one alters any of the data on which expensive_function() relies in this time interval) Then, A should bear the cost of computing the results for 1, 5 and 93. B should bear the cost of computing 3 and 97 -- but should be able to benefit from A's computation of 5. C should bear the cost of computing 95 but benefit from the previous computations of 93 and 97. Thx, --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
Hi Kevin, On 11/25/2012 8:10 AM, Kevin Grittner wrote: Bexley Hall wrote: Specifically, I have several computationally expensive functions that derive their results from specific values of these base types. *Solely*. (For example, area() when applied to a given "circle" always yields the same result... though this is a trivial/inexpensive function, by comparison). I can define the base types to set aside space to store these results and cache them *in* the base type. Then, serve up these cached results when they are needed, again. With plan caching, this should (?) reduce the cost of repeated queries significantly without the need/benefit for caching the actual query results. (Is that true?) To guard against future enhancements to the server (e.g., if query caching is ever implemented, etc.), I assume that all such functions should declare themselves as IMMUTABLE? Or, does my update of the internal representation of the data values (i.e., to include the cached results of each of these functions) conflict with this declaration? As long as a call to a given function with a specific set of arguments always returns the same result, and there are no *user visible* side effects of the internal caching, I don't see a problem with declaring the functions immutable. OK. Out of curiosity, are you planning on using a process-local cache (which would start empty for each new connection) or are you planning to allocate shared memory somehow and coordinate access to that? I was planning on writing back the results of each successful function evaluation into the data type's internal representation. Ideally, back into PostgreSQL's "master copy" of the data (though I would settle for hiding it in an anonymous table behind a view, etc.) The point is NEVER to have to RE-evaluate any of these functions for the data on which they are evaluated once they have been evaluated (assuming the data themselves do not change). And, in doing so, make the results of each evaluation available to other clients regardless of the query which caused them to be evaluated. Thx, --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New Zealand Postgis DBA job vacancy
Hi Gavin, [apologies if msg reference chain mangled -- forgot to Cc list <:-( ] On 12/26/2012 12:45 PM, Gavin Flower wrote: I have a close friend who is working on embedded software for a green house controller. He would love to use PostgreSQL, but they started using SQLite and pg does not appear to be suitable for embedded systems. I'll second that! Though, to be fair, there is a wide range of applications that "embedded" can address. The resources that are available to one may be (several!) orders of magnitude different than others. I am currently using pgsql in an embedded system though suspect I may have to port everything over to another engine for deployment. There are too many assumptions made about the underlying hardware available for pgsql that are at odds with the goals of most embedded systems. (e.g., I need to be able to have the DB reside in "immutable" memory -- and the "writable" memory requirements carefully constrained) So, "cleverer" solutions end up prevailing. A pity as a single embedded system deployment probably puts more "seats" in play (e.g., easily 10,000 - 1,000,000 per design) than *years* of "natural growth" would, otherwise. --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New Zealand Postgis DBA job vacancy
Hi Martin,
[apologies if msg reference chain mangled -- forgot to Cc list <:-( ]
On 12/26/2012 2:04 PM, Martin Gainty wrote:
most of us in the US choose to be *delightfully ignorant* of
anything that doesnt fit into our 30 seconds (or less) lifestyle
"30 seconds"? Seems an *eternity* for most of the folks I meet!
(i.e., if you haven't answered your cell phone by the *second*
ring, the followup text message -- "Where are you?" -- is *delivered*
in that time frame!)
Sheesh! How can people live like this?? Can you spell "short leash"?
metric system requires rudimentary math skills instead of
entertainment so we collectively choose not to think of such things
Thinking (entirely) *in* metric doesn't. The problem is working
with *both*, simultaneously, requires some mental agility.
Nearby, we have one of the few (only?) stretches of roadway that
is marked in metric units (actually, I haven't driven it in a while
and vaguely recall something about RE-marking it in "conventional"
units). To most folks, it is a disturbing experience as they aren't
accustomed to thinking in these. ("No, that's not 100MPH but
100kmph... big difference!")
OTOH, working entirely within the "english" system is significantly
taxing if you want to deal with more than one unit of measure at
a time. Feet/miles/yards/inches. Gills/gallons/pints. etc.
I'm tickled that I've got a 1/3C measuring cup and a 1/4T (not 't')
measuring spoon (I bake a lot). Otherwise, it's a real chore
hitting some of these "odd" measurements!
[The idea of *weighing* ingredients is anathema to me -- and I
can't imagine it would be as expedient as using "fixed volumes"!]
I spent 3 months outside the US this year and was *forced* to use
my brain to convert petrol containers from liters to US gallons
It was nice to be able to come back to the US to put your brain
*in park* and let the big brains in the statehouses and Washington
tell us -what to do and -provide us the funding to do their bidding
at least until 1 Jan 2013!
so...why doesn't Postgres port to embedded systems?
IME, it requires lots of resources (the vast majority of embedded
systems are resource starved -- resources == $$ and when you are
selling things in volume, every penny saved adds up quickly!).
Lots of MIPS, lots of RAM -- even the code footprint is "significant".
OTOH, (again, IME) designing with the "relational table" construct
makes coding a very different experience! Already being biased
in favor of table-driven algorithms, I took this opportunity to
move all the "const" tables out of my executables and into the
DBMS (which takes a performance hit but keeps the code much more
mutable). I've gone so far as to hide the filesystem from the
applications -- objects that would have typically resided in
ad hoc files are now stored in structured tables (eliminates
the need to write lots of special parsers to be able to impose
structure on what would otherwise be unstructured "bytes")
[This last issue owes nothing to pgsql, though, as any RDBMS
can provide that structure/capability]
We'll see... it's been a learning experience (make
one to throw away)
--don
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New Zealand Postgis DBA job vacancy
Hi Martin,
On 12/27/2012 8:31 AM, Martin Gainty wrote:
so...why doesn't Postgres port to embedded systems?
IME, it requires lots of resources (the vast majority of embedded
systems are resource starved -- resources == $$ and when you are
selling things in volume, every penny saved adds up quickly!).
Lots of MIPS, lots of RAM -- even the code footprint is "significant".
OTOH, (again, IME) designing with the "relational table" construct
makes coding a very different experience! Already being biased
in favor of table-driven algorithms, I took this opportunity to
move all the "const" tables out of my executables and into the
DBMS (which takes a performance hit but keeps the code much more
mutable). I've gone so far as to hide the filesystem from the
applications -- objects that would have typically resided in
ad hoc files are now stored in structured tables (eliminates
the need to write lots of special parsers to be able to impose
structure on what would otherwise be unstructured "bytes")
so do you reduce CPU or IO when you take a java variable final int
foo=1; and insert foo as a const column in a table?
[For the most part, I don't use Java as most of my projects have
real-time constraints and the lack of determinism in Java blows
that out of the water]
My (original) comment above is meant to address taking tables
of constants out of code and moving them into the DBMS -- *fetching*
them at run-time and using them in their normal role AS IF they
had been hard-coded into the executable.
E.g., given a Julian day, most folks would convert it to a (month,day)
tuple using a table like:
days[] = {
31, /* Jan */
28, /* Feb */
31, /* Mar */
30, /* Apr */
...
31/* Dec */
}
Instead of putting this table in the code as a const datum, I would
store it in the DBMS and retrieve it as/when needed for the conversion.
[This is a silly example but one that is easy to understand]
Among other things, it allows me to change the contents of the
table without having to release a new executable
[Again, this example is silly in this regard as January will *always*
have 31 days so there will never be a need to change that! OTOH, if
the table is used to determine how far to advance the timing of the
spark in an internal combustion engine with respect to engine RPM's,
then you *may* want to change/tweek that table at some time in the
future to obtain better performance or accommodate changes in the
manufacture of the engine -- without changing all the code that
*references* that table. If that table is embedded in the actual
executable, this isn't very easy.]
As far as my parsing comment is concerned...
The structure that a table can impart to the data that it represents
allows you to identify that data *once*, stuff it into the appropriate
fields and then never have to parse the raw data object again.
E.g., early email readers maintained the user's "mailbox" as a simple
ASCII text file. New messages were appended to the end of the file.
When it came time to enumerate the messages or find a particular
message, the program had to parse the ASCII text sequentially knowing
the form that a message took so that it could identify the start
(and end) of each individual message.
If you were handed that ASCII file and asked "How many messages does
this contain", you would have to parse the file sequentially, knowing
the format of mail messages (in detail!) and count them.
If, instead, you have a table called "mail" with fields called
"From", "To", "Date", "Body", etc. then it's easy to determine
how many messages you have (count) or locate the most recent message
from "Bob", etc. The application can concentrate on the service(s)
that it wants to provide instead of having to be encumbered with
the trivialities of parsing message formats.
[Parsers are rife with opportunity for coding errors -- especially
those coded ad hoc!]
--don
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
