Create a "postgres" user account on OSX?

Question

I just made a mistake: I installed PostgreSQL on Mac OSX 10.6.7 and when I wanted to connect to it I did su postgres, then it asked me for a password and I did sudo passwd postgres to change the password to something I know.

The problem is, as explained here, this created a Unix user account for postgres and afterwards I had two users on my Mac : me and postgres.

So I deleted the postgres account. Now I cannot connect to postgres because I just cannot su postgres. How to recreate a "locked" user account postgres that I could use safely (by doing sudo su postgres as I should have done before) ?

Edit: added version of Mac OS X

Solution

from the following stack overflow:

https://stackoverflow.com/questions/15301826/psql-fatal-role-postgres-does-not-exist

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;

Comments

[Admin]

Uninstall and reinstall PostgreSQL? That's probably the safest route, given that you'd need to assign the correct user ID etc. Other than that, this question contains command line calls on how to create user accounts manually, and the answer explains how to not assign a password. It worked on my machine when I tested it, so chances are it'll work on yours.

[Admin]

It'd be helpful if you'd mention the major release of OS X you're using. Just, you know, things change over 10+ years.

[Admin]

@DanielBeck, thanks. I installed PostgreSQL from source on macOS, so now I need to create a user named postgres. What value should I use for the postgres user's UniqueID? I don't think it matters much, but what number would you suggest and why? And should I also create a new group named postgres? If not, then what value should I put for the postgres user's PrimaryGroupID. Why does macOS come with the user _postgres and the group _postgres but not PostgreSQL itself? Should I just use the _postgres user instead of creating a new one named postgres?

[ma11hew28]

The question is how to create a macOS user under which to run PostgreSQL, not how to create a PostgreSQL database role. See Chapter 18. Server Setup and Operation: 18.1. The PostgreSQL User Account vs the PostgreSQL command CREATE ROLE. (Note: The PostgreSQL command CREATE USER "is now an alias for CREATE ROLE.")