CODEWITHSUNDEEP
sqldatabasepostgresqlaltersuperuser
flossfan
flossfan

Reputation: 10884

Postgres: upgrade a user to be a superuser?

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 ...?

Upvotes: 751

Views: 453359

Answers (12)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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;

Upvotes: -1

hamza._.ghouri
hamza._.ghouri

Reputation: 151

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

Upvotes: 1

Umairus
Umairus

Reputation: 81

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.

Upvotes: 0

Rajat Sanwal
Rajat Sanwal

Reputation: 19

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;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425813

ALTER USER myuser WITH SUPERUSER;

You can read more at the Documentation for ALTER USER

Upvotes: 1442

Robert J
Robert J

Reputation: 968

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:

Upvotes: 1

Bala
Bala

Reputation: 41

alter user username superuser;

Upvotes: 4

Vasilii Suricov
Vasilii Suricov

Reputation: 954

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';"

Upvotes: 6

Sandip Debnath
Sandip Debnath

Reputation: 1083

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';

Upvotes: 15

el fuser
el fuser

Reputation: 630

$ 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;

Upvotes: 39

Chetan kapoor
Chetan kapoor

Reputation: 855

Run this Command

alter user myuser with superuser;

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

\du

Upvotes: 10

ZZ9
ZZ9

Reputation: 2237

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

Upvotes: 76

Related Questions