I'm newbie in postgres and have a question:

we have some clients that have ultra-super-secure-sensitive data on postgres and they don't want to show it anyone. but sometime, the apps experience some degradation because of not quite fresh statistics or another reasons.

Question: How to maintain performance without actual business data access? i mean particular functionality:

  • how to run 'analyze',
  • 'explain analyze' on another user's table
  • query without superuser privileges.

I believe a restricted maintenance user may help here (I guess the only thing that may do this maintenance user is to do 'analyze' and 'explain analyze') but still have lake of knowledge how to achieve it.

May be you have another great ideas in your mind?

  • Who develops and maintains the apps? And backs up the data? – jjanes Nov 7 '19 at 16:41
  • If the developers are trusted to have access to production data, then have a dba on retainer that the developers can call and talk to without showing them the data. – jjanes Nov 7 '19 at 17:11
  • Usually confidentiality agreements between businesses cover normal use cases for develoepers, system admins, etc. for having access to data. If your developers do, it'd be strange to not let someone performing admin functions on the database to not, in my experience. – LowlyDBA Nov 7 '19 at 17:25
  • jjanes and LowlyDBA thank you. But unfortunately developers are not trusted to access data too. For example let it be Postgres RDS, so client have agreement with Amazon, and some traditional DBA duties rely on this DB provider. The thing is - how to have some special support abilities (such as EXPLAIN, described in the main message body) with restricted data access. – Mikhail Aksenov Nov 7 '19 at 18:40
  • 1
    Who do you blame when the developers write crappy code that causes issues on the database? There are many problems on a database that are caused by poor design by the developers instead of the people doing general database manintance. – Joe W Nov 7 '19 at 19:07

The SECURITY DEFINER functions you reference are one way of doing this. But they don't really seem practical to me. Especially with the EXPLAIN ANALYZE (or better, EXPLAIN (ANALYZE, BUFFERS), as I can easily use that to leak information one byte at a time. For example,

EXPLAIN ANALYZE SELECT COUNT(*) from secret where secret_val like 'ABC%';
EXPLAIN ANALYZE SELECT COUNT(*) from secret where secret_val like 'ABD%';
EXPLAIN ANALYZE SELECT COUNT(*) from secret where secret_val like 'ABE%';

So you would need logging, and maybe throttling, written into the SECURITY DEFINER function, and then probably still have someone standing behind me looking over my shoulder. (Either that, or trust the combination of my integrity and your lawyer). And if you do have someone looking over my shoulder, couldn't you just have me looking over their shoulder giving advice instead and forget all the rest of this stuff?

And how would I obtain the queries to know which ones needs to be optimized in the first place? Is someone else going to identify them and hand them to me? Wouldn't that person then need a high level of access?

  • Thank you! Application actually logs query wihout bind and data it may retrieve or manipulate, so that's how problematic queries and objects may be defined. – Mikhail Aksenov Nov 7 '19 at 18:30
  • Query logs without bind data is sometimes enough, but sometimes the problem only shows up with specific values in the query, and I need to know those values to do the work. So it might take multiple iterations between the parties to get anywhere. At the highest levels of compartmentalization, it is very slow (and so expensive) work. – jjanes Nov 7 '19 at 18:37

Trying to hide data from someone who is going to tune a database for performance can be impossible depending on where the performance issues are at. For starters, some of the more useful data that you will need for tuning will contain some of the sensitive data you are trying to hide. If you are trying to tune a query that is searching for sensitive data in the database you may need to know the sensitive data that is being used in the query to determine the cause.

To sum it up if you have a database that you have data you want to restrict access to due to senstive data issues you should work to train the people who do have access or are writing the code that access it to handle those issues so that you are not dependent on people who shouldn't see the data.

  • thank you for othere ideas! Was thinking about developing custom tool that could provide such funtionality too. Other nice topics to explore. – Mikhail Aksenov Nov 7 '19 at 21:18
  • You will have to compromise. Whoever is tasked with finding and tuning problematic statements will have to see sensitive data. Unless the users with security clearance know enough about PostgreSQL to create and correctly obfuscate an execution plan. – Laurenz Albe Nov 8 '19 at 7:14

I believe you need to start looking for a more creative solutions and handling the situation out of the database side. Implementing encryption on the database level is a challenge even you end up implementing encryption like pg_crpto. Even so that, you need to have a pretty good level of access to the Database level.

  1. You can build a simple application portal(a simple HTML page) where defines the operations you want to do. For example, running a vacuum for a table or any other operations.
  2. Provide the operations you would like to do, let's say for section vacuum "table name"
  3. HTML will fire queries on the database without user actually accessing the database.
  • That's cool, thank you, looks like pretty simple and nice feature :) And still wandering how pgcrypto may affect performance not in a better way. – Mikhail Aksenov Nov 7 '19 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.