franGiPane - architecture de la base de données
Discussions/réflexions et détails sur la question : Discussion.
Contraintes
Quelques remarques
L'héritage ne propage ni les clés primaires, ni les contraintes d'unicité, ce qui le rend plutôt... inutilisable en l'état actuel des choses.
Nommer les contraintes, c'est important : les messages d'erreur qui nous remontent, et qui remontent aux scripts, sont beaucoup plus clairs lorsqu'on sait précisément quelle contrainte est violée.
Vues
Par défaut, une vue est protégée en écriture ; pour autant, il est possible de simuler des accès en écriture sur une vue, grâce à des règles de réécriture de requêtes (RULE ... DO INSTEAD).
Contrôle de l'accès à la base
Schémas & users
Chaque service voit les vues qu'il utilise rangées dans un "schéma" (sous-répertoire de la base, en somme), et a son propre compte utilisateur sur la base, qui n'a accès qu'aux informations dont il a besoin, soit, typiquement, son propre schéma.
Doc sur la question :
- <file:///usr/share/doc/postgresql-doc/html/ddl-schemas.html>
- <file:///usr/share/doc/postgresql-doc/html/sql-createschema.html>
Doc
- <file:///usr/share/doc/postgresql-doc/html/user-manag.html>
- <file:///usr/share/doc/postgresql-doc/html/ddl-priv.html>
Fonctions
Que ce soit pour implémenter des contraintes complexes, pour écrire des triggers, pour générer des tables virtuelles, ou bien d'autres raisons encore, SQL et divers "procedural languages" peuvent être utilisés pour écrire des fonctions, côté serveur.
La surcharge de fonctions est possible.
Différents langages sont disponibles, voyons ce qui les différencie.
SQL
- permet le polymorphisme sans problème
- les attributs doivent tous être hard-codés, apparemment ; embêtant pour écrire des fonctions génériques
PL/pgSQL
- ressemble beaucoup à Pascal, mais... permet de faire tout ce qui est imaginable avec PostgreSQL, alors que les autres langages utilisables ne donnent accès qu'à un petit sous-ensemble
- EXECUTE permet de remettre au runtime la préparation (plan) de la requête ; indispensable dans les fonctions polymorphes, par exemple
PL/Perl
- cannot be used to write trigger function
- accès à la base seulement avec un module CPAN expérimental DBD::PgSPI très chiant à installer
PL/Python
- seulement en mode "untrusted"
- bon accès à la base
le reste
Y'a bien le PL/Tcl et le C, m'enfin...
État temporaire du schéma
-- *****************************************************************************
--
-- USERS, GROUPS & SCHEMAS
--
-- Ou comment étanchéifier les différents modes d'accès à la base de fGP.
--
-- *****************************************************************************
--------------------------------------------------------------------------------
--
-- Schemas
--
--------------------------------------------------------------------------------
CREATE SCHEMA config;
CREATE SCHEMA nss;
CREATE SCHEMA dns;
CREATE SCHEMA web;
CREATE SCHEMA mail;
--------------------------------------------------------------------------------
--
-- Des users, des groupes.
--
-- Les mots de passe sont ici en clairs, et stockés cryptés dans la base,
-- grâce à "ENCRYPTED PASSWORD".
-- Les permissions de ces zigotos ne leur sont données qu'à la fin, bicoze
-- un objet doit exister avant qu'on distribue des droits sur lui.
--
--------------------------------------------------------------------------------
--
-- User nss
-- A accès aux informations utilisées par libnss-pgsql, qui sont rangées dans
-- le schéma "nss".
-- NB : quiconque pouvant lire /etc/nss-pgsql.conf peut devenir cet user.
--
CREATE USER nss WITH
ENCRYPTED PASSWORD 'di8jIKbXjs'
NOCREATEDB NOCREATEUSER
;
--
-- User web
-- A accès aux informations utilisées par Apache, qui sont rangées dans
-- le schéma "web".
--
CREATE USER web WITH
ENCRYPTED PASSWORD 'oK93hGxeDZn'
NOCREATEDB NOCREATEUSER
;
--
-- User dns
-- A accès aux informations utilisées par PowerDNS, qui sont rangées dans
-- le schéma "dns".
--
CREATE USER dns WITH
ENCRYPTED PASSWORD 'hA6o81nGDitsp'
NOCREATEDB NOCREATEUSER
;
--
-- User mail
-- A accès aux informations utilisées par Postfix et consors, qui sont
-- rangées dans le schéma "dns".
--
CREATE USER mail WITH
ENCRYPTED PASSWORD 'o9sdhunex748f'
NOCREATEDB NOCREATEUSER
;
-- *****************************************************************************
--
-- FUNCTIONs communes à tout le bazar
--
-- *****************************************************************************
--
-- is_mail_address_valid ( varchar(255) )
-- Renvoie TRUE si, et seulement si, nous considérons l'argument comme une
-- adresse mail valide ; et c'est pas gagné qu'on respecte les RFC, soit dit
-- en passant.
-- Cf. "PostgreSQL 7.4.7 Documentation", II.9.6.3., "POSIX Regular Expressions"
--
CREATE OR REPLACE FUNCTION is_mail_address_valid(varchar(255))
RETURNS boolean AS '
SELECT $1 IS NOT NULL
AND $1 ~* ''^[a-z0-9\\\\.\\\\+_-]+@([a-z0-9-]+\\\\.){1,}(be|biz|com|fr|info|net|nu|org)$''
' LANGUAGE SQL ;
--
-- is_ip_valid ( varchar(255) )
-- Renvoie TRUE si, et seulement si, nous considérons l'argument comme une
-- adresse IP valide.
-- Limiter à une/des classes d'IP ?
--
CREATE OR REPLACE FUNCTION is_ip_valid(varchar(255)) RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 ~ ''^[0-9]{1,3}\\\\.[0-9]{1,3}\\\\.[0-9]{1,3}\\\\.[0-9]{1,3}$''
' LANGUAGE SQL ;
--
-- is_fqdn_valid ( varchar(255) )
-- Renvoie TRUE si, et seulement si, nous considérons l'argument comme un
-- fully-qualified-domain-name (FQDN) valide, pas forcément pour l'héberger,
-- il s'agit plutôt d'une prise de position normative et globalisante de
-- notre part.
-- Ce serait super si "on" lisait les RFC à ce sujet, un jour.
--
CREATE OR REPLACE FUNCTION is_fqdn_valid(varchar(255)) RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 ~ ''^[a-z0-9\\\\.-]+\\\\.(be|biz|com|fr|info|net|nu|org)$''
' LANGUAGE SQL ;
--
-- is_status_valid ( varchar(32) )
-- Renvoie TRUE si, et seulement si, l'argument est un des états que peut
-- avoir un objet de franGiPane, càd 'on' ou bien 'off'.
--
CREATE OR REPLACE FUNCTION is_status_valid(varchar(32)) RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 IN (''on'', ''off'')
' LANGUAGE SQL ;
--
-- is_mail_status_valid ( varchar(32) )
-- Renvoie TRUE si, et seulement si, l'argument est un des états que peut
-- avoir un domaine de franGiPane pour ce qui est du mail, càd 'on', 'off'
-- ou 'maintenance'.
--
CREATE OR REPLACE FUNCTION is_mail_status_valid(varchar(32)) RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 IN (''on'', ''off'', ''maintenance'')
' LANGUAGE SQL ;
--
-- is_transport_valid ( varchar(255) )
-- Renvoie TRUE si, et seulement si, l'argument est un des transports que peut
-- avoir une mail_box de franGiPane.
--
CREATE OR REPLACE FUNCTION is_transport_valid(varchar(255)) RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 IN (''local'', ''maildrop'', ''error:try again later'')
' LANGUAGE SQL ;
-- *****************************************************************************
--
-- DOMAINs communs à tous le bazar
--
-- DOMAIN = type de données associé à des contraintes, et réutilisables dans
-- la définition de nos tables.
--
-- *****************************************************************************
-- fGP_status
CREATE DOMAIN fGP_status AS varchar(32)
DEFAULT 'off'
CONSTRAINT non_null_status NOT NULL
CONSTRAINT valid_status CHECK(is_status_valid(VALUE))
;
-- fGP_mail_status
CREATE DOMAIN fGP_mail_status AS varchar(32)
DEFAULT 'off'
CONSTRAINT non_null_mail_status NOT NULL
CONSTRAINT valid_mail_status CHECK(is_mail_status_valid(VALUE))
;
-- fGP_mail
CREATE DOMAIN fGP_mail AS varchar(255)
CONSTRAINT non_null_mail NOT NULL
CONSTRAINT valid_mail CHECK(is_mail_address_valid(VALUE))
;
-- fGP_fqdn
CREATE DOMAIN fGP_fqdn AS varchar(255)
CONSTRAINT valid_fqdn CHECK(VALUE IS NULL OR is_fqdn_valid(VALUE))
;
-- fGP_hosted_fqdn
CREATE DOMAIN fGP_hosted_fqdn AS varchar(255)
CONSTRAINT non_null_fqdn NOT NULL
CONSTRAINT valid_fqdn CHECK(is_fqdn_valid(VALUE))
;
-- fGP_transport
CREATE DOMAIN fGP_transport AS varchar(255)
DEFAULT 'maildrop'
CONSTRAINT non_null_transport NOT NULL
CONSTRAINT valid_transport CHECK(is_transport_valid(VALUE))
;
-- *****************************************************************************
--
-- TABLES, SEQUENCES et RULES internes
--
-- *****************************************************************************
--------------------------------------------------------------------------------
--
-- config.{num, txt}
--
-- Contiennent des paires (nom, valeurs[]) attribuant une liste de valeurs à
-- une option de configuration.
--
--------------------------------------------------------------------------------
-- config.txt
CREATE TABLE config.txt (
name varchar(32) PRIMARY KEY,
values varchar(255)[]
);
-- debug
INSERT INTO config.txt VALUES ('server_fqdn', ARRAY['frangipane.org']);
INSERT INTO config.txt VALUES ('server_ip', ARRAY ['127.0.0.1', '10.0.0.1']); -- ne PAS mettre ça en vrai, hein
INSERT INTO config.txt VALUES ('web_root_directory', ARRAY['/var/www/franGiPane/']);
-- config.num
CREATE TABLE config.num (
name varchar(32) PRIMARY KEY,
values real[]
);
--debug
INSERT INTO config.num VALUES ('web_min_uid', ARRAY[3000]);
INSERT INTO config.num VALUES ('web_min_gid', ARRAY[3000]);
--
-- config.txt ( name )
-- Renvoie la première valeur attribuée à l'option de config textuelle dont
-- le name est passé en paramètre.
--
CREATE OR REPLACE FUNCTION config.txt(varchar(32)) RETURNS varchar(255) AS '
SELECT values[1] FROM config.txt WHERE name=$1
' LANGUAGE SQL;
--
-- config.num ( name )
-- Renvoie la première valeur attribuée à l'option de config numérique dont
-- le name est passé en paramètre.
--
CREATE OR REPLACE FUNCTION config.num(varchar(32)) RETURNS real AS '
SELECT values[1] FROM config.num WHERE name=$1
' LANGUAGE SQL;
--
-- config.is_config ( name, valeur )
-- Renvoie TRUE si, et seulement si, le second argument est une des values
-- attribuées, dans la table config.txt, à l'option dont le name est passé en
-- premier argument.
--
CREATE OR REPLACE FUNCTION config.is_config(varchar(32), varchar(255))
RETURNS boolean AS '
SELECT $2 = ANY (values) FROM config.txt WHERE name=$1
' LANGUAGE SQL;
--
-- config.is_config ( name, valeur )
-- Renvoie TRUE si, et seulement si, le second argument est une des values
-- attribuées, dans la table config.num, à l'option dont le name est passé en
-- premier argument.
--
CREATE OR REPLACE FUNCTION config.is_config(varchar(32), real)
RETURNS boolean AS '
SELECT $2 = ANY (values) FROM config.num WHERE name=$1
' LANGUAGE SQL;
CREATE TABLE config.mail_special_usernames (
username varchar(255) PRIMARY KEY
);
INSERT INTO config.mail_special_usernames VALUES ('root');
INSERT INTO config.mail_special_usernames VALUES ('mailer-daemon');
INSERT INTO config.mail_special_usernames VALUES ('postmaster');
INSERT INTO config.mail_special_usernames VALUES ('nobody');
INSERT INTO config.mail_special_usernames VALUES ('hostmaster');
INSERT INTO config.mail_special_usernames VALUES ('usenet');
INSERT INTO config.mail_special_usernames VALUES ('news');
INSERT INTO config.mail_special_usernames VALUES ('webmaster');
INSERT INTO config.mail_special_usernames VALUES ('www');
INSERT INTO config.mail_special_usernames VALUES ('ftp');
INSERT INTO config.mail_special_usernames VALUES ('abuse');
INSERT INTO config.mail_special_usernames VALUES ('noc');
INSERT INTO config.mail_special_usernames VALUES ('security');
INSERT INTO config.mail_special_usernames VALUES ('www-data');
--------------------------------------------------------------------------------
--
-- Domains
--
-- DNS
-- Sauf si son type est 'EXTERNALE', un domaine est la racine d'une zone DNS
-- sur laquelle nous sommes maîtres.
-- Nous ne gérons pas les sous-zones, i.e. : si nous hébergeons le domaine
-- 'bam.org', alors le sous-domaine 'truc.bam.org', lui, sera une bête
-- ligne dans la table dns_records, et ne pourra donc pas avoir de
-- sous-domaines.
-- mail
-- Bla bla.
-- Web
-- Bla bla.
--
--------------------------------------------------------------------------------
--
-- is_domain_type_valid ( varchar(32) )
-- Renvoie TRUE si, et seulement si, l'argument est valide, aux yeux de fGP,
-- pour être un domaines.type.
--
CREATE OR REPLACE FUNCTION is_domain_type_valid(varchar(32))
RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 IN (''MASTER'', ''EXTERNAL'')
' LANGUAGE SQL ;
--
-- is_ttl_valid ( ttl )
-- Renvoie TRUE si, et seulement si, l'argument est un "ttl" valide, aux yeux
-- de franGiPane.
--
CREATE OR REPLACE FUNCTION is_ttl_valid (integer)
RETURNS boolean AS '
SELECT $1 IS NOT NULL AND $1 >= 0
' LANGUAGE SQL ;
--
-- is_last_changed_valid ( last_changed )
-- Renvoie TRUE si, et seulement si, l'argument est une "last_changed"
-- valide, aux yeux de franGiPane.
--
CREATE OR REPLACE FUNCTION is_last_changed_valid (timestamp)
RETURNS boolean AS '
SELECT true
' LANGUAGE SQL ;
--
-- Table principale
--
CREATE TABLE domains (
-- ID
id serial PRIMARY KEY,
-- FQDN
name fGP_hosted_fqdn UNIQUE,
-- Contact mail
contact_mail fGP_mail,
-- Données DNS
type varchar(32)
CONSTRAINT non_null_type NOT NULL
CONSTRAINT valid_type
CHECK(is_domain_type_valid(type)),
last_check integer DEFAULT NULL,
notified_serial integer DEFAULT NULL,
ttl integer DEFAULT 86400
CONSTRAINT valid_ttl CHECK(type='EXTERNAL' OR is_ttl_valid(ttl)),
secondary fGP_fqdn DEFAULT NULL,
last_changed timestamp(0) NOT NULL DEFAULT current_timestamp
CONSTRAINT valid_last_changed
CHECK(is_last_changed_valid(last_changed)),
-- État DNS
dns_status fGP_status,
-- État mail
mail_status fGP_mail_status
);
-- Indexons ce qui sert souvent à la vue dns.domains
CREATE INDEX domains_on_id_index ON domains(id) WHERE dns_status = 'on' AND type != 'EXTERNAL';
-- Debug
INSERT INTO domains (name, contact_mail, type, secondary, dns_status, mail_status)
VALUES ('bla.org', 'bla@boum.org', 'MASTER', 'poivron.org', 'on', 'on');
INSERT INTO domains (name, contact_mail, type, dns_status, mail_status)
VALUES ('bli.org', 'bli@boum.org', 'MASTER', 'on', 'on');
INSERT INTO domains (name, contact_mail, type, dns_status, mail_status)
VALUES ('externe.org', 'bli@externe.org', 'EXTERNAL', 'on', 'on');
INSERT INTO domains (name, contact_mail, type, secondary, dns_status, mail_status)
VALUES (config.txt('server_fqdn'), 'intrigeri@krups.mine.nu', 'MASTER', 'squat.net', 'on', 'on');
INSERT INTO domains (name, contact_mail, type, dns_status, mail_status)
VALUES ('maintenance.com', 'intrigeri@krups.mine.nu', 'MASTER', 'on', 'maintenance');
-- Empêcher modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO domains
WHERE
OLD.name != NEW.name
OR OLD.id != NEW.id
DO INSTEAD NOTHING
;
--------------------------------------------------------------------------------
--
-- Enregistrement DNS
--
-- Appartient à un domaine.
-- Sauf si son domaine parent est EXTERNAL, il s'agit d'un "record" de
-- aux yeux de PowerDNS.
--
--------------------------------------------------------------------------------
--
-- is_dns_hostname_valid ( hostname )
-- Renvoie TRUE si, et seulement si, l'argument est un dns_records.hostname
-- valide ; càd : une chaîne de caractères ayant une bonne tête.
--
CREATE OR REPLACE FUNCTION is_dns_hostname_valid(varchar(255)) RETURNS boolean AS '
SELECT $1 ~ ''^[a-z0-9-]*$''
' LANGUAGE SQL ;
--
-- is_dns_type_valid ( type )
-- Renvoie TRUE si, et seulement si, l'argument est un des types DNS que
-- franGiPane sait héberger : A, MX...
--
CREATE OR REPLACE FUNCTION is_dns_type_valid(varchar(32)) RETURNS boolean AS '
SELECT $1 IN (''A'', ''CNAME'', ''MX'', ''PTR'', ''SERVER'')
' LANGUAGE SQL ;
--
-- is_dns_content_valid ( content, type )
-- Renvoie TRUE si, et seulement si, le premier argument est un content
-- valide, aux yeux de franGiPane, pour le type DNS passé en second argument.
--
CREATE OR REPLACE FUNCTION is_dns_content_valid (varchar(255), varchar(32))
RETURNS boolean AS '
SELECT CASE
WHEN $2 IN (''A'')
THEN is_ip_valid($1)
WHEN $2 IN (''CNAME'', ''MX'')
THEN is_fqdn_valid($1)
WHEN $2 IN (''PTR'')
THEN false
WHEN $2 IN (''SERVER'')
THEN true
ELSE false
END
' LANGUAGE SQL ;
--
-- is_prio_valid ( type, prio )
-- Renvoie TRUE si, et seulement si, l'argument est une "prio" valide, aux
-- yeux de franGiPane, pour le type d'enregistrement DNS passé en
-- premier argument.
--
CREATE OR REPLACE FUNCTION is_prio_valid (varchar(32),integer)
RETURNS boolean AS '
SELECT $1 != ''MX'' OR ($2 IS NOT NULL AND $2 > 0)
' LANGUAGE SQL ;
-- Table principale
CREATE TABLE dns_records (
-- ID
id serial PRIMARY KEY,
-- Domaine parent
domain_id integer NOT NULL
CONSTRAINT parent_domain
REFERENCES domains
ON DELETE RESTRICT ON UPDATE RESTRICT,
-- Informations DNS
-- hostname : partie à gauche du fqdn du domaine parent, sans le point.
hostname varchar(255) NOT NULL
CONSTRAINT valid_hostname CHECK(is_dns_hostname_valid(hostname)),
type varchar(32) NOT NULL
CONSTRAINT valid_dns_type
CHECK(is_dns_type_valid(type)),
content varchar(255) NOT NULL,
CONSTRAINT valid_dns_content
CHECK(is_dns_content_valid(content, type)),
CONSTRAINT unique_hostname_X_domain_X_type_X_content UNIQUE(hostname, domain_id, type, content),
prio integer DEFAULT NULL
CONSTRAINT valid_prio CHECK(is_prio_valid(type,prio)),
CONSTRAINT unique_hostname_X_domain_X_type_X_prio UNIQUE(hostname, domain_id, type, prio),
-- Mail
contact_mail fGP_mail,
-- État
status fGP_status
);
-- Indexons ce qui sert souvent à la vue dns.records
CREATE INDEX dns_on_hostname_index ON dns_records(hostname)
WHERE status='on';
CREATE INDEX dns_on_hostname_type_index ON dns_records(hostname,type)
WHERE status='on';
CREATE INDEX dns_on_domain_id_index ON dns_records(domain_id)
WHERE status='on';
--
-- update_mx_prio ()
-- Lorsque l'enregistrement en passe d'être inséré est un MX et qu'aucune
-- prio n'est spécifiée, on lui donne pour prio la plus petite valeur non
-- encore allouée (si y'a déjà un 10 et un 30, on lui donne 20 ; si y'a déjà
-- un 10 et un 20, on lui donne 30).
--
CREATE OR REPLACE FUNCTION update_mx_prio () RETURNS trigger
AS '
row = TD["new"]
if not (row["type"] == "MX" and row["prio"] is None):
return None
plan = plpy.prepare("SELECT count(*) AS nb FROM dns_records WHERE hostname=$1 AND domain_id=$2 AND type=''MX'' AND prio=$3", ["text", "numeric", "numeric"])
prio = 10
while True:
nb = plpy.execute ( plan, [row["hostname"], row["domain_id"], prio] )
if nb[0]["nb"] == 0L:
row["prio"] = prio
return "MODIFY"
break
prio = prio + 10
' LANGUAGE plpythonu;
CREATE TRIGGER update_mx_prio BEFORE INSERT ON dns_records FOR EACH ROW
EXECUTE PROCEDURE update_mx_prio ();
--
-- update_domains_last_changed ()
--
-- Met à jour le champ last_changed du domaine parent de l'enregistrement
-- en passe d'être inséré, modifié ou supprimé.
--
CREATE OR REPLACE FUNCTION update_domains_last_changed() RETURNS trigger AS '
DECLARE
domain integer;
BEGIN
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
domain := NEW.domain_id;
ELSE
domain := OLD.domain_id;
END IF;
UPDATE domains SET last_changed=current_timestamp WHERE id=domain;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER update_domains_last_changed
BEFORE INSERT OR UPDATE OR DELETE ON dns_records FOR EACH ROW
EXECUTE PROCEDURE update_domains_last_changed ();
-- Empêcher modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO dns_records
WHERE
OLD.id != NEW.id
OR OLD.type != NEW.type
OR OLD.domain_id != NEW.domain_id
OR OLD.hostname != NEW.hostname
DO INSTEAD NOTHING
;
-- Debug
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'A', 1, '10.0.0.1', 'contact_mail@bla.org', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'A', 2, '10.0.0.2', 'contact_mail@bli.org', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('ta', 'A', 2, '10.0.0.3', 'contact_mail@ta.bli.org', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'MX', 2, 'poivron.org', 'contact_mail@bli.org', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'A', 4, '127.0.0.1', 'intrigeri@krups.mine.nu', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'A', 3, '127.0.0.1', 'contact_mail@externe.org', 'on');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('bla', 'A', 1, '127.0.0.1', 'bla@bla.org', 'off');
INSERT INTO dns_records (hostname, type, domain_id, content, contact_mail, status)
VALUES ('', 'A', 5, '127.0.0.1', 'bla@maintenance.org', 'on');
--------------------------------------------------------------------------------
--
-- Site web
--
-- Appartient à un enregistrement DNS, lui même assujetti à un domaine.
-- Correspond soit à un sous-répertoire de config.txt('server_fqdn'), soit de la
-- racine d'un autre FQDN hébergé.
-- Géré, administrativement et techniquement, par des web_masters.
--
--------------------------------------------------------------------------------
--
-- is_site_name_valid ( name )
-- Renvoie TRUE si, et seulement si, l'argument est un nom de site acceptable
-- par franGiPane : zéro ou plusieurs lettres et/ou chiffres en minuscules,
-- et éventuellement des tirets, à l'exception de quelques valeurs spéciales.
--
CREATE OR REPLACE FUNCTION is_site_name_valid(varchar(32))
RETURNS boolean AS '
SELECT
$1 ~ ''^[a-z0-9-]*$''
AND
$1 NOT IN (''root'')
' LANGUAGE SQL ;
--
-- dns_record_ok_for_website ( dns_record_id, name )
-- Renvoie TRUE si, et seulement si :
-- le premier argument est l'ID d'un enregistrement DNS de type A, dont
-- l'IP est une des notres
-- ET
-- le site est soit un sous-répertoire de config.txt('server_fqdn'), soit la
-- root d'un autre domaine hébergé.
--
CREATE OR REPLACE FUNCTION dns_record_ok_for_website(integer, varchar(32))
RETURNS boolean AS '
SELECT
dns_records.type = ''A''
AND config.is_config(''server_ip'', content)
AND ( domains.name = config.txt(''server_fqdn'') OR $2 = '''' )
FROM dns_records INNER JOIN domains
ON dns_records.domain_id = domains.id
WHERE dns_records.id=$1
' LANGUAGE SQL ;
-- Table principale
CREATE TABLE web_sites (
-- ID (utilisé pour calculer l'UID & le GID)
id SERIAL PRIMARY KEY,
-- Name
-- Vaut '' pour le site root d'un domaine.
-- Sert à construire au moins :
-- 'http://' || dns_records.hostname || domains.name || '/' || web_sites.name
-- '/var/www/' || dns_records.hostname || domains.name || '/' || (web_sites.name OU 'root')
-- base MySQL et groupe Unix : (name || fqdn s/./_) || (name OU 'root')
name varchar(32) NOT NULL
CONSTRAINT valid_name CHECK(is_site_name_valid(name)),
-- Enregistrement DNS propriétaire
dns_record_id integer NOT NULL
CONSTRAINT parent_dns
REFERENCES dns_records
ON DELETE RESTRICT ON UPDATE RESTRICT,
-- Vérification du type de l'enregistrement DNS parent, et du fait
-- qu'un site est soit un sous-répertoire de config.txt('server_fqdn'),
-- soit la root d'un autre domaine hébergé.
CONSTRAINT valid_dns_parent CHECK(dns_record_ok_for_website(dns_record_id,name)),
-- Pour un enregistrement DNS donné, pas deux sites du même name.
CONSTRAINT unique_name_site_X_dns UNIQUE(name, dns_record_id),
-- Espace disque utilisable
disk_quota real NOT NULL DEFAULT 0
CONSTRAINT positive_disk_quota CHECK(disk_quota >= 0),
-- Espace disque utilisé
disk_usage real NOT NULL DEFAULT 0
CONSTRAINT positive_disk_usage CHECK(disk_usage >= 0),
-- État
web_status fGP_status,
ftp_status fGP_status
);
-- Indexons ce qui sert souvent à la VIEW web.sites
CREATE INDEX web_sites_dns_record_id_index ON web_sites(dns_record_id);
-- Debug
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('', 1, 10, 'on', 'on');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('master', 5, 10, 'on', 'on');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('ouais', 5, 10, 'off', 'off');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('unname', 5, 10, 'on', 'on');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('encore', 5, 10, 'off', 'on');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('', 6, 20, 'on', 'on');
INSERT INTO web_sites (name, dns_record_id, disk_quota, web_status, ftp_status)
VALUES ('', 7, 100, 'on', 'on');
-- Empêcher modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO web_sites
WHERE
OLD.id != NEW.id
OR OLD.dns_record_id != NEW.dns_record_id
OR OLD.name != NEW.name
DO INSTEAD NOTHING
;
--------------------------------------------------------------------------------
--
-- Webmaster
--
-- Administre un site web. Un seul.
--
--------------------------------------------------------------------------------
--
-- is_webmaster_login_valid ( login )
-- Renvoie TRUE si, et seulement si, l'argument est acceptable par franGiPane
-- comme web_masters.login : une ou plusieurs lettres et/ou chiffres en
-- minuscules.
--
CREATE OR REPLACE FUNCTION is_webmaster_login_valid(varchar(32))
RETURNS boolean AS '
SELECT $1 ~ ''^[a-z0-9]+$''
' LANGUAGE SQL ;
-- Table principale
CREATE TABLE web_masters (
-- Login
login varchar(32) PRIMARY KEY
CONSTRAINT valid_login
CHECK(is_webmaster_login_valid(login)),
-- Site web administré (utilisé pour calculer l'UID)
web_site_id integer
CONSTRAINT parent_web_site
REFERENCES web_sites
ON DELETE RESTRICT ON UPDATE RESTRICT,
-- Contact mail
contact_mail fGP_mail,
-- Mot de passe, dans le même format que /etc/shadow ($1$seed$md5)
password varchar(255) NOT NULL,
-- État
status fGP_status
);
-- Indexons ce qui sert souvent dans la vue nss.ftp_passwd
CREATE INDEX web_masters_on_web_site_id_index ON web_masters(web_site_id)
WHERE status = 'on';
-- Debug
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmblaroot1', 1, 'wmblaroot1@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmblaroot2', 1, 'wmblaroot2@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmboummaster', 2, 'ouais@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmboumouais', 3, 'unname@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmboumunname', 4, 'encore@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
INSERT INTO web_masters (login, web_site_id, contact_mail, password, status)
VALUES ('wmboumencore', 5, 'blaroot@boum.org', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/', 'on');
-- Empêcher modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO web_masters
WHERE
OLD.login != NEW.login
OR OLD.web_site_id != NEW.web_site_id
DO INSTEAD NOTHING
;
--------------------------------------------------------------------------------
--
-- mail : fonctions communes
--
--------------------------------------------------------------------------------
--
-- is_username_valid ( username )
-- Renvoie TRUE si, et seulement si, nous voulons bien héberger une adresse
-- mail composée de l'argument suivi de @...
-- Faudrait que "on" jette un oeil noir aux RFCs.
--
CREATE OR REPLACE FUNCTION is_username_valid(varchar(255))
RETURNS boolean AS '
SELECT $1 ~ ''^[a-z0-9_\\\\.-]+$''
' LANGUAGE SQL ;
--
-- is_mail_address_forbidden ( username, domaine )
-- Renvoie TRUE si, et seulement si, l'adresse mail correspondant aux
-- arguments ne doit pas être entrée dans franGiPane.
--
CREATE OR REPLACE FUNCTION is_mail_address_forbidden(varchar(255),varchar(255))
RETURNS boolean AS '
SELECT count(*) > 0 FROM config.mail_special_usernames
WHERE username = $1
' LANGUAGE SQL ;
--------------------------------------------------------------------------------
--
-- mail_boxes
--
-- Boîte mail hébergée.
--
--------------------------------------------------------------------------------
CREATE TABLE mail_boxes (
-- Adresse : username@fqdn
username varchar(255) NOT NULL
CONSTRAINT valid_username
CHECK(is_username_valid(username)),
domain varchar(255) NOT NULL
CONSTRAINT valid_domain REFERENCES domains(name)
ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT unique_mail_box PRIMARY KEY (username,domain),
CONSTRAINT forbidden_addresses
CHECK(NOT is_mail_address_forbidden(username,domain)),
-- Mot de passe, dans le même format que /etc/shadow ($1$seed$md5)
password varchar(255) NOT NULL,
-- Espace disque utilisable
disk_quota real NOT NULL DEFAULT 0
CONSTRAINT postitive_disk_quota CHECK(disk_quota >= 0),
-- Espace disque utilisé
disk_usage real NOT NULL DEFAULT 0
CONSTRAINT positive_disk_usage CHECK(disk_usage >= 0),
-- Transport utilisé par Postfix
transport fGP_transport,
-- État
status fGP_status
);
-- Debug
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bam1', config.txt('server_fqdn'), 'on', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bam2', config.txt('server_fqdn'), 'on', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bamoff', config.txt('server_fqdn'), 'off', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bam1', 'bla.org', 'on', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bam2', 'bla.org', 'on', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
INSERT INTO mail_boxes (username, domain, status, password)
VALUES ('bam', 'maintenance.com', 'on', '$1$YUTDfrwT$X5TiPsiG2EDkhiSe93Xhy/');
-- Empêcher modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO mail_boxes
WHERE
OLD.username != NEW.username
OR OLD.domain != NEW.domain
DO INSTEAD NOTHING
;
--------------------------------------------------------------------------------
--
-- mail_aliases
--
-- Alias mail hébergé.
--
--------------------------------------------------------------------------------
CREATE TABLE mail_aliases (
-- Adresse : username@domain
username varchar(255) NOT NULL
CONSTRAINT valid_username
CHECK(is_username_valid(username)),
domain varchar(255) NOT NULL
CONSTRAINT valid_domain REFERENCES domains(name)
ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT unique_alias PRIMARY KEY (username,domain),
CONSTRAINT forbidden_addresses
CHECK(NOT is_mail_address_forbidden(username,domain)),
-- Adresse de destination
destination fGP_mail,
-- État
status fGP_status
);
-- Debug
INSERT INTO mail_aliases VALUES ('alias', config.txt('server_fqdn'), 'intrigeri@krups.mine.nu', 'on');
INSERT INTO mail_aliases VALUES ('alias', 'bla.org', 'destination@bla.org', 'on');
INSERT INTO mail_aliases VALUES ('aliasoff', 'bla.org', 'destinationoff@bla.org', 'off');
INSERT INTO mail_aliases VALUES ('alias', 'maintenance.com', 'destination@bla.org', 'on');
-- Empêcher les modifications de certains champs
CREATE OR REPLACE RULE forbidden_updates AS
ON UPDATE TO mail_aliases
WHERE
OLD.username != NEW.username
OR OLD.domain != NEW.domain
DO INSTEAD NOTHING
;
-- *****************************************************************************
--
-- VIEWS utilisées par les logiciels de franGiPane
--
-- Par défaut, elles sont totalement protégées en écriture. Pour certaines,
-- l'update est possible à certaines conditions, et propagée aux tables
-- référencées.
--
-- *****************************************************************************
--------------------------------------------------------------------------------
--
-- DNS
--
--------------------------------------------------------------------------------
--
-- dns.domains : domaines actifs au niveau DNS
--
-- Tape dans la table domains.
-- Renvoie les infos réclamées par PowerDNS.
--
CREATE OR REPLACE VIEW dns.domains AS
SELECT
id,
name,
config.txt('server_fqdn') AS master,
last_check,
type,
notified_serial,
VARCHAR(40) 'internal' AS account,
ttl,
secondary,
last_changed
FROM domains
WHERE dns_status = 'on' AND type != 'EXTERNAL'
;
--
-- Seules modifications autorisées et propagées dans la table domains :
-- last_check, notified_serial.
--
CREATE OR REPLACE RULE update AS ON UPDATE TO dns.domains
DO INSTEAD
UPDATE domains
SET
notified_serial = NEW.notified_serial,
last_check = NEW.last_check
WHERE domains.id = OLD.id
;
--
-- dns_records_on : enregistrements DNS actifs "dans l'absolu"
--
-- Tape dans les tables dns_records et domains.
-- Vue auxilliaire utilisée par quelques autres.
--
CREATE OR REPLACE VIEW dns_records_on AS
SELECT
dns_records.id,
content,
ttl,
prio,
dns_records.type,
domain_id,
CASE WHEN char_length(hostname) != 0
THEN hostname || '.' || domains.name
ELSE domains.name
END AS fqdn,
--dns_records.name || domains.name AS fqdn,
domains.type AS type_domaine_parent
FROM dns_records INNER JOIN domains
ON dns_records.domain_id = domains.id
WHERE dns_records.status = 'on'
AND domains.dns_status = 'on'
;
--
-- dns.records : enregistrements DNS actifs au niveau DNS
--
-- Tape dans les vues dns_records_on et dns.domains.
-- Renvoie les infos réclamées par PowerDNS, avec le schéma par défaut de ce
-- dernier. Génère, à la volée, les enregistrements de type NS et SOA
-- nécessaires.
--
CREATE OR REPLACE VIEW dns.records AS
-- Enregistrements stockés dans la base
SELECT
content AS content,
ttl,
prio,
type,
domain_id AS domain_id,
fqdn AS name
FROM dns_records_on
WHERE type_domaine_parent != 'EXTERNAL'
-- Enregistrements NS (config.txt('server_fqdn')) des dns.domains dont nous
-- sommes maîtres
UNION SELECT
config.txt('server_fqdn') AS content,
ttl,
NULL AS prio,
'NS' AS type,
id AS domain_id,
name
FROM dns.domains
-- enregistrements NS (secondarys) des dns.domains dont nous sommes maîtres
UNION SELECT
secondary AS content,
ttl,
NULL AS prio,
'NS' AS type,
id AS domain_id,
name
FROM dns.domains
WHERE secondary IS NOT NULL
-- enregistrements SOA des dns.domains dont nous sommes maîtres
UNION SELECT
config.txt('server_fqdn')
|| ' '
|| 'hostmaster.' || name
|| ' '
|| EXTRACT(EPOCH FROM last_changed)
AS content,
ttl,
NULL AS prio,
'SOA' AS type,
id AS domain_id,
name
FROM dns.domains
;
--------------------------------------------------------------------------------
--
-- Vues auxilliaires utilisées par quelques autres.
--
--------------------------------------------------------------------------------
--
-- web_sites_on : sites web actifs "dans l'absolu"
--
-- Tape dans la table web_sites et dans la vue
-- dns_records_on.
--
CREATE OR REPLACE VIEW web_sites_on AS
SELECT
web_sites.id + config.num('web_min_uid') AS uid,
web_sites.id + config.num('web_min_gid') AS gid,
config.txt('web_root_directory')
|| fqdn
|| '/'
||
CASE WHEN char_length(name) != 0
THEN name
ELSE 'root'
END
|| '/'
AS repertoire,
'http://'
|| fqdn
|| '/'
|| CASE WHEN char_length(name) != 0
THEN name || '/'
ELSE ''
END
AS base_url,
fqdn AS base_fqdn,
replace( fqdn, '.', '_')
|| '_'
|| CASE WHEN char_length(name) != 0
THEN name
ELSE 'root'
END
AS name,
content AS ip,
web_status,
ftp_status
FROM
web_sites INNER JOIN dns_records_on
ON web_sites.dns_record_id = dns_records_on.id
;
--------------------------------------------------------------------------------
--
-- Apache
--
--------------------------------------------------------------------------------
--
-- web.sites : sites web actifs au niveau Apache
--
-- Tape dans la vue web_sites_on.
-- Renvoie les trucs dont Apache a besoin.
--
CREATE OR REPLACE VIEW web.sites AS
SELECT name AS mysql_db, uid, gid, repertoire, base_url, base_fqdn, ip
FROM web_sites_on
WHERE web_status = 'on'
;
--------------------------------------------------------------------------------
--
-- NSS (vsftpd, quoi)
--
--------------------------------------------------------------------------------
--
-- nss.ftp_passwd : comptes FTP actifs
--
-- Tape dans la table web_masters et dans la vue web_sites_on.
-- Renvoie les infos "ala /etc/passwd" utilisées par vsftpd via NSS.
--
CREATE OR REPLACE VIEW nss.ftp_passwd AS
SELECT
login,
password AS passwd,
uid,
gid,
repertoire AS homedir,
varchar(30) '/bin/false' AS shell,
login AS gecos
FROM web_masters INNER JOIN web_sites_on
ON web_masters.web_site_id + config.num('web_min_uid') = web_sites_on.uid
WHERE web_masters.status = 'on' AND ftp_status = 'on'
;
--
-- nss.ftp_group : groupes Unix FTP actifs
--
-- Tape dans la vue web_sites_on.
-- Renvoie les infos "ala /etc/group" utilisées par vsftpd via NSS.
--
CREATE OR REPLACE VIEW nss.ftp_group AS
SELECT
name AS name,
gid,
varchar(30) 'x' AS passwd
FROM web_sites_on
WHERE ftp_status = 'on'
;
--------------------------------------------------------------------------------
--
-- mail
--
--------------------------------------------------------------------------------
-- mail.boxes_on
--
-- Tape dans les tables mail_boxes et domains.
--
CREATE OR REPLACE VIEW mail.boxes_on AS
SELECT
username || '@' || domain AS adress,
transport AS destination
FROM mail_boxes INNER JOIN domains
ON mail_boxes.domain = domains.name
WHERE status='on' AND domains.mail_status='on'
;
-- mail.aliases_on
--
-- Tape dans les tables mail_aliases et domains.
--
CREATE OR REPLACE VIEW mail.aliases_on AS
-- Aliases stockés dans la base
SELECT
username || '@' || domain AS address,
destination
FROM mail_aliases INNER JOIN domains
ON mail_aliases.domain = domains.name
WHERE status='on' AND domains.mail_status='on'
-- Adresses spéciales, pour chaque domaine =! config.txt('server_fqdn')
UNION SELECT
username || '@' || domains.name AS address,
domains.contact_mail AS destination
FROM config.mail_special_usernames CROSS JOIN domains
WHERE domains.mail_status='on'
AND config.txt('server_fqdn') != domains.name
;
-- mail.addresses_on
--
-- Tape dans les vues mail.boxes_on, mail.aliases_on
--
CREATE OR REPLACE VIEW mail.addresses_on AS
SELECT address, destination FROM mail.aliases_on
UNION SELECT adress, destination FROM mail.boxes_on
;
-- mail.domains_maintenance
--
-- Tape dans la table domains.
--
CREATE OR REPLACE VIEW mail.domains_maintenance AS
SELECT name FROM domains
WHERE type!='EXTERNAL' AND mail_status='maintenance'
;
--
-- mail.transport (domain)
--
-- Si Postfix demande le transport correspondant à un domaine en maintenance,
-- renvoie "error:try again later". Sinon, ne renvoie rien, afin que Postfix
-- demande à d'autres transport_maps, et s'il n'y trouve rien non plus,
-- retombe sur son mécanimse de routage habituel.
-- La ligne de configuration qui doit être mise dans la map pgsql adaptée :
-- query = SELECT result FROM transport('%d')
-- Cf. :
-- * /usr/share/doc/postfix/html/pgsql_table.5.html
-- * /usr/share/doc/postfix/html/transport.5.html
-- * /usr/share/doc/postfix/html/DATABASE_README.html
--
CREATE TYPE mail.postfix_lookup AS (lookup varchar(255), result varchar(255));
CREATE OR REPLACE FUNCTION mail.transport(varchar(255))
RETURNS SETOF mail.postfix_lookup AS '
SELECT
CAST(name AS varchar(255)),
varchar(255) ''error:try again later''
FROM mail.domains_maintenance
WHERE name = $1
' LANGUAGE SQL;
-- *****************************************************************************
--
-- Permissions
--
-- Cf "PostgreSQL 7.4.7 Documentation", partie 5.8.4. Schemas and Privileges
--
-- *****************************************************************************
-- Tout interdire, à tout user existant et futur, sur le schéma public.
REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;
-- dns
GRANT USAGE ON SCHEMA dns TO dns;
GRANT SELECT ON TABLE dns.domains, dns.records TO dns;
GRANT UPDATE ON TABLE dns.domains TO dns;
GRANT USAGE ON SCHEMA config TO dns;
GRANT EXECUTE ON FUNCTION config.txt(varchar(32)) TO dns;
GRANT SELECT ON TABLE config.txt TO dns;
-- nss
GRANT USAGE ON SCHEMA nss TO nss;
GRANT SELECT ON TABLE nss.ftp_passwd, nss.ftp_group TO nss;
GRANT USAGE ON SCHEMA config TO nss;
GRANT EXECUTE ON FUNCTION config.num(varchar(32)) TO nss;
GRANT SELECT ON TABLE config.num TO nss;
GRANT EXECUTE ON FUNCTION config.txt(varchar(32)) TO nss;
GRANT SELECT ON TABLE config.txt TO nss;
-- web
GRANT USAGE ON SCHEMA web TO web;
GRANT SELECT ON TABLE web.sites TO web;
-- mail
GRANT USAGE ON SCHEMA mail TO mail;
GRANT EXECUTE ON FUNCTION mail.transport(varchar(255)) TO mail;
GRANT SELECT ON TABLE mail.domains_maintenance TO mail;
-- *****************************************************************************
--
-- DIVERSES FUNCTIONS ET AUTRES TRIGGERS
--
-- Pour l'instant, c'est pour faire joujou.
--
-- *****************************************************************************
-- Modifications autorisées et propagées dans la table web_masters :
-- login, password.
CREATE RULE update AS ON UPDATE TO ftp_passwd
WHERE OLD.uid = NEW.uid
AND OLD.gid = NEW.gid
AND OLD.homedir = NEW.homedir
DO INSTEAD
UPDATE web_masters SET
login = NEW.login,
password = NEW.passwd
WHERE web_masters.web_site_id = OLD.uid
AND web_masters.login = OLD.login
;
CREATE OR REPLACE FUNCTION domaine_depasse_quota_sites (domains)
RETURNS boolean AS '
SELECT $1.usage_sites > $1.quota_sites
FROM domains
' LANGUAGE SQL ;
SELECT domaine_depasse_quota_sites(domains) FROM domains WHERE name='bla.org';
CREATE OR REPLACE FUNCTION pre_insert_web_site_pl (domains) RETURNS boolean AS '
my ($dom) = @_;
elog INFO, "Test de la faisabilité de l''ajout d''un site sur $dom->{''name''}";
return ( $sdom->{''usage_sites''} + 1 <= $dom->{''quota_sites''} );
' LANGUAGE plperl;
SELECT pre_insert_web_site(domains) FROM domains WHERE name='bla.org';
CREATE OR REPLACE FUNCTION pre_insert_web_site_py (domains) RETURNS boolean
AS '
dom = args[0]
return ( dom["usage_sites"] + 1 <= dom["quota_sites"] )
' LANGUAGE plpythonu;
SELECT pre_insert_web_site_py(domains) FROM domains WHERE name='bla.org';
SELECT name, pre_insert_web_site_py(domains) FROM domains;
--
-- plus_petite_prio_dispo ( fqdn )
-- Si le FQDN passé en argument a déjà un ou plusieurs MX, renvoie la plus
-- grande "prio" de ces MX, augmentée de 10. Sinon, renvoie 10.
--
CREATE OR REPLACE FUNCTION plus_petite_prio_dispo (varchar(255)) RETURNS integer
AS '
fqdn = args[0]
plan = plpy.prepare("SELECT max(prio) AS max FROM dns_records WHERE fqdn=$1 AND type=''MX''", ["text"])
max = plpy.execute(plan, [ fqdn ])
if max[0]["max"] is None:
return 10
return ( max[0]["max"] + 10 )
' LANGUAGE plpythonu;
-- *****************************************************************************
--
-- Nettoyage
--
-- *****************************************************************************
DROP VIEW nss.ftp_passwd;
DROP VIEW nss.ftp_group;
DROP VIEW web.sites;
drop VIEW web_sites_on;
DROP TABLE web_masters CASCADE;
DROP TABLE web_sites CASCADE;
DROP VIEW dns.records;
DROP VIEW dns_records_on;
DROP TRIGGER update_mx_prio ON dns_records;
drop trigger update_domains_last_changed ON dns_records;
drop RULE forbidden_updates on dns_records;
DROP TABLE dns_records CASCADE;
DROP FUNCTION mail.transport(varchar(255));
DROP TYPE mail.postfix_lookup;
DROP TABLE mail_adresses CASCADE;
DROP TABLE mail_aliases CASCADE;
DROP TABLE mail_boxes CASCADE;
drop view mail.domains_maintenance;
DROP VIEW dns.domains;
drop RULE forbidden_updates on domains;
DROP TABLE domains CASCADE;
DROP DOMAIN fGP_status;
DROP DOMAIN fGP_mail_status;
DROP DOMAIN fGP_mail;
DROP DOMAIN fGP_fqdn;
DROP DOMAIN fGP_hosted_fqdn;
DROP DOMAIN fGP_transport;
DROP FUNCTION dns_record_ok_for_website(integer,varchar(32));
DROP FUNCTION is_status_valid(varchar(32));
DROP FUNCTION is_mail_status_valid(varchar(32));
drop FUNCTION is_mail_address_valid(varchar(255)) CASCADE;
DROP FUNCTION is_ip_valid(varchar(255));
DROP FUNCTION is_domain_type_valid(varchar(32));
DROP FUNCTION is_dns_type_valid(varchar(32));
DROP FUNCTION is_dns_content_valid(varchar(255),varchar(32));
DROP FUNCTION is_fqdn_valid(varchar(255));
DROP FUNCTION is_site_name_valid(varchar(32));
DROP FUNCTION is_webmaster_login_valid(varchar(32));
DROP FUNCTION is_ttl_valid(integer);
DROP FUNCTION is_prio_valid(varchar(32),integer);
DROP FUNCTION is_last_changed_valid (timestamp);
DROP FUNCTION is_mail_address_forbidden (varchar(255),varchar(255));
DROP FUNCTION is_transport_valid (varchar(255));
DROP USER dns;
DROP USER nss;
DROP USER web;
DROP USER mail;
DROP SCHEMA nss cascade;
drop SCHEMA dns cascade;
drop SCHEMA web cascade;
drop SCHEMA mail cascade;
drop FUNCTION config.txt(varchar(32));
drop FUNCTION config.num(varchar(32));
drop FUNCTION config.is_config(varchar(32),varchar(255));
drop FUNCTION config.is_config(varchar(32),real);
drop table config.num;
drop table config.txt;
drop table config.mail_special_usernames;
drop schema public cascade;