Hi,

Not much more detail to add, beyond that I wholeheartedly support this effort. LedgerSMB is one of the only webapps I'm aware of that does not use some sort of system-level database access, and it greatly limits the ability to plug in other authentication systems. If we can expand this in some way to get the best of both worlds -- authorization managed by the database, while still allowing other authentication methods -- this sounds like a great improvement!

Cheers,

John

On 1/27/20 2:50 PM, Erik Huelsmann wrote:
Hi,

As part of my preparations to introduce web services, I've found that many web APIs provide two ways to authenticate: (a) a session cookie which can be obtained by "logging in", or (b) an "API key" ; either the API key or the session cookie is then provided with each request to the API, authenticating the request with the session and/or user. It should be noted that neither requests providing an API key nor those providing a session cookie contain further credentials.

For our (API) authentication, ideally, I'd like to be able to provide both the "api key" and the "session cookie" patterns for request authorization.

Additionally, I'd like session authentication to be available using:
- Username & password (preferably through CRAM-SHA2 or higher SHA)
  * Note that in this scenario, the password validation could be anything; even a Kerberos or LDAP server
- WebAuth / FIDO2
- OAuth2
- Client certificate
- (and more, if we come up with those)


== The Problem ==
Our current authentication setup requires plain text username and password on the initial login and each further request. We currently use basic auth to supply these values. In addition, we use a session cookie, but that only serves to validate the session. Thus every request currently requires the username, password and session cookie. The username and password are required because those are used to set up the database connection at the start of the request handling.

Most of the authentication systems above don't maintain a username/password combination visible to our server. By consequence, I'm looking for ways to eliminate the need for the username+password in the Web request. Also, if we'd want to use "API key" type authentication, that API key would need to hold in it (encrypted, but decryptable to the server) the username and password in order to be able to log into the database.

Note that the previous sentence explicitly states "in the Web request": ideally, LedgerSMB authentication can happen on two different levels -- the web request level and the database connection level. Basically, I'd like to start making the distinction between the web request authentication and the database authentication in such a way that our database authorization (access control) remains PostgreSQL-role-based (that is: doesn't change from  the current model).

== The Proposal ==
My current idea is to separate the web authentication from the database authentication. The web application would use a generic user to set up the database connection, switching the session authorization to the role associated with the user, as if the user had logged in directly on the database. Technically, this is achieved by the Pg command 'SET SESSION AUTHORIZATION = username'. This means that once the session has been verified (either through the API key provided or the session cookie), authorization can be switched to the (web)authenticating user and regular processing (as we know it today) can continue.
This solution using the session authorization is very similar to what the Graphile (graphile.org) project uses [they use SET ROLE instead of SET SESSION AUTHORIZATION -- there are subtle differences between their approach and my proposal].

The solution has the benefit of supporting direct connections to the database as they are today with all methods built into PostgreSQL, adding into the mix a second set of authorization options better suited to a web authentication scenario.

== The Request ==

Please comment on the above to get a good common understanding of what I'm describing and the best possible implementation at that.


--
Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.

_______________________________________________
devel mailing list -- devel@lists.ledgersmb.org
To unsubscribe send an email to devel-leave@lists.ledgersmb.org