github.com/bcampbell/scrapeomat@v0.0.0-20220820232205-23e64141c89e/store/sqlstore/README.pg (about)

     1  Postgres notes
     2  ==============
     3  
     4  
     5  Permissions setup
     6  -----------------
     7  
     8  Postgresql can be picky on permissions and access.
     9  You might have your own way of working with it, but if not,
    10  here are my notes for setting up a development db:
    11  
    12  I create a new postgresq user ("scrape"):
    13  
    14      $ sudo -u postgres createuser --no-superuser --no-createrole --no-createdb scrape
    15      $ sudo -u postgres psql -c "ALTER ROLE scrape WITH PASSWORD 'SooperSecretPassword'"
    16  
    17  I then set up a pg_ident mapping to allow my usual unix account ("ben")
    18  to connect as that pg user.
    19  So, in /etc/postgresql/x.y/main/pg_ident.conf:
    20  
    21      # MAPNAME   SYSTEM-USERNAME    PG-USERNAME
    22  
    23      scrapedev   ben                     scrape
    24  
    25  To create database ("nzarts":
    26  
    27      $ sudo -u postgres createdb -O scrape -E utf8 nzarts
    28  
    29  And then set up my new database to use that pg_ident mapping.
    30  In /etc/postgresql/x.y/main/pg_hba.conf:
    31  
    32      local   nzarts       scrape      peer map=scrapedev
    33  
    34  
    35  Reload the postgresql config to make the changes take effect:
    36      $ sudo /etc/init.d/postgresql reload
    37  
    38  ...or systemctl or whatever:
    39  
    40      $ sudo systemctl reload postgresql
    41  
    42  Then load the base schema:
    43  
    44      $ cat pg/schema.sql | psql -U scrape nzarts
    45