web application's DML operations, I authorize my users using predefined roles. there are two different methods stuck in my head =>
------1----------------
CREATE USER dml_appuser WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE proddb TO dml_appuser;
GRANT TEMPORARY ON DATABASE proddb TO dml_appuser;
GRANT pg_read_all_data TO dml_appuser;
GRANT pg_write_all_data TO dml_appuser;
------1----------------
------2----------------
CREATE USER dml_appuser WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE proddb TO dml_appuser;
GRANT TEMPORARY ON DATABASE proddb TO dml_appuser;
GRANT USAGE, CREATE ON SCHEMA myschema TO dml_appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO dml_appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_appuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO dml_appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO dml_appuser;
------2----------------
The 1st method seems more applicable because it is shorter. what is the best practices of this process? what are your suggestions?
Hello everyone, i have a question. When defining user/role operations for web application's DML operations, I authorize my users using predefined roles. there are two different methods stuck in my head => 1. Using GRANT pg_read_all_data and pg_write_all_data etc. 2. Using GRANT CONNECT ON DATABASE, GRANT USAGE, CREATE ON SCHEMA etc. The 1st method seems more applicable because it is shorter. what is the best practices of this process? what are your suggestions? Would it be bad for me to use predefined roles?
Обсуждают сегодня