What’s Least Privilege?
Before diving in to the heart of this blog post I think it’s wise to explain what I mean by “Least Privilege” and why it’s worth while. I think it’s also worth discussing why you may not want to bother with it as well.
The “Principle of Least Privilege” basically means you enable a user to do what they need to do but nothing more. If a user only needs to create reports against a database then it’s usually acceptable to give them permissions to query the database and nothing else. You surely don’t need to give a user who needs to create reports the ability to modify the privileges of other users; that’s unnecessary and might cause big problems.
Some of you may be old enough to remember a time when the Windows operating system was plagued by lots and lots of worms and viruses. This was for the simple reason that before the Vista version of Windows, all users on the machine were effectively superusers (or root depending on one’s frame of reference). This, of course, arose from the fact that Windows was originally intended to be used by a single user on a single machine before connectivity was much of a concern. Why would you block someone from doing whatever they want on a machine if you anticipated that they’d be installing their own software? After all PC did mean personal computer right?
Of course as time progressed, computers were networked and connected to the internet. I remember a less computer-literate friend of mine that simply connected to the internet before anyone had installed a firewall for him and immediately his PC was infected with a virus. I installed a firewall on his machine and cleaned things up but he asked me “What did I do? I just checked my email?” and I had to tell him that the simple act of connecting to the internet without a firewall present was enough to expose his machine to a virus.
Now if you’re 100% sure that your application will never ever be connected to the internet, then you don’t need to worry about least privilege. But the likelihood of no connection to the internet reached zero about 20 years ago and today if it’s even less likely, if that’s possible, that a machine will never connect to the internet. Of course we’ve got firewalls, and we’ve got routers to secure things between us and the internet. But even so there are ways to hack machines which have firewalls. And once someone has gotten in to your network, you don’t want them to be able to get at all the data without some sort of validation.
Bear in mind too that keeping people from seeing sensitive data is not the only concern you should have as a developer. If someone can hack your database (or your machine) they can use it to store illicit data. And if you’re like most developers chances are you’ll store application permissions and id’s in the database itself. The wisdom of storing id’s in a database is questionable but again, this is something we need to be aware of as developers.
I may have said “you might not want to bother with least privilege” but it’s hard for me to imagine a situation where it truly isn’t something you want to make the effort to implement.
Secured In The Application Or Secured At The Database?
Another thing that a developer must consider is exactly where he or she secures the data in the database. You can, of course, secure permissions in the application. In fact this is a very common scenario and it’s usually effective and has some real advantages. It’s more likely that your developers will know the language the application is written in and therefore be better able to understand the implications of the security code. If you need to create new permissions, you can modify the source code and you don’t need to worry about the database per se.
The other option is security at the database level. This is a bit harder to create and maintain but it’s also a lot more secure. When security is enforced at the application level a knowledgeable user can circumvent the application (and its protections) and get at data which he or she should not have access to.
Securing at the database level may require more work and it might require a team to have a DBA on staff. Of course having a database specialist on a team isn’t ever a bad idea–at least in my experience. And even if you make the choice to secure the application via application code, it’s still very wise to be quite stingy with what you let users see. You’re much better to give a user too few permissions than too many.
This also conforms more closely to the idea of Zero Trust Networks. That is, no one on the network is trustworthy–including your application.
I want to emphasize that this is not an either/or choice. It’s entirely possible to have some of your security in the application and some in the database. As long as you code your application to deal with the results of attempts to do things which the user is not permitted to do you’re fine.
Having said all this Elixir (and as far as I know Ruby On Rails) favors the security in the application model. Again, there’s no reason to prefer one approach versus the other–it’s just the way that Elixir was designed to interact with Ecto. The ability to perform database migrations is a large, large advantage over the alternative and making this possible at the database level would be quite tricky.
The main reason I want to secure things at the database level is for security for publicly available database. If a database is unlikely to be publicly accessible, then there’s considerably less one needs to worry about in terms of security. I’m working on an application which will host a database publicly so my tendency to being a little paranoid has made me investigate how I can move my security to the database level.
Admittedly this can be a sort of belt and suspenders approach; that is, it’s a little redundant but it’s also more secure. It’s far closer to the underlying notion informing a zero-trust network approach.
Some Additional Preliminaries
1.) Given the prevalence of PostgreSQL in Elixir application use the code in this post will assume you’re working with a PgSQL back-end. The ideas should work equally well with other databases but figuring out the exact Data Definition Language and/or SQL needed is left as an exercise for the reader.
2.) For the purposes of this post, I’m creating a read-only (RO) user and a read-write (RW) user. There are certainly other combinations which can be created. The main point is to provide a user who has privileges tailored to the work it will need to do so that it cannot be exploited by bad actors to mess with your database.
3.) I’ve tested the RO and the RW user with various scenarios and no obvious issues have presented themselves. Be warned though that my testing has not been very extensive since I’ve been creating this implementation for a personal project so bear that in mind.
All that out of the way, let’s finally get to some code shall we?
Creating the Users
Here’s the code I’ve used to create the RO and RW users.
# Create custom users for reading data and writing data
defmodule LeastPrivilege do
@moduledoc """
Create database users with the least privileges necessary to perform their tasks.
"""
@spec create_nonadmin_user(String.t(), String.t()) :: query_result.t()
defp create_nonadmin_user(user_name, password) do
Ecto.Adapters.SQL.query!(
Pta.Repo,
"CREATE USER #{user_name} WITH NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD #{password};"
)
end
@spec grant_table_privileges(String.t(), [String.t()], [String.t()]) :: query_result.t()
defp grant_table_privileges(user_name, privileges, tables) do
for table <- tables do
for privilege <- privileges do
Ecto.Adapters.SQL.query!(
Pta.Repo,
"GRANT #{privilege} ON TABLE #{table} TO #{user_name};"
)
end
end
end
@spec create_query_only_user() :: query_result.t()
def create_query_only_user do
pta_query_password = "'" <> System.fetch_env!("PTA_QUERY_PASSWORD") <> "'"
user_name = "pta_query"
create_nonadmin_user(user_name, pta_query_password)
grant_table_privileges(user_name, ["SELECT"], ["venues", "performances"])
grant_table_privileges(user_name, ["ALL PRIVILEGES"], ["schema_migrations"])
Ecto.Adapters.SQL.query!(Pta.Repo, "GRANT pg_read_all_data TO #{user_name};")
Ecto.Adapters.SQL.query!(Pta.Repo, "GRANT CREATE ON SCHEMA public TO #{user_name};")
end
@spec create_readwrite_user() :: query_result.t()
def create_readwrite_user do
pta_update_password = "'" <> System.fetch_env!("PTA_UPDATE_PASSWORD") <> "'"
user_name = "pta_update"
create_nonadmin_user(user_name, pta_update_password)
grant_table_privileges(user_name, ["SELECT", "INSERT", "UPDATE", "DELETE"], [
"venues",
"performances"
])
grant_table_privileges(user_name, ["ALL PRIVILEGES"], ["schema_migrations"])
Ecto.Adapters.SQL.query!(Pta.Repo, "GRANT pg_write_all_data TO #{user_name};")
Ecto.Adapters.SQL.query!(Pta.Repo, "GRANT CREATE ON SCHEMA public TO #{user_name};")
end
end
LeastPrivilege.create_query_only_user()
LeastPrivilege.create_readwrite_user()
A few notes on this code:
1.) I’ve created this as a separate module but I use it included in my seeds.exs file. This is for the simple reason that if I need to re-initialize the database (via a mix ecto.reset or a similar mechanism) I want to insure that my special least-privileged users get created as well.
2.) You may notice that all of the functions return a query_result. This is the normal behavior of the Ecto.Adapters.SQL.query function; in this case we’re not actually querying any tables but the return type is nonetheless query_result.
3.) In this particular case I’m using two tables. I’m unaware of any reason to believe that these techniques wouldn’t scale up to far more tables and/or privileges.
4.) There are a few grants which might puzzle the reader:
grant_table_privileges(user_name, ["ALL PRIVILEGES"], ["schema_migrations"])
Ecto.Adapters.SQL.query!(Pta.Repo, "GRANT CREATE ON SCHEMA public TO #{user_name};")
These privileges are a concession to the necessity of working with Ecto. I haven’t researched why they needed but I can say for certain that if I attempt to get away with not specifying both privileges I see errors from Ecto.
5.) The operations in this module must be run under a SUPERUSER connection to the database. That is you cannot create a RW user and then log in under that user and create a RO user. Both must be created while the enduser is connected via SUPERUSER. After they’re created you can use the least privileged users (see below).
Using The Least Privileged Users
Finally it’s not very difficult to actually use the least privileged users in your code. For example, if you wanted to use the RW user as your default user within the database you could modify your dev.exs config file in this fashion.
# Configure your database
config :pta, Pta.Repo,
username: "pta_update",
password: *** password ***,
hostname: "localhost",
database: "pta_dev",
stacktrace: true,
show_sensitive_data_on_connection_error: true,
pool_size: 10I haven’t done the work yet to allow me to modify the user dynamically in the code. Again, since I haven’t needed it yet, I haven’t bothered with it.
I hope this blog post helps others who might want to modify their Elixir application to push the database security to the database layer.
Onor.io 