I am using Laravel 9 and PostgreSQL 15 as a part of my stack for personal blog project. During its setup, I ended up with a couple of questions.

1. If I am setting up a separate role (according to this part of PostgreSQL documentation, a user in PostgreSQL is a role with the LOGIN privilege), what is the proper attribute setup for that role so it's not a superuser (using root or postgres role everywhere is generally considered a bad practice due to all-round freedom of these roles) but is sufficient for Laravel to manage the database?
2. Is it necessary to create multiple roles for the same project for more flexible permission management? If yes, what is the typical multi-role approach to use with Laravel?

CodePudding user response：

You need a user that has the create privilege on a schema in order to be able to create tables and work with them. One way to achieve this, is to create an application specific schema and make your app user own it:

create user app_user password '*******';
create schema app_schema authorization app_user;
alter user app_user set search_path = 'app_schema';

app_user can create and alter tables in app_schema and as that user then will be the owner of those table, they can also modify the data in them.

By setting the search_path to app_schema the tables don't need to be schema qualified in your application.

Note that before Postgres 15 every user would have those privileges in the public schema (because they were granted to the pseudo-role "public"). But that was removed for security reasons. Even on an old version, following the above pattern is a good idea for security reasons. If you do this on an old version, you probably want to DROP the public schema completely or at least remove the privileges on it:

remove all privileges on schema public from public;

In some environments the owner of the table and the DB user used to do DML are separate users/roles with the owner not being allowed to login so it can't be exploited.

create role app_owner with nologin;    
create user app_user password '*******';
create schema app_schema authorization app_owner;
grant usage on app_schema to app_user;
alter user app_user set search_path = 'app_schema';

-- Apply default grant for tables created by app_owner
alter default privileges 
  for role app_owner
  in schema app_schema
  grant select,insert,update,delete on tables
  to app_user;

-- if you are still using the outdated serial pseudo-type
-- allow access to all sequences
-- this is not necessary if using identity columns 
alter default privileges 
  for role app_owner
  in schema app_schema
  grant select,usage on sequences
  to app_user;

To create tables, you would login as e.g. the superuser, then use set rol app_owner and create/alter the tables in the schema.

This setup makes schema migrations more complicated. When your application includes the schema migrations this is nearly impossible to use, because the app_owner can't login and granting the app_user that role would defeat the whole setup.

I personally don't think this buys that much more security. After all an attacker than can run delete from customer; isn't really less of a threat than an attacker than can run drop table customer;