postgres no users or roles, can't create but works with rails?

Question

I'm trying to setup postgres on OSX. I was having problems with a rails app ('cannot connect to database') and reinstalled postgres with homebrew. I cannot issue postgres commands from terminal now though:

$ sudo -u _postgres psql
Password:
psql: FATAL:  role "_postgres" does not exist
$ brew list
autoconf    libksba     openssl     python      sqlite
automake    libtool     ossp-uuid   rbenv
gdbm        libyaml     pkg-config  readline
libgpg-error    mongodb     postgresql  ruby-build
$ postgresql
zsh: command not found: postgresql
$ psql
psql: FATAL:  database "connorleech" does not exist

This command does work though:

$ sudo -u _postgres
usage: sudo -h | -K | -k | -L | -V
usage: sudo -v [-AknS] [-g groupname|#gid] [-p prompt] [-u user name|#uid]
usage: sudo -l[l] [-AknS] [-g groupname|#gid] [-p prompt] [-U user name] [-u
            user name|#uid] [-g groupname|#gid] [command]
usage: sudo [-AbEHknPS] [-C fd] [-g groupname|#gid] [-p prompt] [-u user
            name|#uid] [-g groupname|#gid] [VAR=value] [-i|-s] [<command>]
usage: sudo -e [-AknS] [-C fd] [-g groupname|#gid] [-p prompt] [-u user
            name|#uid] file ...

After I reinstalled postgresql with homebrew my rails app began working with this app/config/database.yml:

development:
  adapter: postgresql
  encoding: utf8
  host: localhost
  database: project_development
  pool: 5
  username: 
  password:

Questions:

1. How do I issue postgres commands from the terminal?
2. How is rails running an app if I never created a local postgres user or database?

Almeria

Why do you put an underscore before "postgres" ? Try sudo -u postgres psql instead. Or if your OSX user is actually called _postgres: sudo -u _postgres psql postgres – Zinn

$ sudo -u postgres psql gives error: sudo: unknown user: postgres – Almeria

In that case: try the second one. – Zinn

$ sudo -u _postgres psql postgres after my password gives: psql: FATAL: role "_postgres" does not exist – Almeria

MY bad. Yet another one: sudo -u _postgres psql -u postgres postgres EXPLANATION: postgres has it's own administration for users. Which might differ from the unix user-adminstration. Your OS has named your postgres-user "_postgres"; in pg it is called "postgres". The database "superuser" is also called "postgres". If no username is given when logging on to the DBMS, the DBMS assumes the system-username ( "_postgres" in your case) which does not exist inside the DBMS administration of users. – Zinn

Answer 1

If you installed postgres from here for Mac OSX try this:

1. From root in bash "psql"
2. This will log on in as the user that the app created (in my case this was my mac user name e.g. "jamesbrown")
3. you'll be at a prompt like jamesbrown=#... enter "\du"
4. you'll now see all the roles, mine included jamesbrown and _postgres
5. if you want to add superuser to _postgres, "alter user _postgres superuser;"

Antediluvian

Answer 2

From Homebrew FAQ:

Homebrew is designed to work without using sudo. You can decide to use it but we strongly recommend not to do so. If you have used sudo and run into a bug then it is likely to be the cause...

Your first idea of using sudo -u _postgres psql does not apply to homebrew. I believe it applies to PostgreSQL as packaged by Apple (which ships with OS X Server Edition, not with the desktop version)

The brew postgresql package doesn't need or create users named _postgres or postgres, it suggests that you run the server under your own OS user.

/usr/local/bin/psql alone should successfully launch and connect to your default database if the server is running and there's no other configuration problem.

See also the output of brew info postgresql, it gives the required bits of information to start.

Foumart