[Kea-users] Fwd: Storing host reservation in custom database
Marcin Romanowski
marcin at nicram.net
Mon Jun 1 11:27:42 UTC 2020
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 <marcin at nicram.net> napisał(a):
>
>
> pon., 1 cze 2020 o 00:33 Dajka Tamás <viper at vipernet.hu> 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:kea-users-bounces at lists.isc.org] *On Behalf Of
>> *Marcin Romanowski
>> *Sent:* Sunday, May 31, 2020 11:49 PM
>> *To:* kea-users at lists.isc.org
>> *Subject:* [Kea-users] Fwd: Storing host reservation in custom database
>>
>>
>>
>>
>>
>> niedz., 31 maj 2020 o 22:27 Dajka Tamás <viper at vipernet.hu> 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 at 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:marcin at nicram.net]
>> *Sent:* Sunday, May 31, 2020 5:25 PM
>> *To:* Dajka Tamás <viper at vipernet.hu>
>> *Subject:* Re: [Kea-users] Storing host reservation in custom database
>>
>>
>>
>>
>>
>>
>>
>> niedz., 31 maj 2020 o 16:58 Dajka Tamás <viper at vipernet.hu> 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:marcin at nicram.net]
>> *Sent:* Sunday, May 31, 2020 1:45 PM
>> *To:* Dajka Tamás <viper at vipernet.hu>
>> *Subject:* Re: [Kea-users] Storing host reservation in custom database
>>
>>
>>
>>
>>
>>
>>
>> niedz., 31 maj 2020 o 13:22 Dajka Tamás <viper at vipernet.hu> 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:kea-users-bounces at lists.isc.org] *On Behalf Of
>> *Marcin Romanowski
>> *Sent:* Sunday, May 31, 2020 1:06 PM
>> *To:* kea-users at lists.isc.org
>> *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 <marcin at nicram.net>
>> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.isc.org/pipermail/kea-users/attachments/20200601/32bf2fa3/attachment-0001.htm>
More information about the Kea-users
mailing list