Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
Sqlite has no build-in authentication. This is a potential security risk.
Potentially, in the sense that if you don't give your database file the correct ownership and permissions on disk, it could be read by other users on the machine. Or if you let your machine accept incoming connections that can read files on disk, and can access the SQLite database file, they can potentially read your data.
But these 'security risks' are at a level above SQLite itself–you can have the same security risks whether you're using SQLite or not, and you should be locking down file permissions and network connections on production machines anyway. Calling this a risk with SQLite is calling being on a leaky boat and pointing out that there's a leaky bucket in the boat.
You are right about the file permissions. That is just the first step in the sqlite security plan. But it is not the security risk I'm hinting at.
Because it is not needed to authenticate, there is no way of tracking who corrupted the data if that happens.
Most database systems also come with permissions. This improves the security even more.
I saw there is encryption for the file and there is an authentication extension. So it is possible to get to almost the same level as other databases. But when people are promoting it as an one on one replacement, many who read those posts are going to be missing out on that information.
I'm an old-time programmer for work and for fun. C is my favorite color. Temporarily hooked by AI. I love to share thoughts on programming that go beyond the basics.
This is how I see it: since SQLlite is a library and not a DB like MySQL or Postgres, you don't need the same type of authentication.
With a traditional DB, you have to take care of the actual files that keep the data plus the users of the DB itself (which rarely are also the users of your application).
In the picture, on the left the use of a traditional DB. You have to worry about user authentication, to avoid somebody could access someone else's data through your applications, and DB Authentication, to avoid that somebody could connect to your DB instance and get the data.
With SQLite your application is the one controlling data access so all the security logic has to be implemented there, not in SQLite.
The issue on keeping the access to the data files themselves private is common to both, of course.
since SQLlite is a library and not a DB like MySQL or Postgres, you don't need the same type of authentication.
Why wouldn't you need the same authentication, security, when you are storing the same data in both databases.
Most applications have web content data, and that is what I call low level security data.
And on the other hand there is high level security data, for example with e-commerce; prices, user data, orders, payments.
For the low level security data I think Sqlite is fine. For the other data type I want that extra layer of security "client/server" databases offer out of the box.
But who is going to set up two databases from the start? So in production the more secure database is a better option, even to store the low level security data.
Maybe it is time to consider a multi database setup as the default?
Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
SQLite doesn't have users, roles, or row-level security. Your application handles all authorization. This is actually fine for 99% of SaaS apps where you're checking permissions in code anyway.
This is called out in its own section. If people are going to read the post and then ignore this specific warning, they have bigger problems than security.
Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
They're not needed at all, because the database is a file and it is opened by a single user–the application that uses it. In fact database users and permissions fundamentally don't make sense in SQLite–it's a library running in the same process as the application itself; it's not a separate server that is called by clients. You should read up a bit more on SQLite because you are fundamentally misunderstanding how it works.
So you are saying that a password protected zipfile with a document in it, sqlite, is as secure as a password protected zipfile with a password protected document, other sql databases. Because the document is a pdf instead of word.
My main concern in this thread is data security. I just can't wrap my head around the fact you are reducing security measures.
From a security standpoint it is a best practice to run different applications, web application and database in this case, with different OS users. This reduces the risks when one user is compromised.
Having a database user that needs to log in to read and manipulate data adds an extra layer of security.
And this is what you want to remove from all your data, because of the database application you prefer? This is the tone of the post, replace the "heavy" database with a fast and easy one.
As I mentioned before I see the benefit for certain data, but not for all.
It feels like good decisions have been made in the past. But the reasoning behind the decisions is forgotten, and that is why mistakes from the past are going to be repeated.
Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
If the application server's user is compromised, even with a separate database server running with a different user, it already gives the attacker access to the database, because they can just log in as the application server user and get the credentials to log in to the database. Your extra security layer is not actually adding any extra security.
Looking at it another way, you are effectively saying that an application server can never use local files on disk as any kind of data store for security reasons. This is obviously an absurd argument.
and get the credentials to log in to the database.
Sure it is not a complex security measure to circumvent, but it is a bump in the road attackers need to overcome to get to the data. And that time span could be enough to change the password and kill current connections.
an application server can never use local files on disk as any kind of data store.
Now you are just twisting my words. It is ok for public data, as I mentioned before.
Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
it is a bump in the road attackers need to overcome to get to the data. And that time span
Realistically, that's going to be milliseconds. Attackers are not manually rooting around after logging in, they are just running scripts that automate all the attacks for them. If they already gained login access, you have much bigger problems to worry about than how many more steps it takes to the database.
It is ok for public data,
It is generally understood that an application's data store is part of its persistence layer; it does not typically refer to publicly available data.
Programming languages enthusiast. Author of Learn Type Driven Development: https://www.packtpub.com/application-development/learn-type-driven-development
Your security model relies on the database server somehow being more impregnable against attackers than the application server (ie attackers would try to get the data by compromising the latter and then indirectly access the former, rather than just directly accessing the former). The question that naturally arises then is that if the database server can be made so secure, why can't the application server itself? They are both just computers, there's no magic here.
Imho, this specific model doesn't make sense. It's basically security theatre. Obviously, database servers themselves having some kind of access control mechanism makes sense because they need to be accessed securely from external systems. SQLite files don't.
if the database server can be made so secure, why can't the application server itself
The application server is the public facing server, so that is the obvious attack vector.
When you rob a bank it is easier to go in the front door than tunneling into the vault.
You don't see a bank that is displaying their gold bars in the public area.
The database server is more secure because it is not public. And yet it still has database user accounts, on top of things like IP whitelisting.
I'm sure you have done, and know, all the things I mentioned. And you call it security theater for the purpose of wanting to use a sql database flavor that provides less security features as a one on one replacement.
A scenario where I would feel comfortable to put all data in sqlite is an internal service, in a micro services configuration.
I'm not against using sqlite in production, just be smart about it
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Potentially, in the sense that if you don't give your database file the correct ownership and permissions on disk, it could be read by other users on the machine. Or if you let your machine accept incoming connections that can read files on disk, and can access the SQLite database file, they can potentially read your data.
But these 'security risks' are at a level above SQLite itself–you can have the same security risks whether you're using SQLite or not, and you should be locking down file permissions and network connections on production machines anyway. Calling this a risk with SQLite is calling being on a leaky boat and pointing out that there's a leaky bucket in the boat.
You are right about the file permissions. That is just the first step in the sqlite security plan. But it is not the security risk I'm hinting at.
Because it is not needed to authenticate, there is no way of tracking who corrupted the data if that happens.
Most database systems also come with permissions. This improves the security even more.
I saw there is encryption for the file and there is an authentication extension. So it is possible to get to almost the same level as other databases. But when people are promoting it as an one on one replacement, many who read those posts are going to be missing out on that information.
This is how I see it: since SQLlite is a library and not a DB like MySQL or Postgres, you don't need the same type of authentication.
With a traditional DB, you have to take care of the actual files that keep the data plus the users of the DB itself (which rarely are also the users of your application).
In the picture, on the left the use of a traditional DB. You have to worry about user authentication, to avoid somebody could access someone else's data through your applications, and DB Authentication, to avoid that somebody could connect to your DB instance and get the data.
With SQLite your application is the one controlling data access so all the security logic has to be implemented there, not in SQLite.
The issue on keeping the access to the data files themselves private is common to both, of course.
Why wouldn't you need the same authentication, security, when you are storing the same data in both databases.
Most applications have web content data, and that is what I call low level security data.
And on the other hand there is high level security data, for example with e-commerce; prices, user data, orders, payments.
For the low level security data I think Sqlite is fine. For the other data type I want that extra layer of security "client/server" databases offer out of the box.
But who is going to set up two databases from the start? So in production the more secure database is a better option, even to store the low level security data.
Maybe it is time to consider a multi database setup as the default?
In the post:
This is called out in its own section. If people are going to read the post and then ignore this specific warning, they have bigger problems than security.
That is a confusing paragraph, because it mixes application users and permissions and database users and permissions. Their tasks are different.
From the tone it could be interpreted as database users and permissions are not needed at all.
They're not needed at all, because the database is a file and it is opened by a single user–the application that uses it. In fact database users and permissions fundamentally don't make sense in SQLite–it's a library running in the same process as the application itself; it's not a separate server that is called by clients. You should read up a bit more on SQLite because you are fundamentally misunderstanding how it works.
So you are saying that a password protected zipfile with a document in it, sqlite, is as secure as a password protected zipfile with a password protected document, other sql databases. Because the document is a pdf instead of word.
My main concern in this thread is data security. I just can't wrap my head around the fact you are reducing security measures.
From a security standpoint it is a best practice to run different applications, web application and database in this case, with different OS users. This reduces the risks when one user is compromised.
Having a database user that needs to log in to read and manipulate data adds an extra layer of security.
And this is what you want to remove from all your data, because of the database application you prefer? This is the tone of the post, replace the "heavy" database with a fast and easy one.
As I mentioned before I see the benefit for certain data, but not for all.
It feels like good decisions have been made in the past. But the reasoning behind the decisions is forgotten, and that is why mistakes from the past are going to be repeated.
If the application server's user is compromised, even with a separate database server running with a different user, it already gives the attacker access to the database, because they can just log in as the application server user and get the credentials to log in to the database. Your extra security layer is not actually adding any extra security.
Looking at it another way, you are effectively saying that an application server can never use local files on disk as any kind of data store for security reasons. This is obviously an absurd argument.
Sure it is not a complex security measure to circumvent, but it is a bump in the road attackers need to overcome to get to the data. And that time span could be enough to change the password and kill current connections.
Now you are just twisting my words. It is ok for public data, as I mentioned before.
Realistically, that's going to be milliseconds. Attackers are not manually rooting around after logging in, they are just running scripts that automate all the attacks for them. If they already gained login access, you have much bigger problems to worry about than how many more steps it takes to the database.
It is generally understood that an application's data store is part of its persistence layer; it does not typically refer to publicly available data.
Just like the counter actions are scripted upon attacker detection. Did you really think it would be a manual response?
Your security model relies on the database server somehow being more impregnable against attackers than the application server (ie attackers would try to get the data by compromising the latter and then indirectly access the former, rather than just directly accessing the former). The question that naturally arises then is that if the database server can be made so secure, why can't the application server itself? They are both just computers, there's no magic here.
Imho, this specific model doesn't make sense. It's basically security theatre. Obviously, database servers themselves having some kind of access control mechanism makes sense because they need to be accessed securely from external systems. SQLite files don't.
The application server is the public facing server, so that is the obvious attack vector.
When you rob a bank it is easier to go in the front door than tunneling into the vault.
You don't see a bank that is displaying their gold bars in the public area.
The database server is more secure because it is not public. And yet it still has database user accounts, on top of things like IP whitelisting.
I'm sure you have done, and know, all the things I mentioned. And you call it security theater for the purpose of wanting to use a sql database flavor that provides less security features as a one on one replacement.
A scenario where I would feel comfortable to put all data in sqlite is an internal service, in a micro services configuration.
I'm not against using sqlite in production, just be smart about it