CODEWITHSUNDEEP
sqldatabasepostgresqlubuntusuperuser
Joseph Palmezano
Joseph Palmezano

Reputation: 1685

Create a Superuser in postgres

I'm looking for setup a Rails Environment with Vagrant, for that purpose the box it's been provisioned through bash shell method and includes among others this line:

sudo -u postgres createuser <superuserusername> -s with password '<superuserpassword>'

But I'm getting a configuration error:

createuser: too many command-line arguments (first is "with")

Can you help me with the correct syntax for create a Superuser with a password. Thanks.

Upvotes: 88

Views: 244801

Answers (6)

Eric
Eric

Reputation: 24920

Do it in a single statement within psql:

CREATE ROLE username WITH LOGIN SUPERUSER PASSWORD 'password';

e.g

CREATE ROLE dummy WITH LOGIN SUPERUSER PASSWORD '123456';

@Update

As mentioned in comment, you can also use CREATE USER, a USER is just a ROLE with LOGIN privilege by default.

With CREATE USER, the equivalent sql would be:

CREATE USER username WITH SUPERUSER PASSWORD 'password';

e.g

CREATE USER dummy WITH SUPERUSER PASSWORD '123456';

Upvotes: 104

Talha
Talha

Reputation: 1

CREATE USER username WITH PASSWORD '12345' SUPERUSER;

In PostgreSQL, the correct syntax for creating a user with a password is slightly different.

Upvotes: -2

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can create the superuser john with the password apple using CREATE ROLE, CREATE GROUP or CREATE USER statement as shown below:

CREATE ROLE john WITH LOGIN SUPERUSER PASSWORD 'apple';

Or:

CREATE GROUP john WITH LOGIN SUPERUSER PASSWORD 'apple';

Or:

CREATE USER john WITH LOGIN SUPERUSER PASSWORD 'apple';

*Memos:

  • You can omit WITH which is optional.

  • You must use '' for PASSWORD instead of "" otherwise there is error.

  • You must log in with any superusers(e.g., postgres).

  • By default, CREATE USER without LOGIN can still have LOGIN attribute implicitly.

  • By default, PostgreSQL needs a password so you should set PASSWORD.

And, you can alter the existing user(role) john to a superuser with ALTER ROLE or ALTER USER statement as shown below:

ALTER ROLE john WITH SUPERUSER;

Or:

ALTER USER john WITH SUPERUSER;

*Memos:

  • You can omit WITH which is optional.

  • You must log in with any superusers(e.g., postgres).

  • ALTER GROUP statement cannot alter an existing user(role) to a superuser.

In addition, if you want to drop the superuser john, first, run these SQL below. *My answer explains how to drop a user(role) properly:

REASSIGN OWNED BY john TO postgres;
DROP OWNED BY john;

Finally, you can drop the superuser john with DROP ROLE, DROP GROUP or DROP USER statement as shown below:

DROP ROLE john;

Or:

DROP GROUP john;

Or:

DROP USER john;

Upvotes: 3

Kaka Ruto
Kaka Ruto

Reputation: 5125

For PostgreSQL versions 8.1 and newer

To create a superuser:

CREATE USER username SUPERUSER;

If you need to specify the password:

CREATE USER username WITH SUPERUSER PASSWORD 'passwordstring';

Upvotes: 45

Joseph Palmezano
Joseph Palmezano

Reputation: 1685

Solved with: 

sudo -u postgres createuser -s -i -d -r -l -w <<username>>
sudo -u postgres psql -c "ALTER ROLE <<username>> WITH PASSWORD '<<password>>';"

I know is not an elegant solution, but for now it'll do 😊

Upvotes: 54

Rakesh
Rakesh

Reputation: 841

To create a PostgreSQL user, follow these steps: At the command line, type the following command as the server's root user:

su - postgres

You can now run commands as the PostgreSQL superuser.To create a user, type the following command:

createuser --interactive --pwprompt

At the Enter name of role to add: prompt, type the user's name.
At the Enter password for new role: prompt, type a password for the user.
At the Enter it again: prompt, retype the password.
At the Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n.
At the Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n.
At the Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n.

PostgreSQL creates the user with the settings you specified.

Upvotes: 18

Related Questions