Archives pour janvier, 2014

CentOS : lenteur des logins SSH

L’authentification GSS ralentit l’établissement des connexions SSH sous CentOS 6; la désactivation permet de corriger le problème :

# GSSAPI options
#GSSAPIAuthentication yes
#GSSAPICleanupCredentials yes

OpenLDAP : fixer l’attribut shadowLastChange à une date spécifique

boris.huisgen@wks:~$ echo $(($(date --utc --date "$1" +%s)/86400))
16091
boris.huisgen@wks:~$ echo $(($(date --utc --date "$1" -d '-90day' +%s)/86400))
16002

Réseau : activer un port réseau sur un switch HP

bhuisgen@wks:~$ ssh manager@192.168.251.4
manager@192.168.251.4's password:
ProCurve J9280A Switch 2510G-48

SW-USR4# config

SW-USR4(config)# show interfaces brief

 Status and Counters - Port Status

                    | Intrusion                           MDI   Flow  Bcast
  Port    Type      | Alert     Enabled Status Mode       Mode  Ctrl  Limit
  ------- --------- + --------- ------- ------ ---------- ----- ----- ------
  1       100/1000T | No        Yes     Up     10FDx      MDIX  off   0
  2       100/1000T | No        Yes     Up     1000FDx    MDIX  off   0
  3       100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  4       100/1000T | Yes       Yes     Up     1000FDx    MDIX  off   0
  5       100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  6       100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  7       100/1000T | No        No      Down   1000FDx    MDIX  off   0
  8       100/1000T | No        No      Down   1000FDx    MDI   off   0
  9       100/1000T | No        No      Down   1000FDx    MDI   off   0
  10      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  11      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  12      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  13      100/1000T | No        Yes     Down   1000FDx    MDIX  off   0
  14      100/1000T | No        Yes     Down   1000FDx    MDIX  off   0
  15      100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  16      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  17      100/1000T | No        No      Down   1000FDx    MDI   off   0
  18      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  19      100/1000T | No        No      Down   1000FDx    MDI   off   0
  20      100/1000T | No        No      Down   1000FDx    MDI   off   0
  21      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  22      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  23      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  24      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  25      100/1000T | No        Yes     Up     1000FDx    MDIX  off   0
  26      100/1000T | No        Yes     Up     1000FDx    MDIX  off   0
  27      100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  28      100/1000T | No        Yes     Up     1000FDx    MDIX  off   0
  29      100/1000T | No        Yes     Up     1000FDx    MDIX  off   0
  30      100/1000T | No        Yes     Up     1000FDx    MDI   off   0
  31      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  32      100/1000T | No        No      Down   1000FDx    MDI   off   0
  33      100/1000T | No        No      Down   1000FDx    MDI   off   0
  34      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  35      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  36      100/1000T | No        No      Down   1000FDx    MDI   off   0
  37      100/1000T | No        No      Down   1000FDx    MDI   off   0
  38      100/1000T | No        No      Down   1000FDx    MDI   off   0
  39      100/1000T | No        No      Down   1000FDx    MDI   off   0
  40      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  41      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  42      100/1000T | No        No      Down   1000FDx    MDI   off   0
  43      100/1000T | No        No      Down   1000FDx    MDI   off   0
  44      100/1000T | No        Yes     Down   1000FDx    MDIX  off   0
  45      100/1000T | No        No      Down   1000FDx    MDIX  off   0
  46      100/1000T | No        No      Down   1000FDx    MDI   off   0
  47-Trk2 100/1000T | No        No      Down   1000FDx    MDI   off   0
  48-Trk2 100/1000T | No        Yes     Up     1000FDx    MDIX  off   0

SW-USR4(config)# show port-security 16

 Port Security

  Port : 16
  Learn Mode [Continuous] : Continuous
  Action [None] : None

SW-USR4(config)# interface 16
SW-USR4(eth-16)# enable
SW-USR4(eth-16)# end

PostgreSQL : limiter le temps d’exécution des requêtes

Méthode soft, limiter au niveau de la session client :

SET statement_timeout=10000;

Méthode stricte, limiter au niveau du rôle d’exécution :

ALTER ROLE user1 SET statement_timeout = '10000';

Postfix : configuration backup MX

root@mxbackup ~ # cat /etc/postfix/main.cf
relay_domains = $mydestination my.domain1 my.domain2

smtpd_client_restrictions =
        permit_mynetworks,
        reject_unknown_reverse_client_hostname,
        reject_rbl_client zen.spamhaus.org,
        reject_rbl_client bl.spamcop.net,
        reject_rbl_client cbl.abuseat.org,
        permit

smtpd_helo_restrictions =
        permit_mynetworks,
        reject_invalid_hostname,
        reject_unknown_hostname,
        permit

smtpd_sender_restrictions =
        reject_non_fqdn_sender,
        permit_mynetworks,
        reject_unknown_sender_domain,
        permit

smtpd_recipient_restrictions =
        reject_non_fqdn_recipient,
        permit_mynetworks,
        reject_unknown_recipient_domain,
        reject_unauth_destination,
        permit

smtpd_data_restrictions =
        reject_unauth_pipelining

Zabbix : partitionnement des tables sous PostgreSQL

Petit résumé sur la configuration du partitionnement des tables Zabbix (version 2.2) avec une base de données PostgreSQL 9.3. Le partionnement des tables est automatique ; il peut être désactivé à tout moment. La suppression des tables est quant à elle à automatiser en cron.

Trigger de gestion du partitionnement journalier

-- Function: trg_daily_partition()

-- DROP FUNCTION trg_daily_partition();

CREATE OR REPLACE FUNCTION trg_daily_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;

BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMMDD');

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;

RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock) + ('1 day')::interval));

EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION trg_daily_partition()
OWNER TO zabbix;

Trigger de gestion du partitionnement mensuel

-- Function: trg_monthly_partition()

-- DROP FUNCTION trg_monthly_partition();

CREATE OR REPLACE FUNCTION trg_monthly_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;

BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMM');

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;

RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock) + ('1 month')::interval));

EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION trg_monthly_partition()
OWNER TO zabbix;

Création du schéma

CREATE SCHEMA partitions AUTHORIZATION zabbix

Ajout des triggers sur les tables parentes

CREATE TRIGGER partition_trg BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_uint');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_str_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_str_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_log');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends_uint FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends_uint');

Fonctions de suppression des partitions

-- Function: delete_daily_partitions(integer)

-- DROP FUNCTION delete_daily_partitions(integer);

CREATE OR REPLACE FUNCTION delete_daily_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' day')::interval);

FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 9 THEN
CONTINUE;
END IF;

tmstp := TO_TIMESTAMP(tmp, '_YYYYMMDD');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting daily table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;

RETURN 1;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION delete_daily_partitions(integer)
OWNER TO zabbix;

 

-- Function: delete_monthly_partitions(integer)

-- DROP FUNCTION delete_monthly_partitions(integer);

CREATE OR REPLACE FUNCTION delete_monthly_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
prefix text;
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' month')::interval);

FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 7 THEN
CONTINUE;
END IF;

tmstp := TO_TIMESTAMP(tmp, '_YYYYMM');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting monthly table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;

RETURN 'OK';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION delete_monthly_partitions(integer)
OWNER TO zabbix;

Exemple d’utilisation :

SELECT delete_daily_partitions(31);
SELECT delete_monthly_partitions(12);

PostgreSQL : migration d’un cluster 9.1 vers 9.3

Installation des dépôts

root@zabbix:~# wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
root@zabbix:~# add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main'

Mise à jour du serveur

root@zabbix:~# apt-get update && apt-get install postgresql-9.3

Migration du cluster existant

root@zabbix:/var/lib/postgresql# pg_upgradecluster 9.1 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data /var/lib/postgresql/9.3/main
locale en_US.UTF-8
port 5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database zabbix...
Analyzing database zabbix...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Copying old server.crt...
Copying old server.key...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

pg_dropcluster 9.1 main

Suppression de l’ancien cluster

root@zabbix:~# pg_dropcluster 9.1 main

Désinstallation des anciens packages

root@zabbix:~# apt-get --purge remove postgresql-9.1 postgresql-client-9.1
Haut de page