Can't log into system user postgres

Question

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?

Cabbala

Possible duplicate of su postgres: Sorry? – Westonwestover

The original of this duplicate was closed as off-topic. So I posted the same Question with a solution on the DBA Stack Exchange sister site. – Westonwestover

Answer 1

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.

Katha

Answer 2

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.

Contrivance

This should've been the accepted answer. Brew's installation doesn't have a postgres user. – Frenulum

Is there any way to password protect this system level user? When I run psql postgres and then \password it has no effect. – Connelley

@bryankennedy: most probably this is due to pg_hba.conf using peer or trust locally. Replace these with md5. – Constringe

Answer 3

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:

• PostgreSQL error: Fatal: role "username" does not exist

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

Godman

actually it says "su: unknown login: postgres" – Cabbala

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. – Katha

@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"? – Godman

@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. – Katha

Thanks, I learned something. I was using sudo su out of habbit. sudo -u postgres -i seems like the better alternative. – Godman

Answer 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.

Glossectomy

actually it says "su: unknown login: postgres" – Cabbala

Answer 5

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.

Frivolity