Целесообразно ли регистрировать пользователей сайта как PostgreSQL-пользователей?
Совершенно противоестественная идея.
Пользователи не умеют правильно пользоваться приложением. Не умеют придумывать сложные пароли. Не умеют вообще следить за паролями. Если вы будете раздавать sql доступ к базе, ее обязательно поломают. Сломают других пользователей и так далее. А средствами sql базы безопасность поддерживать можно очень ограниченно.
Среди очевидных проблем, с которым я столкнусь - необходимось поддержывать отдельное подключения к PostgreSQL для каждого пользователя
Даже этого "очевидно" должно хватить чтоб "закопать идею", 1 коннект - 1 процесс и даже если оно не так, сама идея пускать каких-то там пользователей со стороны в СУБД вам не кажется, мягко скажем, странной ?