-
Notifications
You must be signed in to change notification settings - Fork 364
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
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 sylvain@10.0.0.1
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 bob@10.0.0.1
- 10.0.0.1
databases:
- database: foo
grants: ['all privileges']
grant_option: True
- database: baz I 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-user Could 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.2 EDIT: |
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.sql This 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: True It 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_users Really 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.com Here 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_users You can also do, the full destroy (only keeps, salt 'db*' mysql.remove_all_non_admin_user But 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:
- momo@foreignhost.name
- admin@% apply. salt '*' saltutil.sync_all
salt 'db*' state.apply mysql.user Enjoy! Notes:
|
Add a functionality to remove a database user:
Example pillar:
Run:
salt 'db*' state.apply mysql.user
The text was updated successfully, but these errors were encountered: