CODEWITHSUNDEEP
linuxpostgresqlubuntu
Ben Owen
Ben Owen

Reputation: 133

No super user for postgresql

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

Upvotes: 0

Views: 2100

Answers (1)

jjanes
jjanes

Reputation: 44363

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.

Upvotes: 2

Related Questions