[sr-dev] [kamailio/kamailio] v5.0.3: "kamdbctl create" fails to grant permissions on drouting sequences. (#1256)

[00Asgaroth00]

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.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1256
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-dev/attachments/20170929/2708def0/attachment.html>