Re: How to have a blind-superuser

On Tue, Apr 25, 2006 at 09:31:48AM +0800, 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?

No, because a superuser can do anything they want. Say they take a
backup; what's to prevent them from just reading the data in that
backup?

One thing you could try doing is creating functions for every
administrative function you need and set them as being
'security-definer'. That would allow you to not grant superuser access
to 'administrators'.

Keep in mind that anyone who has root on the server can also bypass all
security.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461