Postgres user does not exist?

Question

I have just installed Postgres and have been tinkering with it and various configurations for 1-2 hours.

I am stuck on being unable to change to the postgres-user

$ su - postgres yields the following error: su: unknown login: postgres

$ sudo -u postgres psql yields the following error: sudo: unknown user: postgres

These attempts are made as the normal user. Trying them as root has the exact same results. I have installed postgres via Homebrew on OS X, and I have read the instructions multple times.

On some Mac OS X flavours, with some installation methods, I think the user is created as `postgres_` instead. Note trailing underscore. Also, are you sure you (or the installer)actually created the PostgreSQL instance and started the database? — Craig Ringer, Jan 15 '14 at 02:32
I was too unable to switch to `postgres` user. Solved the problem after installing `postgresql-server` package. — builder-7000, May 22 '19 at 04:43
@krystah I believe you should rather mark kangkyu's answer at the correct answer. That's the most appropriate solution to the above query. — ikartik90, Jan 03 '21 at 18:00

danielM · Accepted Answer · 2015-06-13T14:41:42.570

105

psql: Logs me in with my default username

psql -U postgres: Logs me in as the postgres user

Sudo doesn't seem to be required for me.

I use Postgres.app for my OS X postgres database. It removed the headache of making sure the installation was working and the database server was launched properly. Check it out here: http://postgresapp.com

Edit: Credit to @Erwin Brandstetter for correcting my use of the arguments.

edited Jun 13 '15 at 14:41

answered Jan 14 '14 at 20:21

danielM

2,462
2
20
21

13

I installed using Brew and it didn't create a user called postgres or _postgres (as far as I can see) so this failed for me. @kangkyu solution explains and walks you through the issues - assuming you don't have postgres user then: `psql postgres` and `psql -d postgres`. – notapatch Oct 18 '15 at 15:27

score 43 · Answer 2 · answered Sep 18 '15 at 20:47

By psql --help, when you didn't set options for database name (without -d option) it would be your username, if you didn't do -U, the database username would be your username too, etc.

But by initdb (to create the first database) command it doesn't have your username as any database name. It has a database named postgres. The first database is always created by the initdb command when the data storage area is initialized. This database is called postgres.

So if you don't have another database named your username, you need to do psql -d postgres for psql command to work. And it seems it gives -d option by default, psql postgres also works.

If you have created another database names the same to your username, (it should be done with createdb) then you may command psql only. And it seems the first database user name sets as your machine username by brew.

psql -d <first database name> -U <first database user name>

or,

psql -d postgres -U <your machine username>
psql -d postgres

would work by default.

Still works. I installed postgres 13 with `homebrew` and this one has worked for me. — ufukty, Feb 09 '22 at 18:41

score 19 · Answer 3 · answered Jan 15 '14 at 02:56

19

OS X tends to prefix the system account names with "_"; you don't say what version of OS X you're using, but at least in 10.8 and 10.9 the _postgres user exists in a default install. Note that you won't be able to su to this account (except as root), since it doesn't have a password. sudo -u _postgres, on the other hand, should work fine.

answered Jan 15 '14 at 02:56

Gordon Davisson

118,432
16
123
151

Thanks! This is the answer I was looking for. Is there anything I can do to rename the user to just "postgres"? Logging in as that _postgres user leads to psql complaining that the "_postgres" user doesn't exist. I'm trying to restore a clean postgres instance with a backup file from another database – Chris Reyes - he-him Jan 23 '18 at 19:05
1

@ChrisReyes Changing the name of system accounts may break system components that use them, so I'd strongly recommend against it. You could create another user account with the name "postgres" and use that, but if I understand right (which I might not), what you're running into is that the user account *within the postgres database* is not named _postgres. AIUI the account names within the DB are separate from the system account names, so there shouldn't be a problem running a DB instance under the "_postgres" system account, and opening that DB with the "postgres" database account. – Gordon Davisson Jan 23 '18 at 20:18

score 7 · Answer 4 · answered Mar 29 '20 at 17:49

7

For me This was the solution on macOS ReInstall the psql

brew install postgres

Start PostgreSQL server

pg_ctl -D /usr/local/var/postgres start

Initialize DB

initdb /usr/local/var/postgres

If this command throws an error the rm the old database file and re-run the above command

rm -r /usr/local/var/postgres

Create a new database

createdb postgres_test
psql -W postegres_test

