Postgres disable super user account

Question

Hi all, 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

Answer 1

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

Answer 2

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

Answer 3

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