No super user for postgresql

Question

Trying to run the following on postgresql:

CREATE EXTENSION IF NOT EXISTS pg_trgm

but I get:

HINT:  Must be superuser to create this extension.

I was logged in as postgres

So I checked user roles:

 Role name |                   Attributes                    | Member of 
-----------+-------------------------------------------------+-----------
 devserver |                                                 | {}
 postgres  | Create role, Create DB, Replication, Bypass RLS | {}

What can I do here to add a super user? Reinstalling makes no difference. Just returns to this exact state.

OS: Ubuntu 20.04.2 LTS x86_64

your postgres user does not have superuser roles, quite weird. Are you using postgres profile user to do the query? If no, can you try sudo -i -u postgres on ubuntu -> psql and test the command again? — Mic Fung
– Mic Fung, Commented May 17, 2021 at 19:19
It logs into the user account but outputs: To run a command as administrator (user "root"), use "sudo <command>". See "man sudo_root" for details. — Ben Owen
– Ben Owen, Commented May 17, 2021 at 19:29
Oh yea, that was another problem I had. There is no password set for postgres for some. I was never prompt for one when I installed either so I cannot use sudo psql — Ben Owen
– Ben Owen, Commented May 17, 2021 at 19:35
sudo psql, the password is your ubuntu password, the root account password. it means asking super user on ubuntu to do something for you, so it prompt the password to let's you enter super user password which should be your current account password. — Mic Fung
– Mic Fung, Commented May 17, 2021 at 19:37

jjanes · Accepted Answer · 2021-05-17 20:56:11Z

2

If you have given away superuser status from all of your users, then you will need to shutdown the database and restart it in single-user mode to restore superuser to at least one user.

echo 'alter user postgres superuser' | postgres --single -D /path/to/db

"Reinstall" probably means you reinstalled just the software itself, but it is still using the same data files as it had before, which retains the same settings, which includes the deficit of superusers. If the current database contains no info of value, you could just blow it away and recreate it. One way to do this it specify --purge when uninstall with apt.

answered May 17, 2021 at 20:56

jjanes

45.1k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Owen Over a year ago

To uninstall it you need to specify individual packages. sudo apt purge postgres is not enough

Collectives™ on Stack Overflow

No super user for postgresql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related