I've created a dedicated database, removed the hosts table and created a view.
CREATE OR REPLACE VIEW public.hosts AS SELECT id as host_id, DECODE(REPLACE(LOWER(mac), ':',''), 'hex') as dhcp_identifier, 0 AS dhcp_identifier_type, subnet_id AS dhcp4_subnet_id, subnet_id AS dhcp6_subnet_id, inet_aton(ip4::text) AS ipv4_address, null AS hostname, null AS dhcp4_client_classes, null AS dhcp6_client_classes, null AS dhcp4_next_server, null AS dhcp4_server_hostname, null AS dhcp4_boot_file_name, null AS user_context, null AS auth_key FROM hosts_view WHERE LENGTH(REPLACE(mac, ':','')) = 12 Later I used this https://www.postgresql.org/docs/current/postgres-fdw.html And for me is enough, works pon., 1 cze 2020 o 08:14 Marcin Romanowski <[email protected]> napisał(a): > > > pon., 1 cze 2020 o 00:33 Dajka Tamás <[email protected]> napisał(a): > >> Ahh, I get your point, but I think there is a misunderstanding here. >> >> >> >> You’ve 2 options if you want to store host reservations in MySQL, but >> BOTH involves using kea’s schema, since it’s hardcoded into KEA: >> >> - use a separate database for KEA and ’replicate’ the data into >> it: >> >> o use the REST api from a script to push/update the records in KEA >> >> o use triggers in MySQL (put a trigger on you original hosts table for >> insert, delete and update, which will put/update the same data in kea’s db) >> >> - create a view in your database with same structure and name as >> in KEA’s database schema >> >> That is what I was afraid of :( On my current database I already have > table `hosts` so I cannot create view with the same name :( > > > > >> >> >> About the error: my_database.kea is invalid, since that points to a >> table, not to an entire database. In your case the database config should >> look like something like this (when using a view): >> >> >> >> "Dhcp4": { >> >> "hosts-database": { >> >> "type": "postgres", >> >> "name": "my_database", >> >> "user": "kea_readonly_user", >> >> "password": "secret123", >> >> "host": "localhost", >> >> "port": 5432, >> >> "readonly": true >> >> } >> >> } >> >> >> > > Yes, I know. I thought that kea suppoerts postgresql's schema. > > > > >> Cheers, >> >> >> >> Tom >> >> >> >> *From:* Kea-users [mailto:[email protected]] *On Behalf Of >> *Marcin Romanowski >> *Sent:* Sunday, May 31, 2020 11:49 PM >> *To:* [email protected] >> *Subject:* [Kea-users] Fwd: Storing host reservation in custom database >> >> >> >> >> >> niedz., 31 maj 2020 o 22:27 Dajka Tamás <[email protected]> napisał(a): >> >> Yes, you’re right, there should be just one doc J But you can’t stop >> others, from making a copy… (I did not check it, but I think ’ >> readthedocs.io’ is just a copy, or collector page, not any official >> documentation). >> >> >> >> Yeah, you're right >> >> >> >> >> >> >> >> >> >> Host reservation are in the docs, but it points to kea wiki@gitlab J >> (it’s a bit messy) >> >> >> >> Yes, host reservation are in the docs but assumes that there is >> separate database for kea. This link to wiki >> https://gitlab.isc.org/isc-projects/kea/wikis/designs/commands#23-host-reservations-hr-management >> does't provide any examples and is describing version v1.0 :( >> >> >> >> Currently (Kea 1.0), Kea allows storing host reservations in the >> configuration file and there's work in progress to allow storing HR in >> MySQL and PostgreSQL. >> >> >> >> I think It's out of date :) >> >> >> >> >> >> >> https://kea.readthedocs.io/en/kea-1.6.2/arm/dhcp4-srv.html#storing-host-reservations-in-mysql-postgresql-or-cassandra >> >> >> >> However, one link in the docs (not in the wiki) points you to the IPv6 >> setting, which is the same as v4: >> >> >> >> https://kea.readthedocs.io/en/kea-1.6.2/arm/dhcp6-srv.html#hosts6-storage >> >> >> >> (just replace Dhcp6 with Dhcp4 – use the appropriate config file) >> >> >> >> >> >> The same is in Dhcp4 :) But this is not the point. >> >> Of course, I can make seperate database designed for kea and store in >> hosts, but hosts I have in other database which is always up to date. >> >> >> >> According to this >> >> 9.2.3.2. Using Read-Only Databases for Host Reservations with DHCPv6 >> In some deployments the database user whose name is specified in the >> database backend configuration may not have write privileges to the >> database.[...]. In many cases administrators have deployed inventory >> databases, which contain substantially more information about the hosts >> than just the static reservations assigned to them. The *inventory >> database can be used to create a view* of a Kea hosts database and such >> a view is often *read-only*. >> [...] However, if access to a read-only host database is required for >> retrieving reservations for clients and/or assigning specific addresses and >> options, it is possible to explicitly configure Kea to start in “read-only” >> mode. This is controlled by the readonly boolean parameter >> >> >> >> I need to do it exactly in that way described above - using a view. But, >> kea when connects to database SELECTs from `hosts` table. In my database >> schema I have already that table and it isn't kea's schema. >> >> Second problem is that in official documentation there is no information >> what type of data I should return in my *view *(this I've found on >> https://gitlab.isc.org/isc-projects/kea/-/wikis/docs/editing-host-reservations >> ) >> >> >> >> So any help with nameing is welcome. I've try do create view between kea >> an my_database but postgres doesn't allow creating views between databases >> :( I also tried to create schema for kea in my_database but when I tried to >> configure name with schema "name = my_database.kea" this returned error >> connecting to database :( >> >> >> >> >> >> >> >> ps. Sorry Tom, I didn't notice that I ansewred directly to you instead to >> list :) >> >> >> >> Cheers >> >> >> >> >> >> >> >> Cheers, >> >> >> >> Tom >> >> >> >> >> >> *From:* Marcin Romanowski [mailto:[email protected]] >> *Sent:* Sunday, May 31, 2020 5:25 PM >> *To:* Dajka Tamás <[email protected]> >> *Subject:* Re: [Kea-users] Storing host reservation in custom database >> >> >> >> >> >> >> >> niedz., 31 maj 2020 o 16:58 Dajka Tamás <[email protected]> napisał(a): >> >> gitlab can be misleading, since it can contain the latest (unstable) >> version’s stuff. >> >> >> >> IMHO, readthedocs.io is the same as downloads.isc.org (but the later >> seems a bit more official to me). >> >> >> >> :) IMHO there should be one place where documentation is stored. >> >> >> >> >> >> >> >> Anyway, if you use ’kea-admin db-init’ as stated in the docs, that should >> create the tables for you: >> >> >> >> https://kea.readthedocs.io/en/kea-1.6.2/arm/admin.html >> >> >> >> https://kea.readthedocs.io/en/kea-1.6.2/arm/admin.html#mysql >> >> >> >> >> >> Yes, you are right, kea-admin can create schema for me, but if you are >> going to keep data in separate database and if you have write permissions. >> >> I'm going to store leases in a memory file, I'd like to get hosts >> reservations from my current database where these data are stored. So >> corresponding to documentation I want to use read-only "database" which be >> pointed to my database where I'm going to create a view. I have two >> problems with this: >> >> - kea needs hosts table which is already present in my schema >> >> - there is no in doc which and what type data i should "return" for >> hosts reservation. >> >> >> >> About first, I cannot find solution, where I can set configuration to >> point other table than hosts :) >> >> About second, I've found examples on >> https://gitlab.isc.org/isc-projects/kea/-/wikis/docs/editing-host-reservations >> (IMHO >> this should be in main documentary). >> >> >> >> >> >> >> >> *From:* Marcin Romanowski [mailto:[email protected]] >> *Sent:* Sunday, May 31, 2020 1:45 PM >> *To:* Dajka Tamás <[email protected]> >> *Subject:* Re: [Kea-users] Storing host reservation in custom database >> >> >> >> >> >> >> >> niedz., 31 maj 2020 o 13:22 Dajka Tamás <[email protected]> napisał(a): >> >> I think you should read the WHOLE documentation before trying to set up >> complex things. >> >> >> >> I have read whole documentation on >> https://kea.readthedocs.io/en/kea-1.6.2/index.html , problem is that >> documentation is spreaded, You provided me from downloads.isco.org, I >> read on kea.readthedocs.io and about db structure I have found on gitlab >> so which is official? There should be one place with documentation >> >> >> >> >> >> >> >> >> >> For the SQL you’ll have to set up the schema first – see docs -, set up >> KEA to use MySQL not just for leases, but for hosts too (separate part in >> ipv4/6 config) >> >> >> >> https://downloads.isc.org/isc/kea/1.7.7/doc/html/arm/admin.html <- start >> somewhere here >> >> >> >> >> >> Yes, I have read this and there is that I can provide read-only tables >> (views) from my own database but there is no explanation what data format I >> should return. >> >> >> >> >> >> This documentation >> https://downloads.isc.org/isc/kea/1.7.7/doc/html/arm/dhcp6-srv.html#using-read-only-databases-for-host-reservations-with-dhcpv6 >> also >> doesn't tell what type of data I should return and how to alias hosts >> table. >> >> In my database I already have hosts table but this isn't kea format >> >> >> >> >> >> So this is reason of my questions >> >> >> >> >> >> >> >> >> >> Cheers, >> >> >> >> Tom >> >> >> >> *From:* Kea-users [mailto:[email protected]] *On Behalf Of >> *Marcin Romanowski >> *Sent:* Sunday, May 31, 2020 1:06 PM >> *To:* [email protected] >> *Subject:* Re: [Kea-users] Storing host reservation in custom database >> >> >> >> I have found this documentation >> https://gitlab.isc.org/isc-projects/kea/-/wikis/docs/editing-host-reservations >> this >> what I was looking for. >> >> But I cannot find information, how to configure the "hosts" table :( In >> my database I have that table name already so I can prepare view but this >> cannot be named 'hosts" but kea make SELECT on this table :( >> >> >> >> niedz., 31 maj 2020 o 10:04 Marcin Romanowski <[email protected]> >> napisał(a): >> >> >> >> >> Hello, >> >> I'd like to store dhcpv4 host reservations in my database which is >> currently in production. In documentation I've found, that I can create my >> own view and configure it as read-only. >> >> >> >> In kea database schema there are columns in host table: >> >> host_id SERIAL PRIMARY KEY NOT NULL, >> dhcp_identifier BYTEA NOT NULL, >> dhcp_identifier_type SMALLINT NOT NULL, >> dhcp4_subnet_id INT DEFAULT NULL, >> dhcp6_subnet_id INT DEFAULT NULL, >> ipv4_address BIGINT DEFAULT NULL, >> hostname VARCHAR(255) DEFAULT NULL, >> dhcp4_client_classes VARCHAR(255) DEFAULT NULL, >> dhcp6_client_classes VARCHAR(255) DEFAULT NULL >> >> >> >> >> >> My question is about `dhcp_identifier` column and ipv4_address. This >> should be hw-address, duid corresponding to dhcp_identifier_type. In my >> database ipv4 address I store as ::inet. >> >> dhcp4_subnet_id is integer. So I have to in config add subnet_id argument >> or I can return string for example "192.168.12.0/24" as subnet? >> >> How could I return data in my view to be proper format for kea? >> >> >> >> Best regards >> >> MarcinR >> >> >> >> >> >> >> >> >> -- >> >> Marcin Romanowski / nicraM >> >> > > -- > Marcin Romanowski / nicraM > > > -- Marcin Romanowski / nicraM
_______________________________________________ ISC funds the development of this software with paid support subscriptions. Contact us at https://www.isc.org/contact/ for more information. To unsubscribe visit https://lists.isc.org/mailman/listinfo/kea-users. Kea-users mailing list [email protected] https://lists.isc.org/mailman/listinfo/kea-users
