Postgresql 9.6, if it matters.
We have an audit finding which says that the "postgres" role must not be used at all.
After creating a new SUPERUSER role, is it possible to disable role "postgres" (or do some jobs like autovacuum/autoanalyze require it)? If they are the only ones which need it, I could disable autovacuum/autoanalyze and rely on manual vacuums and analyze.
You can specify the name of the database superuser at the time of running initdb.
It will default to the name of the user running the initdb command itself, but specifying --username or -U will change that.
https://www.postgresql.org/docs/9.6/app-initdb.html
Let me highly suggest that you don't even for a second consider trying to take away any privileges from the database superuser role, regardless of its name. Many privilege checks within the database are coded in such a way that the superuser is always allowed to continue, no questions asked. It is, after all, the superuser role.
I can only assume that your auditors are either clueless or were referring to some use of the postgres role by applications or by developers. In the latter cases, it is definitely advisable to restrict access and create appropriate roles/users within the database for these tasks.
This is my recommendation: make sure that user postgres cannot login from anywhere (besides locally via unix sockets and localhost, and possibly via network for streaming replicas) via pg_hba (and use at least peer and md5 authentication).
Changing the superuser's name does in no way increase security.
We have an audit finding which says that the "postgres" role must not be used at all.
This statement could be viewed in two ways and I'll address both:
- The PostgreSQL cluster must be installed with no reference to an account/role called 'postgres'.
This would be utter nonsense, akin to trying to run a Linux machine without a root account or renaming the "root" account to some other name, like "fred". It relies on Security through Obscurity (another Bad Idea), adds nothing of value and confuses everyone when things they normally expect to find are not there or are called something "weird".
Save your sanity, go back to the Auditors and challenge this "finding", asking them for both justification for it and recommendations on how to make it work.
- The postgres role must not be used for anything other than the internal operation of the PostgreSQL cluster.
By this, I meant that no individual ever logs on directly with this role (and password) or has it granted to their own account and no Application ever makes use of it. DBAs, of course, connect implicitly as the installation owner, but only on from the database host itself.
I'd say that this one is quite a Good Idea.
You don't have to "leak" these highly-elevated credentials, or have to weaken your security to allow it and you keep control of the all-powerful postgres account.
Always keep the biggest and best tools in the box for yourself, to clean up the mess that everyone/everything else makes!
