14

I installed postgresql on mac using brew. I wasn't asked about for the password for postgresql during the installation. Now I need to create a user and can't:

Alexs-MacBook-Air:mydir alex$ su - postgres
Password:
su: Sorry

Whatever password (including empty) I use, it is wrong. What's the easiest way to reset it?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Incerteza
  • 32,326
  • 47
  • 154
  • 261
  • Possible duplicate of [su postgres: Sorry?](https://stackoverflow.com/questions/7765269/su-postgres-sorry) – Basil Bourque Oct 25 '18 at 22:36
  • [The original](https://stackoverflow.com/questions/7765269/su-postgres-sorry) of this duplicate was closed as off-topic. So [I posted the same Question](https://dba.stackexchange.com/q/221043/19079) with [a solution](https://dba.stackexchange.com/a/221044/19079) on the DBA Stack Exchange sister site. – Basil Bourque Oct 25 '18 at 22:38

5 Answers5

39

I installed postgresql on mac using brew. I wasn't asked about for the password for postgresql during the installation.

That's normal because brew doesn't need or create any postgres account. The PostgreSQL processes run under your own account. The other 3 answers so far are wrong in this regard.

See the output of brew info postgres for information.

To create a new database account, you may launch, from your own OS account:

/usr/local/bin/createdb someusername

or within psql:

/usr/local/bin/psql -d postgres

and then as an SQL command: CREATE USER someusername PASSWORD 'somepassword';

This should work because brew has normally created at initdb time:

  • a database superuser account with the same login as your OS account (seems to be alex in your case)
  • a database named postgres that may be used to log info for administrative tasks such as creating a user.

The point of using the full path /usr/local/bin is to reach the commands provided by brew, as opposed to the commands with the same name that come with the system and are located in /usr/bin or the commands with the same name that are potentially installed by other PostgreSQL providers, such as postgres.app or macports or entreprisedb. There are 5-6 competing and incompatible ways of getting postgresql installed on Mac OS X.

EDIT: the newer versions of MacOS X desktop edition no longer have the postgres client-side commands pre-installed. This seems to be the case at least since MacOS X 10.10 (Yosemite) and possibly 10.9.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 2
    This should've been the accepted answer. Brew's installation doesn't have a `postgres` user. – Paulo Freitas Mar 17 '17 at 11:10
  • Is there any way to password protect this system level user? When I run `psql postgres` and then `\password` it has no effect. – bryan kennedy May 09 '18 at 16:28
  • 1
    @bryankennedy: most probably this is due to [`pg_hba.conf`](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) using `peer` or `trust` locally. Replace these with `md5`. – Daniel Vérité May 09 '18 at 17:12
16

You're trying to access the system user named postgres. According to comments you left on the other answers, there is no such user. You can't change the password of a user that doesn't exist.

Perhaps it's _postgres or postgres_? I know some installers on OS X use weird names. Or perhaps your install never successfully created a postgres user on your system in the first place, so you can't set a password for it?

In general you never need to set a password for this user. You can just

sudo -u postgres psql

or whatever command you need to run as the postgres superuser.

Note that you shouldn't need su for anything.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
7

There is no default password. To run a shell as user postgres use (as advised by Craig):

sudo -u postgres -i

Type exit when done. See:

Turns out, the user wasn't created at all. Look to @Daniel's answer.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • actually it says "su: unknown login: postgres" – Incerteza Jun 02 '14 at 02:38
  • Why does everyone use `sudo su - postgres`? `sudo` supports user-impersonation directly, and without the horrible quoting mess you land up with `su`. Just `sudo -u postgres psql` or whatever. – Craig Ringer Jun 02 '14 at 06:41
  • @CraigRinger: If I plan do do a couple of things as the `postgres` user, it's much more convenient to switch to the role and `exit` when done. And what "quoting mess"? – Erwin Brandstetter Jun 02 '14 at 07:28
  • @ErwinBrandstetter For a login shell use `sudo -u postgres -i`. As for quoting: `su` passes its argument to `$SHELL -c`. So you'll have undesired results from (say) `sudo su - postgres -c psql -c "SELECT 1;"`, wheras `sudo -u postgres psql -c "SELECT 1;"` works correctly. To do the equivalent with `su` requires another layer of quoting to protect the argument, like `sudo su - postgres -c "psql -c \"select 1\"";`. Using `su` as well as `sudo` is just redundant. Finally, if you have a `sudoers` that lets you become `postgres` but not `root`, it just won't work. – Craig Ringer Jun 02 '14 at 07:57
  • Thanks, I learned something. I was using `sudo su` out of habbit. `sudo -u postgres -i` seems like the better alternative. – Erwin Brandstetter Jun 02 '14 at 08:52
4

Why not:

sudo passwd postgres

This will prompt you for the password of your current account. Then it will ask you to enter the new password (twice) for the postgres user.

Tamer Shlash
  • 9,314
  • 5
  • 44
  • 82
0

I'm Nura, after I installed postgresql with homebrew on macOS Catalina MBP 13 inch Mid 2012,i do got the same error as Mr. Incerteza. I SOLVED it with different ways, when I got the error, I ran

  1. brew services start postgresql@14
  2. /usr/local/bin/psql -d postgres

Then the terminal database has open.

Muhammad Irfansyahfalah
  • 1