Unfortunately, I have removed super user privileges from postgres user in PostgreSQL. And currently I have not any super user in PostgreSQL. And i want to make superuser. So how can i make it ? Every time I am trying to make postgres to super user. I am facing this problem.
Error : must be superuser to alter superusers.
On
(assuming you have root access on Ubuntu machine)
To enter psql as super user you need to:
sudo -u postgres psql
as suggested in this SO post here
If there is no user called postgres you need to create it on system first, with:
sudo adduser newuser
Else, if you have problems with password not accepted or not created at all you can follow (Ubuntu 14.04 related) instructions here or for more on user accounts look here
On
Adding to Craig Ringer's answer, here is the procedure for MacOS and Brew if you accidentally downgrade your only PostgreSQL user:
brew services stop postgresql/usr/local/Cellar/postgresql/10.4/bin/postgres --single -D /usr/local/var/postgresbackend> ALTER USER "yourname" with superuser; or whatever privilege you need to fixCTRL-Dbrew services start postgresql
On
Assuming that your system user is 'ec2-user'
So try this to enter as superuser
psql -U ec2-user postgres
This will enter you as ec2-user as superuser using postgres db
Now, change postgres user roles to superuser
ALTER USER postgres WITH SUPERUSER;
Quit from above console and now you can open psql using postgres user as superuser
psql -U postgres
Note: I tested this on PostgreSQL 12.5
On
SELECT usename AS role_name, CASE WHEN usesuper AND usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text) WHEN usesuper THEN CAST('superuser' AS pg_catalog.text) WHEN usecreatedb THEN CAST('create database' AS pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END role_attributes FROM pg_catalog.pg_user ORDER BY role_name desc; log with root_user then give superuser to postgres
On
In PG admin : select db->right click->properties->general-> owner-> select role which you want to make owner then follow the below command in pg admin query console:
ALTER ROLE yourusername WITH Superuser;
it will not execute in psql command prompt. if you will try to excute then it will give error saying must be superadmin to assign a superadmin role.
You're going to have to stop the database system and start a stand-alone back-end, which always unconditionally runs as a superuser.
You can use this backend to
ALTERthe user you wish to give superuser rights to. Then shut the standalone backend down and start the database normally.It is important that you completely stop the database server before entering single user mode. PostgreSQL single user mode will refuse to start if there's a postmaster, but to be sure you should make sure there are no PostgreSQL processes running on your system. Under (almost) no circumstances should you ever delete
postmaster.pid- that's pretty much guaranteed to result in database corruption if there's still any PostgreSQL process accessing that data directory.Exactly how to start a standalone back-end depends a bit on your OS/distro and how you installed PostgreSQL. You haven't included this info, so I can only really point you at the manual for the
postgresback-end executable.Make a backup first.
See the section Options for Single User mode and, toward the bottom, Usage. You'll want to run the
postgresbackend with--single, as the unix user that owns the database files, with the path to the datadir. On a typical Linux PostgreSQL install this might be something like:Your datadir and
postgresexecutable location are quite possibly different. The above is for a Fedora system running with PGDG PostgreSQL packages from http://yum.postgresql.org/ .