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

My bad, done. Do you think i'm in the correct via? – Joseph Palmezano Sep 17 '19 at 15:35 — Joseph Palmezano, Sep 17 '19 at 15:35

Eric · Answer 1 · 2021-02-13T07:18:38.150

75

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

edited Feb 13 '21 at 07:18

answered Jan 14 '21 at 06:48

Eric

22,183
20
145
196

5

You have to use single quotes too around the password. Double quotes don't work. – Isaac Pak Feb 12 '21 at 23:26
doesn't seem to work anymore:: syntax error at or near "username" – noone392 Mar 24 '22 at 21:31
@noone392 I just tested with `pg 14.1`, it still works. – Eric Mar 25 '22 at 00:51
@Eric hmm it seems to work if I put single quotes around the name with postgres 12 not sure what is going on. – noone392 Mar 25 '22 at 04:02
@noone392 What's your username ? – Eric Mar 25 '22 at 09:10

score 46 · Accepted Answer · answered Sep 18 '19 at 16:47

46

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

answered Sep 18 '19 at 16:47

Joseph Palmezano

1,245
2
11
16

score 33 · Answer 3 · edited Apr 11 '22 at 06:46

33

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

edited Apr 11 '22 at 06:46

Yegor

3,652
4
22
44

answered Nov 08 '21 at 21:12

Kaka Ruto

4,581
1
31
39

6

In PG14, I had to leave out the 'WITH', so `CREATE USER username SUPERUSER PASSWORD 'passwordstring';` – erwaman Mar 04 '22 at 20:52
1

@erwaman WITH works but it has to be before. As in `CREATE USER admin WITH SUPERUSER PASSWORD 'passwordstring';` – Andre Pena Mar 14 '22 at 06:40
1

Thanks for clarifying, @AndréPena! – erwaman Mar 14 '22 at 17:03

score 16 · Answer 4 · answered Sep 17 '19 at 13:43

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.

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? — Joseph Palmezano, Sep 17 '19 at 14:40

Create a Superuser in postgres

4 Answers4

Linked