It's doable. You have a web role in DB that's used for public facing part of a website and then admin role that's used for backoffice part of the website. You can then separate these backends to different machines or at least to a differnet UNIX users, so that when someone breaks through via public facing backend, he doesn't immediately get access to everything, but still has to work for it a bit.

It's not as simple, you still have to make sure public backend user can't access web logs, that may reveal session id of an backoffice admin account or other information useful for breaking in other parts of the website.

Eventually, the attacker will gain access, but this is useful to slow him down enough, so there's a chance he'll give up, or you notice something suspicious.

For example pgbouncer allows you to connect via multiple roles/credentials to a database. It's just about how you configure your pool.

Not to mention that with the microservices fad you could have different server processes with different credentials in their connection pools for different operations.