I'm setting up my PostgreSQL 9.1. I can't do anything with PostgreSQL: can't createdb , can't createuser ; all operations return the error message Fatal: role h9uest does not exist h9uest is my account name, and I sudo apt-get install PostgreSQL 9.1 under this account. Similar error persists for the root account.

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

Answers (19)

Reputation: 1961

For those that are getting this error when spinning up a Docker container with PostgreSQL, you need to make sure that there aren't existing processes on port 5432 (most commonly used port for PostgreSQL for some reason), or whichever port your database is being exposed through:

sudo lsof -i :5432
- There should only be one process here, com.docke (in your terminal output the full process name is cut off)
- If there are more than one, kill them, these are probably from legacy PostgreSQL installations on your machine, or from you forgetting to properly kill some previous process: sudo kill [pid] (you will see PID from the lsof command above)

I actually found this solution from the following Medium article.

Upvotes: 2

ParaMeterz

Reputation: 9907

You can simply try the below command and you will be able to log in as that user.

sudo -u username bash

Upvotes: 1

Erwin Brandstetter

Reputation: 658392

Use the operating system user postgres to create your database - as long as you haven't set up a database role with the necessary privileges that corresponds to your operating system user of the same name (h9uest in your case):

sudo -u postgres -i

As recommended here or here.

Then try again. Type exit when done with operating as system user postgres.

Or execute the single command createuser as postgres with sudo, like demonstrated by dsrees in another answer.

The point is to use the operating system user matching the database role of the same name to be granted access via ident authentication. postgres is the default operating system user to have initialized the database cluster. The manual:

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.

I have heard of odd setups with non-standard user names or where the operating system user does not exist. You'd need to adapt your strategy there.

Read about database roles and client authentication in the manual.

Upvotes: 1087

Boy Nandi

Reputation: 103

sudo -u postgres createuser --superuser $USER

sudo -u postgres createdb $USER

This should definitely work for you.

Upvotes: 5

Siwei

Reputation: 21557

for those who using docker and correctly followed the instructions from official doc, if you still met this problem, RESTART windows and try again.

Upvotes: 0

Jan

Reputation: 16184

I did a healthcheck with docker-compose.

healthcheck:
  test: ['CMD-SHELL', 'pg_isready']
  interval: 5s
  timeout: 5s
  retries: 5

If you also have that change the user:

healthcheck:
  test: ['CMD-SHELL', 'pg_isready -U postgres'] # <<<---
  interval: 5s
  timeout: 5s
  retries: 5

Upvotes: 33

dsrees

Reputation: 6286

After trying many other people's solutions, and without success, this answer finally helped me.

https://stackoverflow.com/a/16974197/2433309

In short, running

sudo -u postgres createuser owning_user

creates a role with name owning_user (in this case, h9uest). After that you can run rake db:create from the terminal under whatever account name you set up without having to enter into the Postgres environment.

Upvotes: 360

srghma

Reputation: 5353

dump and restore with --no-owner --no-privileges flags

e.g.

dump - pg_dump --no-owner --no-privileges --format=c --dbname=postgres://userpass:username@postgres:5432/schemaname > /tmp/full.dump

restore - pg_restore --no-owner --no-privileges --format=c --dbname=postgres://userpass:username@postgres:5432/schemaname /tmp/full.dump

Upvotes: 2

Abel O'Ryan

Reputation: 4242

psql postgres

postgres=# CREATE ROLE username superuser;
postgres=# ALTER ROLE username WITH LOGIN;

Upvotes: 40

Andriy Tolstoy

Reputation: 6100

For Windows users : psql -U postgres

You should see then the command-line interface to PostgreSQL: postgres=#

Upvotes: 11

Kalyan Halder

Reputation: 1585

For version Postgres 9.5 use following comand:

psql -h localhost -U postgres

Hope this will help.

Upvotes: 23

Natacha

Reputation: 1250

Manually creating a DB cluster solved it in my case.

For some reason, when I installed postgres, the "initial DB" wasn't created. Executing initdb did the trick for me.

This solution is provided in the PostgreSQL Wiki - First steps:

initdb

Typically installing postgres to your OS creates an "initial DB" and starts the postgres server daemon running. If not then you'll need to run initdb

Upvotes: 5

Henry

Reputation: 3328

I installed it on macOS and had to:

cd /Applications/Postgres.app/Contents/Versions/9.5/bin
createuser -U postgres -s YOURUSERNAME
createdb YOURUSERNAME

Here's the source: https://github.com/PostgresApp/PostgresApp/issues/313#issuecomment-192461641

Upvotes: 8

Mohideen bin Mohammed

Reputation: 20196

Working method,

vi /etc/postgresql/9.3/main/pg_hba.conf
local all postgres peer here change peer to trust
restart, sudo service postgresql restart
now try, psql -U postgres

Upvotes: 19

Loli Pronoms

Reputation: 11

Follow These Steps and it Will Work For You :

run msfconsole
type db_console
some information will be shown to you chose the information who tell you to make: db_connect user:pass@host:port.../database sorry I don't remember it but it's like this one then replace the user and the password and the host and the database with the information included in the database.yml in the emplacement: /usr/share/metasploit-framework/config
you will see. rebuilding the model cache in the background.
Type apt-get update && apt-get upgrade after the update restart the terminal and lunch msfconsole and it works you can check that by typing in msfconsole: msf>db_status you will see that it's connected.

Upvotes: -3

Mohammed Saleem

Reputation: 2013

sudo su - postgres

psql template1

creating role on pgsql with privilege as "superuser"

CREATE ROLE username superuser;
eg. CREATE ROLE demo superuser;

Then create user

CREATE USER username; 
eg. CREATE USER demo;

Assign privilege to user

GRANT ROOT TO username;

And then enable login that user, so you can run e.g.: psql template1, from normal $ terminal:

ALTER ROLE username WITH LOGIN;

Upvotes: 185

Miles Erickson

Reputation: 2595

Installing postgres using apt-get does not create a user role or a database.

To create a superuser role and a database for your personal user account:

sudo -u postgres createuser -s $(whoami); createdb $(whoami)

Upvotes: 117

Robert Cambil

Reputation: 99

In local user prompt, not root user prompt, type

sudo -u postgres createuser <local username>

Then enter password for local user.

Then enter the previous command that generated "role 'username' does not exist."

Above steps solved the problem for me. If not, please send terminal messages for above steps.

Upvotes: 8

mateusz.szymborski

Reputation: 1720

This works for me:

psql -h localhost -U postgres

Upvotes: 153

PostgreSQL error: Fatal: role &quot;username&quot; does not exist

Answers (19)

Related Questions

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