Reputation: 3501
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
Reputation: 10970
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
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
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
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
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