Postgres: upgrade a user to be a superuser?

Question

In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.

# alter user myuser ...?

Wellbeing

Answer 1

ALTER USER myuser WITH SUPERUSER;

You can read more at the Documentation for ALTER USER

Provocation

the opposite operation is ALTER USER myuser WITH NOSUPERUSER – Icaria

and how can i detect if myuser is currently superuser? – Kirov

SELECT rolname, rolsuper FROM pg_roles; to @Kirov – Grumble

This has solved the crontab / pg_dump hell I was running into. Brilliant! – Nunez

@Wellbeing +1 your comment, whoever downvoted this question just wants to piss people like you and me off who just want a DAMN ANSWER! – Pram

I get: ERROR: must be superuser to alter superusers – Baton

@Kirov You can do \du to list all users/roles. – Hazel

@stiv you can't make yourself a superuser – Norge

In case the username is a reserved word, you have to put the username inside comas, example ALTER USER "user" WITH SUPERUSER; – Gonta

In case you're using psql and nothing seems to work, try typing out the commands all in lower case. It thought I was going insane but it looks like my psql client doesn't like UPPER CASE syntax. – Chara

@StepanYakovenko use sudo su - postgres and then psql before you try this command. – Excipient

Answer 2

To expand on the above and make a quick reference:

• To make a user a SuperUser: ALTER USER username WITH SUPERUSER;
• To make a user no longer a SuperUser: ALTER USER username WITH NOSUPERUSER;
• To just allow the user to create a database: ALTER USER username CREATEDB;

You can also use CREATEROLE and CREATEUSER to allow a user privileges without making them a superuser.

Documentation

Flexor

CREATE ROLE dba WITH SUPERUSER; GRANT dba TO username; User username can than become superuser only on demand using SET ROLE dba;. In log file are commands still logged under username. – Leafage

Answer 3

$ su - postgres
$ psql
$ \du; for see the user on db
select the user that do you want be superuser and:
$ ALTER USER "user" with superuser;

Childbirth

in this specific case, you have to put the username inside comas, example ALTER USER "user" WITH SUPERUSER; – Gonta

@Gonta Because user is a reserved word, yes. – Donation

Answer 4

May be sometimes upgrading to a superuser might not be a good option. So apart from super user there are lot of other options which you can use. Open your terminal and type the following:

$ sudo su - postgres
[sudo] password for user: (type your password here)
$ psql
postgres@user:~$ psql
psql (10.5 (Ubuntu 10.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# ALTER USER my_user WITH option

Also listing the list of options

SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB  | CREATEROLE | NOCREATEROLE |
CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION|
NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | 
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'

So in command line it will look like

postgres=# ALTER USER my_user WITH  LOGIN

OR use an encrypted password.

postgres=# ALTER USER my_user  WITH ENCRYPTED PASSWORD '5d41402abc4b2a76b9719d911017c592';

OR revoke permissions after a specific time.

postgres=# ALTER USER my_user  WITH VALID UNTIL '2019-12-29 19:09:00';

Excurrent

Answer 5

Run this Command

alter user myuser with superuser;

If you want to see the permission to a user run following command

\du

Batruk

Answer 6

You can create a SUPERUSER or promote USER, so for your case

$ sudo -u postgres psql -c "ALTER USER myuser WITH SUPERUSER;"

or rollback

$ sudo -u postgres psql -c "ALTER USER myuser WITH NOSUPERUSER;"

To prevent a command from logging when you set password, insert a whitespace in front of it, but check that your system supports this option.

$  sudo -u postgres psql -c "CREATE USER my_user WITH PASSWORD 'my_pass';"
$  sudo -u postgres psql -c "CREATE USER my_user WITH SUPERUSER PASSWORD 'my_pass';"

Jeanettajeanette

Answer 7

alter user username superuser;

Hadden

Answer 8

If you reached this because you're using Amazon Redshift you CANNOT assign SUPERUSER

ALTER USER <username> SUPERUSER;

Instead assign CREATEUSER:

ALTER USER <username> CREATEUSER;

Apparently, SUPERUSER isn't an available user assignment in Amazon Redshift clusters. I am utterly confused by this.

https://docs.aws.amazon.com/redshift/latest/dg/r_superusers.html

Screenshots showing this:

• https://i.stack.imgur.com/uyWXt.png
• https://i.stack.imgur.com/ycbeL.png

Unworldly

Answer 9

Login to postgres database with the postgres user.

\c postgres postgres

Issue \du command to check current roles granted to user.

Issue the command to grant superuser: alter user username with superuser;

Howze

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Malcommalcontent

Answer 10

It is quite easy to switch between making user a superuser or a regular user. First do check the permissions of all the users by using following command.

\du

Then make user superuser by using following command

ALTER USER user_name WITH SUPERUSER;

and reverse by using following command

ALTER USER user_name WITH NOSUPERUSER;

for further understanding, explore the following tutorial: How to Change a User to Superuser in PostgreSQL

Krissie

Answer 11

Found a better command to alter the status of a user.

ALTER USER myuser WITH SUPERUSER PASSWORD 'pgpassword' VALID UNTIL '2034-12-2';

Ig there are niche cases for the valid until. You could use that for password rotation and temporary access.

Triable

Answer 12

For example, you can make the user(role) john a superuser (by a superuser) as shown below according to ALTER ROLE and ALTER USER. *You must log in with any superusers(e.g., postgres) and you can omit WITH which is optional:

ALTER ROLE john WITH SUPERUSER;

Or:

ALTER USER john WITH SUPERUSER;

In addition, you can make the user(role) john a non-superuser as shown below:

ALTER ROLE john WITH NOSUPERUSER;

Or:

ALTER USER john WITH NOSUPERUSER;

Consonant