16

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.
crmpicco
  • 16,605
  • 26
  • 134
  • 210
user3094370
  • 211
  • 1
  • 2
  • 7

7 Answers7

14

(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

Community
  • 1
  • 1
Zalom
  • 696
  • 9
  • 18
4

For me helps:

sudo -u gleb psql postgres

where gleb is my mac system user

Gleb Dolzikov
  • 776
  • 8
  • 13
3

Adding to Craig Ringer's answer, here is the procedure for MacOS and Brew if you accidentally downgrade your only PostgreSQL user:

  1. brew services stop postgresql
  2. Wait a few seconds and/or check Activity Monitor to make sure "postgres" is no longer running.
  3. /usr/local/Cellar/postgresql/10.4/bin/postgres --single -D /usr/local/var/postgres
  4. backend> ALTER USER "yourname" with superuser; or whatever privilege you need to fix
  5. CTRL-D
  6. brew services start postgresql
richardkmiller
  • 2,902
  • 3
  • 31
  • 29
2

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 ALTER the 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 postgres back-end executable.

Make a backup first.

In the single-user mode, the session user will be set to the user with ID 1, and implicit superuser powers are granted to this user. This user does not actually have to exist, so the single-user mode can be used to manually recover from certain kinds of accidental damage to the system catalogs.

See the section Options for Single User mode and, toward the bottom, Usage. You'll want to run the postgres backend 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:

sudo systemctl stop postgresql-9.3.service
sudo -u postgres /usr/pgsql-9.3/bin/postgres --single -D /var/lib/pgsql/9.3/data

Your datadir and postgres executable location are quite possibly different. The above is for a Fedora system running with PGDG PostgreSQL packages from http://yum.postgresql.org/ .

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thanks a bunch. I was using a puppet module to define a database, I thought it was OK to set the postgres user as owner of the database, but woops Postgresql::Server::Db[mydb]/Postgresql::Server::Role[postgres]/Postgresql_psql[ALTER ROLE "postgres"NOSUPERUSER]/command) command changed 'notrun' to 'ALTER ROLE "postgres" NOSUPERUSER' – tobixen Mar 20 '17 at 14:08
1

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

Avanish Pandey
  • 61
  • 2
0

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

Hamdy Ahmed
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 03 '22 at 13:23
0

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.

RANDHIR KUMAR
  • 1