Ensure Network Safety with Centralized Logging : Page 5

Postgres Configuration
You'll need to make some configuration changes in Postgres, as well as in the database schema and in the users that can access the data.

Postgres can be configured to allow connections from a certain host implicitly. Inside the $PGDATA directory inside your installation, there will be a pg_hba.conf file. Add a line to that file to allow connections from the host that's sending the syslogs to the syslog server:

host    syslog      c-syslog       <syslog srvr ip>  255.255.255.255  trust
host    syslog      c-syslog-rept  <syslog srvr ip>  255.255.255.255  password

After editing the file, issue a pg_ctl reload to reload the configuration files. This isn't the most secure method of doing this, but since those users can only select or insert data, and someone would have to take over those accounts, it should be relatively secure. Next, you'll need to create the Postgres database that will be holding the data, followed by the creation of the table to hold the data:

bash-2.03$ createdb syslog
CREATE DATABASE

Then, create the PL/pgsql language in the database we just created. This will be used for triggers and functions:

bash-2.03$ createlang plpgsql syslog

Now create the tables and functions surrounding the syslog table. Use the command-line client psql to connect to the database psql syslog and create the tables and functions shown below:

CREATE TABLE syslog (

    r_id serial NOT NULL,
    ip cidr NOT NULL,
    facility character varying(10),
    priority character varying(10),
    level character varying(10),
    tag character varying(10),
    date date,
    time time without time zone,
    program character varying(15),
    msg character varying(2048),
    ins_time timestamp without time zone DEFAULT now(),
    last_mod timestamp without time zone
);

Postgres has a CIDR data type that understands network IP addresses. This allows you to to do things like find all of the IP addresses in a given subnet. The CIDR data type stores IP address in 'ip address/netmask' notation. The main thing to understand about this field is that your network devices in the 'ip' field are going to appear as a.b.c.d/32. Simply ignore the /32 at this point.

CREATE FUNCTION syslog_lm_f()
RETURNS TRIGGER
AS '
BEGIN
   new.last_mod=now();
   RETURN new;
END; '
LANGUAGE 'plpgsql';


CREATE TRIGGER syslog_lm_t
    BEFORE INSERT OR UPDATE ON syslog
    FOR EACH ROW
    EXECUTE PROCEDURE syslog_lm_f();

Next, set permissions on the table. You'll be creating two users. One user will only be able to insert data into the table. The second user will only be able to select from the table. Here are the commands for the insert-only user:

bash-2.03$ createuser -A -D -P c-syslog
Enter password for new user: <somepass>
Enter it again: <somepass>
CREATE USER

These are the commands for the read-only user:

bash-2.03$ createuser -A -D -P c-syslog-rept
Enter password for new user: <somepass>
Enter it again: <somepass>
CREATE USER

Now, set the permissions on the table. Access the database using psql syslog:

REVOKE ALL ON TABLE syslog FROM PUBLIC;
GRANT INSERT ON TABLE syslog TO "c-syslog";
GRANT SELECT ON TABLE syslog TO "c-syslog-rept";

Next, set permissions on the sequence to allow the c-syslog user to increment the counters.

REVOKE ALL ON TABLE syslog_r_id_seq FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE syslog_r_id_seq TO "c-syslog";

For future reference, open up a psql session to the database by running psql syslog from the host as the postgres user and leave it running. On the host that's running the syslog-db.sh script mentioned above, su to c-syslog (by running su c-syslog as root), and type psql -h <your db host> syslog for a remote database host or psql syslog for a local database host to verify connectivity.

Syslog-ng Startup
It's finally time to start up the syslog server. First, run the shell script you previously created as the c-syslog user:

(as root) su c-syslog -c /usr/local/bin/syslog-db.sh &

Second, start up syslog-ng, by either running its init script, or simply running syslog-ng at the prompt as root. Your system log should be up and running at this point. There should be an entry in /var/log/messages as well regarding the syslog-ng successful start-up.

Previous Page

Next Page

---

• Download The Code for this Article

---