In doing so, we can take advantage of a number of pieces of advanced PostgreSQL functionality to provide a much richer and more secure application environment. At the same time there are limitations with this system which must be taken into account as well.
Basic Design Requirements
The LedgerSMB Authentication, Authorization, and Auditing system was designed to offer real security even as the older codebase inherited from SQL-Ledger did not admit of any real security controls, as well as offering an extensible set of authentication options, and an ability to flexibly and consistently apply security permissions across several databases in a cluster.
The basic use cases are as follows:
- A single business will only have one database. The system shouldn't be unmanageable for such a business.
- An accountant/bookkeeper might access books for several businesses, and would need consistent access across databases.
- A midsize business with several subsidiaries might need some people to have access across databases, and others to have permission only to specific databases. Such a business might also need some sort of single sign-on technology, or even integrated authentication.
Overview of Design
The current design uses PostgreSQL roles for managing permissions. On the most basic level every user is a database user. Roles with the nologin attribute is set are used to assign permissions to underlying relations and functions. Generally, the simplest permissions are favored over the most complex. This means that operations that correspond exactly with relational operations are granted relational permissions, while those that do not are granted functional permissions.
Some of these roles are granted to other no-login roles to serve as larger aggregations of permissions. Users may be granted either sets of roles. A set of administrative functions are provided by the software to manage user accounts, permissions, and reset passwords (if applicable).
Obstacle 1: Roles are global to a cluster
This is both an obstacle and a useful tool. This allows users to be consistent across databases, but also poses a challenge regarding the larger database clusters because the roles which grant specific permissions end up being cluster-global as well.
We resolved this by making the db-specific roles prefixed with lsmb_[database_name]__ (note the double underscore after the database name).
Obstacle 2: Authentication methods
Authentication necessarily requires a few specific pieces of information to be passed to the database server, and these must be obtained from the user. The first is a username, and the second is some sort of authentication token. Any intermediary framework must then be able to pass these credentials on to the database in such a way as to properly log in. This can consist of three basic types:
- Password based authentication, where the authentication is a memorized password, typed into a terminal,
- Token-based authentication, where the token is supplied from an external source, and
- Two factor authentication, where the both of the above methods are required.
The other two break down into two questions:
- What can the web server pass on to the database server? and
- What methods can the database server use to authenticate the user?
On the web server, for password authentication, the re-use requirement means that the only possible method for authentication is HTTP-Basic.
On the database server, client-side SSL certificates can only be used to authenticate the web server to the database server. They cannot be used to authenticate users, and so are a not a possible choice for database authentication for a web app of this sort.
Security, it could be said, is an endless series of tradeoffs. These tradeoffs not only include usability vs security, but also how mitigation of concerns are prioritized.
This approach prioritizes mitigating SQL injection and privilege escalation issues in the web application above possible flaws in the PostgreSQL authentication mechanisms and misconfiguration of the web server.
We believe that there is general benefit in pushing authentication back to methods which are better tested and have more peer review. The PostgreSQL community has been, in our experience, extremely professional regarding security, further raising our confidence. So this first trade-off is easily justifiable.
The second tradeoff is a little harder to justify at the outset. If passwords must be re-used by the web server, zero-knowledge authentication systems cannot work. This means the password must be passed in clear text to the web server. This leads to several kinds of attacks, including malicious admins logging passwords, use of the web server without SSL allowing passwords to be overheard by an eavesdropper. We address the second type of attack by requiring SSL in the default configuration of LedgerSMB.
The issues with malicious system administrators can never be completely mitigated using password authentication. If this is a consideration, it's best to look to something like Kerberos (which could be supported on 1.3 with a little bit of effort). With Kerberos support, the web application, the database, and the user can all maintain a healthy distrust of eachother.
Other Implementation Details
A few other basic things are worth mentioning here.
The first is that PostgreSQL allows passwords to expire. We make use of this to differentiate between user-set passwords (valid for a configurable length of time) and administrative set password (valid for one day). Users can reset their own passwords.
Administrators of the application can issue temporary password resets. The way this works is that the administator makes up a password for the user, and the user logs in, and is prompted to change it.