1

Using openresty/openresty:latest in Dockerfile:

FROM openresty/openresty:latest

COPY default-prod.conf /etc/nginx/conf.d/default.conf


RUN apt-get update && apt-get install -y luarocks


RUN luarocks install luasocket
RUN luarocks install pgmoon
RUN luarocks install lua-cjson
RUN luarocks install lua-resty-openssl

and networks: in my docker-compose.yml

  smalljobsdb:
    image: postgres:14
    container_name: smalljobsdb
    env_file:
      - .env.production
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    ports:
      - "5450:5432"
    volumes:
      - ./db-production/postgresql:/var/lib/postgresql/data
    networks:
      - smalljobs

nginx:
    build:
      context: ./nginx
      dockerfile: Dockerfile.prod
    container_name: nginx
    restart: always
    # network_mode: host
    networks:
      - smalljobs
    volumes:
      # - ./certbot/www/:/var/www/certbot/:ro
      - ./server/shared:/usr/share/nginx/html
      - ./nginx/cert:/etc/nginx/ssl:ro
      - storefrontbuild:/usr/share/nginx/html/storefront
    ports:
      - 80:80
      - 443:443
    depends_on:
      smalljobsdb:
        condition: service_started
      smalljobsserver:
        condition: service_started
      smalljobsstorefront:
        condition: service_completed_successfully

when I try to connect to my postgres db during rendering my website in my nginx.conf file:

location ~ ^/[0-9]+$ {
        default_type text/html;
        resolver 127.0.0.11 valid=30s;
        content_by_lua_block {
            local id = ngx.var.uri:match("^/([0-9]+)$")  -- Extract the ID from URI

        if id then
            local cjson = require "cjson"
            local pgmoon = require("pgmoon")

            -- Set up the PostgreSQL connection
            local pg = pgmoon.new({
                host     = "smalljobsdb",
                port     = "", << DELETED BUT ITS USED
                user     = "", << DELETED BUT ITS USED
                password = "", << DELETED BUT ITS USED
                database = ""  << DELETED BUT ITS USED
            })

            ngx.log(ngx.ERR, "BEFORE CONNECTION " .. id)
            -- Connect to the PostgreSQL database
            assert(pg:connect())
            ngx.log(ngx.ERR, "AFTER CONNECTION " .. id)
            pg:keepalive()

I get err:

nginx                  | 2024/11/08 17:09:27 [error] 7#7: *1 [lua] default.conf:60):17: BEFORE CONNECTION 8, client: 85.193.34.174, server: smalljobs.cz, request: "GET /8 HTTP/1.1", host: "smalljobs.cz"
nginx                  | 2024/11/08 17:09:28 [error] 7#7: *1 [lua] default.conf:60):20: AFTER CONNECTION 8, client: 85.193.34.174, server: smalljobs.cz, request: "GET /8 HTTP/1.1", host: "smalljobs.cz"
nginx                  | 2024/11/08 17:09:28 [error] 7#7: *1 attempt to send data on a closed socket: u:00007F1E11F831F8, c:0000000000000000, ft:0 eof:0, client: 85.193.34.174, server: smalljobs.cz, request: "GET /8 HTTP/1.1", host: "smalljobs.cz"
nginx                  | 2024/11/08 17:09:28 [error] 7#7: *1 attempt to receive data on a closed socket: u:00007F1E11F831F8, c:0000000000000000, ft:0 eof:0, client: 85.193.34.174, server: smalljobs.cz, request: "GET /8 HTTP/1.1", host: "smalljobs.cz"
nginx                  | 2024/11/08 17:09:28 [error] 7#7: *1 lua entry thread aborted: runtime error: content_by_lua(/etc/nginx/conf.d/default.conf:60):24: receive_message: failed to get type: closed
nginx                  | stack traceback:
nginx                  | coroutine 0:
nginx                  |    [C]: in function 'assert'
nginx                  |    content_by_lua(/etc/nginx/conf.d/default.conf:60):24: in main chunk, client: 85.193.34.174, server: smalljobs.cz, request: "GET /8 HTTP/1.1", host: "smalljobs.cz"

WHAT I TRIED:

  • Changing my Nginx container to network_mode: host - failed in beging: upstream smalljobsserver { server smalljobsserver:3034; << smalljobsserver not found }
  • connecting through 127.0.0.1 host and resolver 8.8.8.8 or GOOGLE DNS, or failed with no possible to send connection out

WHAT I WANT Get data from db depend on /:id from URL. Anyhow secured, not spoof able

x-magix
  • 111

1 Answers1

0

after a days of try I found simplest solution, using sh & psql & Lua: content is html of file

                        local handle = io.popen("/etc/nginx/fetchData.sh " .. id)
                        local resp = handle:read("*a")
                        handle:close()

                    if not resp then
                        ngx.say(content)
                        ngx.exit(ngx.HTTP_OK)
                    end

                    -- A simple pattern-based approach
                    local data = {}
                    for key, value in resp:gmatch('%&quot;(.-)&quot;%s*:%s*&quot;([^&quot;]-)&quot;') do
                        data[key] = value
                    end

                    if not data then
                        ngx.say(content)
                        ngx.exit(ngx.HTTP_OK)
                    end

                    content = content:gsub(&quot;TITLEMETATAG&quot;, data.title or &quot;Default Title&quot;)
                    content = content:gsub(&quot;DESCRIPTIONMETATAG&quot;, data.descriptionShort or &quot;Default description&quot;)
                    content = content:gsub(&quot;URLMETATAG&quot;, &quot;https://smalljobs.cz/&quot; .. id)
                    content = content:gsub(&quot;LOCATIONMETATAG&quot;, data.city or &quot;&quot;)
                    content = content:gsub(&quot;PRICEMETATAG&quot;, data.offeredPrice or &quot;&quot;)
                    ngx.say(content)

in my .sh file:

# Sanitize ID by escaping any single quotes (prevent SQL injection)
... IMPORTANT TO SANITIZE ... << GIVE A SEARCH ON INTERNET

Run the PostgreSQL query and capture the output


RESULT=$(PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -A -c "SELECT * FROM &quot;MY_TAB&quot; WHERE id = '$ID';")


if [ $? -ne 0 ]; then
  echo "{&quot;error&quot;: &quot;Failed to execute query&quot;}"
  exit 1
fi


Extract individual columns from RESULT using awk


IFS='|' read -r id description title <<< "$RESULT"


Return the result in JSON format with key-value pairs


echo "{
  &quot;id&quot;: &quot;$id&quot;,
  &quot;description&quot;: &quot;$description&quot;,
  &quot;title&quot;: &quot;$title&quot;
}"
x-magix
  • 111