[Spce-user] Fwd: after db restore - DB provisioning access denied

[Andy Clark]

---------- Forwarded message ---------
From: Andy Clark <andyclark05251978 at gmail.com>
Date: Thu, Jun 13, 2019 at 5:36 AM
Subject: Re: [Spce-user] after db restore - DB provisioning access denied
To: Alex Lutay <alutay at sipwise.com>


Hi Alex,
Still getting the same error after

ngcp-sync-constants -root
ngcp-sync-constants -verbose -root
ngcpcfg apply

below are the details

---------- backup version 6.5.4 using  ----------
1. ngcp-update
2. ngcpcfg apply
3. mysqldump --all-databases --single-transaction  --quick
--lock-tables=false > /buckup/full-backup-$(date +%F).sql -u root -p
4. tar -zcvf etc_mysql_sipwise.tar.gz /etc/mysql/sipwise.cnf

---------- restore to new server version 6.5.4 ----------
1. copied full-backup-2019-06-12.sql from old server to new server
2. mysql  -u root -p < /backup/full-backup-2019-06-12.sql
3. vim /etc/mysql/sipwise.cnf
          # SIPWISE_DB_PASSWORD='XXXXXXXXXXXXXXXXX' existing
          SIPWISE_DB_PASSWORD='YYYYYYYYYYYYYYYYYYY' # from backup server
4. ngcpcfg apply
---------- --------------------------------------------------


--------------------then got this error message--------------------

DBI connect('database=provisioning;host=localhost;port=3306','sipwise',...)
failed: Access denied for user 'sipwise'@'localhost' (using password: YES)
at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.
                     Error: Could not connect to database 'provisioning' at
'localhost:3306' as 'sipwise': Access denied for user 'sipwise'@'localhost'
(using password: YES)

----------this morning it tried as you suggested----------
ngcp-sync-grants --verbose --recreate-user
ngcp-sync-constants -verbose -root
ngcpcfg apply



----------after running----------

ngcp-sync-grants --verbose --recreate-user
--> connected to localhost:3306 as sipwise
--> host: spce(self)
--> drop: asterisk at localhost
--> flush privileges
-->   [defaults.asterisk.localhost]
-->   flush privileges
-->     revoke all from: asterisk at localhost
-->     grant all privileges on kamailio.voicemail_users to
asterisk at localhost
-->     grant select on billing.v_subscriber_timezone to asterisk at localhost
-->     grant all privileges on kamailio.voicemail_spool to
asterisk at localhost
-->     grant select on provisioning.voip_dbaliases to asterisk at localhost
-->     grant select on provisioning.voip_subscribers to asterisk at localhost
-->     drop: dbcleaner at localhost
-->     flush privileges
-->   [defaults.dbcleaner.localhost]
-->   flush privileges
-->     revoke all from: dbcleaner at localhost
-->     grant all privileges on accounting.* to dbcleaner at localhost
-->     grant all privileges on kamailio.* to dbcleaner at localhost
-->     drop: debian-sys-maint at localhost
-->     flush privileges
-->   [defaults.debian-sys-maint.localhost]
-->   flush privileges
-->     revoke all from: debian-sys-maint at localhost
-->     grant all privileges on *.* to debian-sys-maint at localhost
-->     drop: exporter at localhost
-->     flush privileges
-->   [defaults.exporter.localhost]
-->   flush privileges
-->     revoke all from: exporter at localhost
-->     grant select on billing.* to exporter at localhost
-->     grant select on provisioning.* to exporter at localhost
-->     grant select on accounting.events_tag_data to exporter at localhost
-->     grant select on accounting.cdr_provider to exporter at localhost
-->     grant select on accounting.cdr_cash_balance to exporter at localhost
-->     grant select,update on accounting.events to exporter at localhost
-->     grant select,update on accounting.cdr to exporter at localhost
-->     grant select on accounting.cdr_relation to exporter at localhost
-->     grant select on billing.billing_zones_history to exporter at localhost
-->     grant select on accounting.cdr_cash_balance_data to
exporter at localhost
-->     grant select on accounting.events_tag to exporter at localhost
-->     grant select on accounting.cdr_relation_data to exporter at localhost
-->     grant select on accounting.cdr_export_status to exporter at localhost
-->     grant select on accounting.cdr_tag to exporter at localhost
-->     grant select on accounting.cdr_tag_data to exporter at localhost
-->     grant select on billing.resellers to exporter at localhost
-->     grant select,insert,update on accounting.mark to exporter at localhost
-->     grant select,insert,update on accounting.cdr_export_status_data to
exporter at localhost
-->     grant select on billing.voip_subscribers to exporter at localhost
-->     grant select on accounting.cdr_direction to exporter at localhost
-->     grant select on accounting.cdr_time_balance to exporter at localhost
-->     grant select on accounting.events_relation to exporter at localhost
-->     grant select on accounting.events_relation_data to
exporter at localhost
-->     grant select on ngcp.timezone to exporter at localhost
-->     grant select on accounting.cdr_time_balance_data to
exporter at localhost
-->     drop: fileshare at localhost
-->     flush privileges
-->   [defaults.fileshare.localhost]
-->   flush privileges
-->     revoke all from: fileshare at localhost
-->     grant all privileges on fileshare.* to fileshare at localhost
-->     grant select on provisioning.voip_domains to fileshare at localhost
-->     grant select on provisioning.voip_subscribers to fileshare at localhost
-->     drop: kamailio at localhost
-->     flush privileges
-->   [defaults.kamailio.localhost]
-->   flush privileges
-->     revoke all from: kamailio at localhost
-->     grant all privileges on kamailio.* to kamailio at localhost
-->     grant select on billing.ncos_lnp_list to kamailio at localhost
-->     grant select on provisioning.voip_sound_files to kamailio at localhost
-->     grant select on provisioning.voip_cf_periods to kamailio at localhost
-->     grant select on provisioning.voip_cf_destinations to
kamailio at localhost
-->     grant select on billing.ncos_pattern_list to kamailio at localhost
-->     grant select on provisioning.voip_fax_preferences to
kamailio at localhost
-->     grant select on provisioning.voip_cf_bnumbers to kamailio at localhost
-->     grant select on provisioning.voip_contract_location_blocks to
kamailio at localhost
-->     grant select on provisioning.voip_cf_mappings to kamailio at localhost
-->     grant select on provisioning.voip_dbaliases to kamailio at localhost
-->     grant select on provisioning.voip_cf_bnumber_sets to
kamailio at localhost
-->     grant select on provisioning.voip_contract_locations to
kamailio at localhost
-->     grant select on provisioning.voip_cf_destination_sets to
kamailio at localhost
-->     grant select on billing.lnp_numbers to kamailio at localhost
-->     grant select on provisioning.voip_sound_sets to kamailio at localhost
-->     grant select on provisioning.voip_peer_hosts to kamailio at localhost
-->     grant select on provisioning.voip_cf_time_sets to kamailio at localhost
-->     grant select on provisioning.voip_sound_handles to
kamailio at localhost
-->     grant select on provisioning.voip_cf_source_sets to
kamailio at localhost
-->     grant select on billing.ncos_levels to kamailio at localhost
-->     grant select on provisioning.voip_cf_sources to kamailio at localhost
-->     grant select on provisioning.voip_subscribers to kamailio at localhost
-->     drop: kamailioro at localhost
-->     flush privileges
-->   [defaults.kamailioro.localhost]
-->   flush privileges
-->     revoke all from: kamailioro at localhost
-->     grant select on kamailio.* to kamailioro at localhost
-->     grant select on billing.ncos_lnp_list to kamailioro at localhost
-->     grant select on provisioning.voip_peer_inbound_rules to
kamailioro at localhost
-->     grant select on provisioning.voip_peer_groups to
kamailioro at localhost
-->     grant select on provisioning.voip_domains to kamailioro at localhost
-->     grant select on provisioning.voip_sound_files to
kamailioro at localhost
-->     grant select on provisioning.voip_cf_periods to kamailioro at localhost
-->     grant select on provisioning.voip_cf_destinations to
kamailioro at localhost
-->     grant select on provisioning.emergency_mappings to
kamailioro at localhost
-->     grant select on billing.v_subscriber_timezone to
kamailioro at localhost
-->     grant select on billing.ncos_pattern_list to kamailioro at localhost
-->     grant select on billing.lnp_providers to kamailioro at localhost
-->     grant select on billing.billing_profiles to kamailioro at localhost
-->     grant select on provisioning.voip_cf_bnumbers to
kamailioro at localhost
-->     grant select on provisioning.voip_cf_mappings to
kamailioro at localhost
-->     grant select on billing.voip_subscribers to kamailioro at localhost
-->     grant select on provisioning.voip_dbaliases to kamailioro at localhost
-->     grant select on provisioning.voip_cf_bnumber_sets to
kamailioro at localhost
-->     grant select on provisioning.voip_cf_destination_sets to
kamailioro at localhost
-->     grant select on billing.billing_fees to kamailioro at localhost
-->     grant select on billing.lnp_numbers to kamailioro at localhost
-->     grant select on provisioning.voip_sound_sets to kamailioro at localhost
-->     grant select on provisioning.voip_peer_hosts to kamailioro at localhost
-->     grant select on provisioning.voip_cf_time_sets to
kamailioro at localhost
-->     grant select on provisioning.voip_sound_handles to
kamailioro at localhost
-->     grant select on provisioning.voip_cf_source_sets to
kamailioro at localhost
-->     grant select on provisioning.voip_cf_sources to kamailioro at localhost
-->     grant select on billing.ncos_levels to kamailioro at localhost
-->     grant select on provisioning.voip_subscribers to
kamailioro at localhost
-->     grant execute on function
billing.get_billing_profile_by_uuid_network to kamailioro at localhost
-->     grant execute on function billing.get_billing_fee to
kamailioro at localhost
-->     drop: ldap at localhost
-->     flush privileges
-->   [defaults.ldap.localhost]
-->   flush privileges
-->     revoke all from: ldap at localhost
-->     grant select on ldap.* to ldap at localhost
-->     grant select on billing.contracts to ldap at localhost
-->     grant select on provisioning.voip_preferences to ldap at localhost
-->     grant select on provisioning.voip_usr_preferences to ldap at localhost
-->     grant select on provisioning.voip_subscribers to ldap at localhost
-->     drop: licensed at localhost
-->     flush privileges
-->   [defaults.licensed.localhost]
-->   flush privileges
-->     revoke all from: licensed at localhost
-->     grant select on billing.products to licensed at localhost
-->     grant select on billing.contracts to licensed at localhost
-->     grant select on billing.voip_subscribers to licensed at localhost
-->     grant select on kamailio.location to licensed at localhost
-->     grant select on billing.billing_mappings to licensed at localhost
-->     grant select on provisioning.voip_subscribers to licensed at localhost
-->     drop: mediator at localhost
-->     flush privileges
-->   [defaults.mediator.localhost]
-->   flush privileges
-->     revoke all from: mediator at localhost
-->     grant all privileges on accounting.* to mediator at localhost
-->     grant select on provisioning.* to mediator at localhost
-->     grant select on billing.* to mediator at localhost
-->     grant select,insert,update on stats.* to mediator at localhost
-->     drop: nagios at localhost
-->     drop: nagios at sp1
-->     drop: nagios at sp2
-->     flush privileges
-->   [defaults.nagios.localhost]
-->   flush privileges
-->     revoke all from: nagios at localhost
-->     grant reload,process,super,replication slave,replication client on
*.* to nagios at localhost
-->     grant select on sipstats.* to nagios at localhost
-->     grant select on mysql.* to nagios at localhost
-->     grant select on stats.* to nagios at localhost
-->     grant select on provisioning.voip_peer_groups to nagios at localhost
-->     grant select on kamailio.dialog to nagios at localhost
-->     grant select on billing.voip_subscribers to nagios at localhost
-->     grant select on kamailio.location to nagios at localhost
-->     grant select on provisioning.voip_peer_hosts to nagios at localhost
-->     grant select on provisioning.voip_subscribers to nagios at localhost
-->     drop: prosody at localhost
-->     flush privileges
-->   [defaults.prosody.localhost]
-->   flush privileges
-->     revoke all from: prosody at localhost
-->     grant all privileges on prosody.* to prosody at localhost
-->     grant select on provisioning.voip_domains to prosody at localhost
-->     grant select on provisioning.voip_pbx_groups to prosody at localhost
-->     grant select on billing.contracts to prosody at localhost
-->     grant select on billing.contacts to prosody at localhost
-->     grant select on kamailio.subscriber to prosody at localhost
-->     grant select on kamailio.domain to prosody at localhost
-->     grant select on billing.voip_subscribers to prosody at localhost
-->     grant select on provisioning.voip_dbaliases to prosody at localhost
-->     grant select on provisioning.voip_preferences to prosody at localhost
-->     grant select on provisioning.voip_rewrite_rule_sets to
prosody at localhost
-->     grant select on provisioning.voip_rewrite_rules to prosody at localhost
-->     grant select on provisioning.voip_usr_preferences to
prosody at localhost
-->     grant select on billing.domains to prosody at localhost
-->     grant select on kamailio.dbaliases to prosody at localhost
-->     grant select on provisioning.voip_subscribers to prosody at localhost
-->     grant select on provisioning.voip_dom_preferences to
prosody at localhost
-->     drop: rateomat at localhost
-->     flush privileges
-->   [defaults.rateomat.localhost]
-->   flush privileges
-->     revoke all from: rateomat at localhost
-->     grant select,insert,update on accounting.* to rateomat at localhost
-->     grant select,insert,update,execute on billing.* to
rateomat at localhost
-->     grant select,update on stats.* to rateomat at localhost
-->     grant select on ngcp.date_range_helper to rateomat at localhost
-->     grant delete on accounting.prepaid_costs to rateomat at localhost
-->     grant select on provisioning.voip_preferences to rateomat at localhost
-->     grant select,insert,update,delete on
provisioning.voip_usr_preferences to rateomat at localhost
-->     grant select on provisioning.voip_subscribers to rateomat at localhost
-->     drop: rsyslog at localhost
-->     flush privileges
-->   [defaults.rsyslog.localhost]
-->   flush privileges
-->     revoke all from: rsyslog at localhost
-->     grant reload on *.* to rsyslog at localhost
-->     grant all privileges on syslog.* to rsyslog at localhost
-->     drop: rtcengine at localhost
-->     flush privileges
-->   [defaults.rtcengine.localhost]
-->   flush privileges
-->     revoke all from: rtcengine at localhost
-->     grant all privileges on rtcengine.* to rtcengine at localhost
-->     drop: rtpengine at localhost
-->     flush privileges
-->   [defaults.rtpengine.localhost]
-->   flush privileges
-->     revoke all from: rtpengine at localhost
-->     grant all privileges on provisioning.recording_metakeys to
rtpengine at localhost
-->     grant all privileges on provisioning.recording_streams to
rtpengine at localhost
-->     grant all privileges on provisioning.recording_calls to
rtpengine at localhost
-->     drop: sems_prepaid at localhost
-->     flush privileges
-->   [defaults.sems_prepaid.localhost]
-->   flush privileges
-->     revoke all from: sems_prepaid at localhost
-->     grant select,insert,update,execute on billing.* to
sems_prepaid at localhost
-->     grant select on provisioning.voip_peer_groups to
sems_prepaid at localhost
-->     grant select on provisioning.voip_domains to sems_prepaid at localhost
-->     grant select on ngcp.date_range_helper to sems_prepaid at localhost
-->     grant select,insert,update,delete on provisioning.voip_reminder to
sems_prepaid at localhost
-->     grant select,insert,update,delete on
provisioning.voip_cf_destinations to sems_prepaid at localhost
-->     grant select on provisioning.voip_subscriber_profile_attributes to
sems_prepaid at localhost
-->     grant select,insert,update,delete on accounting.malicious_calls to
sems_prepaid at localhost
-->     grant select,insert,update on accounting.prepaid_costs to
sems_prepaid at localhost
-->     grant select,insert,update,delete on provisioning.voip_speed_dial
to sems_prepaid at localhost
-->     grant select on provisioning.voip_peer_preferences to
sems_prepaid at localhost
-->     grant select,insert,update,delete on provisioning.voip_cf_mappings
to sems_prepaid at localhost
-->     grant select on provisioning.voip_pbx_autoattendants to
sems_prepaid at localhost
-->     grant select,insert,update,delete on kamailio.sems_registrations to
sems_prepaid at localhost
-->     grant select on kamailio.location to sems_prepaid at localhost
-->     grant select,insert,update,delete on
provisioning.voip_cf_destination_sets to sems_prepaid at localhost
-->     grant select on provisioning.voip_preferences to
sems_prepaid at localhost
-->     grant select on provisioning.voip_rewrite_rule_sets to
sems_prepaid at localhost
-->     grant select on provisioning.voip_peer_hosts to
sems_prepaid at localhost
-->     grant select on provisioning.voip_rewrite_rules to
sems_prepaid at localhost
-->     grant select,insert,update,delete on
provisioning.voip_dom_preferences to sems_prepaid at localhost
-->     grant select,insert,update,delete on
provisioning.voip_usr_preferences to sems_prepaid at localhost
-->     grant select on provisioning.voip_sound_handles to
sems_prepaid at localhost
-->     grant select on provisioning.voip_sound_files to
sems_prepaid at localhost
-->     grant select on provisioning.voip_cc_mappings to
sems_prepaid at localhost
-->     grant select on kamailio.dbaliases to sems_prepaid at localhost
-->     grant select on provisioning.voip_subscribers to
sems_prepaid at localhost
-->     drop: sipwise at localhost
-->     flush privileges
-->   [defaults.sipwise.localhost]
-->   flush privileges
-->     revoke all from: sipwise at localhost
-->     grant all privileges on *.* to sipwise at localhost with grant option
-->     drop: soap at localhost
-->     flush privileges
-->   [defaults.soap.localhost]
-->   flush privileges
-->     revoke all from: soap at localhost
-->     grant all privileges on billing.* to soap at localhost
-->     grant all privileges on kamailio.* to soap at localhost
-->     grant all privileges on sipstats.* to soap at localhost
-->     grant all privileges on accounting.* to soap at localhost
-->     grant all privileges on carrier.* to soap at localhost
-->     grant all privileges on provisioning.* to soap at localhost
-->     grant all privileges on prosody.* to soap at localhost
-->     drop: voisniff at localhost
-->     flush privileges
-->   [defaults.voisniff.localhost]
-->   flush privileges
-->     revoke all from: voisniff at localhost
-->     grant all privileges on sipstats.* to voisniff at localhost
-->     grant select on billing.voip_intercept to voisniff at localhost
--> flush privileges
--> Warning: Recreated users are without passwords,
         please consider running 'ngcp-sync-constants' to update passwords
for them.

root at edge:~# ngcp-sync-constants -verbose -root
--> nodename => spce
--> connected to localhost:3306 as root
--> soap => aaaaaaaaaaaaa
--> rateomat => bbbbbbbbbbb
--> fileshare => cccccccccccccc
--> asterisk => dddddddddddddd
--> licensed => eeeeeeeeeeeeee
--> rsyslog => fffffffffffffffffff
--> prosody => ggggggggggggggg
--> exporter => hhhhhhhhhhhh
--> rtpengine => iiiiiiiiiiiiiiiii
--> nagios => jjjjjjjjjjjjjjjjjjjjjj
--> mediator => kkkkkkkkkkkkkkkkkkkk
--> voisniff => lllllllllllllllllllll
--> rtcengine => mmmmmmmmmmmmmmm
--> ldap => nnnnnnnnnnnnnn
--> dbcleaner => oooooooooooooooooo
--> kamailio => pppppppppppppppp
--> kamailioro => qqqqqqqqqqqqqqqqqqqq
--> sems_prepaid => rrrrrrrrrrrrrrrrrrrrrrrrrrrrr
--> debian-sys-maint => sssssssssssssssssssss
--> sipwise => tttttttttttttttttttttttttttt
--> flush priveleges

 ngcpcfg apply

2019-06-13 04:51:34 spce: Executing sync-db:
                     DBI
connect('database=provisioning;host=localhost;port=3306','sipwise',...)
failed: Access denied for user 'sipwise'@'localhost' (using password: YES)
at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.
                     Error: Could not connect to database 'provisioning' at
'localhost:3306' as 'sipwise': Access denied for user 'sipwise'@'localhost'
(using password: YES)



On Thu, Jun 13, 2019 at 3:34 AM Alex Lutay <alutay at sipwise.com> wrote:

> Hi,
>
> It is hard to say something from this side.
> Maybe 'mysql.user' table was not backuped/restored and
> there is no proper user 'sipwise' inside?
> Maybe you missed "flush hosts; flush privileges"?
>
> You can share your exact commands to backup/restore procedures,
> I will try this on my side. BTW did you use our manuals?
> Which release do you use?
>
> Did it happen once or you still see this error on 'ngcpcfg apply'?
> If so, consider to execute:
> > ngcp-sync-grants --verbose --recreate-user
> > ngcp-sync-constants -verbose -root
>
> It should recreate all the necessary sipwise users and sync their
> passwords with constants.yml
>
> I hope it will help you.
> Cannot trace it further from here.
>
> Have fun!
>
> On 6/12/19 5:52 PM, Andy Clark wrote:
> > 2019-06-12 08:42:25 spce: /var has no support of .services
> > 2019-06-12 08:42:25 spce: Executing sync-db:
> >                      DBI
> > connect('database=provisioning;host=localhost;port=3306','sipwise',...)
> > failed: Access denied for user 'sipwise'@'localhost' (using password:
> > YES) at /usr/share/ngcp-ngcpcfg/helper//sync-db line 79.
> >                      Error: Could not connect to database 'provisioning'
> > at 'localhost:3306' as 'sipwise': Access denied for user
> > 'sipwise'@'localhost' (using password: YES)
> > 2019-06-12 08:42:25 spce: OK:   nothing to commit.
> > 2019-06-12 08:42:25 spce: Synchronizing MySQL grants/credentials
> >
> >
> > restored database (full restore except for accounting.cdr)
> > restored /etc/ngcp-config
> > restored /etc/mysql/sipwise.conf
>
>
> --
> Alex Lutay
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20190613/8a601615/attachment-0001.html>