Remotely connect to the Service Manager Service Portal database

By default, remote access to the Service Manager Service Portal postgres database is blocked. The following steps will help you access the database server with tools such as pgAdmin (pgAdmin: PostgreSQL administration and management tools ).

  1. Modify the IP address that Postgres to listens to.

    1. Change the postgres configuration as the postgres user:

      su - postgres
         cd /var/lib/pgsql/9.5/data
      cp postgresql.conf postgresql.conf.bak
      vi postgresql.conf
    2. Add this new line in the "CONNECTIONS AND AUTHENTICATION" section:

      listen_addresses = '*'

      Note It defaults to 'localhost'; use '*' for all.

    3. Run this command to exit:

      exit
  2. Allow remote connections to Postgres.

    1. Update postges config pg_hba.conf

      su - postgres
      cd /var/lib/pgsql/9.5/data
      cp pg_hba.conf pg_hba.conf.bak
      vi pg_hba.conf 

      Note Here you need to add an IP or network range that is trusted (see the first example), or allow all connections (see the second example).

    2. Insert one of these lines below the "IPv4 local connections" section:

      host all all 16.56.204.1/24 trust
      host all all 0.0.0.0/0 trust
    3. Run this command to exit:

      exit
  3. Restart postgres as root:

    su - root
    systemctl restart postgresql-9.5
  4. Open port 5432 on the Service Manager Service Portal server:

    1. Run the following command:

      vi /etc/sysconfig/iptables
    2. Add a new line below the line with port 9000:

      -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
      
    3. Run the following commands:

      systemctl restart iptables
      iptables -L
    4. Check if the following line is available:

      ACCEPT tcp -- anywhere anywhere tcp dpt:postgres
  5. Locate the databases.

    The <Service Manager Service Portal Installation Directory>/.uninstall/postinst.d directory contains a dozen of shell scripts to drop and recreate the different databases that Service Manager Service Portal uses, including the database name, users and passwords.