Re: State of Perl-based database setup utilities for LedgerSMB 1.3

[Chris Travers <..hidden..>]

On Mon, May 30, 2011 at 10:19 PM, Luke <..hidden..> wrote:
> On Sat, 28 May 2011, Erik Huelsmann wrote:
>
>> On Sat, May 28, 2011 at 7:07 PM, Chris Travers <..hidden..>
>> wrote:
>>>
>>> On Sat, May 28, 2011 at 9:08 AM, Adam Thompson <..hidden..>
>>> wrote:
>>>>>
>>>>> Where would the data be stored?  Do we require write permissions to
>>>>> the ledgersmb directory?
>>>>
>>>> Um, in the case I was describing, yes, I think so.  In the examples I've
>>>> seen, the administrator has the choice of either configuring their
>>>> webserver & system to allow the PHP script to write to the config file,
>>>> *or* updating the config file manually.  Which is what LSMB requires,
>>>> currently.
>>>
>>> I think it's a lot safer not to trust the application with these
>>> things.
>
> Do I understand correctly, that the current design, requires CLI level
> activity, in order to create users, add companies, and populate company
> databases (COA, etc.)?
> If not CLI, then some sort of PostGreSQL web frontend?

Not quite.   Currently you need some way of creating/loading the
company file before hand.  There is an addon as a web interface to do
this.  Once the company is created with a valid administrative user,
you can create users in that company with whatever permissions you
want through the LedgerSMB front-end.

>
> If that is so, is it also correct that no intent exists to integrate these
> operations into a web interface, such as 1.2's admin.pl, because doing so
> would require a PG admin user and admin database to exist, which we no
> longer want?

The addon to create the company through a web interface uses a
workflow quite a bit different from admin.pl  because we don't have to
create general users there.

right now, there is a limitation that a username must be unique to a
database unless you have a custom db routine to handle it.  We expect
to have an ability to override this behavior on a per-database basis
by loading a customization file.

In other words, to create a database you have to:
1)  Create database
2)  Load database schema and set up chart of accounts
3)  Set up database permissions
4)  Create an administrative user who can log in to that database and
create other users.

I think the ideal framework is to allow this to be done using either a
web interface (initiate.pl) and a command line tool.  My major reason
for moving initiate.pl to addons, however, is that I think we want to
ensure we have a very good CLI first.  If we do, then we can go back
and wrap it with initiate.pl instead of the current approach which is
to duplicate a lot of code.

>
> If so, the question has to be asked: what is the target audience for this
> application?

I can't speak for others, but as the individual who seems to be doing
a loot of the main development work, what I'd like to see are actually
three things:
1)  A solid infrastructure for the application
2)  Solid add-ons which can be used to make the program do things
3)  Distributions of core plus add-ons aimed at specific groups of users.

In fact I am going to take this further......
I would like to see 2.0 take shape out of 1.3 add-ons work.  But
again, I think the above three elements are what would be needed.
>
>  > There you have my complete support. I'm running the OTRS ticket
>>
>> tracking tool and it wanted me to open up write access to parts of its
>> directory structure in /usr. I *hate* it when tools require that:
>> libraries and servers which need write access should do that in the
>> /var hierarchy.
>
> but is that the fault of the mechanism, or of the application's notion of
> acceptable filesystem utilization?  Because it sounds like you are faulting
> the latter, but excluding the former as a result.
>
> (By which I mean that you are agreeing with Chris that the application
> should not get write access to one of its own configuration files; and then
> sighting as an example of why that's bad, an application that wants write
> access under /usr, instead of /var [where it should potentially have it].)

Ok, let's focus on details and risks here for a moment.  Also IMO we
are debating mechanisms, not goals.  There is no reason that a
packager can't provide something with appropriate configuration
settings for that application on that platform as a safe default.  In
fact, this is easier on 1.3 than on 1.2.  I install an RPM of
Apache2... I can edit the httpd.conf file if I want but it is up and
running without that.  No reason this should be different for
LedgerSMB, IMO.

There are two real issues here.  The first are installation-wide
configuration files and the other are company-wide configuration
settings.   These pose two different sets of problems, but they both
have in common the fact that we don't want a user of one database to
be able to change settings that affect users of another database.  By
not trusting the application we avoid both those problems.

In my view, there should be nothing company-specific in the
ledgersmb.conf.  This should all be OS-specific or system-specific
These should be stable settings, not prone to many changes.  The
application should have no internal ability to edit this file (just
like Apache has no ability to edit the httpd.conf file).  On the other
hand, anything company-specific should be in the defaults table.  The
application should have no ability to access this information without
a valid user being logged in.  We can trust the user's credentials and
database permissions management rather than the application.

If there is something that needs to be commonly changed at runtime
across all databases I am open to rethinking this, but at the moment,
I think that the two tier structure is the most secure way of doing
this in an adequate way, and it gives the users the ability to edit
what settings an be reasonably expected to be added there.  If
something is in the wrong place (in the ledgersmb.conf instead of the
defaults table) we can move it too.

>
>  > OTRS is a great example of a tool that comes with a great
>>
>> installation
>> script in Debian: you run the command 'apt-get install otrs2' and the
>> database, cron jobs and webserver all are configured when it
>> succesfully completes. That's what I envision for lsmb too, for its
>
> Do you only get one single issue queue [or analogous concept] from that
> action, or can you create more without running the package manager again? If
> you can, how is it done?
>
>  > first company database - created by packagers for specific
>>
>> distributions.
>
> Is the COA part of that first database, and do we still lock users into a
> COA template at company creation time?

Ah... I see your point.  Maybe we should allow chart of accounts to be
loaded from the UI.
>
>> The other scenario would be to run LSMB with access to a user which
>> has enough permissions to do that when LSMB decides it's required to
>> do so. I'm not trusting LSMB enough to depend on that - after all, if
>> there turns out to be a security leak, anybody can start to create or
>> drop tables from my accounting system.
>
> Anyone who could crack you at that level, could presumably crack any other
> PGSQL user as well, and do the same kind of damage on a company by company
> basis.

I am not convinced those risks are analogous.  The basic issue is that
to crack a database user at that level, or finding/exploiting some
sort of permissions elevation vulnerability in Pg (which is a much
larger project with much more review than we have), strikes me as very
different from attacking our application (the product of a smaller
project with less review).

But beyond that, I don't believe there is any other way to retrofit
permissions enforcement onto the legacy code, since SQL-Ledger lacks
any real permissions management.

>
> I am not up on PGSQL permission granularity, but does the user which creates
> a user or database, necessarily have to retain the ability to delete it?

Right now for 1.3, the Pgsql database must be created by a superuser,
which would necessarily retain such rights.
>
> I am suggesting that you could handicap your admin user (we could provide a
> script for this), so that creations can be done by web interface using that
> admin user, but deletions would have to be done manually.
>
I guess the question is how many points of attack do we want to be
responsible for protecting against?  The more of a profile LSMB has as
an application the more of a chance there is of something getting
through.  If we can utilize other mature solutions elsewhere, we have
less of a problem.

So I guess my concern is this:

We are talking a lot of vague "we could" or "wouldn't it be neat if"
sorts of things here.  We could do lots of things.  I think it would
be more productive to talk about specific concerns and see what might
need to be exposed to users or not.  In other words, instead of "all
settings," what settings are currently in the ledgersmb.conf file that
need to be configured through the application?  Which of those provide
a sufficient security risk that we shouldn't allow it even if a lot of
folks want it (for example, I don't think the database host/port
should be configurable from the front-end because that has the
potential to be a major DoS issue)?