remove a mysql user functionality #119
Comments
|
This would be a nice thing to have! If you want to submit a PR that would be awesome! |
|
I'm on it. You can direct people interested to here. I will link information with this ticket. |
add a .absent in any user in the pillar user is skipped in user.sls too run salt 'db*' state.apply mysql.remove-user
|
In #55 there was the ability to define multiple users (mysql's users = row in mysql.user table) That way: mysql:
# Manage users
# you can get pillar for existing server using scripts/import_users.py script
user:
bob:
password_hash: '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
hosts: # <=============== uses hosts (with an S at the end, no example in pillar.example)
- localhost
- 10.0.0.1
databases:
- database: foo
grants: ['all privileges']
grant_option: True
- database: bar
grants: ['select', 'insert', 'update', 'delete']See the jinja in user.sls How to handle deletion of this multiples users? |
|
I'd handle it exactly the same way as adding users, but introduce an |
|
@BABILEN I think I mis-explained my point or I don't understand your proposal, are you speaking about a user or a connexion from a host to be removed? I commited a version in my branch. I used this syntax I used in the pillar is: user.host_absent mysql:
# Manage users
# you can get pillar for existing server using scripts/import_users.py script
user:
frank:
# frank will be removed
absent: True # <======== this will remove the couple frank@localhost
password: 'somepass'
host: localhost
databases:
- database: foo
grants: ['select', 'insert', 'update']
- database: bar
grants: ['all privileges']
sylvain:
password: 'sylvainSecr3t'
# mutiple hosts
hosts:
- localhost
- 10.0.0.2
host_absent:
# removed from this one
- 10.0.0.1 # <======== this will remove the couple [email protected]
databases:
- database: foo
grants: ['all privileges']
grant_option: true
- database: bar
table: foobar
grants: ['select', 'insert', 'update', 'delete']
bob:
password_hash: '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
hosts:
- localhost
- prout
- 10.0.0.2
host_absent: # <======== this will remove the couple [email protected]
- 10.0.0.1
databases:
- database: foo
grants: ['all privileges']
grant_option: True
- database: bazI splited remove-user.sls from user.sls for readability and because you may try to remove user separately. apply with: salt '*' state.apply mysql.remove-userCould you write what your pillar suggestion, would look like? |
|
My suggestion for introducing |
|
@BABILEN, could you please use pillar example syntax. I'm messed up. This has not to be a valid syntax, just a proposal would suffice. Yes, I'm facing two kind of user's removal. User's removal, which I guess, using mysql_user.absent will not totally remove the user. As this query would do: DELETE * FROM user WHERE user = 'frank';For the name of I think you got the point, it is needed to separate An alternative, would be to fetch the database actual couple (pillar) mysql:
user:
bob:
hosts:
- localhost
- 10.0.0.2EDIT: |
|
I was thinking of something like: Where the presence of What is missing from |
|
OK. That what I was speaking about too. :)
Remove all user 'bob' not regarding its host. Somewhat: (fake syntax) slat 'db*' mysql.user_remove bob '*'Which would perform some equivalent to: DELETE * FROM user WHERE user = 'bob';Regardless of its host. I looked at module code here mysql.user_remove, and tested queries. It wont work that way. Why would I want to do that? Because of removing all unwanted entries in the database. It would be better if accomplished in a single SQL connection for every users and followed with a For now, I will go with what I have. |
|
I'm still not entirely sure what the problem with |
|
You have to define an It is designed that way for me: You cannot remove all bob, that way: To reproduce: bob.sql CREATE USER bob@localhost;
CREATE USER bob@'192.168.1.1';
CREATE USER bob@'10.10.0.1';
CREATE USER bob@'%.mydomain.com';
SELECT user, host FROM user WHERE user = 'bob';
DROP USER bob;
SELECT user, host FROM user WHERE user = 'bob';mysql --table -vv -f mysql < bob.sqlThis query doesn't drop all bob, it try to drop boo@'%' which doesn't exists. And it will fail. DROP USER bob;Do you feel the difference? I don't know if we have to handle it from salt's point of view. I'll continue to try the formula. And simulate 300 mysql's users to see if it works. ;) |
|
Sure, but that is intentional, isn't it? Users are created with a specific host and you'd therefore have to pass the user@host tuple when you want to remove it. That's exactly how I mean the basic idea is that you used salt in the past to create a specific user and that you can remove exactly that user by running |
|
OK, thanks @BABILEN. We agreed. ;) I merged both creating and deleting users.
grants are not revoked yet and may denied user re-creation. Multiple user tuple deletion seems to work. Next episode tomorrow. |
as suggested user are removed one bye one associtated with tupple user @ host. With salt.module.mysql.user_remove you cannot delete all couple in one single call. Here's a prototype written in the state as a Jinja macro.
|
I added a prototype to destroy a mysql's user here as a Jinja macro. It completely removes the user from the server and all its grants privileges regardless of the combination Usage in Jinja: # […] loop or user.absent in the pillar
{{ mysql_user_destroy('bob') }}On IRC #salt I got proposed to develop a behavior which would simulate (note: DROP USER doesn't have a LIKE): DROP USER LIKE 'bob%';To be implemented in both the example.sls: mysql_destroy_bob:
mysql_user.absent:
- name: "bob%"
- use_like: True
- drop_all_privileges: TrueIt seems it could be implemented locally in the formula by adding Jinja template for |
|
It might also make sense to simply run a suitable query with https://docs.saltstack.com/en/latest/ref/states/all/salt.states.mysql_query.html But then I'm not convinced that this is a feature many people need as I believe that users are created explicitly and should also be explicitly removed. |
|
Playing with To install and run: salt 'db*' saltutil.sync_all
salt 'db*' mysql.mysql_cleanup_usersReally destructive, be warned. |
|
So here's a new concept, as I feel that doing it in Jinja is just, so bad… mysql:
user:
user1:
hosts:
- web0.example.com
- web2.example.comHere is an execution module for managed users: I didn't yet code the state based on that module, it's more another prototype. It is based on this formula pillar. I kept the function's name, but it is less destructive. It Only removes non managed users. Not all parameters are fully tested, and unittest are missing. salt 'db*' mysql.cleanup_usersYou can also do, the full destroy (only keeps, salt 'db*' mysql.remove_all_non_admin_userBut you can also check what would happen: salt 'db*' mysql.list_user_to_keep
salt 'db*' mysql.list_user_to_drop
salt 'db*' mysql.list_user_managed |
use in a state of mysql.cleanup_users for simple user management with removal. user.absent is supported too. pillar.example is updated
|
Here's the related state {% if salt['pillar.get']('mysql:server:auto_remove_user_not_managed') %}
remove_user_not_managed:
module.run:
- name: mysql.cleanup_users
- keep_extra: {{ salt['pillar.get']('mysql:server:keep_user_extra', []) }}
{% endif %}How to use it:
mysql:
server:
root_user: 'root'
root_password: "Prout@le-Mammouth"
auto_remove_user_not_managed: True
# list of couple user@host, can be user@%,
# note: % will become a regexp (.*) matching everything
keep_user_extra:
- [email protected]
- admin@%apply. salt '*' saltutil.sync_all
salt 'db*' state.apply mysql.userEnjoy! Notes:
|
Add a functionality to remove a database user:
Example pillar:
Run:
salt 'db*' state.apply mysql.userThe text was updated successfully, but these errors were encountered: