70

I use postgresql command 'createuser myusername'. It prompts me for password always. If I leave password blank it gives an error that no password is specified.

I simply want to create a user with blank/no password and I'm not able to do it with Postgresql 9 under mac os x.

What steps should I take to create a user with no password. The command always asks me to enter a password.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
othman
  • 4,428
  • 6
  • 34
  • 45

4 Answers4

72

Fixing this with createuser won't work. Per the man page for createuser, it says that login will fail if you really need a password and set --no-password. createuser just creates a user. It does not decide how the user will have to authenticate.

Authentication is handled mainly in pg_hba.conf, which decides whether or not to require passwords at login. I've never run PostgreSQL on OS X. On Linux, my distro's stock conf looks like this:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Make sure you have something like the first line for for development access. Note: psql will try to connect with a unix socket if there is no --host option set. The second line would be used if you try to connect with psql --host localhost

Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
nate c
  • 8,802
  • 2
  • 27
  • 28
  • 1
    where is the location of file pg_hba.conf on a mac os x or linux box? – othman Mar 25 '11 at 14:05
  • 2
    For Ubuntu, you can find it at `/etc/postgresql/9.0/main/pg_hba.conf` (for PostgreSQL 9.0). – jmonteiro Jan 13 '12 at 23:30
  • 4
    For OS X 10.6.8, you can find it at `/Library/PostgreSQL/8.3/data/pg_hba.conf`. Note that it is owned by the `postgres` user, so you will need to `su postgres` to edit it. – Chris Jan 17 '12 at 06:51
  • 1
    The location on CentOS is `/var/lib/pgsql/9.3/data/pg_hba.conf`. – XåpplI'-I0llwlg'I - Jun 25 '14 at 17:52
  • Using the installer for PostgreSQL 9.4 on OSX 10.10.2, the default location is `/Library/PostgreSQL/9.4/data/pg_hba.conf` . Note, after editing the method from md5 to ident, I had to restart the db: `sudo -u postgres pg_ctl -D /Library/PostgreSQL/9.4/data restart` – hert Mar 19 '15 at 14:41
  • What does this mean? "Note: psql will try to connect with a unix socket if there is no --host option set"? – Zach Smith Aug 24 '19 at 12:35
40

Use .pgpass

Rather than creating a user with no password, an alternative is to create a .pgpass file with the password in to have it automatically supplied. This will be respected by all applications that use libpq to connect, which is practically all of them except the JDBC driver and the .NET driver.

Here is how to create an example .pgpass file for the user "foobar" and the password "password", with the right file permissions:

echo 'localhost:*:*:foobar:password' >> ~/.pgpass
chmod 600 ~/.pgpass
Flimm
  • 136,138
  • 45
  • 251
  • 267
araqnid
  • 127,052
  • 24
  • 157
  • 134
  • Worked on my mac, not working on ubuntu though (through WSL) – Daniel Vilela Dec 03 '21 at 22:33
  • You my friend are a champion. This is my preferred solution it worked on my Mac where my postgres user had a password set but a script I needed to run doesn't supply one since it can run on anyone's machine. – Steve Nov 22 '22 at 16:45
14

The additional way (I prefere it) to change user's password for PG user is to login to postgres console (as a postgres user on a local pc), and issue SQL request:

$ sudo -u postgres psql -w

# ALTER USER postgres WITH PASSWORD '';
Малъ Скрылевъ
  • 16,187
  • 5
  • 56
  • 69
  • 1
    be aware, this may disable the account if the server requires passwords! – 0atman Sep 01 '16 at 12:53
  • 6
    doesn't work for me. Error: psql: fe_sendauth: no password supplied – Romulus Nov 17 '16 at 10:07
  • @Romulus have you setup a password earlier for the system user `postgres`? Also you have to try with `-w` i.e. `psql -w` – Малъ Скрылевъ Nov 17 '16 at 12:42
  • I didn't setup a password for the `postgres` system user. When I update the password in psql it didn't complain that it can not update the password, but when I try to use the password to login, it's giving me the above error. But funny thing is that it's not working also with other users. Maybe I have to configure psql to accept users without password? – Romulus Nov 17 '16 at 13:58
  • @Romulus the ploblem is not in in log to postgres linux/unix userm, but to connecting to the same name db role – Малъ Скрылевъ Nov 17 '16 at 14:01
  • 4
    Aaa yeap, sure. I changed the password in the psql, not to the system user. The problem is that if I set a password, it works (any password), but if I try to change it NULL, then I get the error: `Error: psql: fe_sendauth: no password supplied`. – Romulus Nov 17 '16 at 14:11
3
postgres=# ALTER USER postgres WITH PASSWORD '';
NOTICE:  empty string is not a valid password, clearing password
ALTER ROLE
postgres=#
McNally Paulo
  • 192
  • 1
  • 8