[Kea-users] Storing host reservation in custom database

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Kea-users] Storing host reservation in custom database

Marcin Romanowski


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




_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Storing host reservation in custom database

Marcin Romanowski
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 <[hidden email]> 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



_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Storing host reservation in custom database

Dajka Tamás

I think you should read the WHOLE documentation before trying to set up complex things.

 

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

 

Cheers,

 

               Tom

 

From: Kea-users [mailto:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 1:06 PM
To: [hidden email]
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 <[hidden email]> 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


_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

[Kea-users] Fwd: Storing host reservation in custom database

Marcin Romanowski

niedz., 31 maj 2020 o 22:27 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 5:25 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 16:58 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 1:45 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 13:22 Dajka Tamás <[hidden email]> 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:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 1:06 PM
To: [hidden email]
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 <[hidden email]> 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



_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Fwd: Storing host reservation in custom database

Dajka Tamás

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

 

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

    }

}

 

Cheers,

 

               Tom

 

From: Kea-users [mailto:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 11:49 PM
To: [hidden email]
Subject: [Kea-users] Fwd: Storing host reservation in custom database

 

 

niedz., 31 maj 2020 o 22:27 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 5:25 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 16:58 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 1:45 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 13:22 Dajka Tamás <[hidden email]> 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:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 1:06 PM
To: [hidden email]
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 <[hidden email]> 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


_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Fwd: Storing host reservation in custom database

Marcin Romanowski
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


And for me is enough, works

pon., 1 cze 2020 o 08:14 Marcin Romanowski <[hidden email]> napisał(a):


pon., 1 cze 2020 o 00:33 Dajka Tamás <[hidden email]> 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:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 11:49 PM
To: [hidden email]
Subject: [Kea-users] Fwd: Storing host reservation in custom database

 

 

niedz., 31 maj 2020 o 22:27 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 5:25 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 16:58 Dajka Tamás <[hidden email]> 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:[hidden email]]
Sent: Sunday, May 31, 2020 1:45 PM
To: Dajka Tamás <[hidden email]>
Subject: Re: [Kea-users] Storing host reservation in custom database

 

 

 

niedz., 31 maj 2020 o 13:22 Dajka Tamás <[hidden email]> 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:[hidden email]] On Behalf Of Marcin Romanowski
Sent: Sunday, May 31, 2020 1:06 PM
To: [hidden email]
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 <[hidden email]> 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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [Kea-users] Storing host reservation in custom database

Tomek Mrugalski-2
In reply to this post by Marcin Romanowski
On 31.05.2020 13:06, Marcin Romanowski wrote:
> 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 :(
Kea expects the tables to match exactly with the provided schema. There
is no way to tell Kea to use a differently named table. And there won't
be. Implementing such a thing would be asking for troubles. It would
be very easy to misconfigure it and also it would break down in some
non-obvious ways.

As others have suggested, please read section 4 of the Kea ARM.
The kea-admin tool uses schema scripts in
src/share/database/scripts/{mysql,pgsql,cql} to create the schema. You
may take a look at the
schema files if you want to understand the internals.

You may take a look at those.

Tomek
_______________________________________________
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users