I have just installed postgresql and I specified password x during installation. When I try to do createdb
and specify any password I get the message:
createdb: could not connect to database postgres: FATAL: password authentication failed for user
Same for createuser
.
How should I start? Can I add myself as a user to the database?
postgres
user. It's the SU
of PostgreSQL, and it opens you up for login-attacks against it. Create another super-user, and reserve the locked postgres
for UNIX admins with root.
The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.
Connect to the default database with user postgres: sudo -u postgres psql template1 Set the password for user postgres, then exit psql (Ctrl-D): ALTER USER postgres with encrypted password 'xxxxxxx'; Edit the pg_hba.conf file: sudo vim /etc/postgresql/9.1/main/pg_hba.conf and change "peer" to "md5" on the line concerning postgres: local all postgres peer md5 To know what version of postgresql you are running, look for the version folder under /etc/postgresql. Also, you can use Nano or other editor instead of VIM. Restart the database : sudo /etc/init.d/postgresql restart (Here you can check if it worked with psql -U postgres). Create a user having the same name as you (to find it, you can type whoami): sudo createuser -U postgres -d -e -E -l -P -r -s
Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgres
from the shell (console or ssh).
$ psql -U postgres
Then you would just create a new database as usual:
CREATE ROLE myuser LOGIN password 'secret';
CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER myuser;
This should work without touching pg_hba.conf
. If you want to be able to do this using some GUI tool over the network - then you would need to mess with pg_hba.conf
.
psql: FATAL: Peer authentication failed for user "postgres"
even with sudo.
psql -d mydatatabase -U myuser
.
There are two methods you can use. Both require creating a user and a database.
Using createuser and createdb, $ sudo -u postgres createuser --superuser $USER
$ createdb mydatabase
$ psql -d mydatabase
Using the SQL administration commands, and connecting with a password over TCP $ sudo -u postgres psql postgres
And, then in the psql shell CREATE ROLE myuser LOGIN PASSWORD 'mypass';
CREATE DATABASE mydatabase WITH OWNER = myuser;
Then you can login, $ psql -h localhost -d mydatabase -U myuser -p
Sidenote: the postgres user
I suggest NOT modifying the postgres
user.
It's normally locked from the OS. No one is supposed to "log in" to the operating system as postgres. You're supposed to have root to get to authenticate as postgres. It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as postgres which is the PostgreSQL equivalent of SQL Server's SA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway. By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.
sudo -u postgres createuser -U postgres -d -e -E -l -P -r -s $(whoami)
-s
makes the user a superuser.
This is my solution:
su root
su postgres
psql
sudo bash
.
EDIT: Warning: Please, read the answer posted by Evan Carroll. It seems that this solution is not safe and not recommended.
This worked for me in the standard Ubuntu 14.04 64 bits installation.
I followed the instructions, with small modifications, that I found in http://suite.opengeo.org/4.1/dataadmin/pgGettingStarted/firstconnect.html
Install postgreSQL (if not already in your machine):
sudo apt-get install postgresql
Run psql using the postgres user
sudo –u postgres psql postgres
Set a new password for the postgres user:
\password postgres
Exit psql
\q
Edit /etc/postgresql/9.3/main/pg_hba.conf and change:
#Database administrative login by Unix domain socket local all postgres peer
To:
#Database administrative login by Unix domain socket local all postgres md5
Restart postgreSQL:
sudo service postgresql restart
Create a new database
sudo –u postgres createdb mytestdb
Run psql with the postgres user again:
psql –U postgres –W
List the existing databases (your new database should be there now):
\l
postgres
user is a bad idea. stackoverflow.com/a/41604969/124486
In MacOS, I followed the below steps to make it work.
For the first time, after installation, get the username of the system.
$ cd ~
$ pwd
/Users/someuser
$ psql -d postgres -U someuser
Now that you have logged into the system, and you can create the DB.
postgres=# create database mydb;
CREATE DATABASE
postgres=# create user myuser with encrypted password 'pass123';
CREATE ROLE
postgres=# grant all privileges on database mydb to myuser;
GRANT
If you're running macOS like I am, you may not have the postgres user.
When trying to run sudo -u postgres psql
I was getting the error sudo: unknown user: postgres
Luckily there are executables that postgres provides.
createuser -D /var/postgres/var-10-local --superuser --username=nick
createdb --owner=nick
Then I was able to access psql
without issues.
psql
psql (10.2)
Type "help" for help.
nick=#
If you're creating a new postgres instance from scratch, here are the steps I took. I used a non-default port so I could run two instances.
mkdir /var/postgres/var-10-local
pg_ctl init -D /var/postgres/var-10-local
Then I edited /var/postgres/var-10-local/postgresql.conf
with my preferred port, 5433.
/Applications/Postgres.app/Contents/Versions/10/bin/postgres -D /Users/nick/Library/Application\ Support/Postgres/var-10-local -p 5433
createuser -D /var/postgres/var-10-local --superuser --username=nick --port=5433
createdb --owner=nick --port=5433
Done!
Note: textdb is the database which you are going to explore with 'alex' user
root@kalilinux:~# sudo su - postgres
postgres=# psql
postgres=# create database testdb;
postgres=# create user alex with password 'alex';
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO alex;`enter code here`
You probably need to update your pg_hba.conf
file. This file controls what users can log in from what IP addresses. I think that the postgres user is pretty locked-down by default.
/var/lib/pgsql/data/pg_hba.conf
. Change ident
to trust
for host
(and local
if you are using ssh).
Just browse up to your installation's directory and execute this file "pg_env.bat", so after go at bin folder and execute pgAdmin.exe. This must work no doubt!
Success story sharing
pg_hba.conf
must be the first line, otherwise it could get ignored because of other rules (pgsql 9.3.5 on F21). It took me some time to figure this out, that uncommenting and changing already-commented-out lines for postgresql user will not simply do.