PostgreSQL Initial Setup.md

Describes what I always do when installed PostgreSQL.

1. Create new user who has the same power as postgres
2. Change login method when connecting it via host names

1. Create new user who has the same power as postgres

Connect postgres DB as postgres user:

sudo su postgres
psql

or

psql -h localhost -U postgres

Roles that new user should have are as follows:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then, execute the following command:

postgres=# CREATE ROLE necojackarc WITH SUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;

To confirm if it succeeded:

postgres=# \du
                                   List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 necojackarc | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Done!

2. Change login method when connecting it via host names

For development, I usually use the password login, so enable it when connecting PostgreSQL via host names.

$ sudo vi /etc/postgresql/9.6/main/pg_hba.conf 

Change them from:

host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

to:

host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password

To reflect the changes, run sudo service postgresql restart.

That's it!