Thread

1. User privileges-verification required

   Nauman Naeem <nauman.naeem@gmail.com> — 2006-02-24T13:25:13Z

   Hello all,
   
   While playing around with database users, I noticed one odd thing
   regarding user privileges.
   
   In case I have only one user in my database who is also the DBSystem
   (who carries out initdb), user then if that user takes away his
   "createuser" privileges, he is unable to grant himself that privilege
   again or grant that privilege to any other user that he creates.This
   creates quite a fix since you end up with no superusers, nor a way to
   create any other superusers.
   
   In my opinion we should cater for such a situation, and two possible
   solutions come to my mind for this:
   
   1. Place a restriction that there should be more than one superuser
   before you can issue a "NOCREATEUSER" command.
   
   2 Only the DBsystemuser should be allowed to grant and revoke the
   "createuser" privileges.
   
   Let me know what you all think.
   
   Thanks,
   Nauman
   
   

2. Re: User privileges-verification required

   Peter Eisentraut <peter_e@gmx.net> — 2006-02-24T13:42:55Z

   Am Freitag, 24. Februar 2006 14:25 schrieb Nauman Naeem:
   > In case I have only one user in my database who is also the DBSystem
   > (who carries out initdb), user then if that user takes away his
   > "createuser" privileges, he is unable to grant himself that privilege
   > again or grant that privilege to any other user that he creates.This
   > creates quite a fix since you end up with no superusers, nor a way to
   > create any other superusers.
   
   Shut down the server, start in single-user mode, then you can fix it.
   
   -- 
   Peter Eisentraut
   http://developer.postgresql.org/~petere/
   
   

3. Re: User privileges-verification required

   Nauman Naeem <nauman.naeem@gmail.com> — 2006-02-24T14:34:11Z

   I tried the single-user mode option and it worked, thanks! but, don't
   you people think that we should provide this privilege in multi-user
   mode as well.In accordence to my second point.
   
   On 2/24/06, Peter Eisentraut <peter_e@gmx.net> wrote:
   > Am Freitag, 24. Februar 2006 14:25 schrieb Nauman Naeem:
   > > In case I have only one user in my database who is also the DBSystem
   > > (who carries out initdb), user then if that user takes away his
   > > "createuser" privileges, he is unable to grant himself that privilege
   > > again or grant that privilege to any other user that he creates.This
   > > creates quite a fix since you end up with no superusers, nor a way to
   > > create any other superusers.
   >
   > Shut down the server, start in single-user mode, then you can fix it.
   >
   > --
   > Peter Eisentraut
   > http://developer.postgresql.org/~petere/
   >
   
   

4. Re: User privileges-verification required

   Tom Lane <tgl@sss.pgh.pa.us> — 2006-02-24T15:47:25Z

   "Nauman Naeem" <nauman.naeem@gmail.com> writes:
   > I tried the single-user mode option and it worked, thanks! but, don't
   > you people think that we should provide this privilege in multi-user
   > mode as well.In accordence to my second point.
   
   No.  Restricting what a superuser can do is pointless --- he can always
   manage to shoot himself in the foot if he tries hard enough.  (Consider
   eg "DELETE FROM pg_authid".)  Trying to fix it in the reverse direction
   (re-establishing superuser after the last one's been deleted) has
   obvious security issues.
   
   The problem comes up sufficiently seldom that the single-user-mode
   backdoor seems sufficient.
   
   			regards, tom lane
   
   

5. Re: User privileges-verification required

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-26T05:04:41Z

   > In my opinion we should cater for such a situation, and two possible
   > solutions come to my mind for this:
   
   I've done exactly this before, and had to use single user mode to 
   recover.  Annoying.
   
   > 1. Place a restriction that there should be more than one superuser
   > before you can issue a "NOCREATEUSER" command.
   
   I agree :)
   
   Chris
   
   
   

6. Re: User privileges-verification required

   Nauman Naeem <nauman.naeem@gmail.com> — 2006-02-27T16:09:50Z

   Yeah it really was :-) BTW I have implemented this for multiuser-mode as
   well......Both the approaches as mentioned in my very first email...
   
   1- The minimum super user count should not be less then 1.
   2- Only the main database system owner is eligible to reassign.
   
   My personal believe is, this minimum functionality should be a part of
   multiuser mode! I was oblivious of this and did initdb twice,  may be I am
   dumb :-)
   
   Thanks,
   Nauman
   
   On 2/26/06, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
   >
   > > In my opinion we should cater for such a situation, and two possible
   > > solutions come to my mind for this:
   >
   > I've done exactly this before, and had to use single user mode to
   > recover.  Annoying.
   >
   > > 1. Place a restriction that there should be more than one superuser
   > > before you can issue a "NOCREATEUSER" command.
   >
   > I agree :)
   >
   > Chris
   >
   >