Accessing database.sqlite

Hi

I have a pretty newbie question. I installed an instance of n8n using docker and it has been some time since i accessed it and cannot remember by username :man_shrugging:.

How can i get this from the command like. I see there is a database.sqlite but have never worked with this and only mysql.

I went and installed sqlite3 in the command line…

Is there a way for me to access the database to see my username so i can do a password reset ?

Hey @Surge.Media

If you use basic auth check env variable there will be your login and password (unless u used hash, then need to change password)
If you use new user management system, please log in to container via the following command:

docker exec -it <my-n8n-container-name> /bin/sh

if you do not know the name of container type

docker ps -a

Ones you inside docker type this
WARNING: This will reset user management to status from the installation

n8n user-management:reset

Now you can set up user management by login to n8n isntance

1 Like

Thanks for the direction. However this will also delete/reset the entire instance, so any workflows I have created will be lost ?

my .env file looks like this:

# Folder where data should be saved
DATA_FOLDER=/root/n8n/

# The top level domain to serve from
DOMAIN_NAME=mydomain.com

# The subdomain to serve from
SUBDOMAIN=n8n

# DOMAIN_NAME and SUBDOMAIN combined decide where n8n will be reachable from
# above example would result in: https://n8n.example.com

# The user name to use for authentication - IMPORTANT ALWAYS CHANGE!
[email protected]

# The password to use for authentication - IMPORTANT ALWAYS CHANGE!
N8N_BASIC_AUTH_PASSWORD=myPassword

# Optional timezone to set which gets used by Cron-Node by default
# If not set New York time will be used
GENERIC_TIMEZONE=Europe/Berlin

# The email address to use for the SSL certificate creation
[email protected]

However when I try and login with the username and password above I am unable, and also if i do a password reset i get nothing, so really trying to see if there is a way to actually look inside the database.sqlite in order to see the table of users and see what the username is

Hey,

So yes, indeed this will delete all data I believe ;/
Do you see a prompt like below?
image

Basically it’s basic auth if you have this prompt, you should be able to username with login and password from N8N_BASIC_AUTH_USER and N8N_BASIC_AUTH_PASSWORD.
BTW. N8N_BASIC_AUTH_USER does not need to be email. Also, you may need to put it in "" but not sure, so if it worked before then no worry about that.

You can try to add following variable what could disable USM, I believe it won’t work since you have already user in USM, but might be worth to try:

N8N_USER_MANAGEMENT_DISABLED=true

BTW @MutedJam do you as team maybe consider add this variable work even when user management system (Ill call is UMS) is already set up? Also, I see many users struggling with USM things like resetting password or sth. So maybe worth to consider add variable what will make user able to reset USM, reset password etc. from UI. Something like struggling more, :smiley: I feel it could be worth to consider from my perspective.

About UMS first I suggest set up SMTP environments as it wrote here. This will make you able to send link to reset password.

If that doesn’t work you may try this on your own risk, probably not danger, but I cannot guarantee:
Run this on your database:

delete from user;
delete from 'role';
update settings SET value = 'false' WHERE key = 'userManagement.isInstanceOwnerSetUp';

read more here: Password recovery - #3 by Shirobachi

Let us know if it won’t help we will try different approches

1 Like

Hi @Shirobachi

Firstly big thanks for the replies and efforts to help.

I have followed your directions:

  1. I did the very first suggestion and it worked, however wiped out all my workflows :-). Luckily I had a backup on the server and recovered it.
  2. I have setup SMTP settings on another instance, that I know works and did the same on this instance. I tried to do a password recover on myemail address from the .env file and get nothing :frowning:
  3. I am still stumped as why / how i can actually get into the database.sqlite. I mean if i have a mariadb/mysql/postgres I can get into the database with no problem, then i can do show tables, desc users, select * from users (examples) and see what is actually going on.

So i have this file database.sqlite - how can i actually access it ?
I went and installed sqlite3 app. but no idea how to actually use it correctly. I tried .open database.sqlite

I have read all the other posts from the link you sent here and onwards and I can see you have given commands to delete from user etc… question again, how do i access this db from the command line to run these commands and at least see a list of users/emails.

  1. Oh, no good that you are doing backup :smiley:
  2. Yeah, so with SMTP I saw that is some issues (not super into this topic, but I assume there is some bug, unfortunately)
  3. I will provide two options:
  4. Download db to your computer and use GUI like sqlbrowser
  5. Do it via command line like following:
    1. Download sqlite3 if not installed sudo apt install sqlite3
    2. Run sqlite3 via sqlite3
    3. Open file via .open FILENAME
    4. Run .tables if you see all tables them it works, this post might be worth to read: SQLite Database: How to Create, Open, Backup & Drop Files
2 Likes

Hey all, just to clarify, n8n user-management:reset would not delete your workflows and credentials and should work fine on recent n8n versions.

If that has happened, is there a chance you didn’t configure persistence @Surge.Media? Like not configuring a docker volume to store your n8n container data?

1 Like

Got dammit Monday…

I meant it will remove users and all USM settings :man_facepalming:
Sorry for that, I’ll drink more coffee :grin:

1 Like

I mean, it seems to have happened for @Surge.Media unfortunately, though I believe this might be a different issue (that just came up at the same time as the forgotten password).

Btw, at least for desktop app users things got much simpler with the recent release. The reset option now appears in the menu bar:

image

As for docker containers it’s also important to ensure the reset command is executed as the correct user (for example by using the -u option docker exec -it -u node container-name n8n user-management:reset):

Just tested this once again and after restarting I could re-create my owner account as expected with my workflows still available:

image

1 Like