Replacing Postgres User

Question

I'm new to postgres. It might sound a silly question, due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user (for example:abc) created by me.

To test this, I created a database user 'abc' and grated all superuser permission(like postgres user) to this user. However, when I'm trying to connect to postgresql prompt from my linux machine with below command:

    sudo su - abc -c "psql"

It wasn't working. I know there is a default database with postgres user 'postgres' in postgres and it's template database. Postgres is open source and I believe, there should be a possibility to accomplish what I want to. Anyone could please let me how can I make it happen?

Answer 1

It might sound a silly question,

I think you have a security requirement written by someone who doesn't know what he is doing, and then implemented by someone else who doesn't know what he is doing. "Silly" is far too benign a word to describe this. I hope this is a toy system completely firewalled off from the internet.

sudo su - abc -c "psql"

You said you created a database user named "abc", but now you are trying to do something as the operating system user named "abc", which probably does not exist, since you didn't describe creating it.

Try psql -U abc. It will probably get farther than your last effort, but still not work due to pg_hba.conf settings. Once you see the error message produced (look in the server log file for postgresql for the complete error message, the one returned to the client is intentionally vague to avoid leaking sensitive information to a presumed attacker), then you can decide what to do about it.

Answer 2

... due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user ... created by me

Go back to whoever came up with this "requirement" and ask the question "Why?".

Seriously.

There are many, many Postgres installations running all over the planet using postgres as their superuser account and having no problems at all.

All too often, we see so-called "Requirements" that are a "Technical Solution in search of a Problem"(TM), a complete waste of time and effort or, worst of all, things that actually endanger the operation, maintainability or recoverability of your databases which, IMHO, unacceptable.

Here's a couple of random "How to secure Postgres" articles that I found in about 30 seconds ...

https://www.upguard.com/articles/10-ways-to-bolster-postgresql-security

https://severalnines.com/blog/how-secure-your-postgresql-database-10-tips

Neither mention getting rid of or changing the postgres superuser account - but they do talk about how to properly protect that account.