63 lines
1.7 KiB
PL/PgSQL
63 lines
1.7 KiB
PL/PgSQL
SET client_min_messages TO WARNING;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS geoip;
|
|
SET search_path TO geoip, public;
|
|
|
|
CREATE TABLE IF NOT EXISTS geoip_metadata (
|
|
key text PRIMARY KEY,
|
|
value text NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS city_lookup (
|
|
network cidr PRIMARY KEY,
|
|
geoname_id integer,
|
|
country text,
|
|
country_iso_code text,
|
|
region text,
|
|
region_iso_code text,
|
|
city text,
|
|
latitude double precision,
|
|
longitude double precision,
|
|
time_zone text
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS city_lookup_network_gist ON city_lookup USING gist (network inet_ops);
|
|
CREATE INDEX IF NOT EXISTS city_lookup_geoname_id_idx ON city_lookup (geoname_id);
|
|
|
|
CREATE OR REPLACE FUNCTION lookup_city(ip inet)
|
|
RETURNS TABLE (
|
|
ip inet,
|
|
country text,
|
|
region text,
|
|
city text,
|
|
latitude double precision,
|
|
longitude double precision
|
|
) LANGUAGE sql STABLE AS $$
|
|
SELECT
|
|
$1::inet AS ip,
|
|
c.country,
|
|
c.region,
|
|
c.city,
|
|
c.latitude,
|
|
c.longitude
|
|
FROM city_lookup c
|
|
WHERE c.network >>= $1
|
|
ORDER BY masklen(c.network) DESC
|
|
LIMIT 1;
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'geoip_readonly') THEN
|
|
CREATE ROLE geoip_readonly LOGIN PASSWORD 'geoip_readonly';
|
|
ALTER ROLE geoip_readonly SET default_transaction_read_only = on;
|
|
END IF;
|
|
END$$;
|
|
|
|
GRANT USAGE ON SCHEMA geoip TO geoip_readonly;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA geoip TO geoip_readonly;
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA geoip TO geoip_readonly;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA geoip GRANT SELECT ON TABLES TO geoip_readonly;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA geoip GRANT EXECUTE ON FUNCTIONS TO geoip_readonly;
|