Как создать нового юзера и новую базу в postgres, на убунту?

Question

[yellow_pus]

Я совсем новичок в убунту, до этого всегда создавал базы и таблицы в опенсервере на винде, Как я понял, есть дефолтный суперюзер postgres, войти в которого можно через терминал, прописав sudo -i -u postgres и внутри него уже работать с базой данных. Но сейчас у меня появилась необходимость создать нового юзера, отдельно от postgres и уже внутри него создавать новые базы, НО помимо этого, мне бы еще хотелось подключить эту базу в pgadmin, который в свою очередь требует логин и пароль от базы, как можно создать новую базу, в новом юзере и откуда брать логин и пароль от этой базы?

Answer 1

[Melkij]

Парольная аутентификация лишь один из множества доступных вариантов. Хотя наиболее простой и распространённый. Какой способ аутентификации будет требовать СУБД - зависит от списка правил в 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

Answer 2

[Василий Банников]

откуда брать логин и пароль от этой базы?

Ты их указываешь, когда в первый раз базу создаёшь.
Если не указал, то лезь в /etc/postgresql/[версия]/main/pg_hba.conf и разрешай беспарольный доступ.

# IPv4 local connections:
host    all     all     127.0.0.1/32      trust
# IPv6 local connections:
host    all     all     ::1/128           trust

Главное потом обратно верни.

После изменения конфига надо постгрес перезапустить.

И тогда ты сможешь подключиться примерно так:
psql -U postgres -h localhost

Для создания нового пользователя - подключаешься к базе через psql или через pgadmin, или ещё как, и делаешь запрос CREATE USER
https://www.postgresql.org/docs/14/sql-createuser.html
Для создания базы, соответственно CREATE DATABASE
https://www.postgresql.org/docs/14/sql-createdatab...

прописав sudo -i -u postgres

Это совершенно не обязательно (я даже не уверен, что так можно)

Comments

[yellow_pus]

То есть, не нужно никак указывать, под каким юзером я создаю ту или иную базу?

[Drno]

yellow_pus,
создается юзер
создается база (можно сразу указать какому юзеру разрешен доступ)
дается доступ нужному юзер к нужной базе(привелегии)

Это всё есть в документации постгре

задайте эти вопросы в гугл, найдете команды