permission denied for large object during dump action about greenmask HOT 10 CLOSED

Give me a few minutes I will think about workaround

from greenmask.

looking into this more, I suspect it's still on my side. I'll close this issue for now. Thanks again for the lightning quick support!

from greenmask.

Hi! That kind of interesting thing. Is the user from whom you're running a greenmask a super user of postgresql instance?

from greenmask.

If the user you're running a backup on is not the owner of a large object, is not a super user, and doesn't have grants on this object the error on opening a large object is expected. You can check it by running a query

begin; SELECT lo_open(loid, 75897); rollback;

from greenmask.

I just verified, it's not the super user. Is that a hard requirement? I'm using the simple dump as a test to eventually work my way up to obfuscate an entire database.

from greenmask.

There is one problem that I don't know how to resolve it correctly. On the one hand, Large Objects are independent objects in PostgreSQL, on another hand, you can reference these objects in the table record in any way. It rather depends on the logic you have. That's why the pg_dump utility originally does not have an option for skipping large objects dumps. And that's why we didn't decide to introduce this option in greenmask. But, since we are working with database obfuscation we might want to get rid of large objects or leave them empty without data. And it looks worthy to implement.

The simplest way to overcome this issue is to introduce two options in greenmask

• --no-large-objects - do not dump large objects at all. But if you have references in tables on those objects you will receive the error during restoration. You will be forced to create empty large objects or set NULL values on references
• --large-object - inclusive list of large objects we want to dump. Other large objects will be skipped

What do you think about those options? Are they worthy of implementation?

But for now, without a patch with the options above, or without having the proper grants on the objects, I suspect you can't make a backup in that case.

from greenmask.

Thanks for the quick and detailed response, I really appreciate it!
I think the solution for my problem will have to be on my end, i.e get superuser access. I'm not super familiar with pg_dump or even large objects, but I can imagine a --no-large-objects could be useful for other use cases.

from greenmask.

Thank you! I will raise a feature request for this option tomorrow.

from greenmask.

I found something else that is interesting: I tried creating a dump with the same config for multiple different tables, but I always get the same error. I checked and saw that these tables do not have any BLOB types. Could this be an error? Or could I be missing something in my setup?

from greenmask.

The large object dumper is independent from table dumper, whether table has references on LO or not. The problem is that even if you listed inclusively required tables the large object dumper will run anyway. That's why the option we discussed might be helpful

from greenmask.