I set up a localhost pgsql installation (os x catalina, homebrew, 12.*). I am developing a sql data warehouse and I’d like my dev environment to be as close to the production one as possible. Homebrew created a default superuser, chris. Is it ok (and possible) to revoke the login attribute from this user? I have set up dedicated users to interact with the data warehouse and the actual data in the system.



You will need a superuser that can login for administrative purposes. So don't change the existing superuser to NOLOGIN unless you create another superuser first (but tgen you could just as well rename the existing superuser). The secure thing to do is

  • use a good password for the user

  • severely restrict access for that user in pg_hba.conf, ideally to local connections

There is one scenario where you could change the only existing superuser to NOLOGIN: create a regular user admin with NOINHERIT and

GRANT chris TO admin;

Then admin can always assume superuser privileges with

SET ROLE chris;

This setup has the advantage that you don't need a shared password for the superuser. Each administrator can have her own normal user that can assume superuser privileges when necessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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