firebird
firebird copied to clipboard
FirebirdSQL
Move ExtAuth from examples to standard Auth method
To port the plugin and include it in the standard authentication methods, need to do the following:
- [ ] Add a parameter template for the plugin to plugins.conf (or a separate ExtAuth.conf configuration file)
- [ ] Can remove the keygen utility and instead generate a key via isql with the RSA_PRIVATE system function - describe in the documentation
- [ ] Add the ability to call ESOE not only from the current user, but also from the effective user if the procedure is created with SQL SECURITY DEFINER
- [ ] Extend the message exchange protocol inside the plugin between client and server. To maintain backward compatibility in case new functionality is added to the plugin. For example, using ClumpletWriter
- [ ] Add description and examples to the documentation
There is also a request to add role forwarding, but still in question how to do it.
If it was intended as an example, I would assume it is not a robust implementation.
If it was intended as an example, I would assume it is not a robust implementation.
It was initially developed by a customer request and was used in production since its invention. However, it wasn't complete enough to be offered as an official plugin. This ticket is about resolving the remaining issues and finally make it complete and official.
On 4/28/25 11:14, Dimitry Sibiryakov wrote:
aafemt left a comment (FirebirdSQL/firebird#8545) https://github.com/FirebirdSQL/firebird/issues/8545#issuecomment-2834357192
First of all a blind trust to a remote client is a big security hole.
Blind trust - definitely. But when remote system can prove (choose any reliable cryptographic procedure for it) that it can be trusted - why not? At least a lot of soft (starting with SSH) work this way.
On 4/28/25 09:51, Andrey Kravchenko wrote:
XaBbl4 created an issue (FirebirdSQL/firebird#8545) https://github.com/FirebirdSQL/firebird/issues/8545
To port the plugin and include it in the standard authentication methods, need to do the following:
- Add a parameter template for the plugin to plugins.conf (or a separate ExtAuth.conf configuration file)
If you use default access to plugin config it's user's choice where to place plugin's parameters, it can be plugins.conf or ExtAuth.conf. BTW, such one parameter is already used by plugin to store a key.
- Can remove the keygen utility and instead generate a key via isql with the RSA_PRIVATE system function - describe in the documentation
First of all a way how plugin works with keys should be seriously reworked. Existing approach when single secret key is copied to N systems in a group is more or less acceptable for a sample but not enough for release. (Moreover - use of RSA actually currently does not make sense, any symmetric cypher can be used instead).
As a minimum I suggest to make each system have own private key, and those which are happy to accept connections from it should use it's public key to perform required checks. Even better to have list of users who may use particular key - a kind of grant. I.e. any system may have
1 private key, a list of users for each, and a set of public keys from remote systems, probably also with a list of users that may connect with them. Does not it look like a set of tables in database may suite better to store all this compared with .conf file? Except sooner of all private key's that may be stored in config file for security reasons.
- Add the ability to call ESOE not only from the current user, but also from the effective user if the procedure is created with SQL SECURITY DEFINER
Definitely useful.
- Extend the message exchange protocol inside the plugin between client and server. To maintain backward compatibility in case new functionality is added to the plugin. For example, using ClumpletWriter
Very good.
- Add description and examples to the documentation
There is also a request to add role forwarding, but still in question how to do it.
That (sooner of all effective user ability too) will require some small additions to plugin call point from the code or may be more info in auth block. Do not see any tragic in it :-)
At least a lot of soft (starting with SSH) work this way.
This soft used to trust to a third-party authority: certification centers, domain controller or system administrator. Firebird is often used by inexperienced admins who can easily endanger data. That said, I'm not against this kind of trusted auth but I think that it should not be in the list of standard auth plugins.
On 4/29/25 15:19, Dimitry Sibiryakov wrote:
aafemt left a comment (FirebirdSQL/firebird#8545) https://github.com/FirebirdSQL/firebird/issues/8545#issuecomment-2838473355
At least a lot of soft (starting with SSH) work this way.This soft used to trust to a third-party authority: certification centers, domain controller or system administrator. Firebird is often used by inexperienced admins who can easily endanger data. That said, I'm not against this kind of trusted auth but I think that it should not be in the list of standard auth plugins.
This makes sense. Specially taking into an account what was requested by mentioned in the thread clients :-( Such plugin in order to get into standard plugins list should have well-thought setup procedure to avoid trivial bugs from inexperienced admin, a kind of seriously improved keygen. As always - when changing .conf manually no guarantees.
fbSampleExtAuth-plugin based on tomcrypt librarary, which support "best and modern", f.e, Ed25519. May be not use "ancient and weak" RSA-keys at all or by default?
Supporting non RSA keys is definitely useful feature. But first of all (on my mind) plugin should learn to separate roles of public & private keys (private one to be known to one and only one host), without this changing asymmetric cypher makes no sense.
Key pair must use something beyond RSA/DSA. In this regard cryptographics primitives are basis for build "trusted net".
"pair" is key word here - current sample is always using only private key :-(
AFAIK there is no way for server-side auth plugin to get IP address or name of connecting host, so it cannot confirm ownership of the key as it is done for certificates in TLS.
Plugin cannot overcome this limitation without support from server.
Key algorithm doesn't matter much in such circumstances.
AFAIK there is no way for server-side auth plugin to get IP address or name of connecting host, so it cannot confirm ownership of the key as it is done for certificates in TLS.
"Key owner" are not related witn "host identification".
Remind only. Term: KP - key pair or (simple) keys OK - open (public) key of KP SK - secret (private) key of KP O-prefix - owner's (long time usage) KP E-prefix - ephemeral (one-time usage) KP Keys owner (just owner later) generate (one-time) E-KP. Owner encrypt pair of O-OK and E-SK on pair E-OK and O-SK and forgot (wipe) E-OK. Owner send pair of O-OK and E-SK twice - as-is (open) and as encrypted data. Anyone can decrypt data on pair of O-OK and E-SK and compare it with decripted data, but nobody can't more encrypt on (that) E-SK.
I don't know about elliptic curves but in RSA OK is derived from SK, so part "send E-SK as is and forget E-OK" makes no sense as anyone received E-SK can get E-OK from it.
That's the whole point of asymmetric encryption: SK must be never revealed or copied, and that's why it is supposed to be kept in a hardware wallet without ability to be extracted: to make sure that key owner is still own it alone.
For ExtAuth plugin, key owner is a host, so host identification is owner identification.
AFAIK any algorithm operate on key pair, must not "derive OK from SK" - key pair generated as whole. Knowlege only one key from pair doesn't permit "recover" another key of pair. SK may provide some (secret) data to derive another (short-time) secret. Key to (en/de)crypt for some (simmetric) cypher as example.
But, any way - "host identification" fully unrelated to crypto. Out of scope.
You can generally derive the public key from the secret key because, IIRC, the secret key usually includes the public key (i.e. it's usually actually a key pair, not just the secret key).
Yes, may included. But "proof of ownership" based on ability destroy (forget/wipe) one part of ephemeral key pair.
According to https://stackoverflow.com/questions/696472/given-a-private-key-is-it-possible-to-derive-its-public-key indeed for "pure" RSA key where only d and n are kept derivation of e is impossible, but for elliptic curves (Ed25519) such derivation is always possible and trivial.
But "proof of ownership" based on ability destroy (forget/wipe) one part of ephemeral key pair.
Suppose a private key from host A was stolen and used on host B. How operations with a session-long generated ephemeral key can prevent the Firebird server from accepting a brand-new session from host B using the stolen key?
Scenaries of key compromise are part of "threat model". Key leak does not affect on key/algorithm strength. "Quality" of "secure storage" fully out of scope this FR or use concrete cryptoalg.
Based on the comments, I've updated the original post to include a separate storage option for keys on the server and client, and also updated the plugin's interaction algorithm. Please review this post for any additional comments.
Just a few details:
To port the plugin and include it in the standard authentication methods, need to do the following:
* * [ ] The server stores only clients public keys in a separate table PLG$EXTAUTH in the security database. Also stores a list of users it can access using these keys
Does in mean - users that are granted remote access to server using this key? (just to make sure)
* * [ ] Or write a manager plugin that will create this structure and extend the dsql parser to work with it* [ ] Remove the keygen utility and instead generate a key via isql with the RSA_PRIVATE system function
The main problem with plugin setup&configure is to correctly perform initial key exchange between servers in the group. I do not see how management plugin and SQL parser help with it. On contrary special utility (yes, I see no big problems if it will be SQL script) should be able to generate client's key, put it into .conf file and create a file (call it 'clidef' later) to be delivered to server (how - not our deal). At server paired utility (or script) script should be used to add client from clidef file to PLG$EXTAUTH table. BTW, remember that server can have multiple security databases.
Next step is to setup list of users. How do you plan to store it? Take into an account that remote access to secDb is prohibited by default. May be adding not management plugin but set of management UDR on server will be good idea?
The initial interaction algorithm will change:
* The client transmits login and public key (or ID of public key, which is also stored on the server for increased security);
just send it's hash as an ID
* The server checks whether this key is in the security database and whether the user can use it;
No - better leave that check for later steps, at least not to provide info about enabled users to the world.
* As before, the server generates a random number, encrypts it with the public key, and sends it to the client; * The client decrypts the random number, signs the data set with its private key, and sends it back; * The server verifies this signature with the public key and, based on this, allows the connection.
Here is a good place to break connection for wrong user. Exact reason of it goes to log.
This random number is the session key (as before).
Does in mean - users that are granted remote access to server using this key? (just to make sure)
Yes, let's say there's a public key and users ALICE and BOB, and the key is granted only to BOB. Therefore, if the client execute statement '...' on external '...' as user alice then authorization via ExtAuth will fail and continue check to next plugin.
The main problem with plugin setup&configure is to correctly perform initial key exchange between servers in the group. I do not see how management plugin and SQL parser help with it. On contrary special utility (yes, I see no big problems if it will be SQL script) should be able to generate client's key, put it into .conf file and create a file (call it 'clidef' later) to be delivered to server (how - not our deal). At server paired utility (or script) script should be used to add client from clidef file to PLG$EXTAUTH table. BTW, remember that server can have multiple security databases.
I don't see any practical benefit in using a special utility.
I added creation manager as an alternative to the SQL script. But with a separate manager, it would allow use standard syntax, such as create user id_pk password '<public_key>' using plugin ExtAuth, without requiring local access to the security database. I thought this was a good option.
If use scripts, need access to the security database, but since this setting is not done often, the administrator can connect to security, make all keys and grants, and return to the way it was.
Next step is to setup list of users. How do you plan to store it? Take into an account that remote access to secDb is prohibited by default. May be adding not management plugin but set of management UDR on server will be good idea?
I thought about storing them in a separate table PLG$EXTAUTH_GRANTS. I was thinking that needed to expand the capabilities of the management plugin. Regarding about management UDR, I haven’t thought about how they can be made. What options are available with UDR?
The plugin's table structure currently looks like this:
PLG$EXTAUTHwith fields (id, public_key, comment, active)PLG$EXTAUTH_GRANTSwith fields (id, user_name) If user_name equal '*' then allow to all use this key.
No - better leave that check for later steps, at least not to provide info about enabled users to the world.
The server may have multiple public keys, and without this check, we won't be able to encrypt the random number with the correct key so the client can decrypt it. At this point, we can simply ignore this authentication plugin and return AUTH_CONTINUE.
ExtAuth is about trusted relation between servers. Trusted server may use mapping for (some) client on external connection without knowlege client's secret's ExtAuth must not store client's secret's anywhere. If you want see authorization on certificates - make, please, separate FR.
ExtAuth must not store client's secret's anywhere.
Um... a client in this context is a server that wants to access another server via an execute statement.
It's impossible to verify a client (another server) without storing a private key on it.
on external server may:
- send client secret's to another server and destroy client's security;
- setup trusted relations with another server and use mapping.
ExtAuth must use second approach. Such as any trusted authorization.