Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-8474

Documentation: Add database driver and DataSource for PostgreSQL

    Details

    • Type: Task
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: EJBCA 7.3.0
    • Component/s: None
    • Labels:
      None
    • Sprint:
      EJBCA Team Alice - 2019 w34

      Description

      Update EJBCA documentation with PostgreSQL instructions to create database, add database driver and DataSource:

      Database Setup

      PostgreSQL
      After installing the PostgreSQL database, you need to set the client authentication for local unix domain socket connections from "peer" to "md5":

       $ sudo vi /etc/postgresql/10/main/pg_hba.conf

      # "local" is for Unix domain socket connections only
      local       all        all         md5
      

       Restart PostgreSQL service:
       $ sudo systemctl restart postgresql

      Login to the user postgres, and assign a password:
       $ sudo -i -u postgres
       $ createuser ejbca_user -P

      Create a new database and username:
       $ createdb ejbcadb -O ejbca_user
       $ logout

      Create EJBCA tables and indexes:
       $ psql -U ejbca_user -W ejbcadb
      > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-tables-ejbca-postgres.sql
      > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-index-ejbca.sql
      > \q

      Add Database Driver

       PostgreSQL
       $ cp postgresql-42.2.5.jar wildfly_home/standalone/deployments/postgresql.jar

      Add DataSource

       PostgreSQL
      > wildfly_home/bin/jboss-cli.sh -c
      > data-source add --name=ejbcads --driver-name="org.postgresql.Driver" --connection-url="jdbc:postgresql://127.0.0.1/ejbcadb" --jndi-name="java:/EjbcaDS" --use-ccm=true --driver-name="postgresql.jar" --driver-class="org.postgresql.Driver" --user-name="ejbca_user" --password="foo123" --validate-on-match=true --background-validation=false --prepared-statements-cache-size=50 --share-prepared-statements=true --min-pool-size=5 --max-pool-size=150 --pool-prefill=true --transaction-isolation=TRANSACTION_READ_COMMITTED --check-valid-connection-sql="select 1;"
      > :reload
       

        Attachments

          Activity

            People

            Assignee:
            tomas Tomas Gustavsson
            Reporter:
            maikel Maikel Kasselias
            Verified by:
            Maikel Kasselias
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 30 minutes
                30m
                Remaining:
                Time Spent - 20 minutes Remaining Estimate - 10 minutes
                10m
                Logged:
                Time Spent - 20 minutes Remaining Estimate - 10 minutes
                20m