Remote Access and Dspam w/PostgreSQL: Difference between pages

From QmailToaster
(Difference between pages)
Jump to navigation Jump to search
(Created page with " 1. Remote Desktop Server a. Install Xfce Desktop (above) b. # yum install xrdp && systemctl enable xrdp && systemctl start xrdp && echo "xfce4-session" > ~/.Xclients && chmod a+x ~/.Xclients c. # firewall-cmd --zone=public --add-port=3389/tcp --permanent && firewall-cmd --reload d. Access email server with Windows/Linux Remote Desktop Client, and accept default configuratioin 2. VNC Server a. Install Desktop (above) b. # yum install tigervnc-server t...")
 
No edit summary
 
Line 1: Line 1:
1. Remote Desktop Server
[[Dspam|Back]]<br>
  a. Install Xfce Desktop (above)
<pre>
  b. # yum install xrdp && systemctl enable xrdp && systemctl start xrdp && echo "xfce4-session" > ~/.Xclients && chmod a+x ~/.Xclients
Assumes Dspam is already installed
  c. # firewall-cmd --zone=public --add-port=3389/tcp --permanent && firewall-cmd --reload
  d. Access email server with Windows/Linux Remote Desktop Client, and accept default configuratioin


2. VNC Server
(1) Install and start postgresql
  a. Install Desktop (above)
# dnf install postgresql-server postgresql-contrib dspam-pgsql
  b. # yum install tigervnc-server tigervnc-server-minimal
# postgresql-setup --initdb
  c. # cp -p /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@:1.service
# systemctl enable --now postgresql
   d. # perl -pi -e 's/^User=<USER>/User=root/g; s/=\/home\/<USER>/=\/root/g; s/^ExecStart=.*/ExecStart=\/sbin\/runuser -l root -c "\/usr\/bin\/vncserver %i -geometry
# systemctl status postgresql
        1280x800"/g' /etc/systemd/system/vncserver@:1.service
 
  e. # systemctl daemon-reload
 
  f. # mkdir ~/.vnc
(2) Edit postgres files:
  g. # printf '%s\n\n%s\n%s\n%s\n' '#!/bin/sh' 'unset SESSION_MANAGER' 'unset DBUS_SESSION_BUS_ADDRESS' 'exec /bin/sh /etc/xdg/xfce4/xinitrc' > ~/.vnc/xstartup
# /var/lib/pgsql/data/postgresql.conf
  h. # chmod ugo+x ~/.vnc/xstartup
    listen_addresses = '0.0.0.0' # IPv4
  i. # vncpasswd
    listen_addresses = '*'      # IPv4/6
  j. # systemctl enable vncserver@:1.service
    port = 5432 
  k. # systemctl start vncserver@:1.service
# /var/lib/pgsql/data/pg_hba.conf
  l. # firewall-cmd --zone=public --add-port=5901/tcp --permanent
    # IPv4 local connections:
  m. # firewall-cmd --reload
    #host    all            all            127.0.0.1/32            ident
  n. Access email server w/RealVNC vewier, TigerVNC viewer, or TightVNC viever
    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>
</pre>

Latest revision as of 11:10, 30 March 2024

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>
Retrieved from "http://wiki.qmailtoaster.org:80/index.php?title=Remote_Access&oldid=829"