Парольная аутентификация лишь один из множества доступных вариантов. Хотя наиболее простой и распространённый. Какой способ аутентификации будет требовать СУБД - зависит от списка правил в pg_hba.conf. Очень редко нужно что-то кроме служебного правила peer для postgres (именно из-за него psql после sudo -iu postgres не спрашивает никакие пароли, но только у postgres'а) и md5 для всего остального.
Базово создание новой базы с отдельным пользователем-владельцем выглядит так (от суперпользователя базы):
CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
REVOKE ALL ON DATABASE $NEWDB FROM public;
В соседнем ответе вам неверно подсказывают, что "можно сразу указать какому юзеру разрешен доступ". create database разрешит доступ всем (но именно подключение к базе, а не ко всему внутри базы - что частая проблема непонимания прав). И имеет смысл наоборот отобрать доступ от всех прочих, т.е. от public. Owner, конечно, доступ сохранит.
Чуть ближе к production имеет смысл использовать такой шаблон:
CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
CREATE ROLE ${NEWDB}_role;
CREATE ROLE ${NEWDB}_ro;
CREATE ROLE ${NEWDB}_rw;
GRANT ${NEWDB}_role TO ${NEWDB}_ro, ${NEWDB}_rw;
REVOKE ALL ON DATABASE $NEWDB FROM public;
GRANT CONNECT ON DATABASE $NEWDB TO ${NEWDB}_role;
GRANT ${NEWDB}_rw TO "$NEWOWNER";
\c $NEWDB
ALTER SCHEMA public OWNER TO "$NEWOWNER";
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO ${NEWDB}_ro, ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON TABLES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;
В результате получим:
- пользователя-владельца базы, который предназначен выполнять всякие миграции схемы данных
- роль имя_базы_role которую можно давать другим пользователям для возможности подключения к этой базе, но без доступа к таблицам приложения (например, используем для мониторинга)
- роль имя_базы_ro которая даст select-only права ко всем (в том числе будущим) таблицам, созданным от пользователя-владельца это базы
- роль имя_базы_rw - соответственно для выполнения select,insert,update,delete
