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

User · Answer

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

User · Answer

Run this Command  alter user myuser with superuser    If you want to see the permission to a user run following command   du

User · Answer

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

User · Answer

ALTER USER myuser WITH SUPERUSER    You can read more at the Documentation

User · Answer

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

User · Answer

alter user username superuser

User · Answer

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

[sql] postgres: upgrade a user to be a superuser?

Examples related to sql

Examples related to postgresql