Show
Ignore:
Timestamp:
06/11/08 02:10:17 (2 years ago)
Author:
benoitg
Message:
  • Authenticator.php: Fix SQL error closing connections.
  • auth/index.php: Probably fix the "Access denied" message when user click twice problem that resurfaced.
  • At long last, implement operational Dynamic abuse control (out of the embryo of the token architecture)! Configurable in Network preferences if you have the permissions. Unlike static limits, this is a sliding window (typically a month) during which user have a bandwidth and connection duration limit, per node, and totalled throughout the network.
  • UIAllowedBandwidth: New, simplistic content type to show the user how much of his allocation he consumed. Off course, the goal is actually to make it into a pretty graph....
Files:
1 modified

Legend:

Unmodified
Added
Removed

  • trunk/wifidog-auth/sql/wifidog-postgres-schema.sql

    r1261 r1351  
    2525 
    2626-- 
    27 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: - 
    28 -- 
    29  
    30 COMMENT ON SCHEMA public IS 'Standard public schema'; 
    31  
    32  
    33 -- 
    3427-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: - 
    3528-- 
    … …  
    5043CREATE TABLE connections ( 
    5144    conn_id integer NOT NULL, 
    52     token character varying(32) DEFAULT ''::character varying NOT NULL, 
    53     token_status character varying(10) DEFAULT 'UNUSED'::character varying NOT NULL, 
     45    token_id character varying(32) DEFAULT ''::character varying NOT NULL, 
    5446    timestamp_in timestamp without time zone, 
    5547    node_id character varying(32), 
    … …  
    6153    last_updated timestamp without time zone NOT NULL, 
    6254    incoming bigint, 
    63     outgoing bigint 
    64 ); 
    65  
    66  
    67 -- 
    68 -- Name: connections_conn_id_seq; Type: SEQUENCE; Schema: public; Owner: - 
    69 -- 
    70  
    71 CREATE SEQUENCE connections_conn_id_seq 
    72     INCREMENT BY 1 
    73     NO MAXVALUE 
    74     NO MINVALUE 
    75     CACHE 1; 
    76  
    77  
    78 -- 
    79 -- Name: connections_conn_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 
    80 -- 
    81  
    82 ALTER SEQUENCE connections_conn_id_seq OWNED BY connections.conn_id; 
     55    outgoing bigint, 
     56    max_total_bytes integer, 
     57    max_incoming_bytes integer, 
     58    max_outgoing_bytes integer, 
     59    expiration_date timestamp without time zone, 
     60    logout_reason integer 
     61); 
    8362 
    8463 
    … …  
    295274CREATE TABLE content_key_value_pairs ( 
    296275    content_id text NOT NULL, 
    297     "key" text NOT NULL, 
     276    key text NOT NULL, 
    298277    value text 
    299278); 
    … …  
    451430    gmaps_map_type text DEFAULT 'G_NORMAL_MAP'::text NOT NULL, 
    452431    theme_pack text, 
     432    connection_limit_window interval, 
     433    connection_limit_network_max_total_bytes integer, 
     434    connection_limit_network_max_usage_duration interval, 
     435    connection_limit_node_max_total_bytes integer, 
     436    connection_limit_node_max_usage_duration interval, 
    453437    CONSTRAINT networks_gmaps_map_type CHECK ((gmaps_map_type <> ''::text)), 
    454438    CONSTRAINT networks_name CHECK ((name <> ''::text)), 
    … …  
    536520    last_heartbeat_wifidog_uptime integer, 
    537521    last_heartbeat_sys_memfree integer, 
    538     last_heartbeat_sys_load real 
     522    last_heartbeat_sys_load real, 
     523    connection_limit_node_max_total_bytes_override integer, 
     524    connection_limit_node_max_usage_duration_override interval 
    539525); 
    540526 
    … …  
    672658 
    673659 
     660-- 
     661-- Name: token_lots; Type: TABLE; Schema: public; Owner: -; Tablespace:  
     662-- 
     663 
     664CREATE TABLE token_lots ( 
     665    token_lot_id text NOT NULL, 
     666    token_lot_comment text, 
     667    token_lot_creation_date timestamp without time zone DEFAULT now() NOT NULL 
     668); 
     669 
     670 
    674671SET default_with_oids = true; 
    675672 
    … …  
    682679); 
    683680 
     681 
     682SET default_with_oids = false; 
     683 
     684-- 
     685-- Name: token_templates; Type: TABLE; Schema: public; Owner: -; Tablespace:  
     686-- 
     687 
     688CREATE TABLE token_templates ( 
     689    token_template_id text NOT NULL, 
     690    token_template_network text NOT NULL, 
     691    token_template_creation_date timestamp without time zone DEFAULT now() NOT NULL, 
     692    token_max_incoming_data integer, 
     693    token_max_outgoing_data integer, 
     694    token_max_total_data integer, 
     695    token_max_connection_duration interval, 
     696    token_max_usage_duration interval, 
     697    token_max_wall_clock_duration interval, 
     698    token_max_age interval, 
     699    token_is_reusable boolean DEFAULT true 
     700); 
     701 
     702 
     703-- 
     704-- Name: tokens; Type: TABLE; Schema: public; Owner: -; Tablespace:  
     705-- 
     706 
     707CREATE TABLE tokens ( 
     708    token_id text NOT NULL, 
     709    token_template_id text, 
     710    token_status text, 
     711    token_lot_id text, 
     712    token_creation_date timestamp without time zone DEFAULT now() NOT NULL, 
     713    token_issuer text NOT NULL, 
     714    token_owner text 
     715); 
     716 
     717 
     718-- 
     719-- Name: tokens_template_valid_nodes; Type: TABLE; Schema: public; Owner: -; Tablespace:  
     720-- 
     721 
     722CREATE TABLE tokens_template_valid_nodes ( 
     723    token_template_id text NOT NULL, 
     724    token_valid_at_node text NOT NULL 
     725); 
     726 
     727 
     728SET default_with_oids = true; 
    684729 
    685730-- 
    … …  
    723768    never_show_username boolean DEFAULT false, 
    724769    prefered_locale text, 
     770    open_id_url text, 
    725771    CONSTRAINT check_user_not_empty CHECK (((user_id)::text <> ''::text)) 
    726772); 
    … …  
    764810 
    765811-- 
     812-- Name: connections_conn_id_seq; Type: SEQUENCE; Schema: public; Owner: - 
     813-- 
     814 
     815CREATE SEQUENCE connections_conn_id_seq 
     816    INCREMENT BY 1 
     817    NO MAXVALUE 
     818    NO MINVALUE 
     819    CACHE 1; 
     820 
     821 
     822-- 
     823-- Name: connections_conn_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - 
     824-- 
     825 
     826ALTER SEQUENCE connections_conn_id_seq OWNED BY connections.conn_id; 
     827 
     828 
     829-- 
    766830-- Name: conn_id; Type: DEFAULT; Schema: public; Owner: - 
    767831-- 
    … …  
    847911 
    848912ALTER TABLE ONLY content_key_value_pairs 
    849     ADD CONSTRAINT content_key_value_pairs_pkey PRIMARY KEY (content_id, "key"); 
     913    ADD CONSTRAINT content_key_value_pairs_pkey PRIMARY KEY (content_id, key); 
    850914 
    851915 
    … …  
    10991163 
    11001164-- 
     1165-- Name: token_lots_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
     1166-- 
     1167 
     1168ALTER TABLE ONLY token_lots 
     1169    ADD CONSTRAINT token_lots_pkey PRIMARY KEY (token_lot_id); 
     1170 
     1171 
     1172-- 
    11011173-- Name: token_status_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
    11021174-- 
    … …  
    11071179 
    11081180-- 
     1181-- Name: token_templates_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
     1182-- 
     1183 
     1184ALTER TABLE ONLY token_templates 
     1185    ADD CONSTRAINT token_templates_pkey PRIMARY KEY (token_template_id); 
     1186 
     1187 
     1188-- 
     1189-- Name: tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
     1190-- 
     1191 
     1192ALTER TABLE ONLY tokens 
     1193    ADD CONSTRAINT tokens_pkey PRIMARY KEY (token_id); 
     1194 
     1195 
     1196-- 
     1197-- Name: tokens_template_valid_nodes_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
     1198-- 
     1199 
     1200ALTER TABLE ONLY tokens_template_valid_nodes 
     1201    ADD CONSTRAINT tokens_template_valid_nodes_pkey PRIMARY KEY (token_template_id, token_valid_at_node); 
     1202 
     1203 
     1204-- 
    11091205-- Name: user_has_content_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:  
    11101206-- 
    … …  
    11831279 
    11841280-- 
     1281-- Name: idx_content_display_log; Type: INDEX; Schema: public; Owner: -; Tablespace:  
     1282-- 
     1283 
     1284CREATE INDEX idx_content_display_log ON content_display_log USING btree (last_display_timestamp); 
     1285 
     1286 
     1287-- 
    11851288-- Name: idx_content_group_element_content_group_id; Type: INDEX; Schema: public; Owner: -; Tablespace:  
    11861289-- 
    … …  
    12111314 
    12121315-- 
     1316-- Name: idx_nodes_node_deployment_status; Type: INDEX; Schema: public; Owner: -; Tablespace:  
     1317-- 
     1318 
     1319CREATE INDEX idx_nodes_node_deployment_status ON nodes USING btree (node_deployment_status); 
     1320 
     1321 
     1322-- 
    12131323-- Name: idx_token; Type: INDEX; Schema: public; Owner: -; Tablespace:  
    12141324-- 
    12151325 
    1216 CREATE INDEX idx_token ON connections USING btree (token); 
    1217  
    1218  
    1219 -- 
    1220 -- Name: idx_token_status_and_user_id; Type: INDEX; Schema: public; Owner: -; Tablespace:  
    1221 -- 
    1222  
    1223 CREATE INDEX idx_token_status_and_user_id ON connections USING btree (token_status, user_id); 
     1326CREATE INDEX idx_token ON connections USING btree (token_id); 
     1327 
     1328 
     1329-- 
     1330-- Name: idx_token_status; Type: INDEX; Schema: public; Owner: -; Tablespace:  
     1331-- 
     1332 
     1333CREATE INDEX idx_token_status ON tokens USING btree (token_status); 
    12241334 
    12251335 
    … …  
    12321342 
    12331343-- 
     1344-- Name: idx_users_topen_id_url; Type: INDEX; Schema: public; Owner: -; Tablespace:  
     1345-- 
     1346 
     1347CREATE INDEX idx_users_topen_id_url ON users USING btree (open_id_url); 
     1348 
     1349 
     1350-- 
    12341351-- Name: profile_template_fields_semantic_id; Type: INDEX; Schema: public; Owner: -; Tablespace:  
    12351352-- 
    12361353 
    12371354CREATE INDEX profile_template_fields_semantic_id ON profile_template_fields USING btree (semantic_id); 
    1238  
    1239  
    1240 -- 
    1241 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: - 
    1242 -- 
    1243  
    1244 ALTER TABLE ONLY connections 
    1245     ADD CONSTRAINT "$1" FOREIGN KEY (token_status) REFERENCES token_status(token_status); 
    12461355 
    12471356 
    … …  
    16071716 
    16081717-- 
     1718-- Name: fk_roles; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1719-- 
     1720 
     1721ALTER TABLE ONLY network_stakeholders 
     1722    ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles(role_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1723 
     1724 
     1725-- 
     1726-- Name: fk_roles; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1727-- 
     1728 
     1729ALTER TABLE ONLY node_stakeholders 
     1730    ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles(role_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1731 
     1732 
     1733-- 
     1734-- Name: fk_roles; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1735-- 
     1736 
     1737ALTER TABLE ONLY server_stakeholders 
     1738    ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles(role_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1739 
     1740 
     1741-- 
     1742-- Name: fk_tokens; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1743-- 
     1744 
     1745ALTER TABLE ONLY connections 
     1746    ADD CONSTRAINT fk_tokens FOREIGN KEY (token_id) REFERENCES tokens(token_id) ON UPDATE CASCADE ON DELETE RESTRICT; 
     1747 
     1748 
     1749-- 
    16091750-- Name: fk_users; Type: FK CONSTRAINT; Schema: public; Owner: - 
    16101751-- 
    … …  
    17911932 
    17921933-- 
     1934-- Name: token_templates_token_template_network_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1935-- 
     1936 
     1937ALTER TABLE ONLY token_templates 
     1938    ADD CONSTRAINT token_templates_token_template_network_fkey FOREIGN KEY (token_template_network) REFERENCES networks(network_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1939 
     1940 
     1941-- 
     1942-- Name: tokens_template_valid_nodes_token_template_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1943-- 
     1944 
     1945ALTER TABLE ONLY tokens_template_valid_nodes 
     1946    ADD CONSTRAINT tokens_template_valid_nodes_token_template_id_fkey FOREIGN KEY (token_template_id) REFERENCES token_templates(token_template_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1947 
     1948 
     1949-- 
     1950-- Name: tokens_template_valid_nodes_token_valid_at_node_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1951-- 
     1952 
     1953ALTER TABLE ONLY tokens_template_valid_nodes 
     1954    ADD CONSTRAINT tokens_template_valid_nodes_token_valid_at_node_fkey FOREIGN KEY (token_valid_at_node) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1955 
     1956 
     1957-- 
     1958-- Name: tokens_token_issuer_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1959-- 
     1960 
     1961ALTER TABLE ONLY tokens 
     1962    ADD CONSTRAINT tokens_token_issuer_fkey FOREIGN KEY (token_issuer) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1963 
     1964 
     1965-- 
     1966-- Name: tokens_token_lot_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1967-- 
     1968 
     1969ALTER TABLE ONLY tokens 
     1970    ADD CONSTRAINT tokens_token_lot_id_fkey FOREIGN KEY (token_lot_id) REFERENCES token_lots(token_lot_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1971 
     1972 
     1973-- 
     1974-- Name: tokens_token_owner_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1975-- 
     1976 
     1977ALTER TABLE ONLY tokens 
     1978    ADD CONSTRAINT tokens_token_owner_fkey FOREIGN KEY (token_owner) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1979 
     1980 
     1981-- 
     1982-- Name: tokens_token_status_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1983-- 
     1984 
     1985ALTER TABLE ONLY tokens 
     1986    ADD CONSTRAINT tokens_token_status_fkey FOREIGN KEY (token_status) REFERENCES token_status(token_status) ON UPDATE CASCADE ON DELETE RESTRICT; 
     1987 
     1988 
     1989-- 
     1990-- Name: tokens_token_template_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
     1991-- 
     1992 
     1993ALTER TABLE ONLY tokens 
     1994    ADD CONSTRAINT tokens_token_template_id_fkey FOREIGN KEY (token_template_id) REFERENCES token_templates(token_template_id) ON UPDATE CASCADE ON DELETE CASCADE; 
     1995 
     1996 
     1997-- 
    17931998-- Name: user_has_profiles_profile_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - 
    17941999--