Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How does one go about disabling a super user account within postgres without deleting it. I looked at the Alter Role documentation and I am drawing blanks. In addition this is the only super user account and the subordinate accounts own the tables that they are responsible for

share|improve this question

migrated from stackoverflow.com Jun 8 '11 at 13:09

This question came from our site for professional and enthusiast programmers.

    
Why would you want to do that? The use postgres can't be disabled. –  jmz Aug 2 '10 at 17:44
    
@jmz its necessary for a security requirement –  Woot4Moo Aug 2 '10 at 17:49
    
I don't see how that increases security. You might succeed in using a different name though, perhaps the name you use to connect to the DB. Drop your cluster, deluser postgres, create the user you want as a Unix user, initdb with that user. The user is now ID=1 and is the only superuser. It works just like the postgres user was. So you're at square one. The point is that you need the automatically generated user and the postgres account (or some other). You could just as well use the postgres account to connect. –  jmz Aug 2 '10 at 18:10
    
@jmz please tell me there is some white paper explaining this as well so that I may present it. –  Woot4Moo Aug 2 '10 at 18:20
1  
Woot4Moo: Read postgresql.org/docs/8.4/interactive/creating-cluster.html for security hints. –  jmz Aug 2 '10 at 18:38
show 4 more comments

3 Answers

up vote 3 down vote accepted

You could configure pg_hba.conf to reject the super user so it can't log in.

share|improve this answer
    
Indeed. Disabling login would effectively disable the account. Also seems to be the pattern Linux and some Windows installations follow. –  aib Aug 3 '10 at 23:20
add comment

I assume that you have created a new user with super user privileges and you don't want to disable the postgres account, right?

To disable an account try revoke:

REVOKE ALL PRIVILEGES
ON DATABASE mydb
FROM mysuperuser;

I might have missed something in the snippet above, check out the docs here: http://www.postgresql.org/docs/8.4/static/sql-revoke.html

To remove the user, become super user yourself, ie postgres. Then use DROP ROLE:

DROP ROLE mysuperuser;

http://www.postgresql.org/docs/8.4/interactive/sql-droprole.html

share|improve this answer
add comment

There is always a superuser, you can't maintain your database without this role.

share|improve this answer
    
Any documents to validate this? –  Woot4Moo Aug 3 '10 at 1:06
    
Just check the manual, many statements need a superuser (CREATE LANGUAGE is one of them) and you need a superuser to create a superuser role. Without a superuser, you're on a dead end street. –  Frank Heikens Aug 3 '10 at 6:01
    
Thanks. So logically my argument is going to be that a non super user does not have the necessary rights to grant privileges back to my super user in the case of maintenance –  Woot4Moo Aug 3 '10 at 12:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.