Create a Superuser in postgres

Question

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.

Towny

The error might be useful. – Triune

My bad, done. Do you think i'm in the correct via? – Towny

Answer 1

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 😊

Towny

Answer 2

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';

Neolamarckism

You have to use single quotes too around the password. Double quotes don't work. – Urus

doesn't seem to work anymore:: syntax error at or near "username" – Kistna

@Kistna I just tested with pg 14.1, it still works. – Neolamarckism

@Neolamarckism hmm it seems to work if I put single quotes around the name with postgres 12 not sure what is going on. – Kistna

@Kistna What's your username ? – Neolamarckism

Answer 3

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';

Combo

In PG14, I had to leave out the 'WITH', so CREATE USER username SUPERUSER PASSWORD 'passwordstring'; – Teahan

@Teahan WITH works but it has to be before. As in CREATE USER admin WITH SUPERUSER PASSWORD 'passwordstring'; – Exorbitant

Thanks for clarifying, @AndréPena! – Teahan

Answer 4

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.

Palladian

The matter is i'm looking to create a superuser with password, through a script which executes sequentially. sudo -u postgres createuser postgres with password 'postgres' -s # Set super unsafe defaultz (dev only) sudo sh -c "echo 'local all postgres peer\nlocal all all peer\nhost all all 127.0.0.1/32 md5\nhost all all ::1/128 md5' > /etc/postgresql/10/machine/pg_hba.conf" sudo /etc/init.d/postgresql reload I'm a noob so, can you clarificate your answer? am i in the correct via? – Towny

Answer 5

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;

Ending

Answer 6

CREATE USER username WITH PASSWORD '12345' SUPERUSER;

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

Dole

Please don't duplicate existing answers – Astred