PostgreSQL DB 연결 - PostgreSQL DB yeongyeol

Question

KeyCloak 서버는 기본적으로 Java 기반의 H2 데이터베이스로 기동된다.

목차 Show

1) Connect to PostgreSQL database server using psql
2) Connect to PostgreSQL database server using pgAdmin
Connect to PostgreSQL database from other applications

=> H2가 아닌 PostgreSQL 로 기동하는 방법에 대해 정리한다.

* 참고. 외부 DB 연결 후, 기동 시 KeyCloak 에서 제공해주는 테이블들이 해당 스키마에 설치된다.

1. JDBC 설정

해당 moduls 디렉토리 아래에 jdbc driver 파일을 위치시킨다.

$ cd keycloak-11.0.3\modules\system\layers\keycloak\org\

> 1.0 jdbc driver 다운로드한다.

> 1.1 org > postgresql > main 폴더 생성한다.

> 1.2 main 폴더 안에 module.xml 빈 파일 생성한다.

> 1.3 main 폴더 안에 postgresql-42.2.18..jar 등 JDBC Driver 를 위치 시킨다.

- module.xml 작성

<module xmlns="urn:jboss:module:1.3" name="org.postgresql">

    <resources>
        <resource-root path="postgresql-42.2.18.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>

</module>

2. 데이터 소스 설정

KeyCloak/standalone/configuration/standalone.xml 수정

- java:jboss/datasources/KeycloakDS 이 H2로 작성되어있는데, 아래의 코드로 수정한다.

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:postgresql://localhost:5432/{PostgreSQL Database}</connection-url>
    <driver>postgresql</driver>
    <pool>
        <max-pool-size>20</max-pool-size>
    </pool>
    <security>
        <user-name>{PostgreSQL User}</user-name>
        <password>{PostgreSQL Password}</password>
    </security>
</datasource>

- driver 부분에 h2 부분 밑에 추가한다.

<driver name="postgresql" module="org.postgresql">
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

3. 데이터베이스 설정

KeyCloak/standalone/configuration/standalone.xml 수정

- Database 스키마 설정 추가한다.

* 만약, 스키마를 public 이 아닌, 다른 스키마로 변경하고 싶을 때 설정을 추가해주면 된다.

디폴트 : public

<spi name="connectionsJpa">
    <provider name="default" enabled="true">
        <properties>
            <property name="dataSource" value="java:jboss/datasources/KeycloakDS"/>
            <property name="initializeEmpty" value="true"/>
            <property name="migrationStrategy" value="update"/>
            <property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
            <property name="schema" value="public"/>
    </properties>
</provider>

4. 기동

standalone.bat -Djboss.socket.binding.port-offset=100

또는

standalone.bat

=> 기동 이후, DB 확인 시 KeyCloak 에서 제공하는 테이블들이 설치된 것을 확인할 수 있다.

5. 로컬 PC 의 로컬 PostgreSQL 의 경우 (외부접속 허용)

KeyCloak/standalone/configuration/standalone.xml 수정

- 만약 ( 로컬 PC에서 기동 중인 KeyCloak 서버 )를 다른 PC 에서 접속하고 싶을 때,

-- 변경 전 코드
        <interface name="management">
            <inet-address value="${jboss.bind.address.management:127.0.0.1}"/>
        </interface>
        <interface name="public">
            <inet-address value="${jboss.bind.address:127.0.0.1}"/>
        </interface>
        

-- 변경 후 코드
        <interface name="management">
            <any-address/>
        </interface>
        <interface name="public">
            <any-address/>
        </interface>
        <interface name="unsecure">
            <inet-address value="${jboss.bind.address.unsecure:127.0.0.1}"/>
        </interface>
--

Summary: in this tutorial, you will learn how to connect to the PostgreSQL Database Server via an interactive terminal program called psql and via the pgAdmin application.

When you installed the PostgreSQL database server, the PostgreSQL installer also installed some useful tools for working with the PostgreSQL database server. In this tutorial, you will learn how to connect to the PostgreSQL database server via the following tools:

psql – a terminal-based front-end to PostgreSQL database server.
pgAdmin – a web-based front-end to PostgreSQL database server.

1) Connect to PostgreSQL database server using psql

psql is an interactive terminal program provided by PostgreSQL. It allows you to interact with the PostgreSQL database server such as executing SQL statements and managing database objects.

The following steps show you how to connect to the PostgreSQL database server via the psql program:

First, launch the psql program and connect to the PostgreSQL Database Server using the postgres user:

Second, enter all the information such as Server, Database, Port, Username, and Password. If you press Enter, the program will use the default value specified in the square bracket [] and move the cursor to the new line. For example, localhost is the default database server. In the step for entering the password for user postgres, you need to enter the password the user postgres that you chose during the PostgreSQL installation.

Third, interact with the PostgreSQL Database Server by issuing an SQL statement. The following statement returns the current version of PostgreSQL:

SELECT version();
Code language: SQL (Structured Query Language) (sql)

Please do not forget to end the statement with a semicolon (;). After pressing Enter, psql will return the current PostgreSQL version on your system.

2) Connect to PostgreSQL database server using pgAdmin

The second way to connect to a database is by using a pgAdmin application. The pgAdmin application allows you to interact with the PostgreSQL database server via an intuitive user interface.

The following illustrates how to connect to a database using pgAdmin GUI application:

First, launch the pgAdmin application.

The pgAdmin application will launch on the web browser as shown in the following picture:

Second, right-click the Servers node and select Create > Server… menu to create a server

Third, enter the server name e.g., PostgreSQL and click the Connection tab:

Fourth, enter the host and password for the postgres user and click the Save button:

Fifth, click on the Servers node to expand the server. By default, PostgreSQL has a database named postgres as shown below:

Sixth, open the query tool by choosing the menu item Tool > Query Tool or click the lightning icon.

Seventh, enter the query in the Query Editor, click the Execute button, you will see the result of the query displaying in the Data Output tab:

Connect to PostgreSQL database from other applications

Any application that supports ODBC or JDBC can connect to the PostgreSQL database server. In addition, if you develop an application that uses a specific driver, the application can connect to the PostgreSQL database server as well:

Connect to PostgreSQL from PHP
Connect to PostgreSQL from Python
Connect to PostgreSQL from Java

In this tutorial, you’ve learned how to connect to the PostgreSQL database server by using different client tools including psql and pgAdmin GUI application.

Was this tutorial helpful ?