615

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 ...?
1217
ALTER USER myuser WITH SUPERUSER;

Docs.

  • 150
    the opposite operation is ALTER USER myuser WITH NOSUPERUSER – d.raev Apr 18 '13 at 6:47
  • 2
    and how can i detect if myuser is currently superuser? – masterweily May 10 '13 at 8:18
  • 20
    SELECT rolname, rolsuper FROM pg_roles; to @masterweily – caulfield May 30 '13 at 11:44
  • 5
    I get: ERROR: must be superuser to alter superusers – Stepan Yakovenko Mar 18 '14 at 8:03
  • 14
    @masterweily You can do \du to list all users/roles. – XåpplI'-I0llwlg'I - Apr 6 '14 at 23:38
60

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

22

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

  • in this specific case, you have to put the username inside comas, example ALTER USER "user" WITH SUPERUSER; – Carlos.V Mar 29 '17 at 1:59
8

Run this Command

alter user myuser with superuser;

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

\du
7

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
[email protected]:~$ 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';
3

alter user username superuser;

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.