You will be logged into this db and can create a user in here to login

answered Mar 29 '20 at 17:49

Adnan Fayaz

139
2
9

Great Answer! Helped me. There's a small typo in create-a-new-database: `createdb postgres_test`and then `psql -W postgres_test`. You have an extra "e" in postegres_test. – Fredster May 31 '21 at 15:37
@Fredster Glad, it helped you! – Adnan Fayaz Jun 04 '21 at 13:52

score 5 · Answer 5 · answered Apr 16 '14 at 12:58

5

psql -U postgres

Worked fine for me in case of db name: postgres & username: postgres. So you do not need to write sudo.

And in the case other db, you may try

psql -U yourdb postgres

As it is given in Postgres help:

psql [OPTION]... [DBNAME [USERNAME]]

answered Apr 16 '14 at 12:58

cloudberry

89
1
3

1

My case: `$ psql -U postgres` generates output `psql: FATAL: role "postgres" does not exist`, but `psql postgres` works. – Frozen Flame Aug 25 '15 at 05:54
I got error `psql: error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?` – Muaath Aug 07 '21 at 14:56

score 4 · Answer 6 · edited Apr 13 '17 at 12:42

I get exactly the same errors as kryshah with su - postgres and sudo -u postgres psql. DanielM's answer gives also errors.

Outputs when wrong settings

Answer however from przbabu's comment.

masi$ psql
psql: FATAL:  database "masi" does not exist
masi$ psql -U postgres
psql: FATAL:  role "postgres" does not exist
masi$ psql postgres
psql (9.4.1)
Type "help" for help.

I think the some part of this problem may be in owner settings in OSX

masi$ ls -al /Users/
total 0
drwxr-xr-x   7 root      admin  238 Jul  3 09:50 .
drwxr-xr-x  37 root      wheel 1326 Jul  2 19:02 ..
-rw-r--r--   1 root      wheel    0 Sep 10  2014 .localized
drwxrwxrwt   7 root      wheel  238 Apr  9 19:49 Shared
drwxr-xr-x   2 root      admin   68 Jul  3 09:50 postgres
drwxr-xr-x+ 71 masi      staff 2414 Jul  3 09:50 masi

but doing sudo chown -R postgres:staff /Users/postgres gives chown: invalid user: ‘postgres:staff’.

In short, this is not the solution the problem. Use the tools provided by the postgres installation to create a user and database.

To get right settings and outputs

There are specific commands after postgres installation to add a new user to the database system. After initdb, run the following as described here

createuser --pwprompt postgres
createdb -Opostgres -Eutf8 masi_development
psql -U postgres -W masi_development

To avoid the password request all the time, you have three choices as described here.

score 3 · Answer 7 · edited May 23 '17 at 10:31

3

the discussion and answer here was massively helpful to me:

psql: FATAL: database "<user>" does not exist

edited May 23 '17 at 10:31

Community

1
1

answered Mar 27 '14 at 16:08

daroo

342
4
8

2

And this one https://stackoverflow.com/questions/17633422/psql-fatal-database-user-does-not-exist/46951367#46951367 – unom Oct 26 '17 at 10:01

score 0 · Answer 8 · edited Oct 16 '22 at 20:30

0

You can try this to create a new role, you can name :

sudo adduser <new_user_role>

edited Oct 16 '22 at 20:30

buddemat

4,552
14
29
49

answered Oct 11 '22 at 09:16

Majid Mohammed

1

score 0 · Answer 9 · answered Jun 21 '23 at 13:50

This video helped me. While using "initdb" you need to use --username="username", here you can use any username. When you initialize another db instance with this utility then there is no default user for that which you need to tell postgres.

You can use this: initdb -D "location of data directory" --username=youruser -W

To have a clear understanding of this you can watch: https://www.youtube.com/watch?v=tdijTz8n5ok

score 0 · Answer 10 · answered Aug 16 '23 at 11:18

0

Install postgres-server:

yum install postgresql-server -y

answered Aug 16 '23 at 11:18

yogender

496
4
7

score -1 · Answer 11 · answered Nov 08 '16 at 11:04

-1

The solution is simple:
log in as root
and after:

su - postgres

psql

answered Nov 08 '16 at 11:04

el fuser

606
1
6
10

Postgres user does not exist?

11 Answers11

Outputs when wrong settings

To get right settings and outputs

Linked