CODEWITHSUNDEEP
postgresqlpsql
Eric Baldwin
Eric Baldwin

Reputation: 3501

Logged in as postgres but getting the error createuser: creation of new role failed: ERROR: must be superuser to create superusers

I need to create a superuser so I can create a db, but I'm having trouble with this. I'm logged in as the user postgres:

sudo su - postgres

But when I try to create a superuser, I get the following problem:

$createuser glassboard;
Shall the new role be a superuser? (y/n) y

createuser: creation of new role failed: ERROR: must be superuser to create superusers

This also happens if I try to create a new user in psql and then make him a superuser:

$ psql -U postgres
psql (9.1.4)
Type "help" for help.

postgres=> create user glassboard
postgres-> ;
ERROR:  permission denied to create role

How do I create a superuser?

output of \du in postgres:

postgres=> \du

                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 main      | Superuser, Create role, Create DB, Replication | {}
 postgres  |                                                | {}

Upvotes: 8

Views: 45127

Answers (5)

epox
epox

Reputation: 10970

On Mac OS default installation

The default superuser is not postgres, but the current mac-os user. In order to solve that:

1 . Connect to your DB with your current (system) user. The below example connects to DB named "postgres":

psql -d postgres

you should get into psql prompt, like postgres=#

2 . Grant superuser to well-known postgres username:

ALTER ROLE postgres superuser;

If everything went smoothly, you'll see the response: ALTER ROLE

3 . Enjoy! (\q to quit)

Upvotes: 7

Adrian Stanica
Adrian Stanica

Reputation: 77

Had the same issue about not being able to create a new user, after creating a database and logging into that database as the superuser.

This is what worked for me (unfortunately I didn't have time to study the why), on Debian 10, Postgresql12

Log into the system default db (postgres) as the default superuser (in my case I had just installed it, so).

psql -d postgres

and then I was able to create the user, with exactly the same command line that didn't work when I was logged under the database I had created: create user xxxx with password 'yyyyyy';

Then I granted all on the new database that I had created

grant all on database newly_created_database to xxxx;

Upvotes: -2

leandroico
leandroico

Reputation: 1227

I am using PostgreSQL 9.6 (not saying my solution shouldn't work for older or newer versions) on macOS Sierra (10.12.2) and what really worked for me was to create another user with the following command line:

createuser -s anotheruser

Upvotes: 1

yellavon
yellavon

Reputation: 2881

In my case on PostgreSQL 9.2, the postgres superuser was created, but when I went to create additional superusers from the postgres user, I was never prompted with Shall the new role be a superuser? (y/n) so the new user was created with default permissions. To fix this, I just ran this command as the postgres user: ALTER USER myuser WITH SUPERUSER;

Upvotes: 5

Daniel Vérité
Daniel Vérité

Reputation: 61666

Some OSX packages don't create a postgres superuser database account. The superuser is named differently, in your case it's main.

When you do psql -U main without specifying a database, it defaults to the same name as the user. If you don't have a database named main, indicate a different database with the -d option.

If you have no database to connect to, use template1

psql -U main -d template1

If still you want to grant superuser to postgres, do once logged inside psql:

alter user postgres superuser;

Upvotes: 14

Related Questions