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 ...?
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.
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
.
$ 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;
ALTER USER "user" WITH SUPERUSER;
user
is a reserved word, yes.
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';
Run this Command
alter user myuser with superuser;
If you want to see the permission to a user run following command
\du
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';"
alter user username superuser;
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
Success story sharing
ALTER USER myuser WITH NOSUPERUSER
SELECT rolname, rolsuper FROM pg_roles;
to @masterweily\du
to list all users/roles.