Wednesday 17 December 2014

Reseting Postgres user password - create root user and grant superuser

As root user on the system, edit the pg_hba.conf
# vi /var/lib/pgsql/data/pg_hba.conf

# This file is managed by Puppet. DO NOT EDIT.

# Rule Name: local access as postgres user
# Description: none
# Order: 001
#local all postgres  ident 
local all postgres  md5 

# sudo -u postgres psql
psql (8.4.20)
Type "help" for help.

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# \quit

# psql -U postgres -W
Password for user postgres: 
psql (8.4.20)
Type "help" for help.

postgres=# create user root with password '';
CREATE ROLE
postgres=# alter user root with superuser;
ALTER ROLE
postgres=#