When attempting to create the extra modules tables using "kamdbctl create" using the PGSQL DBENGINE the create fails when kamdbctl tries to set the permissions on the sequences that are to be created with the table.
Example output (dr_gateways): ``` # kamdbctl create INFO: creating database kamailio_alexia ... INFO: Core Kamailio tables succesfully created. INFO: creating presence tables into kamailio_alexia ... INFO: Presence tables succesfully created. INFO: creating extra tables into kamailio_alexia ... ERROR: relation "dr_gateways_id_seq" does not exist ERROR: relation "dr_gateways_id_seq" does not exist ERROR: Grant privileges to extra tables failed! ```
Example output (dr_rules) ``` # kamdbctl create INFO: creating database kamailio_alexia ... INFO: Core Kamailio tables succesfully created. INFO: creating presence tables into kamailio_alexia ... INFO: Presence tables succesfully created. INFO: creating extra tables into kamailio_alexia ... ERROR: relation "dr_rules_id_seq" does not exist ERROR: relation "dr_rules_id_seq" does not exist ERROR: Grant privileges to extra tables failed! ```
This appears to be related to to primary key being being different in these two tables compared to what kamdbctl expects.
drouting-create.sql ``` # cat drouting-create.sql CREATE TABLE dr_gateways ( gwid SERIAL PRIMARY KEY NOT NULL, type INTEGER DEFAULT 0 NOT NULL, address VARCHAR(128) NOT NULL, strip INTEGER DEFAULT 0 NOT NULL, pri_prefix VARCHAR(64) DEFAULT NULL, attrs VARCHAR(255) DEFAULT NULL, description VARCHAR(128) DEFAULT '' NOT NULL );
INSERT INTO version (table_name, table_version) values ('dr_gateways','3');
CREATE TABLE dr_rules ( ruleid SERIAL PRIMARY KEY NOT NULL, groupid VARCHAR(255) NOT NULL, prefix VARCHAR(64) NOT NULL, timerec VARCHAR(255) NOT NULL, priority INTEGER DEFAULT 0 NOT NULL, routeid VARCHAR(64) NOT NULL, gwlist VARCHAR(255) NOT NULL, description VARCHAR(128) DEFAULT '' NOT NULL );
INSERT INTO version (table_name, table_version) values ('dr_rules','3');
CREATE TABLE dr_gw_lists ( id SERIAL PRIMARY KEY NOT NULL, gwlist VARCHAR(255) NOT NULL, description VARCHAR(128) DEFAULT '' NOT NULL );
INSERT INTO version (table_name, table_version) values ('dr_gw_lists','1');
CREATE TABLE dr_groups ( id SERIAL PRIMARY KEY NOT NULL, username VARCHAR(64) NOT NULL, domain VARCHAR(128) DEFAULT '' NOT NULL, groupid INTEGER DEFAULT 0 NOT NULL, description VARCHAR(128) DEFAULT '' NOT NULL );
INSERT INTO version (table_name, table_version) values ('dr_groups','2'); ```
Then in kamdbctl.pgsql we see the following section for granting permissions and this is where it appears to fail:
kamdbctl.pgsql ``` 241 for TABLE in $EXTRA_TABLES; do 242 sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;" 243 sql_query "$1" "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;" 244 if [ $TABLE != "route_tree" ] ; then 245 sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE "$TABLE"_id_seq TO $DBRWUSER;" 246 sql_query "$1" "GRANT SELECT ON TABLE "$TABLE"_id_seq TO $DBROUSER;" 247 fi 248 if [ $? -ne 0 ] ; then 249 merr "Grant privileges to extra tables failed!" 250 exit 1 251 fi 252 done ```
Here it clearly expects the primary key of each table to be called "id", and thus the associated sequences created will be ${TABLE}_id_seq, however in this case these fail as the primary keys for these two tables are named differently.
If I modify the primary keys for the dr_gateways and dr_rules tables to be "id", then kamdbctl is able to complete creating the tables.
Looking at the drouting module documentation it appears that the module expects these primary keys to be called "gwid" and "ruleid".
Can someone take a look please.