Re: How to have a blind-superuser

On Apr 24, 2006, at 6:31 PM, Qingqing Zhou wrote:

>
> ""Jim C. Nasby"" <jnasby(at)pervasive(dot)com> wrote
>> On Mon, Apr 24, 2006 at 06:16:30PM +0800, Qingqing Zhou wrote:
>>> Is it possible to have a superuser who could do CHECKPOINT,
>>> BACKUP and
>>> whatever but could not see any user data?
>>
>> Not for backup. It'd be rather tricky to allow backing up data
>> without
>> being able to read it, afterall.
>>
>> I believe CHECKPOINT is protected since repeatedly calling it could
>> result in performance problems, but you can probably get around
>> that if
>> needed by using a security-definer function.
>>
>> Why do you want non-superusers to be able to checkpoint, anyway?
>>
>
> Basically I wonder if I can have a superuer that he has every
> priviliges as
> he does now (create language, rotate log files, create checkpoint and
> everything superuser can do) but one thing I want to make sure is
> that he
> could not see any user data for security reason (just think my
> database is
> filled with very important UFO data ;-)). In another word, I need a
> superuser be able to maintain database but he know nothing about
> what in the
> database. Is there a solution for this in PG?

To be able to backup the database the user needs to be able to
write it to a file. They can then read that file, and so read anything
in the database.

So... you're not going to be able to do this _at_all_ from within
the database. You're going to need an external solution, probably
a hideous seteuid thing, if you really want to do this. And it's
a really bad idea, so you probably don't want to.

Cheers,
Steve