Dspam w/PostgreSQL

Back

Assumes Dspam is already installed

(1) Install and start postgresql
 # dnf install postgresql-server postgresql-contrib dspam-pgsql
 # postgresql-setup --initdb
 # systemctl enable --now postgresql
 # systemctl status postgresql


(2) Edit postgres files:
 # /var/lib/pgsql/data/postgresql.conf
    listen_addresses = '0.0.0.0' # IPv4
    listen_addresses = '*'       # IPv4/6
    port = 5432  
 # /var/lib/pgsql/data/pg_hba.conf
    # IPv4 local connections:
    #host    all             all             127.0.0.1/32            ident
    host    all             all             127.0.0.1/32            md5

(3) PostgreSQL User/DB creation:
           # adduser dspampg
           # passwd dspampg (p4ssw3rd)
           # su - postgres
           $ createdb dspam
           $ psql -d dspam -f /usr/share/dspam/sql-scripts/pgsql/pgsql_objects.sql
           $ psql -d dspam -f /usr/share/dspam/sql-scripts/pgsql/virtual_users.sql
           $ psql dspam
     dspam=#
     dspam=# \d
                             List of relations
           Schema |          Name          |   Type   |  Owner
          --------+------------------------+----------+----------
           public | dspam_preferences      | table    | postgres
           public | dspam_signature_data   | table    | postgres
           public | dspam_stats            | table    | postgres
           public | dspam_token_data       | table    | postgres
           public | dspam_virtual_uids     | table    | postgres
           public | dspam_virtual_uids_seq | sequence | postgres
          (6 rows)

     dspam=# \q
           $
           $ psql template1
 template1=# CREATE USER dspampg WITH PASSWORD 'p4ssw3rd';
 template1=# GRANT ALL PRIVILEGES ON DATABASE dspam TO dspampg;
 template1=# \q
           $ psql -c "ALTER USER dspampg WITH SUPERUSER" (May not need to do the next 3 commands)
           $ psql -d dspam -c "GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public to dspampg"
           $ psql -d dspam -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES    IN SCHEMA public to dspampg"
           $ psql -d dspam -c "GRANT ALL PRIVILEGES ON ALL FUNCTIONS    IN SCHEMA public to dspampg"
           $ exit
           # systemctl restart postgresql



(4) Dspam configuration:
           # vi /etc/dspam.conf (comment mysql, uncomment pgsql)
              (1)
              #StorageDriver /usr/lib64/dspam/libmysql_drv.so
              StorageDriver /usr/lib64/dspam/libpgsql_drv.so

              (2)
              #
              # Storage driver settings: Specific to a particular storage driver. Uncomment
              # the configuration specific to your installation, if applicable.
              #
              #MySQLServer            /var/lib/mysql/mysql.sock
              #MySQLPort
              #MySQLUser              dspam
              #MySQLPass              p4ssw3rd
              #MySQLDb                dspam
              #MySQLCompress          true
              #MySQLReconnect         true

              
              # --- PostgreSQL ---

              # For PgSQLServer you can Use a TCP/IP address or a socket. If your socket is
              # in /var/run/postgresql/.s.PGSQL.5432 specify just the path where the socket
              # resits (without .s.PGSQL.5432).

              PgSQLServer             127.0.0.1
              PgSQLPort               5432
              PgSQLUser               dspampg
              PgSQLPass               p4ssw3rd
              PgSQLDb                 dspam
              
(5) Restart dspam
           # systemctl restart dspam
           # systemctl status dspam


(6) Test connection with dspam paramters to PostreSQL
           # psql -h 127.0.0.1 -U dspampg -d dspam --port=5432  --password
             password: 
               psql (13.13)
               Type "help" for help.

               dspam=>
               dspam=>\q
           <success>