Menu
Create a database with three users and restrict their privileges (I'm just thinking out loud, so my user separation is also open to correction):
postgres superuser works for me, so this one is done.First off, looking at the (generally excellent) PostgreSQL documentation, the page on Grant pretty much leaves me cross-eyed. After spending a few hours reading about PostgreSQL roles and privileges I'm generally confused. I think with a bit more work I'll be able to nail down what I want for the admin user, but I'm pretty stuck on the "app user". I've gotten about this far (naming and passwords are all just placeholders):
$ psql -U postgres
postgres=# CREATE USER "app-admin" WITH PASSWORD 'password';
CREATE ROLE
postgres=# CREATE USER "app-user" WITH PASSWORD 'password';
CREATE ROLE
postgres=# CREATE DATABASE "test-database" WITH OWNER "app-admin";
CREATE DATABASE
postgres=# \c "test-database"
You are now connected to database "test-database" as user "postgres".
test-database=# DROP SCHEMA "public";
DROP SCHEMA
test-database=# CREATE SCHEMA "app" AUTHORIZATION "app-admin";
CREATE SCHEMA
And here's where I get unsure. I feel like the answer I'm trying to avoid is "revoke everything by default then enumerate all the privileges you'll need at all the different levels on all the different objects". I'm trying to avoid that because I straight up don't know what I need there. If that ends up being the answer, then I'll just have to hunker down and read a bunch more, but generally when I start going down paths like that I've missed something.
How do I restrict privileges for app-user so they are unable to modify any structural data (e.g. cannot add or destroy tables) but are able to connect and do anything with rows (row level security is not even on my radar). Is this general model of privileges not really in sync with what PostgreSQL expects? I feel like I'm missing something if I have to walk through every option on that "grant" page to accomplish something like this - whether it be my motivation for doing it in the first place or the means by which I'm going about it.
I'm trying to build my first end-to-end web application. I've done enough general software development and web app development, now I'm trying to understand the pieces that I generally take for granted day to day. I'm trying to set up a PostgreSQL server while keeping the principle of least privilege in mind.
I haven't seen this done on web apps where I have simply joined the development team, although they're generally small and not heavily used. Does doing this actually accomplish anything? Does anyone have compelling reasons for why to do something like this, or why it's a bad or ineffective idea? My assumption was that if I ultimately ended up with a SQL injection vulnerability, this would mitigate the damage because the database user would have limited access. Is that misguided?
923
Check PostgreSQL User Privileges Once you're connected to your database cluster, you can use the \du command to list users that currently exist and see their roles.
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
I'll answer your “side-quest” question first:
you are completely right with your worries and concerns, and everybody who designs an application should think about the same things. Everything else is sloppy and careless.
To mitigate the damage that can be caused by a successful SQL injection attack, you should definitely employ the principle of least privilege.
It should be quite simple to set up a system that matches your requirements.
I'll use the object names from your exaple, except that I'll use underscores instead of minuses. It is good practive to use only lower case letters, underscores and numbers in object names, since it will make your life easier.
/* create the database */
\c postgres postgres
CREATE DATABASE test_database WITH OWNER app_admin;
\c test_database postgres
/* drop public schema; other, less invasive option is to
REVOKE ALL ON SCHEMA public FROM PUBLIC */
DROP SCHEMA public;
/* create an application schema */
CREATE SCHEMA app AUTHORIZATION app_admin;
/* further operations won't need superuser access */
\c test_database app_admin
/* allow app_user to access, but not create objects in the schema */
GRANT USAGE ON SCHEMA app TO app_user;
/* PUBLIC should not be allowed to execute functions created by app_admin */
ALTER DEFAULT PRIVILEGES FOR ROLE app_admin
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
/* assuming that app_user should be allowed to do anything
with data in all tables in that schema, allow access for all
objects that app_admin will create there */
ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app
GRANT SELECT, USAGE ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA app
GRANT EXECUTE ON FUNCTIONS TO app_user;
But if you take the principle of least seriously, you should grant table permissions individually and e.g. not allow app_user to DELETE and UPDATE data in tables where there is no need for the user to do so.
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
