Applying Least Privilege Principal To Ecto

[Onorio Catenacci]

This is going to be a pretty extensive email so apologies in advance.

I am a firm believer that security usually arises from all sorts of small decisions.  While it's important to take care of the various known attack vectors,  I also consider that a lot of problems can be avoided by limiting permissions to only those which are absolutely necessary.  I mean one of the reasons that Windows used to be such a favorite target of malicious actors was the fact that by default the user on Windows is effectively a root user and hence able to change everything.  Of course this practice violated the principle of least privilege. 

Applying this idea of least privileges to databases, when one is running in production, one should try to code an id that can only query the database and then only query tables that a particular user should have access to.  That way should some bad actor discover the ID and the password, all they can do is read data from some tables (which is still far from ideal but it helps to limit the damage).

This is why I started researching how one might

a.) Create readonly users

b.) Use an Elixir app to access the database via the readonly user.

c.) Create a separate user to write data to the database.  Yes, it would be easier to have the same user be able to query and insert new data but that's also less secure.

So I've done a little research (which I'll gladly share if anyone's interested) but it occurred to me that I sort of skipped what should have been a reasonable first step: inquiring if others have done research on this idea and, if so, what they found.

So I wanted to ask about a few things:

1.) Has anyone else done research on creating a readonly user in Postgres? I've found some stuff myself but I'd love to hear what anyone else may have found.

2.) Has anyone else done research on interacting with a database via Ecto using a readonly user?  Again, I've done a bit of research but I wanted to see if anyone else had done this as well.

3.) Anyone have an idea of how we might connect to the database with a different id without having to alter the config file?

4.) I've run some tests using my readonly (and readwrite) ID to run GraphQL code and it seems to work fine. But anyone have any ideas of what else I should make sure I test?

Thanks in advance for any replies anyone cares to share.

--

Onorio

[Jon Rowe]

Having multiple repos pointing to the same database is a fairly common strategy for splitting read and writes, this is often done for performance reasons (e.g read replicas vs primary) but there’s no reason it couldn’t be done for “just” security reasons.

You also likely want a third user with permissions to alter the database to run migrations.

If you’re using “12 factor” style configuration your injecting the user / password via environment variable or other secret store on run time so it’s easy to change the user for just migrations but for multiple repos you’d want to pass in multiple configurations.

Cheers

Jon

--

You received this message because you are subscribed to the Google Groups "elixir-ecto" group.

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CACW5TXTYMeOkVGNSt_6RhCoGgpUUgnq3w0L6tE7x%2BXEts%2BUr8A%40mail.gmail.com.