PostgreSQL의 프로시져(PL/pgSQL) 예제

추후 PostgreSQL에서 프로시져를 작성할 때 참조하기 위해 글을 남깁니다.

CREATE OR REPLACE FUNCTION 
    get_two_rows(id1 TEXT, lyr1 TEXT, id2 TEXT, lyr2 TEXT) 
RETURNS 
TABLE (geom GEOMETRY)
AS $$
BEGIN
	RETURN QUERY EXECUTE
		'SELECT the_geom FROM ' || lyr1 || ' WHERE networkid::text = ''' || id1 || '''' || 
		' UNION ALL ' || 
		'SELECT the_geom FROM ' || lyr2 || ' WHERE networkid::text = ''' || id2 || '''';

END; $$
LANGUAGE PLPGSQL;

SQL문을 문자열 형태로 조합해서 실행하기 위해 ‘QUERY EXECUTE’를 사용해야 하며, 외따옴표(‘)를 문자열에 넣기 위해 연속된 외따옴표 2개가 필요하다는 것에 주의해야 합니다.

사실 위의 프로시져는 단순해서 그냥 SQL문을 통해 Java의 PrepareStatement를 사용해 실행해도 되는데.. PrepareStatement를 통한 SQL 문 실행시 SQL의 문자열 값 중 SELECT의 FROM절에 대한 테이블명은 바인딩할 수 없다(?에 의한..)는 제약때문에 프로시져를 사용하게 되었습니다. 소스코드에 대한 정적검사에서 SQL문에 대한 SQL Injection 보안성 오류로 지적받아.. 위와 같이 상당히 불필요한 프로시져까지 사용하게 되었습니다.

소스코드 정적 분석 대응

개발된 프로그램에 대한 소스코드 분석을 통해 개발자가 놓친 보안 등과 관련된 위험요소를 검출해 주는 프로그램으로 sparrow라는 프로그램이 있습니다. 성과물 제출전에 인수검사를 받는데, 그 과정에서 sparrow라는 프로그램을 통해 위험요소 검출을 자동으로 문서화해 각 개발사에게 전달하고 해결하라고 합니다. 일단 제가 해결해야 할 프로그램의 작성 코드에 대해 검출된 위험 요소를 나열해 봅니다.

  • AvoidCatchingGenericException : 일반적인 예외를 try-catch 블록에서 처리하는 경우 이를 검출합니다.
  • AvoidReassigningParameters : 메소드 작성시 입력받은 파라미터에 재할당 하는 구문을 검출합니다.
  • BAD_EQUALITY_EXPRESSION.FLOAT : Float.compare(numFloat(num), num)로 비교할 것
  • BAD_INIT.MISSING_AFTER_LOCAL_DECLARATION : 지역 변수를 선언 할 경우 반드시 초기화해야 합니다. 플래그 변수, 누적 카운터, 반환 코드를 저장하는 변수 등을 초기화 하지 않았을 경우를 검출합니다.
  • ConstructorCallsOverridableMethod : 부모 클래스의 생성자 안에서 가상 메소드를 호출하게 되면 자식클래스의 필드가 초기화되기 전에 읽게 되는 문제가 발생합니다.
  • EmptyFinallyBlock : Finally 블록이 비어있는 경우, 삭제 가능하므로 이를 검출합니다.
  • EmptyStatementNotInLoop : Loop 문 이외에서 발견되는 비어있는 구문(단일 세미콜론)을 검출합니다.
  • EXPOSURE_OF_SYSTEM_DATA : 시스템이나 디버깅 정보를 드러내는 것은 악의적인 공격을 계획하기 쉽게 만듭니다. 시스템이나, 디버깅 정보는 output stream 이나 logging 기능을 통하여 새어나가게 됩니다.
  • FIELD_ASSIGNED_IN_CONSTRUCTOR_SHOULD_BE_FINAL : synchronization의 과다적용을 자제해야 합니다. 메소드 단위의 synchronization 보다는 블럭 단위의 synchronization 사용을 권장합니다.
  • FORBIDDEN.STATEMENT_EXECUTE_QUERY : java.sql.Statement.executeQuery는 사용되는 쿼리문의 argument 보안에 취약해서 SQL Injection 공격에 노출될 가능성이 있습니다. Statement.excuteQuery() 대신에 PreparedStatement.executeQuery()를 사용하는 것이 더 안전합니다.
  • HARD_CODED_USER_NAME_AND_PASSWORD : 하드코드 된 비밀번호는 위험하다고 간주합니다. java.sql.DriverManager.getConnection에서 암호에 해당하는 파라미터로 상수 문자열이 들어가는 경우 취약하다고 판정합니다. 비밀번호는 암호화하여 별도의 파일에 저장하여 사용하는 것이 바람직합니다.
  • IMPROPER_CHECK_FOR_UNUSUAL_OR_EXCEPTIONAL_CONDITION : 프로그램 수행 중에 함수의 결과 값에 대한 적절한 처리 또는 예외상황에 대한 조건을 적절하게 검사하지 않을 경우, 예기치 않은 문제를 야기 할 수 있습니다.
  • INTEGER_OVERFLOW : 동적 메모리 할당을 위해서 사용되는 변수가 이전 처리 과정에서 오버플로우에 의해서 음수값으로 변환될 경우를 검출합니다. 정수형 변수의 오버플로우는 정수값이 증가하면서, Java에서 허용된 가장 큰 값보다 더 커져서 실제 저장되는 값은 의도하지 않게 아주 작은 수이거나 음수가 될 수 있습니다. 이러한 상황을 검사하지 않고 그 값을 순환문의 조건이나 메모리 할당, 메모리 복사 등에 쓰거나, 그 값에 근거해서 보안 관련 결정을 하면 취약점을 야기할 수 있습니다.
  • LEFTOVER_DEBUG_CODE : 디버거 목적으로 삽입된 코드를 검출합니다. 디버거 목적으로 삽입된 코드가 제거되지 않고 운영상에서 그대로 남아있게 되면, 사용자 식별과정을 우회하거나 의도하지 않은 정보가 유출될 수 있습니다.
  • NULL_RETURN : 널 값이 반환되는 경우 널 확인을 하지 않고 역 참조하는 경우에 경고를 냅니다. 보통 모든 프로그램에서 널을 반환할 수 있는 함수들의 결과들은 항상 널 확인을 하고 사용해야 합니다. 이렇게 하지 않을 경우, 시스템의 기능을 멈추게 할 수도 있습니다.
  • NULL_RETURN_STD : Java standard library 들 중에 널을 반환할 가능성이 있는 메소드 들로부터 값을 특정 변수에 할당한 뒤, 이 변수의 널 확인을 하지 않고 이 변수를 바로 역 참조하는 경우에 경고를 냅니다. 보통 모든 프로그램에서 널을 반환할 수 있는 함수들의 결과들은 항상 널 확인을 하고 사용해야 합니다. 이렇게 하지 않을 경우, 시스템의 기능을 멈추게 할 수도 있습니다.
  • RESOURCE_LEAK : 특정 자원이 할당이 되었지만, Java 가상 머신의 garbage collector에 의해서 빠르게 자원회수가 되지 않을 경우에 발생합니다. 대부분의 Heap memory와 관련된 메모리 관리는 JVM이 책임지지만, Socket, Stream, Channel과 같은 자원은 그렇지 않습니다. 그러므로 이러한 자원은 프로그램 작성자가 적절히 해제를 해주는 코드를 작성하여야 합니다. 그렇지 못할 경우에는 성능저하, 시스템기능의 멈춤, DOS(Denial of Service), 또 다른 자원을 획득함에 있어서의 실패를 야기할 수 있습니다. Resource Leak 관련 CWE 설명: Resource Leak 오류는 프로그램의 유용성과 기밀성관련 문제를 야기합니다. 첫 번째인 유용성 문제는, 대부분의 해제가 되지 않은 자원은 소프트웨어의 신뢰성 문제를 야기합니다. 만약, 공격자가 고의적으로 Resource Leak을 유발한다면, 공격자는 자원 풀의 모든 자원을 고갈시켜서 DOS(Denial of Service) 공격을 시도할 수 있습니다. 두 번째인 기밀성 문제는, 민감한 정보가 포함된 자원이 재대로 해제가 되지 않는다면, 공격자는 이 정보를 유출시켜서 악의적인 방법으로 사용할 수 있습니다.
  • SQL_INJECTION : SQL 삽입 공격은 클라이언트에서 응용프로그램으로 들어가는 입력데이터를 통하여 SQL 쿼리를 삽입 혹은 “주입”을 함으로써 이루어집니다. SQL 삽입에 성공하면, 공격자는 데이터베이스로부터 민감한 데이터를 읽거나, 수정하거나 (삽입/업데이트/ 삭제), 데이터베이스에 관리작업 명령어(i.e. 데이터베이스 종료)를 수행할 수 있고, 데이터베이스상에 존재하는 파일 중에, 운영체제관련 명령어들이 기록되어 있을 수 있는 파일을 얻어올 수 있습니다. SQL 삽입 공격들은 삽입공격 형식 중에 하나이고, SQL 삽입 공격은 미리 정의된 SQL 명령어들을 실행할 수 있도록 데이터수준의 입력 값에 SQL 명령어들이 삽입 됩니다. -공격자는 SQL 삽입을 통하여 신분을 도용하거나, 기존의 데이터를 조작할 수 있고, 트랜잭션을 무효화 하거나, 잔금을 변경하는 등의 지불거절 문제를 일으킬 수 있습니다. 또한 공격자는 시스템의 모든 데이터를 드러내거나, 삭제, 이용 불가능하게 할 수 있고, 데이터베이스 서버의 관리자 권한을 획득할 수 있습니다. -SQL 삽입은 이전 기능 인터페이스의 유행으로 인해, PHP 와 ASP 응용프로그램에서는 아주 흔하게 발생합니다. J2EE 와 ASP.NET 응용프로그램은 프로그램의 특성상, SQL 삽입 공격이 덜 발생합니다. -SQL 삽입 공격의 위험도는 공격자의 기술과 창의력, 데이터베이스 서버 링크 시 낮은 권한으로 링크를 하는 등의 보호조치가 얼마나 잘 되어 있는 지에 따라 좌우됩니다. 일반적으로 SQL 삽입은 위험성이 높은 공격이라고 간주합니다.
  • SystemPrintln : System.out.print(), 혹은 System.err.print() 메소드 호출을 검출합니다.
  • UNUSED_IMPORT : import된 클래스가 하나도 사용되지 않은 경우를 검출합니다.
  • UnusedLocalVariable : 사용하지 않는 지역변수을 검출합니다.
  • USE_BLOCK_SYNCHRONIZED : synchronization의 과다적용을 자제해야 합니다. 메소드 레벨의 synchronization 보다 블록 레벨 synchronization 을 사용하는 것이 바람직합니다.
  • USING_DYNAMIC_CLASS_LOADING : 동적으로 코드를 로드할 필요가 있다면, 이해하기 쉽고 문서화가 잘되어야 합니다. 악의적인 코드는 동적으로 로드된 코드에 포함될 수 있습니다. 동적으로 로드된 코드는 공격자가 응용프로그램이 실행중에 악의적인 코드를 삽입할 수 있습니다.

위처럼 검출된 항목의 종류는 몇개 되지 않지만, 검출된 항목의 개수는 정확히 605개!! ㅡOㅡ; 그래도 다른 개발사에 비하면 반의 반도 되지 않는다는 것에 위안을 가지는.. 이런거에 위안 가지만 안되는데…..

FingerEyes-Xr에서 파이 차트(Pie Chart) 표현하기

지도 상에 통계 데이터를 차트를 통해 표현하는 코드를 정리해 봅니다. 차트의 종류는 많지만 이중 지도와 가장 잘맞는 차트는 파이 차트인데요. 이 파이 차트를 표시해 보도록 하겠습니다.

먼저 차트를 표시할 수치지도 레이어를 추가합니다. 차트 표시를 위해 반드시 수치지도가 필요한 것은 아니지만.. 수치지도는 차트를 표시할 위치와 통계 데이터를 속성 값으로 가질 수 있으므로 차트에 대한 예제로 사용하기에 좋습니다. 아래의 코드는 수치지도를 표시하는 코드입니다.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />

    <style>
        body {
            margin: 0px;
            padding: 0px;
        }

        #map {
            top: 0px;
            left: 0px;
            position: absolute;
            width: 100%;
            height: 100%;
            border: none;
            outline: none;
        }
    </style>

    <script src="../../scripts/fingereyes-xr/Xr.js">

    <script>
        var map = null;

        function onLoad() {
            map = new Xr.Map("map", {});

            var lyr = new Xr.layers.ShapeMapLayer("lyr",
                {
                    url: "http://168.192.76.10:8080/Xr?layerName=li_a@test"
                }
            );
            
            var theme = lyr.theme();
            theme.penSymbol().color("black");
            theme.brushSymbol().color("lightgray");
            lyr.needAttribute(true);

            var lm = map.layers();
            lm.add(lyr);

            map.onLayersAllReady(onLayersReady);

            window.addEventListener("resize", onResize);
        }

        var bFinishResizing = true;

        function onResize() {
            if (bFinishResizing) {
                bFinishResizing = false;

                setTimeout(function () {
                    var newWidth = window.innerWidth;
                    var newHeight = window.innerHeight;

                    map.resize(newWidth, newHeight);
                    map.update();

                    bFinishResizing = true;
                }, 500);                
            }
        }

        function onLayersReady() {
            var cm = map.coordMapper();
            var lyr = map.layers("lyr");
            var mbr = lyr.MBR();

            cm.zoomByMBR(mbr);

            map.update();
        }
    </script>

    <title></title>
</head>

<body onload="onLoad()">
    <div id="map" />
</body>
</html>

위의 코드에서 중요한 부분은 40번째 코드에서 수치지도 레이어에 대한 속성값이 필요하다고 명시하고 있다는 점입니다. 차트 표시를 위한 통계값으로써 수치지도의 속성값이 필요하므로 이 코드가 필요합니다. 만약 수치지도 레이어가 라벨 표시를 사용한다면 자동으로 속성값을 호출하므로 굳이 이 코드가 필요하지는 않습니다. 이제 버튼을 올려두고 이 버튼을 클릭했을때 차트를 지도 상에 표시하는 코드를 작성해 보도록 하겠습니다.

이제 이 수치지도에 차트를 구성하는 코드를 작성할 것인데요. 아래의 doCharting 함수가 차트를 구성합니다.

 function doCharting() {
    var gl = new Xr.layers.GraphicLayer("chart");
    map.layers().add(gl);

    var graphicRows = gl.rowSet();
    var lyr = map.layers("lyr");
    var rows = lyr.shapeRowSet().rows();
    var ars = lyr.attributeRowSet();
    var fieldSet = lyr.fieldSet();
    var idx_sum_po_u65 = fieldSet.fieldIndex("sum_po_u65");
    var idx_sum_po_u75 = fieldSet.fieldIndex("sum_po_u75");
    var minValue = Number.MAX_VALUE;
    var maxValue = -Number.MAX_VALUE;
            
    for (var fid in rows) {
        var aRow = ars.row(fid);
        var val_sum_po_u65 = aRow.valueAsFloat(idx_sum_po_u65);
        var val_sum_po_u75 = aRow.valueAsFloat(idx_sum_po_u75);
        var total = val_sum_po_u65 + val_sum_po_u75;

        if (minValue > total) minValue = total;
        if (maxValue < total) maxValue = total;
    }

    var graphMaxSize = 80;
    var graphMinSize = 30;

    for (var fid in rows) {
        var aRow = ars.row(fid);
        var sRow = rows[fid];
        var pt = sRow.shapeData().representativePoint();
        var val_sum_po_u65 = aRow.valueAsFloat(idx_sum_po_u65);
        var val_sum_po_u75 = aRow.valueAsFloat(idx_sum_po_u75);
        var totalValue = val_sum_po_u65 + val_sum_po_u75;
        var radiusOut = (((graphMaxSize - graphMinSize) * totalValue / (maxValue - minValue)) + graphMinSize) / 2;
        var pcisd = new Xr.data.PieChartItemShapeData({
            x: pt.x, y: pt.y,
            values: [val_sum_po_u65, val_sum_po_u75],
            radiusOut: radiusOut, radiusIn: radiusOut * 0.4
        });

        var pcigr = new Xr.data.PieChartItemGraphicRow(fid, pcisd);
        graphicRows.add(pcigr);

        pcigr.penSymbol().color("#ffffff").width(1);
        pcigr.brushSymbol(0).opacity(1).color('#1abc9c');
        pcigr.brushSymbol(1).opacity(1).color('#34495e');
    }

    gl.refresh();
}

위의 코드를 하나씩 살펴보면... 먼저 1-2번 코드는 chart라는 이름의 그래픽 레이어를 추가하고 있습니다. 바로 이 그래픽 레이어에 차트가 표현됩니다. 그리고 10번-11번에서는 2개의 필드에 대한 인덱스값을 저장하고 있습니다. doCharting 함수는 2개의 속성값을 이용해 파이차트를 구성하고 있는데, 이때 사용하는 필드는 sum_po_u65와 sum_po_u7 이고 그 값을 얻기 위해 필드 인덱스값이 필요합니다. 15번-23번 코드는 차트를 구성하는 값의 최대값과 최소값을 계산합니다. 25-26번은 파이 차트를 그릴때 최대값에서의 차트 크기와 최소값에서의 차트 크기를 나타냅니다. 차트 크기의 단위는 px입니다. 28번-48번이 그래픽 레이어에 차트 그래픽 요소를 추가하는 함수인데요. 차트 그래픽 요소를 구성하는 값들로써 차트가 표시될 위치, 값에 대한 배열, 차트의 크기가 있습니다. 이 함수를 실행하면 다음과 같은 결과 화면을 볼 수 있습니다.

CentOS 7에서 PostgreSQL 10.2, PostGIS 2.4.3 설치(인터넷 환경)

# postgresql10, postgis YUM 저장소 업데이트
rpm -Uvh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

위의 url은 변경될 수 있으며 2020년 8월 31일에는 rpm -Uvh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm로 하여 설치를 진행했으며, postgresql은 10.10.14를, postgis는 3.0.2를 설치하였음.

# 데이터베이스 설치
yum install postgresql10-server.x86_64 postgresql10

# 데이터베이스 저장소 생성
cd /usr/pgsql-10/bin/
./postgresql-10-setup initdb

# 서비스 실행
systemctl enable postgresql-10
systemctl enable postgresql-10.service
systemctl start postgresql-10.service

# 5432 포트 방화벽 허용
firewall-cmd –zone=public –add-port=5432/tcp

# 외부 접속 허용
cd /var/lib/pgsql/10/data
vi postgresql.conf
(편집 내용)
listen_address = “*”

# 암호설정
su – postgres
psql
\password postgres
\q
su – root

# 외부 접속을 위한 보안 설정
cd /var/lib/pgsql/10/data
vi pg_hba.conf
(편집 내용)
local all all peer 문자열을 local all all md5 로 변경
host all all 127.0.0.1/32 ident 문자열을 host all all 0.0.0.0/0 md5 로 변경
host all all ::1/128 ident 문자열을 host all all ::1/128 md5 로 변경

# PostGIS 설치
yum install epel-release
yum install postgis24_10.x86_64
systemctl restart postgresql-10.service