|
|
| Line 1: |
Line 1: |
| | | | |
| − | This page describes the steps to follow to build a Jabber server using Yate
| |
| − |
| |
| − | === Introduction ===
| |
| − |
| |
| − | The following modules are required:
| |
| − | * [[Jabber Server Module]] - Manage jabber streams, route jabber stanzas
| |
| − | * [[jbfeatures|Jabber server features]] - Implements jabber server features: roster, vcard, private data, offline messages and in stream user management (create, delete, modify)
| |
| − | * [[Subscriptions]] - Store, retrieve, change users' roster
| |
| − | * [[Users]] - Users management (create, delete, modify)
| |
| − | * [[OpenSSL]] - TLS implementation used to encrypt streams when needed
| |
| − | * [[Register]] - Authenticate users and write users location to database
| |
| − | * [[PostgreSQL]] or [[MySQL]] - Database access
| |
| − |
| |
| − | '''Note:''' All database queries were used with PostgreSQL
| |
| − |
| |
| − | === Configure jabber server module ===
| |
| − |
| |
| − | Minimum required configuration for the jabber server is the serviced domain and listeners for server to server and client to server stream connections.<br>
| |
| − | For our example we are going to use '''example.com''' as jabber server domain.<br>
| |
| − | The configuration file is '''jabberserver.conf''':
| |
| − |
| |
| − | [general]
| |
| − | domains=example.com
| |
| − |
| |
| − | [listener client]
| |
| − | enable=yes
| |
| − | type=c2s
| |
| − |
| |
| − | [listener server]
| |
| − | enable=yes
| |
| − | type=s2s
| |
| − |
| |
| − | See [[Jabber Server Module]] for more configuration options.
| |
| − |
| |
| − | === Configure jabber server features module ===
| |
| − |
| |
| − | In our configuration users are not allowed to create or delete accounts but they can change their password after succesfull login.<br>
| |
| − | We will set the database account name and queries used to add, update or delete private data, vcard and offline chat.<br>
| |
| − | We'll store maximum 100 offline chat messages for an user (set maxcount=0 to remove offline messages storage limit).<br>
| |
| − | A database stored procedure is used to add/update users private data because we need to update an existing row if already added.<br>
| |
| − | A database stored procedure is used to add an offline chat message because we need it to check if the maximum allowed messages limit is reached.<br>
| |
| − | The configuration file is '''jbfeatures.conf''':
| |
| − |
| |
| − | [general]
| |
| − | account=jabberserver
| |
| − |
| |
| − | [register]
| |
| − | allow_management=no
| |
| − | allow_change=yes
| |
| − |
| |
| − | [vcard]
| |
| − | get=SELECT vcard FROM users WHERE username='${username}'
| |
| − | set=UPDATE users SET vcard VALUES ('${vcard}') WHERE username='${username}'
| |
| − |
| |
| − | [private_data]
| |
| − | maxcount=100
| |
| − | get=SELECT xml FROM privatedata WHERE username='${username}' AND tag='${tag}' AND xmlns='${xmlns}'
| |
| − | set=SELECT * FROM privatedata_set('${username}', '${tag}', '${xmlns}', '${xml}')
| |
| − | clear_user=DELETE FROM privatedata WHERE username='${username}'
| |
| − |
| |
| − | [offline_chat]
| |
| − | get=SELECT * FROM offlinechat WHERE username='${username}' ORDER BY time
| |
| − | add=SELECT * FROM offlinechat_add('${username}', '${xml}', ${time}, ${maxcount})
| |
| − | clear_user=DELETE FROM offlinechat WHERE username='${username}'
| |
| − |
| |
| − | See [[jbfeatures|Jabber server features]] for more configuration options.
| |
| − |
| |
| − | === Configure the subscriptions module ===
| |
| − |
| |
| − | For this module we must configure the database account name and queries used to retrieve and update users' roster.<br>
| |
| − | Database stored procedures are used to make sure we update existing rows if already added.<br>
| |
| − | The configuration file is '''subscription.conf''':
| |
| − |
| |
| − | [general]
| |
| − | account=jabberserver
| |
| − | user_roster_load=SELECT users.username, roster.* FROM users LEFT OUTER JOIN roster ON \
| |
| − | users.username=roster.username WHERE users.username='${username}'
| |
| − | user_roster_delete=DELETE FROM roster WHERE username='${username}'
| |
| − | contact_load=SELECT * FROM roster WHERE username='${username}' AND contact='${contact}'
| |
| − | contact_subscription_set=SELECT * FROM subscription_set('${username}','${contact}','${subscription}')
| |
| − | contact_set=SELECT * FROM roster_set('${username}','${contact}','${name}','${groups}') AS \
| |
| − | t(username text,contact text,name text,groups text)
| |
| − | contact_set_full=SELECT * FROM roster_set_full('${username}','${contact}','${name}','${groups}','${subscription}') \
| |
| − | AS t(username text,contact text,name text,groups text,subscription text)
| |
| − | contact_delete=DELETE FROM roster WHERE username='${username}' AND contact='${contact}'
| |
| − |
| |
| − | See [[Subscriptions]] for more configuration options.
| |
| − |
| |
| − | === Configure the users module ===
| |
| − | For this module we need to configure the database account name and user add, update, remove and retrieve queries.<br>
| |
| − | The configuration file is '''users.conf''':
| |
| − |
| |
| − | [database]
| |
| − | account=jabberserver
| |
| − | add_user=SELECT * FROM user_add('${user}','${password}')
| |
| − | update_user=UPDATE users SET password='${password}' WHERE username='${user}'
| |
| − | remove_user=DELETE FROM users WHERE username='${user}'
| |
| − | select_user=SELECT * FROM users WHERE username='${user}'
| |
| − |
| |
| − | See [[Users]] for more configuration options.
| |
| − |
| |
| − | === Configure the openssl module ===
| |
| − |
| |
| − | For this module we need to configure a certificate.<br>
| |
| − | The certificate will be used when users will want to encrypt their connection to the server or another server will want to encrypt the connection to us.<br>
| |
| − | We are using a certificate stored in a file named '''server.crt'''.<br>
| |
| − | The file must exist in yate configuration directory.<br>
| |
| − | The certificate will be used for the domain '''example.com''' and its subdomains (e.g. yate.example.com).<br>
| |
| − | The configuration file is '''openssl.conf''':
| |
| − |
| |
| − | [server]
| |
| − | enable=yes
| |
| − | domains=example.com,*.example.com
| |
| − | certificate=server.crt
| |
| − | key=
| |
| − |
| |
| − | See [[OpenSSL]] for more configuration options.
| |
| − |
| |
| − | === Configure the register module ===
| |
| − |
| |
| − | We will set the database account name and queries for user authentication and user location set and delete.<br>
| |
| − | The configuration file is '''register.conf''':
| |
| − |
| |
| − | [general]
| |
| − | user.auth=yes
| |
| − | user.register=yes
| |
| − | user.unregister=yes
| |
| − |
| |
| − | [default]
| |
| − | priority=90
| |
| − | account=jabberserver
| |
| − |
| |
| − | [user.auth]
| |
| − | query=SELECT password FROM users WHERE username='${username}'
| |
| − | result=password
| |
| − |
| |
| − | [user.register]
| |
| − | query=SELECT * FROM register_set('${username}','${data}')
| |
| − |
| |
| − | [user.unregister]
| |
| − | query=DELETE FROM lines WHERE line='${username}' AND location='${data}'
| |
| − |
| |
| − | See [[Register]] for more configuration options.
| |
| − |
| |
| − | ===Database storage and access configuration===
| |
| − | ==== Database configuration====
| |
| − |
| |
| − | Our example configures a connection to a local PostgreSQL database named '''jabberserver'''.<br>
| |
| − | The configuration file is '''pgsqldb.conf''':
| |
| − |
| |
| − | [jabberserver]
| |
| − | host=localhost
| |
| − | port=5432
| |
| − | database=jabberserver
| |
| − | user=postgres
| |
| − |
| |
| − | See [[PostgreSQL]] for more configuration options.
| |
| − |
| |
| − | ==== PostgreSQL example database dump ====
| |
| − |
| |
| − | --
| |
| − | -- PostgreSQL database dump
| |
| − | --
| |
| − |
| |
| − | SET client_encoding = 'UTF8';
| |
| − | SET standard_conforming_strings = off;
| |
| − | SET check_function_bodies = false;
| |
| − | SET client_min_messages = warning;
| |
| − | SET escape_string_warning = off;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1756 (class 1262 OID 16393)
| |
| − | -- Name: jabberserver; Type: DATABASE; Schema: -; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE DATABASE jabberserver WITH TEMPLATE = template0 ENCODING = 'UTF8';
| |
| − |
| |
| − |
| |
| − | ALTER DATABASE jabberserver OWNER TO postgres;
| |
| − |
| |
| − | \connect jabberserver
| |
| − |
| |
| − | SET client_encoding = 'UTF8';
| |
| − | SET standard_conforming_strings = off;
| |
| − | SET check_function_bodies = false;
| |
| − | SET client_min_messages = warning;
| |
| − | SET escape_string_warning = off;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 311 (class 2612 OID 16396)
| |
| − | -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE PROCEDURAL LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
| |
| − |
| |
| − | SET search_path = public, pg_catalog;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 20 (class 1255 OID 16397)
| |
| − | -- Dependencies: 311 3
| |
| − | -- Name: offlinechat_add(text, text, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION offlinechat_add(text, text, integer, integer) RETURNS integer
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _xml ALIAS FOR $2;
| |
| − | _time ALIAS FOR $3;
| |
| − | _max ALIAS FOR $4;
| |
| − | n integer;
| |
| − | BEGIN
| |
| − | SELECT INTO n COUNT(*) FROM users WHERE username=_username;
| |
| − | IF n > 0 THEN
| |
| − | n := 1;
| |
| − | IF _max > 0 THEN
| |
| − | SELECT INTO n COUNT(*) FROM offlinechat WHERE username=_username;
| |
| − | IF n < _max THEN
| |
| − | n := 1;
| |
| − | ELSE
| |
| − | n := 0;
| |
| − | END IF;
| |
| − | END IF;
| |
| − | IF n = 1 THEN
| |
| − | INSERT INTO offlinechat (username,xml,time) VALUES (_username,_xml,_time);
| |
| − | END IF;
| |
| − | END IF;
| |
| − | RETURN n;
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.offlinechat_add(text, text, integer, integer) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 21 (class 1255 OID 16398)
| |
| − | -- Dependencies: 3 311
| |
| − | -- Name: privatedata_set(text, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION privatedata_set(text, text, text, text) RETURNS text
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _tag ALIAS FOR $2;
| |
| − | _xmlns ALIAS FOR $3;
| |
| − | _xml ALIAS FOR $4;
| |
| − | data record;
| |
| − | BEGIN
| |
| − | SELECT INTO data xml FROM privatedata WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
| |
| − | IF not found THEN
| |
| − | INSERT INTO privatedata (username,tag,xmlns,xml) VALUES (_username,_tag,_xmlns,_xml);
| |
| − | ELSE
| |
| − | UPDATE privatedata SET xml=_xml WHERE username=_username AND tag=_tag AND xmlns=_xmlns;
| |
| − | END IF;
| |
| − | return '''';
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.privatedata_set(text, text, text, text) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1759 (class 0 OID 0)
| |
| − | -- Dependencies: 21
| |
| − | -- Name: FUNCTION privatedata_set(text, text, text, text); Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON FUNCTION privatedata_set(text, text, text, text) IS 'Add or update user''s private data';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 22 (class 1255 OID 16399)
| |
| − | -- Dependencies: 3 311
| |
| − | -- Name: register_set(text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION register_set(text, text) RETURNS text
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _line ALIAS FOR $1;
| |
| − | _location ALIAS FOR $2;
| |
| − | data record;
| |
| − | BEGIN
| |
| − | SELECT INTO data line FROM lines WHERE line=_line AND location=_location;
| |
| − | IF not found THEN
| |
| − | INSERT INTO lines (line,location) VALUES (_line,_location);
| |
| − | ELSE
| |
| − | UPDATE lines SET location=_location WHERE line=_line;
| |
| − | END IF;
| |
| − | return '''';
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.register_set(text, text) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 23 (class 1255 OID 16400)
| |
| − | -- Dependencies: 3 311
| |
| − | -- Name: roster_set(text, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION roster_set(text, text, text, text) RETURNS SETOF record
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _contact ALIAS FOR $2;
| |
| − | _name ALIAS FOR $3;
| |
| − | _groups ALIAS FOR $4;
| |
| − | data record;
| |
| − | BEGIN
| |
| − | SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
| |
| − | IF not found THEN
| |
| − | INSERT INTO roster (username,contact,name,groups) VALUES (_username,_contact,_name,_groups);
| |
| − | ELSE
| |
| − | UPDATE roster SET name=_name, groups=_groups WHERE username=_username AND contact=_contact;
| |
| − | END IF;
| |
| − | SELECT INTO data username,contact,name,groups FROM roster WHERE username=_username AND contact=_contact;
| |
| − | return next data;
| |
| − | return;
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.roster_set(text, text, text, text) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1760 (class 0 OID 0)
| |
| − | -- Dependencies: 23
| |
| − | -- Name: FUNCTION roster_set(text, text, text, text); Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON FUNCTION roster_set(text, text, text, text) IS 'Add or update a roster item
| |
| − | Return added/updated row';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 24 (class 1255 OID 16401)
| |
| − | -- Dependencies: 311 3
| |
| − | -- Name: roster_set_full(text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION roster_set_full(text, text, text, text, text) RETURNS SETOF record
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _contact ALIAS FOR $2;
| |
| − | _name ALIAS FOR $3;
| |
| − | _groups ALIAS FOR $4;
| |
| − | _sub ALIAS FOR $5;
| |
| − | data record;
| |
| − | BEGIN
| |
| − | SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
| |
| − | IF not found THEN
| |
| − | INSERT INTO roster (username,contact,name,groups,subscription) VALUES (_username,_contact,_name,_groups,_sub);
| |
| − | ELSE
| |
| − | UPDATE roster SET name=_name, groups=_groups, subscription=_sub WHERE username=_username AND contact=_contact;
| |
| − | END IF;
| |
| − | SELECT INTO data username,contact,name,groups,subscription FROM roster WHERE username=_username AND contact=_contact;
| |
| − | return next data;
| |
| − | return;
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.roster_set_full(text, text, text, text, text) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1761 (class 0 OID 0)
| |
| − | -- Dependencies: 24
| |
| − | -- Name: FUNCTION roster_set_full(text, text, text, text, text); Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON FUNCTION roster_set_full(text, text, text, text, text) IS 'Fully add or update a roster item (including subscription).
| |
| − | Return added/updated row
| |
| − | ';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 25 (class 1255 OID 16402)
| |
| − | -- Dependencies: 311 3
| |
| − | -- Name: subscription_set(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION subscription_set(text, text, text) RETURNS tex t
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _contact ALIAS FOR $2;
| |
| − | _sub ALIAS FOR $3;
| |
| − | data record;
| |
| − | BEGIN
| |
| − | SELECT INTO data * FROM roster WHERE username=_username AND contact=_contact;
| |
| − | IF not found THEN
| |
| − | INSERT INTO roster (username,contact,subscription) VALUES (_username,_contact,_sub);
| |
| − | ELSE
| |
| − | UPDATE roster SET subscription=_sub WHERE username=_username AND contact=_contact;
| |
| − | END IF;
| |
| − | return '''';
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.subscription_set(text, text, text) OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1762 (class 0 OID 0)
| |
| − | -- Dependencies: 25
| |
| − | -- Name: FUNCTION subscription_set(text, text, text); Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON FUNCTION subscription_set(text, text, text) IS 'Add or update a roster item subscription.
| |
| − | Return added/updated row
| |
| − | ';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 26 (class 1255 OID 16403)
| |
| − | -- Dependencies: 3 311
| |
| − | -- Name: user_add(text, text); Type: FUNCTION; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | CREATE FUNCTION user_add(text, text) RETURNS integer
| |
| − | AS $_$
| |
| − | DECLARE
| |
| − | _username ALIAS FOR $1;
| |
| − | _password ALIAS FOR $2;
| |
| − | n integer;
| |
| − | BEGIN
| |
| − | SELECT INTO n COUNT(*) FROM users WHERE username=_username;
| |
| − | IF n > 0 THEN
| |
| − | n := 0;
| |
| − | ELSE
| |
| − | INSERT INTO users (username,password) VALUES (_username,_password);
| |
| − | n := 1;
| |
| − | END IF;
| |
| − | RETURN n;
| |
| − | END;
| |
| − | $_$
| |
| − | LANGUAGE plpgsql;
| |
| − |
| |
| − |
| |
| − | ALTER FUNCTION public.user_add(text, text) OWNER TO postgres;
| |
| − |
| |
| − | SET default_tablespace = '';
| |
| − |
| |
| − | SET default_with_oids = true;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1483 (class 1259 OID 16410)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: lines; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
| |
| − | --
| |
| − |
| |
| − | CREATE TABLE lines (
| |
| − | line text,
| |
| − | location text
| |
| − | );
| |
| − |
| |
| − |
| |
| − | ALTER TABLE public.lines OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1763 (class 0 OID 0)
| |
| − | -- Dependencies: 1483
| |
| − | -- Name: COLUMN lines.location; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN lines.location IS 'User location';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1484 (class 1259 OID 16416)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: offlinechat; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
| |
| − | --
| |
| − |
| |
| − | CREATE TABLE offlinechat (
| |
| − | username text NOT NULL,
| |
| − | xml text NOT NULL,
| |
| − | "time" integer NOT NULL
| |
| − | );
| |
| − |
| |
| − |
| |
| − | ALTER TABLE public.offlinechat OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1764 (class 0 OID 0)
| |
| − | -- Dependencies: 1484
| |
| − | -- Name: TABLE offlinechat; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON TABLE offlinechat IS 'Store users offline chat';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1765 (class 0 OID 0)
| |
| − | -- Dependencies: 1484
| |
| − | -- Name: COLUMN offlinechat.xml; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN offlinechat.xml IS 'Chat';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1766 (class 0 OID 0)
| |
| − | -- Dependencies: 1484
| |
| − | -- Name: COLUMN offlinechat."time"; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN offlinechat."time" IS 'The time the message was receievd by the server';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1485 (class 1259 OID 16428)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: privatedata; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
| |
| − | --
| |
| − |
| |
| − | CREATE TABLE privatedata (
| |
| − | username text NOT NULL,
| |
| − | tag text NOT NULL,
| |
| − | xmlns text NOT NULL,
| |
| − | xml text NOT NULL
| |
| − | );
| |
| − |
| |
| − |
| |
| − | ALTER TABLE public.privatedata OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1767 (class 0 OID 0)
| |
| − | -- Dependencies: 1485
| |
| − | -- Name: TABLE privatedata; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON TABLE privatedata IS 'Users private data';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1768 (class 0 OID 0)
| |
| − | -- Dependencies: 1485
| |
| − | -- Name: COLUMN privatedata.username; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN privatedata.username IS 'User owning the private data';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1769 (class 0 OID 0)
| |
| − | -- Dependencies: 1485
| |
| − | -- Name: COLUMN privatedata.tag; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN privatedata.tag IS 'XML tag';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1770 (class 0 OID 0)
| |
| − | -- Dependencies: 1485
| |
| − | -- Name: COLUMN privatedata.xmlns; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN privatedata.xmlns IS 'XMPP namespace';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1771 (class 0 OID 0)
| |
| − | -- Dependencies: 1485
| |
| − | -- Name: COLUMN privatedata.xml; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON COLUMN privatedata.xml IS 'Private XML data';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1486 (class 1259 OID 16434)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: roster; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
| |
| − | --
| |
| − |
| |
| − | CREATE TABLE roster (
| |
| − | username text NOT NULL,
| |
| − | contact text NOT NULL,
| |
| − | subscription text,
| |
| − | name text,
| |
| − | groups text
| |
| − | );
| |
| − |
| |
| − |
| |
| − | ALTER TABLE public.roster OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1772 (class 0 OID 0)
| |
| − | -- Dependencies: 1486
| |
| − | -- Name: TABLE roster; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON TABLE roster IS 'Users roster';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1487 (class 1259 OID 16440)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
| |
| − | --
| |
| − |
| |
| − | CREATE TABLE users (
| |
| − | username text NOT NULL,
| |
| − | password text,
| |
| − | vcard text
| |
| − | );
| |
| − |
| |
| − |
| |
| − | ALTER TABLE public.users OWNER TO postgres;
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1773 (class 0 OID 0)
| |
| − | -- Dependencies: 1487
| |
| − | -- Name: TABLE users; Type: COMMENT; Schema: public; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | COMMENT ON TABLE users IS 'Users table';
| |
| − |
| |
| − |
| |
| − | --
| |
| − | -- TOC entry 1758 (class 0 OID 0)
| |
| − | -- Dependencies: 3
| |
| − | -- Name: public; Type: ACL; Schema: -; Owner: postgres
| |
| − | --
| |
| − |
| |
| − | REVOKE ALL ON SCHEMA public FROM PUBLIC;
| |
| − | REVOKE ALL ON SCHEMA public FROM postgres;
| |
| − | GRANT ALL ON SCHEMA public TO postgres;
| |
| − | GRANT ALL ON SCHEMA public TO PUBLIC;
| |
| − |
| |
| − | --
| |
| − | -- PostgreSQL database dump complete
| |
| − | --
| |
| − |
| |
| − | === Creating jabber users ===
| |
| − |
| |
| − | The examples below will add, change or remove an user '''gigi@example.com'''.<br>
| |
| − | This section describes the way users can be created:
| |
| − | * Use the pgAdmin utility to manually edit the '''users''' table
| |
| − | * Connect to yate using telnet. Issue the following commands:
| |
| − | ** Add: users add gigi@example.com password=some_password
| |
| − | ** Delete: users delete gigi@example.com
| |
| − | ** Change: users update gigi@example.com password=changed_password
| |
| − | * Any other database access tools and scripts
| |
| − |
| |
| − | '''Notes:'''
| |
| − | * The recommended method is to use the users module
| |
| − | * If an user is deleted using the users module all its connections to the server will be closed and all related data (roster, private data, offline messages) will be removed.
| |
| − |
| |
| − | === Generating a certificate ===
| |
| − |
| |
| − | See http://www.openssl.org/docs/HOWTO/certificates.txt for more info on how to create a certificate using the openssl utility.
| |
