pgdb/gutenberg_robots.sql at main · gutenbergtools/pgdb · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.15
-- Dumped by pg_dump version 10.15

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: robots; Type: SCHEMA; Schema: -; Owner: gutenberg
--

CREATE SCHEMA robots;


ALTER SCHEMA robots OWNER TO gutenberg;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: blocks; Type: TABLE; Schema: robots; Owner: gutenberg
--

CREATE TABLE robots.blocks (
    ip cidr NOT NULL,
    host text,
    has_info boolean DEFAULT false,
    is_blocked boolean DEFAULT true,
    is_whitelisted boolean DEFAULT false,
    created timestamp without time zone DEFAULT now(),
    expires timestamp without time zone,
    types text[],
    org text,
    country text,
    note text,
    whois text,
    user_agents text[],
    requests text[],
    count integer DEFAULT 1 NOT NULL,
    hits integer DEFAULT 0 NOT NULL,
    cidr cidr,
    asn text,
    client_ip cidr,
    proxy_type text,
    proxy_ip cidr,
    headers text[]
);


ALTER TABLE robots.blocks OWNER TO gutenberg;

SET default_with_oids = true;

--
-- Name: ips; Type: TABLE; Schema: robots; Owner: gutenberg
--

CREATE TABLE robots.ips (
    ip inet,
    firstseen timestamp without time zone,
    lastseen timestamp without time zone,
    hits integer,
    rhits integer,
    hhits integer,
    ua character varying
);


ALTER TABLE robots.ips OWNER TO gutenberg;

--
-- Name: blocks blocks_pkey; Type: CONSTRAINT; Schema: robots; Owner: gutenberg
--

ALTER TABLE ONLY robots.blocks
    ADD CONSTRAINT blocks_pkey PRIMARY KEY (ip);


--
-- Name: ix_blocks_expires; Type: INDEX; Schema: robots; Owner: gutenberg
--

CREATE INDEX ix_blocks_expires ON robots.blocks USING btree (expires);


--
-- Name: ix_ips_ip; Type: INDEX; Schema: robots; Owner: gutenberg
--

CREATE INDEX ix_ips_ip ON robots.ips USING btree (ip);


--
-- Name: SCHEMA robots; Type: ACL; Schema: -; Owner: gutenberg
--

GRANT USAGE ON SCHEMA robots TO backupuser;


--
-- Name: TABLE blocks; Type: ACL; Schema: robots; Owner: gutenberg
--

GRANT SELECT ON TABLE robots.blocks TO backupuser;


--
-- Name: TABLE ips; Type: ACL; Schema: robots; Owner: gutenberg
--

GRANT SELECT ON TABLE robots.ips TO backupuser;


--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: robots; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots REVOKE ALL ON SEQUENCES  FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots GRANT ALL ON SEQUENCES  TO gutenberg;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots GRANT SELECT ON SEQUENCES  TO backupuser;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: robots; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots REVOKE ALL ON TABLES  FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots GRANT ALL ON TABLES  TO gutenberg;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA robots GRANT SELECT ON TABLES  TO backupuser;


--
-- PostgreSQL database dump complete
--