Scratch notes for setting up a user account in Postgres (Ubuntu Server)

postgres_notes.txt

================================================================================
Postgres Notes
================================================================================
1.  Configure a Postgresql user account and database

➜  ~  sudo -i -u postgres
[sudo] password for jfhogarty:
postgres@LeftFootMedia:~$ psql
psql (9.3.10)
Type "help" for help.

postgres=# SELECT rolname FROM pg_roles;
  rolname
------------
 postgres
 jfhogarty
 cnxdb_user
(3 rows)

postgres=# select * from pg_shadow;
  usename   | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil | useconfig
------------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
 postgres   |       10 | t           | t        | t         | t       |                                     |          |
 jfhogarty  |    16384 | t           | t        | t         | f       |                                     |          |
 cnxdb_user |    16386 | t           | t        | t         | f       | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |          |
(3 rows)

postgres=# SELECT rolname FROM pg_roles;
  rolname
------------
 postgres
 jfhogarty
 cnxdb_user
(3 rows)

postgres=# \q


postgres@LeftFootMedia:~$ createuser -P -e -d your_user_name_here
Enter password for new role:
Enter it again:
CREATE ROLE ognt_user PASSWORD 'enter_your_password_here_make_it_so' NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;
 press ENTER or type command to continue

postgres@LeftFootMedia:~$ psql
psql (9.3.10)
Type "help" for help.

postgres=# SELECT rolname FROM pg_roles;
  rolname
------------
 postgres
 jfhogarty
 cnxdb_user
 ognt_user
(4 rows)

postgres=# CREATE DATABASE ogntdb OWNER ognt_user;
CREATE DATABASE
postgres=# \q
postgres@LeftFootMedia:~$
postgres@LeftFootMedia:~$ exit
logout
➜  ~ sudo vim /etc/postgresql/9.3/main/pg_hba.conf

[UPDATE AS NEEDED - SEE OTHER ACCOUNTS]
Example:
{--snip--}
# Database administrative login by Unix domain socket
local      all          postgres                      peer
local      all          cnxdb_user                    md5
local      all          ognt_user                     md5
{--snip--}

➜  ~  sudo /ect/init.d/postgresql restart
sudo: /ect/init.d/postgresql: command not found
➜  ~  sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                                                                                                                     [ OK ]
➜