How do I rename the default postgres superuser to "root"?

Question

I currently log in to PostgreSQL using psql -U postgres. How do I rename postgres user to root?

If I am already logged in as postgres then trying ALTER USER postgres RENAME TO root will say ERROR: session user cannot be renamed.

Is it possible to rename the user without logging as postgres user? I don't think I have any other superuser since this is a fresh install of PostgreSQL.

By the way, I am running Gentoo on Amazon EC2.

Erlindaerline

I'd imagine: log in as postgres, create a second super user, log in as new superuser, and run your ALTER – Ewing

Why would you do this? You can't run the postmaster as "root" as it will only start when run as an unprivileged user. – Estray

Does the postgres user in Linux have to match with the postgres role in the database? I log in as root so I just want to type psql -d something to log in as oppose to psl -U postgres -d something. – Erlindaerline

Answer 1

You should be able to just create a new postgres superuser called root by logging in as the postgres user and (at the shell) typing;

createuser --superuser root
psql> create database root owner root

After that, when logged in as root, you should be able to do what you want with the postgres user.

Rihana

Are you saying to type that inside psql? It didn't seem to do anything. – Erlindaerline

@Erlindaerline No, from the regular shell, not in psql. – Rihana

@Erlindaerline Don't have a gentoo box handy, just an ubuntu, the paths may not be set up if you're having a problem. – Rihana

Why it's create database for actually creating a user? – Vituperate

Answer 2

You can try

update pg_authid set rolname ='root' where rolname = 'postgres';

But be aware, that munching the system catalogues by hand is always a little dangerous.

Eccrinology

That seems to work. What's the difference between a role and a user? Was I logged in as postgres user or role when I did psql -U postgres? – Erlindaerline

@hobbes3: AFAIK "user" and "group" are the historic terms which were then collapsed into "role". So these terms are interchangeable to a great degree. So you were logged in with the role/user "postgres". – Eccrinology

@hobbes3: In modern PostgreSQL, "users" are roles with a login, "groups" are without. Internally there are only roles. – Hyper

@Eccrinology Thanks for the heads up on "munching system catalogues", I thought I'd save 5 minutes doing it this way. Instead I cost myself about an hour (I broke it). Just as a hint to anyone else: remove postgres & the dependencies with --purge (apt-get) if you need to reinstall. – Percept

Answer 3

What about:

ALTER ROLE postgres RENAME TO root;

using a different superuser role?

Litta

"ERROR: session user cannot be renamed". Note, that OP was logged in as postgres. – Huesman

Answer 4

In order to rename current user oldname to newname, first log in as oldname and create a temporary superuser as such:

CREATE ROLE temp LOGIN SUPERUSER PASSWORD 'mytemporarypassword';

Log out, then log in using the temp superuser and rename the oldname user. You will have to reset the password for that user at the same time, as PostgreSQL will tell you: "MD5 password cleared because of role rename".

ALTER ROLE oldname RENAME TO newname;
ALTER USER newname WITH PASSWORD 'My-Mega-5ecure-P4ssw0rd';

Now log in using the newname user and remove the temporary user that we created previously:

DROP ROLE temp;

Crysta