1.3. Создание базы данных. Создание пользователя и базы данных postgresql

PostgreSQL. Краткий справочник.

Этот пост - краткая инструкция для начинающих, для тех кто впервые установил PostgreSQL. Здесь вся необходимая информация для того, чтобы начать работу с PostgreSQL.

Подключение к СУБД

Первое, что нужно сделать - получить доступ к PostgreSQL, доступ в качестве суперпользователя. Настройки аутентификации находятся в файле pg_hba.conf.

# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer

Эта строка говорит о том, что пользователь postgres может подключаться к любой базе данных локальной СУБД PostgreSQL через сокет. Пароль при этом вводить не надо, операционная система передаст имя пользователя, и оно будет использовано для аутентификации. Подключаемся:

$ sudo -u postgres psql postgres postgres

Чтобы иметь возможность подключаться по сети, надо в pg_hdba.conf добавить строку:

# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 0.0.0.0/0 md5

Метод аутентификации md5 означает, что для подключения придется ввести пароль. Это не очень удобно, если вы часто пользуетесь консолью psql. Если вы хотите автоматизировать какие-то действия, то плохая новость в том, что psql не принимает пароль в качестве аргумента. Есть два пути решения этих проблем: установка соответствующей переменной окружения и хранение пароля в специальном файле .pgpass.

Установка переменной окружения PGPASSWORD

Сразу скажу, что лучше этот способ не использовать, потому что некоторые операционные системы позволяют просматривать обычным пользователям переменные окружение с помощью ps. Но если хочется, то надо написать в терминале:

export PGPASSWORD=mypasswd

Переменная будет доступна в текущей сессии. Если нужно задать переменную для всех сессий, то надо добавить строку из примера в файл .bashrc или .bash_profile

Хранение пароля в файле .pgpass

Если мы говорим о Linux, то файл должен находится в $HOME (/home/username). Права на запись и чтение должны быть только у владельца (0600). В файл нужно записывать строки вида:

hostname:port:database:username:password

В первые четыре поля можно записать "*", что будет означать отсутствие фильтрации (полную выборку).

Получение справочной информации

\? - выдаст все доступные команды вместе с их кратким описанием, \h - выдаст список всех доступных запросов, \h CREATE - выдаст справку по конкретному запросу.

Управление пользователями СУБД

Как получить список пользователей PostgreSQL?

Или можно сделать запрос к таблице pg_user.

SELECT * FROM pg_user;

Создание нового пользователя PostgreSQL

Из командной оболочки psql это можно сделать с помощью команды CREATE.

CREATE USER username WITH password 'password';

Или можно воспользоваться терминалом.

createuser -S -D -R -P username

Ввод пароля будет запрошен.

Изменение пароля пользователя

ALTER USER username WITH PASSWORD 'password';

Изменение ролей пользователя

Чтобы пользователь имел право создавать базы данных, выполните запрос:

ALTER ROLE username WITH CREATEDB;

Управление базами данных

Вывод списка баз данных в терминале psql:

Тоже самое из терминала Linux:

psql -l

Создание базы данных из psql (PostgreSQL Terminal)

CREATE DATABASE dbname OWNER dbadmin;

Создание новой базы данных при помощи терминала:

createdb -O username dbname;

Настройка прав доступа к базе данных

Если пользователь является владельцем (owner) базы данных, то у него есть все права. Но если вы хотите дать доступ другому пользователю, то сделать это можно с помощью команды GRANT. Запрос ниже позволит пользователю подключаться к базе данных. Но не забывайте о конфигурационном файле pg_hba.conf, в нем тоже должны быть соответствующие разрешения на подключение.

GRANT CONNECT ON DATABASE dbname TO dbadmin;

Частые вопросы

Как узнать, существует ли база данных?

Check if database exists in postgreSQL using shell

Как узнать, существует ли пользователь?

Например, можно выполнить команду:

psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USER_NAME'"

Если пользователь существует, будет возращена единица, иначе ничего не будет возвращено.

Дополнительная информация

PostgreSQL. Файл pg_hba.conf.

www.mvoronin.pro

Установка и первоначальная настройка PostgreSQL

1. Установка

1.1. Установка из официального репозитория

Если для Вас важна последняя доступная версия PostgreSQL (если нет, я советую Вам хорошенько подумать), то Вам необходимо установить ее из официального репозитория PostgreSQL. Сделать это можно, следуя официальной инструкции. Затем, следует обновить пакеты:

$ sudo apt-get update

И установить PostgreSQL командой:

$ sudo apt-get install postgresql-x.x

x.x - необходимая версия

Список всех доступных версий можно посмотреть командой:

$ sudo apt-cache search postgresql

1.2. Установка из репозитория ОС

Установка PostgreSQL из репозитория ОС производится путем добавления двух основных пакетов:

$ sudo apt-get install postgresql postgresql-contrib

2. Консоль PostgreSQL

Все доступные операции над базами данных и пользователями производится из консоли psql.

2.1. Вход в консоль

Для начала необходимо войти в систему от пользователя postgres, это возможно только с правами root:

# su - postgres

Пользователь postgres - это своеобразный суперпользователь для базы данных PostgreSQL. Затем, из-под пользователя postgres можно войти в консоль:

$ psql

Или проще, сразу входим в консоль psql под пользователем postgres:

$ sudo -u postgres psql

2.2. Выход из консоли

Когда выполнены все операции над пользователями и базами данных PostgreSQL в консоли psql, не сразу можно сообразить, как из нее выйти. Тут все очень просто:

postgres=# \q

И, если это необходимо, уходим от пользователя postgres:

$ exit

3. Пользователи PostgreSQL

Рекомендуется для каждой базы данных создать независимую прослойку, создав нового пользователя, и только ему дать права к управлению ей. Манипуляции с пользователями производятся с консоли psql.

3.1. Создание пользователя

Тут все достаточно просто:

# CREATE USER username WITH PASSWORD '12345';

username - логин нового пользователя
‘12345’ - Пароль пользователя. Вводится в кавычках

3.2. Удаление пользователя

Тут еще проще:

# DROP USER username;

username - логин пользователя, которого необходимо удалить.

4. Базы данных

Все манипуляции с базой данных также производятся в консоли psql.

4.1. Создание базы данных

Тут все также, как при создании пользователя:

# CREATE DATABASE dbname;

dbname - имя создаваемой базы данных

4.2. Удаление базы данных

# DROP DATABASE dbname;

dbname - имя удаляемой базы данных

Прошу заметить, что база данных откажется удаляться в определенных случаях:

Если имеются пользователи с правами на эту базу. Перед удалением базы данных их права необходимо отозвать. Об этом далее.
Если имеется хотя бы одна незакрытая сессия соединения с базой данных. В этом случае сессии необходимо будет закрыть. В крайнем случае, можно просто остановить сервера, взаимодействующие с этой базой данных на момент ее удаления (хотя, смысла их запускать после удаления базы данных мало)

4.3. Назначение прав пользователям

Наличие базы данных и пользователей в системе PostgreSQL само по себе не дает результатов. Для корректной работы определенного пользователя с определенной базой данных, ему необходимо назначить права на работу с ней. Для этого выполняем комманду:

# GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

dbname - имя базы данных, права над работой которой необходимо дать доступ
username - имя пользователя, которому будут предоставлены права над указанной базой данных

4.4. Удаление прав пользователей

Иногда, возникает необходимость сменить пользователя, управляющего базой данных, или просто отозвать права для ее последующего удаления. Рекомендую не пренебрегать этой командой и действовать по принципу “Один пользователь управляет одной базой данных”.

# REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

dbname - имя базы данных, права над работой которой необходимо отозвать
username - имя польователя, права которого необходимо отозвать

karimov.info

PostgreSQL : Документация: 9.4: Создание базы данных : Компания Postgres Professional

Первое, как можно проверить, есть ли у вас доступ к серверу баз данных, — это попытаться создать базу данных. Работающий сервер PostgreSQL может управлять множеством баз данных, что позволяет создавать отдельные базы данных для разных проектов и пользователей.

Возможно, ваш администратор уже создал базу данных для вас и сообщил вам её имя. В этом случае вы можете пропустить этот этап и перейти к следующему разделу.

Для создания базы данных, в этом примере названной mydb, выполните следующую команду:

$ createdb mydb

Если вы не увидите никаких сообщений, значит операция была выполнена успешно и продолжение этого раздела можно пропустить.

Если вы видите сообщение типа:

createdb: command not found

значит PostgreSQL не был установлен правильно. Либо он не установлен вообще, либо в путь поиска команд оболочки не включён его каталог. Попробуйте вызвать ту же команду, указав абсолютный путь:

$ /usr/local/pgsql/bin/createdb mydb

У вас этот путь может быть другим. Свяжитесь с вашим администратором или проверьте, как были выполнены инструкции по установке, чтобы исправить ситуацию.

Ещё один возможный ответ:

createdb: не удалось подключиться к базе postgres: не удалось подключиться к серверу: No such file or directory Он действительно работает локально и принимает соединения через доменный сокет "/tmp/.s.PGSQL.5432"?

Это означает, что сервер не работает или createdb не может к нему подключиться. И в этом случае пересмотрите инструкции по установке или обратитесь к администратору.

Также вы можете получить сообщение:

createdb: не удалось подключиться к базе postgres: ВАЖНО: роль "joe" не существует

где фигурирует ваше имя пользователя. Это говорит о том, что администратор не создал учётную запись PostgreSQL для вас. (Учётные записи PostgreSQL отличаются от учётных записей пользователей операционной системы.) Если вы сами являетесь администратором, прочитайте Главу 20, где написано, как создавать учётные записи. Для создания нового пользователя вы должны стать пользователем операционной системы, под именем которого был установлен PostgreSQL (обычно это postgres). Также возможно, что вам назначено имя пользователя PostgreSQL, не совпадающее с вашим именем в ОС; в этом случае вам нужно явно указать ваше имя пользователя PostgreSQL, используя ключ -U или установив переменную окружения PGUSER.

Если у вас есть учётная запись пользователя, но нет прав на создание базы данных, вы увидите сообщение:

createdb: создать базу данных не удалось: ОШИБКА: нет прав на создание базы данных

Создавать базы данных разрешено не всем пользователям. Если PostgreSQL отказывается создавать базы данных для вас, значит вам необходимо соответствующее разрешение. В этом случае обратитесь к вашему администратору. Если вы устанавливали PostgreSQL сами, то для целей этого введения вы должны войти в систему с именем пользователя, запускающего сервер БД.

Вы также можете создавать базы данных с другими именами. PostgreSQL позволяет создавать сколько угодно баз данных. Имена баз данных должны начинаться с буквы и быть не длиннее 63 символов. В качестве имени базы данных удобно использовать ваше текущее имя пользователя. Многие утилиты предполагают такое имя по умолчанию, так что вы сможете упростить ввод команд. Чтобы создать базу данных с таким именем, просто введите:

$ createdb

Если вы больше не хотите использовать вашу базу данных, вы можете удалить её. Например, если вы владелец (создатель) базы данных mydb, вы можете уничтожить её, выполнив следующую команду:

$ dropdb mydb

(Эта команда не считает именем БД по умолчанию имя текущего пользователя, вы должны явно указать его.) В результате будут физически удалены все файлы, связанные с базой данных, и так как отменить это действие нельзя, не выполняйте его, не подумав о последствиях.

Узнать о командах createdb и dropdb больше можно в справке createdb и dropdb .

postgrespro.ru

PostgreSQL : Документация: 9.6: 22.2. Создание базы данных : Компания Postgres Professional

22.2. Создание базы данных

Для создания базы данных сервер PostgreSQL должен быть развёрнут и запущен (см. Раздел 18.3).

База данных создаётся SQL-командой CREATE DATABASE:

CREATE DATABASE имя;

где имя подчиняется правилам именования идентификаторов SQL. Текущий пользователь автоматически назначается владельцем. Владелец может удалить свою базу, что также приведёт к удалению всех её объектов, в том числе, имеющих других владельцев.

Создание баз данных это привилегированная операция. Как предоставить права доступа, описано в Разделе 21.2.

Поскольку для выполнения команды CREATE DATABASE необходимо подключение к серверу базы данных, возникает вопрос как создать самую первую базу данных. Первая база данных всегда создаётся командой initdb при инициализации пространства хранения данных (см. Раздел 18.2.) Эта база данных называется postgres. Далее для создания первой «обычной» базы данных можно подключиться к postgres.

Вторая база данных template1, также создаётся во время инициализации кластера. При каждом создании новой базы данных в рамках кластера по факту производится клонирование шаблона template1. При этом любые изменения сделанные в template1 распространяются на все созданные впоследствии базы данных. Следует избегать создания объектов в template1, за исключением ситуации, когда их необходимо автоматически добавлять в новые базы. Более подробно в Разделе 22.3.

Для удобства, есть утилита командной строки для создания баз данных, createdb.

createdb dbname

Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет ранее описанную SQL-команду CREATE DATABASE. Подробнее о её вызове можно узнать в createdb. Обратите внимание, что команда createdb без параметров создаст базу данных с именем текущего пользователя.

Примечание

Глава 20 содержит информацию о том, как ограничить права на подключение к заданной базе данных.

Иногда необходимо создать базу данных для другого пользователя и назначить его владельцем, чтобы он мог конфигурировать и управлять ею. Для этого используйте одну из следующих команд:

CREATE DATABASE имя_базы OWNER имя_роли;

из среды SQL, или:

createdb -O имя_роли имя_базы

из командной строки ОС. Лишь суперпользователь может создавать базы данных для других (для ролей, членом которых он не является).

postgrespro.ru

Создание базы данных | PostgreSQL

Первый тест, с помощью которого будет видно, что вы можете получить доступ к серверу баз данных - это попытка создать базу данных. Запущенный сервер PostgreSQL может управлять множеством баз данных. Обычно, для каждого проекта или каждого пользователя используется отдельная база данных.

Возможно, что администратор вашей машины уже создал базу данных для вас. Он должен был бы сказать вам имя вашей базы данных. В этом случае вы можете пропустить данную секцию и перейти к следующей.

Для создания новой базы данных, в этом примере называющейся mydb, вы можете использовать следующую команду:

$ createdb mydb

Если команда завершилась без каких-либо сообщений, то данный шаг был пройден успешно и вы можете пропустить написанное далее до конца этой секции.

Если вы получите что-то похожее на:

createdb: command not found

то это означает, что продукт PostgreSQL не был правильно установлен. Или было установлено не все или путь поиска в вашем командном интерпретаторе не был установлен соответствующим образом. Попытайтесь вызвать эту команду, используя полный путь:

$ /usr/local/pgsql/bin/createdb mydb

Полный путь на вашей машине может быть другим. Свяжитесь с вашим администратором или проверьте инструкции по установке, чтобы исправить ситуацию.

Другое сообщение, которое вы можете получить:

createdb: could not connect to database postgres: could not connect to server: No such file or directory No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Это означает, что сервер не был запущен или что он не был запущен так, как этого ожидает команда createdb. Снова, проверьте инструкции по установке или проконсультируйтесь с администратором.

Ещё одно сообщение, которое вы можете получить:

createdb: could not connect to database postgres: FATAL: user "joe" does not exist

где вместо joe упоминается ваше имя регистрации. Такое сообщение появляется если администратор не создал для вас пользователя в PostgreSQL. (Пользователь PostgreSQL — это не пользователь операционной системы). Если вы являетесь администратором, смотрите информацию о создании пользователя в Chapter 20. Вам понадобится зарегистрироваться в операционной системе под именем того пользователя, из под которого работает PostgreSQL (обычно postgres), чтобы создать первого пользователя в СУБД. Для команды создания базы, вы также можете указать в PostgreSQL имя пользователя, которое отличается от вашего текущего имени пользователя в операционной системе; в этом случае вам необходимо задать имя пользователя PostgreSQL с помощью опции -U или установки переменной окружения PGUSER.

Если нужный пользователь существует, но у него нет прав, требуемых для создания базы данных, вы увидите следующее сообщение:

createdb: database creation failed: ERROR: permission denied to create database

Не каждый пользователь имеет авторизацию для создания новых баз данных. Если PostgreSQL отвергает ваши попытки создания баз данных, то администратору вашей машины нужно дать вам права для создания баз данных. Проконсультируйтесь с ним в этом случае. Если вы устанавливали PostgreSQL сами, то вы должны для целей преследуемых этим учебным руководством подключиться как пользователь, от имени которого запускается сервер баз данных.

Вы можете также создавать базы данных с любыми другими именами. PostgreSQL позволяет вам создавать любое количество баз данных на одном сервере. Имена баз данных должны состоять из букв и цифр (вначале всегда должна быть буква) и быть не более 63 байт длиной. Довольно удобно создавать базу данных с таким же именем как у пользователя. Многие инструменты по умолчанию считают, что имя базы данных именно такое, так что вы сможете не нажимать лишние кнопки. Чтобы создать такую одноимённую с именем пользователя базу данных, просто наберите:

$ createdb

Если вы не хотите использовать вашу базу данных в будущем, вы можете удалить ее. Например, если вы являетесь владельцем (создателем) базы данных mydb, вы можете уничтожить её, используя следующую команду:

$ dropdb mydb

(Для этой команды вы должны обязательно задать имя базы данных, она не будет считать, что имя базы данных - это имя текущего пользователя). Данная команда физически удалит все файлы, связанные с указанной базой данных так, что их нельзя будет восстановить, так что выполняйте эту операцию с большой осторожностью.

Больше информации о командах createdb и dropdb можно найти соответственно в createdb и dropdb.

postgresql.ru.net

PostgreSQL : Документация: 10: 22.2. Создание базы данных : Компания Postgres Professional

22.2. Создание базы данных

Для создания базы данных сервер PostgreSQL должен быть развёрнут и запущен (см. Раздел 18.3).

База данных создаётся SQL-командой CREATE DATABASE:

CREATE DATABASE имя;

Создание баз данных это привилегированная операция. Как предоставить права доступа, описано в Разделе 21.2.

Для удобства, есть утилита командной строки для создания баз данных, createdb.

createdb dbname

Примечание

Глава 20 содержит информацию о том, как ограничить права на подключение к заданной базе данных.

CREATE DATABASE имя_базы OWNER имя_роли;

из среды SQL, или:

createdb -O имя_роли имя_базы

postgrespro.ru

Postgres Pro Standard : Документация: 9.5: 1.3. Создание базы данных : Компания Postgres Professional

1.3. Создание базы данных

Первое, как можно проверить, есть ли у вас доступ к серверу баз данных, — это попытаться создать базу данных. Работающий сервер Postgres Pro может управлять множеством баз данных, что позволяет создавать отдельные базы данных для разных проектов и пользователей.

Для создания базы данных, в этом примере названной mydb, выполните следующую команду:

$ createdb mydb

Если вы видите сообщение типа:

createdb: command not found

значит Postgres Pro не был установлен правильно. Либо он не установлен вообще, либо в путь поиска команд оболочки не включён его каталог. Попробуйте вызвать ту же команду, указав абсолютный путь:

$ /usr/local/pgsql/bin/createdb mydb

Ещё один возможный ответ:

Также вы можете получить сообщение:

createdb: не удалось подключиться к базе postgres: ВАЖНО: роль "joe" не существует

где фигурирует ваше имя пользователя. Это говорит о том, что администратор не создал учётную запись Postgres Pro для вас. (Учётные записи Postgres Pro отличаются от учётных записей пользователей операционной системы.) Если вы сами являетесь администратором, прочитайте Главу 20, где написано, как создавать учётные записи. Для создания нового пользователя вы должны стать пользователем операционной системы, под именем которого был установлен Postgres Pro (обычно это postgres). Также возможно, что вам назначено имя пользователя Postgres Pro, не совпадающее с вашим именем в ОС; в этом случае вам нужно явно указать ваше имя пользователя Postgres Pro, используя ключ -U или установив переменную окружения PGUSER.

Если у вас есть учётная запись пользователя, но нет прав на создание базы данных, вы увидите сообщение:

createdb: создать базу данных не удалось: ОШИБКА: нет прав на создание базы данных

Создавать базы данных разрешено не всем пользователям. Если Postgres Pro отказывается создавать базы данных для вас, значит вам необходимо соответствующее разрешение. В этом случае обратитесь к вашему администратору. Если вы устанавливали Postgres Pro сами, то для целей этого введения вы должны войти в систему с именем пользователя, запускающего сервер БД.

Вы также можете создавать базы данных с другими именами. Postgres Pro позволяет создавать сколько угодно баз данных. Имена баз данных должны начинаться с буквы и быть не длиннее 63 символов. В качестве имени базы данных удобно использовать ваше текущее имя пользователя. Многие утилиты предполагают такое имя по умолчанию, так что вы сможете упростить ввод команд. Чтобы создать базу данных с таким именем, просто введите:

$ createdb

$ dropdb mydb

Узнать о командах createdb и dropdb больше можно в справке createdb и dropdb.

postgrespro.ru