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.

I'd imagine: log in as postgres, create a second super user, log in as new superuser, and run your ALTER — Frank Farmer, Mar 07 '12 at 19:05
Why would you do this? You can't run the postmaster as "root" as it will only start when run as an unprivileged user. — Matthew Wood, Mar 07 '12 at 20:00
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`. — hobbes3, Mar 08 '12 at 16:44

score 17 · Accepted Answer · edited Nov 19 '13 at 21:03

17

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.

edited Nov 19 '13 at 21:03

sorin

161,544
178
535
806

answered Mar 07 '12 at 19:06

Joachim Isaksson

176,943
25
281
294

Are you saying to type that inside `psql`? It didn't seem to do anything. – hobbes3 Mar 07 '12 at 19:17
@hobbes3 No, from the regular shell, not in psql. – Joachim Isaksson Mar 07 '12 at 19:17
@hobbes3 Don't have a gentoo box handy, just an ubuntu, the paths may not be set up if you're having a problem. – Joachim Isaksson Mar 07 '12 at 19:19
Why it's `create database` for actually creating a user? – bravmi Oct 21 '21 at 16:02

score 8 · Answer 2 · answered Mar 07 '12 at 19:09

8

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.

answered Mar 07 '12 at 19:09

A.H.

63,967
15
92
126

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`? – hobbes3 Mar 07 '12 at 19:16
1

@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". – A.H. Mar 07 '12 at 19:20
2

@hobbes3: In modern PostgreSQL, "users" are [roles with a login](http://www.postgresql.org/docs/current/interactive/sql-createuser.html), "groups" are without. Internally there are only roles. – Erwin Brandstetter Mar 07 '12 at 22:21
2

@A.H. 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. – Scott Stevens Feb 06 '13 at 04:04

score 6 · Answer 3 · answered Mar 24 '21 at 13:58

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;

score 6 · Answer 4 · answered Mar 07 '12 at 19:14

6

What about:

ALTER ROLE postgres RENAME TO root;

using a different superuser role?

answered Mar 07 '12 at 19:14

Frank Heikens

117,544
24
142
135

10

"ERROR: session user cannot be renamed". Note, that OP was logged in as `postgres`. – Jawa Sep 09 '13 at 09:33

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

4 Answers4