0

I am creating extension to postgres in C (c++). It is new data type that behave like text but it is encrypted by HSM device. But I have problem to use more then one key to protect data. My idea is to get original sql query and process it to choose what key I should use. But I don't know how to do that or if it is even possible?

My goal is to change some existing text fields in database to encrypted ones. And that's why I can't provide key number to my type in direct way. Type must be seen by external app as text.

Normally there is userID field and single query always use that id to get or set encrypted data. Base on that field I want to chose key. HSM can have billions of keys in itself and that's mean every user can have it's own key. It's not a problem if I need to parse string by myself, I am more then capable of doing that. Performance is not issue too, HSM is so slow that I can encode or decode only couple fields in one second.

Improve this question
4
  • Creating an extension in C or C++, which one is it? Commented Feb 4, 2014 at 13:49
  • I'm developing in C++ but postgres extension interface need to be in C and also postgres provides C functions to communicate with it. Commented Feb 5, 2014 at 0:14
  • @Logman Are you aware that Pg uses longjmp based error handling that doesn't work well with C++ exceptions? You'll need to use extern "C" interfaces at boundaries between Pg and the C++, and catch C++ exceptions and turn them into PostgreSQL errors (ereport) at each boundary. You also cannot call any PostgreSQL function that might call elog or ereport with ERROR severity or above from anywhere that needs stack unwinding. In practice that means writing a C++ lib that doesn't use libpq and exposes only C interfaces, then a wrapper PostgreSQL type for it. Commented Feb 5, 2014 at 10:57
  • Short answer is yes ;). Actually I wrote whole extension but for now I use only one key. Commented Feb 5, 2014 at 14:40

1 Answer 1

Reset to default
1

In most parts of the planner and executor the current (sub)query is available in a passed PlannerInfo struct, usually:

PlannerInfo *root

This has a parse member containing the Query object.

Earlier in the system, in the rewriter, it's passed as Query *root directly.

In both cases, if there's evaluation of a nested subquery going on, you get the subquery. There's no easy way to access the parent Query node.

The query tree isn't always available deeper in execution paths, such as in expression evaluation. You're not supposed to be referring to it there; expressions are self contained, and don't need to refer to the rest of the query.

So you're going to have a problem doing what you want. Frankly, that's because it's a pretty bad design from the sounds. What you should consider instead is:

  • Using a function to encode/decode the type to/from cleartext, allowing you to pass parameters; or possibly

  • Using the typmod of the type to store the desired information (but be aware that the typmod is not preserved across casts, subqueries, etc).

There's also the debug_query_string global, but really don't use that. It's unparsed query text so it won't help you anyway. If you (ab)use this in your code, I will cry. I'm only telling you it exists so I can tell you not to use it.

By far and away your best option is going to be to use a function-based interface for this.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I don't want to make you cry, but what you are saying my only option is to use debug_query_string. I can't use functions because I can't change queries to database. Could you tell me how to access debug_query_string from C interface?
@Logman Look at the headers. Be warned, though: debug_query_string isn't always set (think e.g. autovacuum); it may also not be set to the same query you're executing, particularly where nested queries and subqueries or pl/pgsql are involved. It's really not right to use it. Going down this path makes it likely your extension will crash randomly, encrypt things with the wrong key (unpredictably), etc. You might get away with it if you know all queries that will ever be run against the DB and nothing ever changes, but ... reality isn't like that, and if it is, it doesn't stay like that.
@Logman Perhaps if you explained the critiera for key selection I could offer more useful suggestions. And explained how you intend to automagically pick the right key based on the client query text. (edit the question?)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.