List:Internals« Previous MessageNext Message »
From:David Phillips Date:February 5 2002 4:01pm
Subject:Re: Jailed MySQL for hosting customers
View as plain text  
> With current MySQL versions it is almost madness. Not because of MySQL
> itself, but becasue of SQL nature.
> You said "unlimited". Whatever you meant it raises some very important
> questions:

I meant unlimited in the sense that there are no artificial limits.  We
offer unlimited websites, email accounts, etc., where some providers set a
specific limit, like ten email accounts.  It's just an entry in a database,
so it doesn't make sense to set a limit on the number.  We charge for space
used, not number of things used.

> 1) What about Disk Space control?
> You need control over diskspace used by user DB. Diskquota is not an
> option because MySQL uses many temporary tables and files in many cases
> which can be sometimes much larger than your tables.
> There is no such options like:
> MAX_TABLES_PER_DB
> MAX_TABLE_SIZE
> MAX_ROWS_PER_TABLE
> ...and many others you will like to see

This is a difficult issue.  But we still have to deal with it, even if we
give a customer a single database.  Our current solution is to check how
much space a customer is using, and charge him/her accordingly.

> 2) What about priorities?
> You definitely need DBA acess with higher priority than user processes
> and maybe you need some User-Level support

I'm not sure what you mean here by higher priority.  We do things like
backups, of course, but don't provide DBA type services, at least at the
moment.  Most customers that want a MySQL database or two are using them for
somethine simple like a PHP message board.  However, I realize other people
wanting a similar feature might have different needs.

> 3) What about preventing  user from seeing other users data?
> Answer is not so easy like it seems to be.

I thought my idea would solve that problem.  Only the mysql user has access
to the databases, and users only have access to their databases when they
authenticate.  Their databases would be in their directory.  Sort of a
chroot or jail for the databases.

> Running mysqld for every user is not an option at all. For 3-5 users
> maybe it is possible, maybe you can run up to 8 mysqld, but regular
> hosting server can have hundreds of users.

I agree.  This is the problem.

> So using 1 mysqld with several user DB means mysqld has access to all
> this DB. Currently this means you never want to give users File_priv and
> you lose functionality. - No LOAD DATA INFILE...., no SELECT INTO
> OUTFILE ...
> The problem is that User with File_Priv can read any file on server
> readable by mysqld !!

Right.

> Ok. Looks like chroot environment can solve this.
> Not at all if you want to place DB in user homedir.

I don't see any need for this, because they are owned by the mysql user.
Might as well put them all in one place.  And in our setup, we have a
separate customer database server (probably several in the future), so there
are no home directories on it anyway.

> With this setup user has read only access to his DB dir, mysqld has rw
> and user DB is seen in mysql as database 'username' and because user is
> charged for homedir space DB is included there.

My idea was to have the MySQL connection change the base directory for users
on authentication, based on the username.  For example, assume the databases
are stored in the following directory:

/db

When user joe authenticates, MySQL would open databases from the following
directory:

/db/joe

When user sam authenticates, MySQL would use a different directory:

/db/sam

This way, both users can have databases of the same name, and neither can
see the other's databases.  The users can also create/modify/delete
databases at will, because they do not conflict with anyone other user's
databases.  This is all I'm after here.  Something simple.

> What will happen if I try to chroot mysql at /usr/local/mysql/ ?

I don't see the need for this.

> 4) Most important. Any user can render your server unusable and there is
> no way to prevent this!!! I.e. DoS attack is very easy even by simple
> mistake and nothing can be done about it.

Yes, that is correct.  However, this problem occurs even when you give the
user a single database.  Anytime you have users, you have to keep an eye on
resources and make sure everything is running smoothly.  If a user is doing
bad queries that tie up the server, then we help him/her fix the query or
code.  Paying customers don't generally intentionally perform DoS attacks
against their provider.  And if they do, there is a simple solution.

> 3) As you said Name Space conlficts can occur.
> It is not about DB names only, but usernames too

Wouldn't this be point five?  :)

> Ok you can establish rule like: UserDB is called 'username_db' where
> username is login name, but why users must be forced to use such mysql
> users? Every user may want to have users: Admin, Webuser etc.

You're getting ahead of me here.  Yes, I have thought about what you are
proposing, and that would be an awesome feature to have, but I didn't see a
good way of doing it, and I don't need it at the moment.  I figure that the
simple namespace extension for databases would be simpler, and useful to
more people.

With revising the way MySQL handles everything, I could only come up with
one way of doing that.  Usually when you have virtual servers where each
customer gets root on a virtual server on a shared server, each virtual
server has an IP address.  Presumably all users for that customer would be
coming from that IP address, so you could use that, instead of the username,
to determine which database directory to use.  You could then have multiple
users with the same name, and multiple databases with the same name.

However, this seems like a lot of work, and way over my head for the time
being.

> With 'Complex Setups' - many DB, different DBA for every DB, several
> Users per DB, Complex Privileges it would be better if Privileges can be
> somehow moved to DB.
> I mean that in this case Privileges are DB Property, not 'Global' like
> mysql.* tables currently are.
> I will repeat suggestion about making them 'visible' within every DB.
> Like : SYSTEM_user, SYSTEM_tables_priv
> With many considerations like "If user has File_Priv - it is 'chrooted'
> at some_dir like PHP open_basedir option

That would be very cool, and popular among hosting providers that provide
MySQL support.  I'd hope that it would be optional, and keep MySQL as easy
to setup and use as it is now for simple setups.

> Sounds like total revising of current MySQL behaviour isn't it?

Yes :(

That's not necessarily a bad thing, as the current system does have it's
drawbacks, but I'm sure it's a ton of work, and would break anything that
depends on the current system.

> Don't you think cases like this become more and more common?
> MySQL used as Webserver back-end on Hosting server.

I don't know of any other database that offers such functionality.


Thread
Jailed MySQL for hosting customersDavid Phillips5 Feb
  • Re: Jailed MySQL for hosting customersSasha Pachev5 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips5 Feb
    • Re: Jailed MySQL for hosting customersSasha Pachev5 Feb
  • Re: Jailed MySQL for hosting customersAlexander Keremidarski5 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips5 Feb
    • Re: Jailed MySQL for hosting customersPaul DuBois5 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb
  • Re: Jailed MySQL for hosting customersJeremy Zawodny6 Feb
  • Re: Jailed MySQL for hosting customersDavid Phillips6 Feb
    • Re: Jailed MySQL for hosting customersJeremy Zawodny6 Feb
    • Re: Jailed MySQL for hosting customersMichael Widenius10 Feb