Postgres

Using PostgRest

version: '2'
services:
  dashboard:
    container_name: dashboard
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    links:
      - db:db
    environment:
      PGRST_DB_URI: postgresql://USER:PASSWORD@db:5432/workflow_network
      PGRST_DB_SCHEMA: repository
      PGRST_DB_ANON_ROLE: workflow_network_data_writer
    depends_on:
        - db
  db:
    container_name: db
    image: postgres:9.6
    ports:
      - "5434:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./src/main/resources/db/init:/docker-entrypoint-initdb.d
  swagger:
    container_name: swagger
    image: swaggerapi/swagger-ui
    ports:
      - "9080:8080"
    expose:
      - "9080"
    environment:
      API_URL: http://localhost:3000/

Handy Postgres scripts

• Consolidate usernames like in Activation service
• Please add flyway profile to the pom such that we can test your changes in a easy manner.

Validate permissions as following:

• Can flyway user drop table?
• Is flyway_schema_history accessible for app user?
• Can app user drop the table?
• Can app user SELECT record from the table 'site' inside the schema?
• Can app user INSERT record from the table 'site' inside the schema?
• Can app user UPDATE record from the table 'site' inside the schema?
• Can app user DELETE record from the table 'site' inside the schema?

Check priviliges

# Check user cannot drop table
psql -h localhost -U app_user -W -d activations -c "begin; drop table activation_data.site; rollback;"


# Check user has no access to 
psql -h localhost -U app_user -W -d activations -c "begin; select * from  activation_data.flyway_schema_history; rollback;"


# can user SELECT
psql -h localhost -U app_user -W -d activations -c "select * from  activation_data.site;"


# Can user INSERT
psql -h localhost -U app_user -W -d activations -c "INSERT INTO activation_data.site(s_name) VALUES   ('martin');"


# Can user UPDATE
psql -h localhost -U app_user -W -d activations -c "update activation_data.site set s_name ='vijay' WHERE s_name = 'martin';"

# Can user DETELE
psql -h localhost -U app_user -W -d activations -c "delete from activation_data.site WHERE s_name ='vijay';"

PSQL Commands

# Connecting to a database using PSQL
psql -h localhost -p 5432 -U zalando -d zalando

# Quiting PSQL
\q

# Changing schema path
ALTER ROLE zalando SET search_path TO zfadvice_data, public;

# Listing schema
\dn