Based on PostgreSQL 9.5.6 | Official 9.5 Documentation

Context I’m scraping reports off of a inventory management system for a client. My approach is scraping and cleaning HTML tables using python, selenium, the pandas library and the beautifulsoup library. To store this data, I’m going to be using PostgreSQL. This was me getting up to speed on PostgreSQL.

Installing Postgres ¶

#on mac osx
$ brew install postgresql

#on ubuntu (16.04+)
$ sudo apt install postgresql

Default configuration ¶

After installing postgresql, a system-wide postgres user is created. Postgres uses ident authentication by default. This means Postgres assumes a postgres role with the same name as the user. It also create a database with the same name -> postgres.

So user ‘postgres’ has the role ‘postgres’ and is the owner of the database ‘postgres’.

$ sudo su - postgres
$ psql #enter psql shell

Basic commands in the psql shell ¶

/* Create a new role with the name 'newrole' that is allowed to login, but requires no password */
# CREATE USER newrole; 


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


/* Check connection */
# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"


/* Alter roles */
# ALTER ROLE newrole createdb;
# ALTER ROLE newrole replication;


/* List all databases */
# \l
                                        List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres


/* Create new database. By default the owner will be the user that you used to create the database from */
# CREATE DATABASE newdatabase;


/* Change the owner of the database */
# ALTER DATABASE newdatabase OWNER TO newrole;

/* Grant all database priviliges to role */
# GRANT ALL ON DATABASE inventory TO inventory;

/* Finally shoud look like this */
# \l

                                       List of databases
   Name        |   Owner     | Encoding |   Collate   |    Ctype    |   Access privileges
---------------+-------------+----------+-------------+-------------+-----------------------
  newdatabase  | newrole     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/inventory        +
               |             |          |             |             | inventory=CTc/inventory
  postgres     | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
  template0    | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |             |          |             |             | postgres=CTc/postgres
  template1    | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |             |          |             |             | postgres=CTc/postgres
(4 rows)

Now you’re no longer using the default database, and have a limited access user for accessing your newly created database.

pg_hba.conf ¶

From obervations, it seems a Postgresql installation on Ubuntu by default is set to authenticate via a ‘peer’ connection (i.e. need a system user that matches the database user). While a Mac OSX installation allows ‘trust’ connections (i.e. trust all local connections regardless of username).

These and other configuration options are specified in the pg_hba.conf file.

Location of the pg_hba.conf file:

• ubuntu: /etc/postgresql/[version number]/[cluster name]/pg_hba.conf
• osx: /usr/local/var/postgres/pg_hba.conf (installed via brew)

On Ubuntu, in the pg_hba.conf file, for local socket connections, under METHOD, change it from peer to trust for local development, as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

Make sure to restart postgresql after any changes to this file:

• ubuntu: sudo service postgresql restart or sudo systemctl reload postgresql
• osx: brew services restart postgresql

To make sure postgres starts on boot:

• ubuntu: sudo systemctl enable postgresql
• osx: more complicated, see here

Now you can set alias(es) to quickly access your databases using psql. In your .bashrc/.bash_profile/.bash_aliases file:

alias pgnewrole="psql newdatabase -U newrole"