Обсуждение: How to have a blind-superuser
Is it possible to have a superuser who could do CHECKPOINT, BACKUP and whatever but could not see any user data? Regards, Qingqing
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? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
""Jim C. Nasby"" <jnasby@pervasive.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? Thanks, Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> 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?
AFAICS this is insoluble anywhere, not just in PG. CREATE LANGUAGE for
instance implies the ability to load arbitrary code into the backend.
If you don't trust your DBA, you could imagine encrypting all your data
on the client side before you store it, though this would for instance
severely limit your ability to search it. But there's no such thing as
a partial superuser: either you have unlimited access privileges or you
don't.
regards, tom lane
On Tue, Apr 25, 2006 at 09:31:48AM +0800, Qingqing Zhou wrote: > > ""Jim C. Nasby"" <jnasby@pervasive.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Apr 24, 2006, at 6:31 PM, Qingqing Zhou wrote: > > ""Jim C. Nasby"" <jnasby@pervasive.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
Qingqing Zhou wrote: >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? When I ran into this, it was a credit card field for order data and we needed to make sure our admin could not run off with the credit card list. We ended up AES encrypting the cc data before stuffing it into the database and then stored the last 4 digits in another field for display purposes. We could still do CC searches (since the same credit card number always has the same AES encryption). This made security much easier as we now just needed to limit access to the keys themselves and could issue developer keys for the developers running the local version of the application.
After a long battle with technology, "Qingqing Zhou" <zhouqq@cs.toronto.edu>, an earthling, wrote: > ""Jim C. Nasby"" <jnasby@pervasive.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? Would that also include vacuuming? For sure, this is *not* a user good for doing backups :-). -- "cbbrowne","@","gmail.com" http://linuxdatabases.info/info/slony.html "Generally in war the best policy is to take a state intact; to ruin it is inferior to this. To win one hundred victories in one hundred battles is not the acme of skill. To subdue the enemy without fighting is the acme of skill." -- Sun-Tzu, The Art of War
Steve Atkins wrote: > 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. Thinking out loud on this one, so feel free to shoot it full of holes folks. I'm also assuming a UNIX based system. I don't suggest this solution, but it might be closer to what you're looking for. Create a root permission id that has the permissions to backup the database. Set the id's shell in /etc/passwd so that it executes a script that performs the backup. Again, I don't recommend this, but it might be a workable solution. Now, you login to the machine and the script is executed. Personally, I still maintain that if you can't trust the person that is expected to backup your database, you should get rid of them. -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin
Tom Lane wrote: > If you don't trust your DBA, You should fire him/her... -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin