Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Firebird is freezing when trying to manage users via triggers [CORE6412] #6650

Closed
firebird-automations opened this issue Oct 4, 2020 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @luronumen

ACTUAL RESULT
- Firebird is freezing when trying to manage users via triggers

EXPECTED RESULT
- Firebird should NOT freeze when trying to manage users via triggers

STEPS TO REPRODUCE THE ISSUE
1- Make sure that the FirebirdSQL 3.0 service is NOT running:
net stop FirebirdServerDefaultInstance

2- Run the following command to create the TEST database:
ECHO SET SQL DIALECT 3;CREATE DATABASE 'TEST' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1252; | "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -user SYSDBA

3- Run the following command to create the SYSDBA user inside the TEST database using the SRP plugin:
ECHO CREATE OR ALTER USER SYSDBA SET PASSWORD 'masterkey' USING PLUGIN SRP;COMMIT; | "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -user SYSDBA "TEST"

4- Restart the firebird 3.0 service:
net start FirebirdServerDefaultInstance

5- Create the following USERS table:

CREATE TABLE USERS
(
AUTHENTICATION VARCHAR(32) CHARACTER SET WIN1252 COLLATE WIN_PTBR
);
COMMIT;

6- Create the following triggers for USERS table for after Insert or Update or Delete:

SET TERM ^ ;
CREATE TRIGGER USERS_AI_AU_AD FOR USERS
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
IF ((OLD.AUTHENTICATION IS NOT NULL) AND ((NEW.AUTHENTICATION IS NULL) OR (OLD.AUTHENTICATION<>NEW.AUTHENTICATION)))
THEN BEGIN
EXECUTE STATEMENT 'REVOKE RDB$ADMIN FROM "' || OLD.AUTHENTICATION || '" GRANTED BY "SYSDBA"';
EXECUTE STATEMENT 'DROP USER "' || OLD.AUTHENTICATION || '" USING PLUGIN SRP';
END
IF ((NEW.AUTHENTICATION IS NOT NULL) AND ((OLD.AUTHENTICATION IS NULL) OR (OLD.AUTHENTICATION<>NEW.AUTHENTICATION)))
THEN BEGIN
EXECUTE STATEMENT 'GRANT RDB$ADMIN TO "' || NEW.AUTHENTICATION || '" GRANTED BY "SYSDBA"';
EXECUTE STATEMENT 'CREATE OR ALTER USER "' || NEW.AUTHENTICATION || '" SET PASSWORD ''123456'' USING PLUGIN SRP GRANT ADMIN ROLE';
END
END^
SET TERM ; ^
COMMIT;

7- Run the following command add a register on USERS table (No issue so far):

INSERT INTO USERS (AUTHENTICATION) VALUES ('AAA');
COMMIT;

7- Try to run the following command to update the previous register (Firebird Freeze!):

UPDATE USERS SET AUTHENTICATION='BBB' WHERE AUTHENTICATION='AAA';
COMMIT;

IMPORTANT NOTES
- This issue is NOT reproducible if the security database is security3.fdb instead of the database itself (TEST);

ENVIRONMENT SETUP

firebird.conf

#⁠SETUP
UserManager = Srp, Legacy_UserManager
WireCrypt = Enabled

databases.conf

TEST = D:\APPLICATIONS\LURONUMEN\DB\TEST.FDB
{
AuthServer = Srp
SecurityDatabase = TEST
UserManager = Srp
}

Commits: e57f0c8 98e8418 1cecd8e 0cfdd3d

====== Test Details ======

Checked on 4.0.0.2249 - no hang, but if this test runs in loop, w/o pauses for at least ~4s, then starting from 2nd run following fail raises:
Statement failed, SQLSTATE = 08006
Error occurred during login, please check server firebird.log for details
Error occurred during login, please check server firebird.log for details
Content of firebird.log will be added with following lines:
Srp Server
connection shutdown
Database is shutdown.

Problem exists on SS and SC.
Classic is not affected.

Sent report to Alex et al, 09.11.2020.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Issue confirmed

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Luciano, I've committed a fix into master branch (FB4). You may try with tomorrow (15-oct-2020) or later snapshot. Fix for FB3 will be done after 3.0.7 release.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Hi Alexander,

Thank you very much for committed a fix for this issue! I will validate it as soon as I create an installer for FirebirdSQL 4.0 (FB40_INSTALLER.iss) as well as I created and shared it on the Firebird support list: [firebird-support] Firebird 3.0.5 freezes with many connections just like version 3.0.6.
Why not immediately propagate this fix to the Firebird 3.0 branch instead of waiting to do this only after Firebird 3.0.7? Was it a risky fix that could cause some side effect? Is Firebird 3.0.7 very close to being released to the point that we don't have time to test enough this fix?

Once again, thanks for spend your time for fix it!

Best Regards,
Luciano

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

The behavior when dealing with self security database changed significantly. One side effects was found during debugging thanks to your sample with managing from trigger. But I'm afraid that something else may be present, therefore committing this to FB3 right before release is bad idea. Let it better be checked on shapshots.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.8 [ 10960 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Retest result on Firebird V3.0.7.33380 (Snapshot): PASSED!

Thank you very much for the fix Alexander!

Best Regards,
Luciano

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done with caveats

Test Details: Checked on 4.0.0.2249 - no hang, but if this test runs in loop, w/o pauses for at least ~4s, then starting from 2nd run following fail raises:
Statement failed, SQLSTATE = 08006
Error occurred during login, please check server firebird.log for details
Error occurred during login, please check server firebird.log for details
Content of firebird.log will be added with following lines:
Srp Server
connection shutdown
Database is shutdown.

Problem exists on SS and SC.
Classic is not affected.

Sent report to Alex et al, 09.11.2020.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment