Hi Felipe, On Wed, Apr 22, 2015 at 1:54 PM, Felipe Gasper <felipe@felipegasper.com> wrote:

Sweet! I’ll look forward to it! :)

Here's the blog post: https://mariadb.com/blog/configuring-pam-authentication-and-user-mapping-mar... Let me know if you have any questions. Thanks, Geoff

On Thu, Apr 23, 2015 at 2:42 PM, Felipe Gasper <felipe@felipegasper.com> wrote:

This looks really cool--thank you for posting!

One question: how readily might this be able to support using MariaDB’s own authentication for the user rather than /etc/shadow?

Example:

GRANT USAGE ON *.* TO 'temp_g5fj3s'@'' IDENTIFIED BY 'my_secret'; GRANT PROXY ON 'frank'@'localhost' TO 'temp_g5fj3s'@'';

e.g., I log in as “temp_g5fj3s” using “my_secret”, and MariaDB would then just make that user behave as 'frank'@'localhost'.

I believe that the proxy user functionality in MySQL/MariaDB requires that the authentication plugin change the user name to that of the proxied user: https://dev.mysql.com/doc/refman/5.5/en/proxy-users.html As far as I know, MariaDB's default authentication doesn't support this kind of thing. The PAM authentication plugin does. However, if you are using MariaDB 10.0, you could use roles: https://mariadb.com/kb/en/mariadb/roles-overview/ What you are trying to do would look like this: CREATE USER 'temp_g5fj3s'@'%' IDENTIFIED BY 'my_secret'; CREATE ROLE 'frank'; GRANT 'frank' TO 'temp_g5fj3s'@'%'; When 'temp_g5fj3s' logs in, the user would have to do this to inherit frank's privileges: SET ROLE frank; Starting in 10.1., the user would also be able to do this to inherit frank's privileges automatically: SET DEFAULT ROLE frank FOR 'temp_g5fj3s'@'%'; Geoff

On Thu, Apr 23, 2015 at 3:17 PM, Felipe Gasper <felipe@felipegasper.com> wrote:

FWIW, MySQL 5.7 has made mysql_native_password support proxy users:

https://dev.mysql.com/doc/refman/5.7/en/proxy-users.html

Is there a feature request for this with MariaDB, I wonder? Would be very, very useful.

Very interesting. I've submitted a feature request for MariaDB here: https://mariadb.atlassian.net/browse/MDEV-8042 Feel free to comment, watch and/or upvote. I'm not a big fan of this bit from the MySQL documentation: "When a single account has been granted proxy privileges on more than one account, the server mapping is nondeterministic. Therefore, granting proxy privileges on multiple accounts to a single account is discouraged." Nondeterministic behavior can be pretty messy. Maybe improving the role system to support more use cases would be better than going down this route?

And/or, I wonder about rigging up our own PAM authentication for the temp/proxy user …

If you decide to hack together your own solution, maybe you could use our PAM user mapping module as a guide. The source code is linked in the blog post, but I'll link it here as well: https://github.com/MariaDB/server/blob/10.1/plugin/auth_pam/mapper/pam_user_...

The problem with roles is that they apparently can’t log in; our need is literally to have one user impersonate another. The only way for us to use MariaDB roles as they stand currently would seem to be to retool our entire application so that only roles, not users, have privileges. Given the size of our project that would be quite a change--and probably not feasible given that we’re deployed on commercial servers worldwide.

That makes sense. Judging by the original JIRA issue for role support, separating roles and user accounts into different namespaces was a design decision: https://mariadb.atlassian.net/browse/MDEV-4397 It would be nice to have the flexibility to allow roles to log in (similar to how PostgreSQL roles can be defined with "WITH LOGIN" role attributes), but I'm not sure if MariaDB will get that feature. Maybe submit a feature request to our JIRA? Thanks, Geoff

Hi, Felipe! On Apr 24, Felipe Gasper wrote:

On 4/23/15 5:48 PM, Geoff Montee wrote:

...

I'm not a big fan of this bit from the MySQL documentation:

"When a single account has been granted proxy privileges on more than one account, the server mapping is nondeterministic. Therefore, granting proxy privileges on multiple accounts to a single account is discouraged."

Nondeterministic behavior can be pretty messy. Maybe improving the role system to support more use cases would be better than going down this route?

Agreed. It should fail, IMO, when you try to add a 2nd PROXY privilege to the same user. Very strange design.

That's because they're abusing the PROXY privilege. PROXY privilege is exactly that, a *privilege* - it allows a given user to pretend that he's some other given user. What user should it be - this is defined by a plugin. A plugin says "let's Alice be Bob for this session". And DBMS checks whether Alice is allowed to be Bob. And, naturally, there can be many proxies granted to the same user. Alice might also be allowed to be Carol and Dave, but not Eve. PROXY privilege was never supposed to be an *instruction* of what a user will be. When you grant a SELECT privilege on a table to someone, this someone doesn't get the table dumped out to him every time he connects, does he? :) A privilege is not an instruction, it doesn't tell what to do, only what you can do.

...

It would be nice to have the flexibility to allow roles to log in (similar to how PostgreSQL roles can be defined with "WITH LOGIN" role attributes), but I'm not sure if MariaDB will get that feature. Maybe submit a feature request to our JIRA?

Done: https://mariadb.atlassian.net/browse/MDEV-8047

I’m not sure it’s filed in quite the best way (e.g., it didn’t let me select “improvement” as the type)

You've done it correctly, the type "Task" is what it should be.

I wonder what the perceived advantage was/is of keeping users and roles as separate concepts.

I think that's what SQL standard says. We've implemented roles in 10.0 looking into the standard and following it almost up to the letter. In 10.1 we've added an extension - default roles. We can add another extention, that's surely possible. Regards, Sergei