Skip to content

Instantly share code, notes, and snippets.

@necojackarc
Last active October 8, 2019 20:36
Show Gist options
  • Save necojackarc/8c2ec0a7c472acc2639ff8010f99a87d to your computer and use it in GitHub Desktop.
Save necojackarc/8c2ec0a7c472acc2639ff8010f99a87d to your computer and use it in GitHub Desktop.

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment