Sql

Создать базу данных postgresql: Шпаргалка по PostgreSQL

Содержание

Шпаргалка по PostgreSQL

PostgreSQL это кроссплатформенная полнофункциональная объектно-реляционная база данных. Эта небольшая заметка написана с целью собрать в одном месте небольшой туториал по установке и настройке этой СУБД. Действие происходит в ОС Ubuntu 16.04.

Установка PostgreSQL

По умолчанию PostgreSQL входит в пакет Ubuntu. Систему можно установить следующей командой:


sudo apt-get install postgresql-9.4

Для установки самой последней версии СУБД, необходимо выполнить следующие шаги:

  1. Создаём файл /etc/apt/sources.list.d/pgdg.list
  2. Добавляем  в него следующее содержимое: 
    
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
  3. Импортируем новый ключ для подписи, выполнив 
    
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add -
sudo apt-get update
  4. Устанавливаем последнюю версию: 
    
apt-get install postgresql-9.5

Настройка

После успешной установки самоё время заняться базовой настройкой СУБД PostgreSQL.

По умолчанию доступ к СУБД имеет пользователь postgres. Заходим под ним:


sudo -u postgres psql

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


CREATE DATABASE mydatabase;

Создаём отдельного пользователя с логином myuser и паролем password:


CREATE USER myuser WITH password 'password';

Чтобы дать полные привилегии над нашей новой базой данных mydatabase пользователю myuser выполняем:


GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Чтобы сделать нового пользователя администратором нашей СУБД, выполняем:


ALTER USER myuser WITH SUPERUSER;

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


ALTER USER myuser WITH NOSUPERUSER;

Если пользователю нужно дать лишь права на создание новой БД (например, при запуске тестов в Django, фреймворк создаёт отдельную БД с префиксом test_):


ALTER USER myuser CREATEDB;

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


psql -h localhost mydatabase myuser

Для выхода из сеанса, необходимо выполнить команду \q

Фишки при работе с PostgreSQL

Чтобы не запоминать массу команд, существуют GUI утилиты для работы с PostgreSQL. Наиболее популярной считается pgadmin3.


sudo apt-get install pgadmin3

Для тех, кто всё же предпочитает оставаться в терминале, есть интересный скрипт, написанный на Python: pgcli. Его особенностью является автодополнение и подсветка синтаксиса SQL команд.


pip install pgcli
pgcli mydatabase

или


pgcli postgres://myuser:password@localhost:5432/mydatabase

Просто и удобно.

Снятие дампа БД PostgreSQL можно сделать вот так:


pg_dump -c -h localhost -U myuser mydatabase > ./mydump.sql

Импорт в PostgreSQL делается следующим образом:


psql -h localhost -U username dbname < dump.sql

PostgreSQL масштабная и сложная СУБД, и для её настройки потребуется немало времени, если вы столкнулись с ней в работе впервые. Эта заметка лишь начало интересного пути. Постараюсь делиться опытом и знаниями по мере возможности и сил 🙂

💌 Присоединяйтесь к рассылке

Понравился контент? Пожалуйста, подпишись на рассылку.

Иллюстрированный самоучитель по PostgreSQL › Управление базами данных › Создание и удаление баз данных [страница — 215] | Самоучители по программированию

Создание и удаление баз данных

В процессе установки PostgreSQL создаются два стандартных шаблона баз данных, template() и templatel, на основе которых создаются новые базы данных. Из этих двух шаблонов вы можете подключиться только к templatel. Это связано с тем, что шаблон template() всегда остается пустым, a tempi atel можно модифицировать и включать в него поддержку языков, функции и даже объекты баз данных (таблицы, представления и последовательности). Удаление шаблонов из системы не разрешается.

Далее описаны процедуры создания и удаления баз данных в PostgreSQL.

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

В PostgreSQL существует два способа создания новых баз данных: команда SQL CREATE DATABASE и программа createdb, работающая в режиме командной строки. Оба способа требуют наличия необходимых прав. Для создания базы данных не нужно быть суперпользователем PostgreSQL, но в таблице pg_shadow должно быть установлено право usecreatedb.

Если вы не уверены в том, обладает ли этим правом конкретный пользователь, для проверки можно воспользоваться запросом к представлению pgjjser (которое, в свою очередь, получает информацию из таблицы pg_shadow; прямые запросы к pg_shadow разрешены только суперпользователям). Поле usecreatedb таблицы pg_shadow содержит логический признак наличия права создания таблиц у данного пользователя. В листинге 9.9 приведен пример запроса к представлению pg_user, проверяющего наличие права usecreatedb для пользователя guest.

Листинг 9.9. Проверка наличия права usecreatedb.

tempiatel › SELECT usecreatedb FROM pg_user WHERE usename='guest':
usecreatedb
f
(1 row)

Команда CREATE DATABASE

Команда SQL CREATE DATABASE имеет следующий синтаксис:

CREATE DATABASE база_дднных
[ WITH [ LOCATION = 'каталог' ]
[ TEMPLATE = шаблон ]
[ ENCODING = кодировка ] ]

Параметр база_данных определяет имя создаваемой базы данных. Имена баз данных должны начинаться с алфавитного символа, а их длина не должна превышать 31 символа. PostgreSQL позволяет создать в заданном каталоге любое количество баз данных (естественно, при наличии свободного места на диске).

За необязательным ключевым словом WITH можно указать до трех дополнительных атрибутов.

  • LOCATION = ‘каталог’. В апострофах передается имя переменной среды, инициализированной в среде пользователя, запустившего серверный процесс PostgreSQL. Предположим, в файл /home/postgres/.bash_profile входит следующая строка:
export PGDATA2="/usr/local/pgsql/data2"

После запуска PostgreSQL (при наличии определенной переменной PGDATA2) переменная может использоваться в качестве значения параметра LOCATION. Эта общая мера безопасности предотвращает запись в посторонние каталоги файловой системы. Если ключевое слово LOCATION отсутствует, PostgreSQL создает базу данных в каталоге данных по умолчанию (например, /usr/local/pgsql/data).

  • TEMPLATE = шаблон. Шаблон, используемый в качестве прототипа для создания новой базы данных. Все объекты, присутствующие в шаблоне, воспроизводятся в созданной базе. Если шаблон не задан, PostgreSQL создает новую базу данных на основе шаблона templatel. Если вы хотите создать пустую базу данных, не содержащую объектов из tempi atel, укажите шаблон tempi ateO.
  • ENCODING = кодировка. Значение параметра определяется либо в виде строковой константы, описывающей тип кодировки (SQL_ASCII, LATIN1 и т. д.), либо в виде эквивалентной числовой константы PostgreSQL. Допустимые типы кодировок PostgreSQL и их числовые значения перечислены в приложении А. Если ключевое слово ENCODING не задано, PostgreSQL создает базу данных в кодировке по умолчанию. Обычно это кодировка SQL_ASCII, хотя в процессе первоначальной настройки конфигурации PostgreSQL можно выбрать другую кодировку (процедура выбора кодировки описана в главе 2).

Внимание
Значение, передаваемое с ключевым словом LOCAi ION, должно определять имя переменной среды. Оно не может напрямую описывать системный путь (например, /usr/local/pgsql/data2), если только в процессе первоначальной компиляции и установки PostgreSQL команде gmake не был передан аргумент CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS.

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

Запускаем psql под пользователем postgres:

Теперь в консоли PostgreSQL вводим следующие команды:

create database dbname with encoding='UNICODE';
create user dbuser with password 'dbpass';
grant all privileges on database dbname to dbuser;

А также не забываем дать доступ новому пользователю к серверу, для этого в файле /var/lib/pgsql/data/pg_hba.conf

:

# For dbuser:
host    dbname dbuser   127.0.0.1/32           md5
local   dbname dbuser                          md5

Где:

  • dbname – имя базы данных
  • dbuser – имя пользователя
  • dbpass – пароль пользователя dbuser

И говорим серверу PostgreSQL перечитать измененный конфиг pg_hba.conf:

root@Linux# service postgresql reload

Скрипт для удобства:

#!/bin/sh

PGPORT=

echo 'Input dbname: '
read dbname

dbuser=${dbuser:-$dbname}
echo "Input dbuser: [$dbuser]"  # Generate by dbname.

randpass=$(tr -cd A-Za-z < /dev/urandom | head -c8)
echo "Input dbpass [$randpass]:"  # Generate random.
read dbpass
dbpass=${dbpass:-$randpass}

# Facepalm centos... Need fill path to binary psql: /usr/pgsql-9.3/bin/psql
su - postgres -c "/usr/pgsql-9.3/bin/psql --dbname=$DB --port=$PGPORT <<_EOF
\x
CREATE DATABASE $dbname WITH ENCODING='UNICODE';
CREATE USER $dbuser WITH PASSWORD '$dbpass';
GRANT ALL PRIVILEGES ON DATABASE $dbname TO $dbuser;
_EOF
"
# TODO: check error code here.

# Do backup modified cfg files.
cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf_`date +%s`

PGPORT=${PGPORT:-5432}
[ -n "$dbname" -a -n "$dbuser" ] && cat <<-_EOF >>/var/lib/pgsql/data/pg_hba.conf

 # For $dbuser, generated at `date`:
 host    $dbname $dbuser   127.0.0.1/32           md5
 local   $dbname $dbuser                          md5
_EOF

# Do reload.
service postgresql-9.3 reload

Ссылки по теме:

  1. PostgreSQL. Как создать базу данных и пользователя для нее.

PostgreSQL / Postgres Create Database: как создать пример

  • Home

  • Testing

      • Back
      • Agile Testing
      • BugZilla
      • Cucumber
      • 000 J2000
      • 000 J2000 Testing
      • Назад
      • JUnit
      • LoadRunner
      • Ручное тестирование
      • Мобильное тестирование
      • Mantis
      • Почтальон
      • QTP
      • Назад
      • Центр качества SAP
      • SoapUI
      • Управление тестированием
      • TestLink

  • SAP

      • Назад
      • ABAP
      • APO
      • Начинающий
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • MMO
      • Crystal Reports
      • Заработная плата
      • Назад
      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Back Tutorials
      • 9007

          • Apache
          • AngularJS
          • ASP.Net
          • C
          • C #
          • C ++
          • CodeIgniter
          • СУБД
          • JavaScript
          • Назад
          • Java
          • JSP
          • Kotlin
          • Linux
          • Linux
          • Kotlin
          • Linux
            • js
          • Perl
          • Назад
          • PHP
          • PL / SQL
          • PostgreSQL
          • Python
          • ReactJS
          • Ruby & Rails
          • Scala
          • SQL
            • 000
          • SQL
            • 000
          • SQL
            • 0000003 SQL000
          • UML
          • VB.Net
          • VBScript
          • Веб-службы
          • WPF

      • Обязательно учите!

          • Назад
          • Бухгалтерский учет
          • Алгоритмы
          • Android
          • Блокчейн
          • Business Analyst
          • Создание веб-сайта
          • Облачные вычисления
          • COBOL
          • Встроенный
          • Compiler Design
              9003

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

          PostgreSQL — одна из самых популярных систем реляционных баз данных с открытым исходным кодом.За более чем 30-летний опыт разработки PostgreSQL зарекомендовала себя как высоконадежная и устойчивая база данных, способная обрабатывать большое количество сложных рабочих нагрузок с данными. PostgreSQL считается основной базой данных с открытым исходным кодом при миграции с коммерческих баз данных, таких как Oracle. Amazon Web Services (AWS) предоставляет два варианта управляемого PostgreSQL: Amazon Relational Database Service (Amazon RDS) для PostgreSQL и Amazon Aurora PostgreSQL. В этом посте я расскажу о некоторых лучших практиках управления пользователями и ролями в PostgreSQL.

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

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

          Рекомендуемый подход к настройке детального контроля доступа в PostgreSQL следующий:

          • Используйте главного пользователя для создания ролей для каждого приложения или варианта использования, например только для чтения и для чтения и записи .
          • Добавьте разрешения, чтобы разрешить этим ролям доступ к различным объектам базы данных. Например, роль только для чтения может выполнять только запросы SELECT .
          • Предоставьте ролям минимально возможные разрешения, необходимые для работы.
          • Создавайте новых пользователей для каждого приложения или отдельных функций, например app_user и reporting_user .
          • Назначьте этим пользователям соответствующие роли, чтобы быстро предоставить им те же разрешения, что и роли.Например, предоставьте роль чтения и записи пользователю app_user и предоставьте роль только для чтения пользователю reporting_user .
          • В любой момент вы можете удалить роль пользователя, чтобы отозвать разрешения.

          Эти рекомендации обобщены на следующей диаграмме:

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

          Пользователи, группы и роли

          Пользователи, группы и роли — это то же самое в PostgreSQL, с той лишь разницей, что пользователи имеют разрешение на вход по умолчанию. Операторы CREATE USER и CREATE GROUP фактически являются псевдонимами для оператора CREATE ROLE .

          В других системах управления реляционными базами данных (СУБД), таких как Oracle, пользователи и роли — это две разные сущности.В Oracle роль не может использоваться для входа в базу данных. Роли используются только для групповых грантов и других ролей. Затем эту роль можно назначить одному или нескольким пользователям, чтобы предоставить им все разрешения. Для получения дополнительных сведений о переносе пользователей, ролей и грантов из Oracle в PostgreSQL см. Сообщение в блоге AWS Использование SQL для сопоставления пользователей, ролей и грантов из Oracle в PostgreSQL.

          Чтобы создать пользователя PostgreSQL, используйте следующий оператор SQL:

            СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ myuser С ПАРОЛЕМ 'secret_passwd';

          Вы также можете создать пользователя с помощью следующего оператора SQL:

            СОЗДАТЬ РОЛЬ myuser С ПАРОЛЕМ ДЛЯ ВХОДА 'secret_passwd';

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

          Публичная схема и публичная роль

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

          PostgreSQL использует концепцию пути поиска. Путь поиска — это список имен схем, который PostgreSQL проверяет, если вы не используете полное имя объекта базы данных. Например, когда вы выбираете из таблицы с именем «mytable», PostgreSQL ищет эту таблицу в схемах, перечисленных в пути поиска. Он выбирает первое найденное совпадение.По умолчанию путь поиска содержит следующие схемы:

            postgres = # show search_path;
   search_path
-----------------
 "$ user", общедоступный
(1 ряд)

          Имя « $ user » преобразуется в имя текущего вошедшего в систему пользователя. По умолчанию схемы с тем же именем, что и имя пользователя, не существует. Таким образом, общедоступная схема становится схемой по умолчанию всякий раз, когда используется неквалифицированное имя объекта.Из-за этого, когда пользователь пытается создать новую таблицу без указания имени схемы, таблица создается в общедоступной схеме . Как упоминалось ранее, по умолчанию все пользователи имеют доступ для создания объектов в общедоступной схеме , и поэтому таблица создается успешно.

          Это становится проблемой, если вы пытаетесь создать пользователя только для чтения. Даже если вы ограничите все привилегии, разрешения, унаследованные через публичную роль , позволяют пользователю создавать объекты в публичной схеме .

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

            ОТМЕНА СОЗДАНИЯ НА СХЕМЕ public ОТ PUBLIC;

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

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

            ОТЗЫВАТЬ ВСЕ НА БАЗЕ ДАННЫХ mydatabase ОТ PUBLIC;

          Это гарантирует, что пользователи не могут подключаться к базе данных по умолчанию, если это разрешение не предоставлено явно.

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

          Создание ролей базы данных

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

          Роль только для чтения

          Первым шагом является создание новой роли с именем только для чтения , используя следующий оператор SQL:

          Это базовая роль без разрешений и без пароля.Его нельзя использовать для входа в базу данных.

          Предоставьте этой роли разрешение на подключение к целевой базе данных с именем «mydatabase»:

            GRANT CONNECT ON DATABASE mydatabase TO только для чтения;

          Следующим шагом является предоставление доступа к вашей схеме для использования этой роли. Предположим, схема называется myschema :

          .
            ПРЕДОСТАВЛЕНИЕ ИСПОЛЬЗОВАНИЯ НА СХЕМЕ myschema ТОЛЬКО для чтения;

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

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

            GRANT SELECT ON TABLE mytable1, mytable2 TO только для чтения;

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

            GRANT SELECT ДЛЯ ВСЕХ ТАБЛИЦ В СХЕМЕ myschema TO только для чтения;

          Предыдущий оператор SQL предоставляет SELECT доступ к роли только для чтения для всех существующих таблиц и представлений в схеме myschema .Обратите внимание, что любые новые таблицы, которые будут добавлены в будущем, не будут доступны пользователю только для чтения . Чтобы гарантировать, что новые таблицы и представления также доступны, запустите следующий оператор для автоматического предоставления разрешений:

            ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;

          Роль чтения / записи

          Процесс добавления роли чтения / записи очень похож на процесс роли только для чтения, рассмотренный ранее.Первый шаг — создание роли:

          Предоставьте этой роли разрешение на подключение к целевой базе данных:

            GRANT CONNECT ON DATABASE mydatabase TO readwrite;

          Предоставить привилегию использования схемы:

            ПРЕДОСТАВЛЕНИЕ ИСПОЛЬЗОВАНИЯ НА СХЕМЕ myschema ДЛЯ readwrite;

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

            ПРЕДОСТАВЛЯЙТЕ ИСПОЛЬЗОВАНИЕ, СОЗДАЙТЕ НА СХЕМЕ myschema ДЛЯ чтения и записи;

          Следующий шаг — предоставить доступ к таблицам.Как упоминалось в предыдущем разделе, грант может относиться к отдельным таблицам или всем таблицам в схеме. Для отдельных таблиц используйте следующий SQL:

            GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;

          Для всех таблиц и представлений в схеме используйте следующий SQL:

            GRANT SELECT, INSERT, UPDATE, DELETE ДЛЯ ВСЕХ ТАБЛИЦ В СХЕМЕ myschema TO readwrite;

          Для автоматического предоставления разрешений на таблицы и представления, добавленные в будущем:

            ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

          Для ролей чтения / записи обычно также требуется использовать последовательности.Вы можете предоставить выборочный доступ следующим образом:

            ПРЕДОСТАВИТЬ ИСПОЛЬЗОВАНИЕ ПОСЛЕДОВАТЕЛЬНОСТИ myseq1, myseq2 ДЛЯ чтения и записи;

          Вы также можете предоставить разрешение для всех последовательностей, используя следующий оператор SQL:

            ПРЕДОСТАВЛЕНИЕ ИСПОЛЬЗОВАНИЯ ДЛЯ ВСЕХ ПОСЛЕДОВАТЕЛЬНОСТЕЙ В СХЕМЕ myschema ДЛЯ readwrite;

          Для автоматического предоставления разрешений последовательностям, добавленным в будущем:

            ИЗМЕНИТЬ ПРИВИЛЕГИИ ПО УМОЛЧАНИЮ В СХЕМЕ myschema ПРЕДОСТАВЛЯТЬ ИСПОЛЬЗОВАНИЕ ПОСЛЕДОВАТЕЛЬНОСТЕЙ ДЛЯ РЕДАКТИРОВАНИЯ;

          Вы можете предоставить больше или меньше разрешений в зависимости от требований.Документация по команде PostgreSQL GRANT предоставляет более подробную информацию об объектах, для которых могут быть предоставлены разрешения, и о необходимых операторах SQL.

          Создание пользователей базы данных

          При наличии ролей процесс создания пользователей упрощается. Просто создайте пользователя и предоставьте ему одну из существующих ролей. Вот операторы SQL для этого процесса:

            СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ myuser1 С ПАРОЛЕМ 'secret_passwd';
ПРЕДОСТАВИТЬ myuser1 только для чтения;

          Это предоставляет myuser1 те же разрешения, что и роль только для чтения .

          Точно так же вы можете предоставить пользователю доступ для чтения и записи, назначив роль для чтения и записи . Документация PostgreSQL CREATE USER содержит более подробную информацию о параметрах, которые вы можете установить при создании пользователя. Например, вы можете указать срок действия для пользователя или разрешить пользователю создавать базы данных.

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

          После создания пользователя вы должны предоставить эти учетные данные приложению, чтобы оно могло получить доступ к базе данных.Важно убедиться, что эти учетные данные не жестко закодированы в исходном коде и не помещены в некоторые общие файлы конфигурации в виде открытого текста. AWS предлагает решение этой проблемы с помощью AWS Secrets Manager. Используя Secrets Manager, вы можете сохранить учетные данные, а затем использовать AWS Identity and Access Management (IAM), чтобы только определенные пользователи и роли IAM могли читать учетные данные. Инструкции по выполнению этого действия см. В разделе Создание секретов и управление ими с помощью AWS Secrets Manager в Руководстве пользователя AWS Secrets Manager .

          Помимо хранения учетных данных, очень полезной функцией, которую предоставляет Secrets Manager, является ротация пароля пользователя базы данных. Вы можете использовать эту функцию, чтобы настроить политику для автоматической смены пароля с определенной периодичностью. Дополнительные сведения о том, как настроить это, чтобы не было простоев для приложений, см. В разделе Ротация секретов AWS Secrets Manager.

          Amazon RDS и Amazon Aurora PostgreSQL предоставляют новую функцию ограниченного управления паролями, которая поддерживается в PostgreSQL 10.6 и выше. Используя новый параметр и особую роль, вы можете ограничить изменение пароля пользователя базы данных членами этой особой роли. Таким образом вы обеспечиваете больший контроль над управлением паролями на стороне клиента (например, требования к истечению срока действия и требования к сложности).

          Аутентификация базы данных IAM

          Amazon RDS и Aurora PostgreSQL интегрированы с IAM, поэтому вы можете аутентифицироваться в своем инстансе БД с помощью аутентификации базы данных IAM. Эта функция доступна для Amazon RDS PostgreSQL версии 9.5.14, 9.6.9 или новее, а также версия 10.4 или новее. Для Aurora PostgreSQL эта функция доступна для версий 9.6.9 или новее и 10.4 или новее. Ключевым преимуществом этой функции является то, что вы можете использовать IAM для централизованного управления доступом к ресурсам базы данных вместо управления доступом индивидуально для каждого экземпляра БД. Используя этот метод, администратор может легко разрешить или отозвать доступ к базе данных через политику IAM.

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

          Для получения дополнительной информации об этой функции см. Аутентификация базы данных IAM для MySQL и PostgreSQL. В документации также содержатся подробные инструкции по настройке аутентификации IAM DB.

          Этот метод касается только части аутентификации. Предоставление разрешений для различных объектов базы данных выполняется в самой базе данных, как описано в этом сообщении. Например, чтобы предоставить этому пользователю доступ для чтения и записи , запустите следующий оператор SQL:

            GRANT readwrite TO db_user;

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

          Используя метод, описанный ранее, очень легко отозвать привилегии у пользователя.Например, вы можете удалить разрешение на чтение и запись из myuser1, используя следующий оператор SQL:

            REVOKE readwrite FROM myuser1;

          Аналогичным образом вы можете предоставить новую роль следующим образом:

            GRANT только для чтения для myuser1;

          Мониторинг использования

          Вы можете отслеживать активность пользователей, задав параметры ведения журнала PostgreSQL, доступные в группах параметров RDS.Например, вы можете установить параметры log_connections и log_disconnections , чтобы фиксировать все новые соединения и разъединения. После установки этих параметров в группе параметров вы увидите следующие сообщения в файлах журнала:

            2018-11-09 21:08:39 UTC: XX-XX-XX-XX.amazon.com (27585): myuser @ mydb: [18014]: LOG: соединение разрешено: user = myuser database = mydb SSL включен (протокол = TLSv1.2, шифр = ECDHE-RSA-AES256-GCM-SHA384, сжатие = выключено)

2018-11-09 21:09:19 UTC: XX-XX-XX-XX.amazon.com (27585): myuser @ mydb: [18014]: LOG: отключение: время сеанса: 0: 00: 39.649 user = myuser database = mydb host = XX-XX-XX-XX.amazon.com port = 27585

          Если вам требуется более подробная информация о пользовательском аудите на уровне сеанса или объекта, вы можете использовать расширение pgAudit. Шаги по настройке pgAudit с Amazon RDS и Aurora PostgreSQL доступны в разделе Работа с расширением pgaudit в Руководстве пользователя Amazon RDS .

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

          Проверка предоставленных ролей

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

            ВЫБРАТЬ
      r.rolname,
      ARRAY (ВЫБЕРИТЕ b.rolname
            ОТ pg_catalog.pg_auth_members m
            ПРИСОЕДИНЯЙТЕСЬ к pg_catalog.pg_roles b ON (m.roleid = b.oid)
            ГДЕ m.member = r.oid) как memberof
ИЗ pg_catalog.pg_roles r
ГДЕ r.rolname НЕ ВХОДИТ ('pg_signal_backend', 'rds_iam',
                        'rds_replication', 'rds_superuser',
                        'rdsadmin', 'rdsrepladmin')
ЗАКАЗАТЬ ПО 1;

          Вот пример вывода из тестового экземпляра RDS:

            имя пользователя | член
---------------- + -----------------
 app_user | {читай пиши}
 postgres | {rds_superuser}
 только чтение | {}
 readwrite | {}
 Reporting_user | {только для чтения}

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

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

          Резюме

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

          Чтобы обобщить концепции, я предоставил следующие справочные операторы SQL для реализации пользователей и ролей на примере сценария:

          1. База данных PostgreSQL была создана с первичной базой данных с именем mydatabase .
          2. Создана новая схема с именем myschema с несколькими таблицами.
          3. Необходимо создать двух пользователей отчетов с разрешениями на чтение всех таблиц в схеме myschema .
          4. Необходимо создать двух пользователей приложения с разрешениями на чтение и запись во все таблицы в схеме myschema , а также на создание новых таблиц.
          5. Пользователи должны автоматически получать разрешения на любые новые таблицы, которые будут добавлены в будущем.

          Для реализации этого сценария необходимо подключиться к базе данных mydatabase с помощью главного пользователя, а затем запустить следующие операторы SQL, используя любой из клиентов PostgreSQL, например psql или pgAdmin:

            - отозвать привилегии для публичной роли
ОТМЕНА СОЗДАНИЯ НА СХЕМЕ общедоступной ОТ ОБЩЕСТВЕННОСТИ;
ОТЗЫВАТЬ ВСЕ НА БАЗЕ ДАННЫХ mydatabase ОТ PUBLIC;

- Роль только для чтения
СОЗДАТЬ РОЛЬ только для чтения;
ПРЕДОСТАВИТЬ СОЕДИНЕНИЕ БАЗЫ ДАННЫХ mydatabase ТОЛЬКО для чтения;
ПРЕДОСТАВЛЯЙТЕ ИСПОЛЬЗОВАНИЕ НА СХЕМЕ myschema ТОЛЬКО для чтения;
GRANT SELECT ДЛЯ ВСЕХ ТАБЛИЦ В СХЕМЕ myschema ТОЛЬКО для чтения;
ИЗМЕНИТЬ ПРИВИЛЕГИИ ПО УМОЛЧАНИЮ В СХЕМЕ myschema GRANT SELECT ON TABLES TO readonly;

- Роль чтения / записи
CREATE ROLE readwrite;
ПРЕДОСТАВИТЬ СОЕДИНЕНИЕ НА БАЗЕ ДАННЫХ mydatabase ДЛЯ чтения и записи;
ПРЕДОСТАВЛЯЙТЕ ИСПОЛЬЗОВАНИЕ, СОЗДАВАЙТЕ НА СХЕМЕ myschema ДЛЯ чтения и записи;
GRANT SELECT, INSERT, UPDATE, DELETE ДЛЯ ВСЕХ ТАБЛИЦ В СХЕМЕ myschema TO readwrite;
ИЗМЕНИТЬ ПРИВИЛЕГИИ ПО УМОЛЧАНИЮ В СХЕМЕ myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
ПРЕДОСТАВЛЯЙТЕ ИСПОЛЬЗОВАНИЕ ВСЕХ ПОСЛЕДОВАТЕЛЬНОСТЕЙ В СХЕМЕ myschema для чтения и записи;
ИЗМЕНИТЬ ПРИВИЛЕГИИ ПО УМОЛЧАНИЮ В СХЕМЕ myschema ПРЕДОСТАВИТЬ ИСПОЛЬЗОВАНИЕ ПОСЛЕДОВАТЕЛЬНОСТЕЙ ДЛЯ readwrite;

- Создание пользователей
СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ Reporting_user1 С ПАРОЛЕМ 'some_secret_passwd';
СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ Reporting_user2 С ПАРОЛЕМ 'some_secret_passwd';
СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ app_user1 С ПАРОЛЕМ 'some_secret_passwd';
СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ app_user2 С ПАРОЛЕМ 'some_secret_passwd';

- Предоставлять привилегии пользователям
ПРЕДОСТАВИТЬ ТОЛЬКО ДЛЯ ЧТЕНИЯ Reporting_user1;
ПРЕДОСТАВЛЯТЬ ТОЛЬКО ДЛЯ ЧТЕНИЯ Report_user2
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

          Дополнительную информацию о пользователях и ролях PostgreSQL можно найти на веб-сайте документации PostgreSQL.

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

          Об авторе

          Ясер Раджа — старший консультант отдела профессиональных услуг в Amazon Web Services . Он работает с клиентами над созданием масштабируемых, высокодоступных и безопасных решений в облаке AWS. Его основная область — однородная и разнородная миграция локальных баз данных на AWS RDS и Aurora PostgreSQL.

          PostgreSQL — Gentoo Wiki

          PostgreSQL — это бесплатная система управления реляционными базами данных (СУБД) с открытым исходным кодом. Он поддерживает такие вещи, как транзакции, схемы и внешние ключи, и часто рекламируется как более строгое соблюдение стандартов SQL и более безопасный по умолчанию, чем любая другая база данных, коммерческая или иная.

          Посетите страницу «О программе» на postgresql.org для получения дополнительной информации.

          Установка, обновление и миграция

          См. Gentoo PostgreSQL Quick Start Guide для получения подробной информации об установке, обновлении или миграции PostgreSQL.

          Добавить пользователя

          пользователь $ psql -U postgres -d postgres 

           psql (9.1.1)
Введите "help" для получения справки.

postgres = # СОЗДАТЬ РОЛЬ имя пользователя С ЛОГИН;
СОЗДАТЬ РОЛЬ
postgres = # \ пароль имя пользователя
Введите новый пароль:
Введите его еще раз:
postgres = # СОЗДАТЬ БАЗУ ДАННЫХ testdb С именем пользователя OWNER; - имя пользователя имеет все права на testdb
СОЗДАТЬ БАЗУ ДАННЫХ
postgres = # РАЗРЕШИТЬ СОЕДИНЕНИЕ НА БАЗЕ ДАННЫХ otherdb TO имя пользователя; - имя пользователя теперь может подключаться к otherdb
ГРАНТ
postgres = # \ c otherdb
Теперь вы подключены к базе данных "otherdb" как пользователь "postgres".otherdb = # GRANT SELECT ON test TO имя пользователя; - имя пользователя теперь может запрашивать (операторы SELECT) тестовую таблицу на otherdb.
ГРАНТ

          См. Главу 20. Роли баз данных в официальной документации PostgreSQL для получения дополнительной информации об управлении ролями.

          Подробнее о GRANT и REVOKE см. В документации PostgreSQL,

          Изменение кодировки по умолчанию для новых баз данных

          При создании новой базы данных (например, с помощью createdb mydb ) PostgreSQL фактически копирует базу данных шаблона.Существует два предопределенных шаблона: template0 — это обычный шаблон, а template1 — это шаблон на сайте, который может изменять администратор, и используется по умолчанию. Чтобы изменить кодировку по умолчанию для новых баз данных, одним из вариантов является изменение локального шаблона1. Для этого войдите в оболочку PostgreSQL ( psql ) и выполните следующее:

          1. Сначала нам нужно сбросить template1. Поскольку шаблоны не могут быть отброшены, нам сначала нужно изменить его на обычную базу данных:

           ОБНОВЛЕНИЕ pg_database SET datistemplate = FALSE WHERE datname = 'template1';

          2.После этого его можно уронить:

           DROP DATABASE template1;

          3. Следующим шагом будет создание новой базы данных из template0 с новой кодировкой по умолчанию. (Попался: в PostgreSQL Unicode является синонимом UTF-8.)

           СОЗДАТЬ БАЗУ ДАННЫХ template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

          4. Теперь нам нужно заменить template1 на шаблон:

           ОБНОВЛЕНИЕ pg_database SET datistemplate = TRUE WHERE datname = 'template1';

          5.(НЕОБЯЗАТЕЛЬНО) Если вы не хотите, чтобы кто-либо подключался к этому шаблону, установите для datallowconn значение FALSE:

           ОБНОВЛЕНИЕ pg_database SET datallowconn = FALSE WHERE datname = 'template1';

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

          пользователь $ createdb -U postgres testdb

          Если вы снова войдете в psql и проверите базы данных, вы должны увидеть правильную кодировку вашей новой базы данных:

          пользователь $ psql -U postgres -d postgres 

           psql (9.1.1)
Введите "help" для получения справки.

postgres = # \ l
                              Список баз данных
  Имя | Владелец | Кодирование | Сортировка | Ctype | Права доступа
---------- + ---------- + ----------- + ----------- + ---- --- + ----------------------
testdb | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | = c / postgres
                                                     : postgres = CTc / postgres
template1 | postgres | UTF8 | C | C |

          PostgreSQL — установите PostgreSQL, а затем создайте базу данных и учетную запись пользователя — CodingBee

          Введение

          Это руководство дает вам ускоренный курс по:

          Объявление

          Я выпустил свой новый курс на Udemy, Kubernetes By Example .Зарегистрируйтесь сейчас, чтобы получить бесплатный пожизненный доступ!
          1. Установка программного обеспечения PostgreSQL на ваш сервер
          2. Создайте свою самую первую базу данных PostgreSQL — в нашем случае мы собираемся создать базу данных с именем «reviewdb»
          3. Создайте новую учетную запись пользователя БД и предоставьте ей полный доступ к новой БД
          4. Подключитесь к новой базе данных, используя новую учетную запись пользователя базы данных
          5. Удаленное подключение к новой базе данных с использованием новой учетной записи пользователя базы данных
          6. Повышение безопасности удаленных подключений

          Установка программного обеспечения PostgreSQL на ваш сервер

          Сначала загрузите rpm, используя curl / wget.Сначала решите, какой rpm-пакет postgresql вам нужен. например:

          curl http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-oraclelinux93-9.3-1.noarch.rpm -o postgresql.rpm

          затем, находясь в этом каталоге, установите его с помощью yum:

          yum установить postgresql.rpm

          В качестве альтернативы мы можем объединить приведенные выше команды curl + yum в одну команду и выполнить следующую команду вместо двух указанных выше команд:

           yum install http: //yum.postgresql.org / 9.3 / redhat / rhel-6-x86_64 / pgdg-oraclelinux93-9.3-1.noarch.rpm

          На самом деле он еще не установил postgres, он установил только репозиторий postgres yum, который в моем случае является репозиторием yum для postgres 9.3, это версия, которая меня интересует:

          [root @ puppetmaster tmp] # ls -l /etc/yum.repos.d/ | grep "pgdg"
-rw-r - r-- 1 root root 442 21 апреля 2014 pgdg-93-oraclelinux.repo

          В моем случае я хочу установить postresql v9.3, поэтому я и буду искать это.

          [root @ puppetmaster tmp] # ням поиск "postgresql93"
Загруженные плагины: refresh-packagekit
================================================== ======================================== N / S Соответствует: postgresql93 ==== ================================================== ====================================
postgresql93-debuginfo.x86_64: Отладочная информация для пакета postgresql93
postgresql93-jdbc-debuginfo.x86_64: Отладочная информация для пакета postgresql93-jdbc
postgresql93-odbc-debuginfo.x86_64: Отладочная информация для пакета postgresql93-odbc
postgresql93-python-debuginfo.x86_64: отладочная информация для пакета postgresql93-python
postgresql93.x86_64: клиентские программы и библиотеки PostgreSQL
  postgresql93-contrib.x86_64: добавленный исходный код и двоичные файлы, распространяемые с PostgreSQL 
postgresql93-devel.x86_64: файлы заголовков и библиотеки разработки PostgreSQL
postgresql93-docs.x86_64: Дополнительная документация для PostgreSQL
postgresql93-jdbc.x86_64: драйвер JDBC для PostgreSQL
postgresql93-libs.x86_64: разделяемые библиотеки, необходимые для любых клиентов PostgreSQL
postgresql93-odbc.x86_64: драйвер ODBC PostgreSQL
postgresql93-plperl.x86_64: процедурный язык Perl для PostgreSQL
postgresql93-plpython.x86_64: процедурный язык Python для PostgreSQL
postgresql93-pltcl.x86_64: процедурный язык Tcl для PostgreSQL
postgresql93-python.x86_64: Модуль разработки для кода Python для доступа к базе данных PostgreSQL
  postgresql93-server.x86_64: программы, необходимые для создания и запуска сервера PostgreSQL 
postgresql93-test.x86_64: набор тестов, распространяемый с PostgreSQL

  Только имя и краткое совпадение, для всего используйте "искать все".[root @ puppetmaster tmp] #

          Два об / мин, которые мы выделили жирным шрифтом выше, — это те, которые нам нужны. Также есть еще один из перечисленных выше, который называется «postgresql93.x86_64», он устанавливает только клиентскую сторону, что полезно для проверки того, можете ли вы подключиться к своей базе данных postgresql с другой машины. Однако два вышеупомянутых также установят утилиту на стороне клиента.

          Следовательно, мы идем дальше и устанавливаем их:

           [root @ puppetagent01 ~] # yum install postgresql93-server postgresql93-contrib
pgdg93 | 3.7 кБ 00:00
pgdg93 / primary_db | 147 кБ 00:01
Настройка процесса установки
Разрешение зависимостей
-> Выполняется проверка транзакции
---> Пакет postgresql93-contrib.x86_64 0: 9.3.6-1PGDG.rhel6 будет установлен
-> Зависимость обработки: postgresql93 = 9.3.6 для пакета: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
-> Зависимость обработки: libpq.so.5 () (64 бит) для пакета: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
-> Зависимость обработки: libossp-uuid.so.16 () (64 бит) для пакета: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64
---> Будет установлен пакет postgresql93-server.x86_64 0: 9.3.6-1PGDG.rhel6
-> Выполняется проверка транзакции
---> Пакет postgresql93.x86_64 0: 9.3.6-1PGDG.rhel6 будет установлен
---> Пакет postgresql93-libs.x86_64 0: 9.3.6-1PGDG.rhel6 будет установлен
---> Пакет uuid.x86_64 0: 1.6.1-10.el6 будет установлен
-> Завершенное разрешение зависимостей

Решенные зависимости

================================================== ================================================== ==
 Размер репозитория версии пакета Arch
================================================== ================================================== ==
Установка:
 postgresql93-contrib x86_64 9.3.6-1PGDG.rhel6 pgdg93 486 кбайт
 postgresql93-сервер x86_64 9.3.6-1PGDG.rhel6 pgdg93 4.1 M
Установка для зависимостей:
 postgresql93 x86_64 9.3.6-1PGDG.rhel6 pgdg93 1.0 M
 postgresql93-libs x86_64 9.3.6-1PGDG.rhel6 pgdg93 191 кБ
 uuid x86_64 1.6.1-10.el6 public_ol6_latest 53 КБ

Сводка транзакции
================================================== ================================================== ==
Установить 5 пакетов

Общий размер загрузки: 5.8 млн
Установленный размер: 23 M
Это нормально [да / нет]: да
Скачивание пакетов:
(1/5): postgresql93-9.3.6-1PGDG.rhel6.x86_64.rpm | 1.0 МБ 00:03
(2/5): postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64.rpm | 486 кБ 00:00
(3/5): postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64.rpm | 191 кБ 00:00
(4/5): postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm | 4,1 МБ 00:05
(5/5): uuid-1.6.1-10.el6.x86_64.rpm | 53 кБ 00:00
-------------------------------------------------- -------------------------------------------------- -
Всего 348 кБ / с | 5.8 МБ 00:17
Запуск rpm_check_debug
Запуск теста транзакции
Проверка транзакции прошла успешно
Выполняемая транзакция
  Установка: postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64 1/5
  Установка: postgresql93-9.3.6-1PGDG.rhel6.x86_64 2/5
  Устанавливаем: uuid-1.6.1-10.el6.x86_64 3/5
  Установка: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64 4/5
  Установка: postgresql93-server-9.3.6-1PGDG.rhel6.x86_64 5/5
  Проверяем: uuid-1.6.1-10.el6.x86_64 1/5
  Проверка: postgresql93-9.3.6-1PGDG.rhel6.x86_64 2/5
  Проверка: postgresql93-server-9.3.6-1PGDG.rhel6.x86_64 3/5
  Проверка: postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64 4/5
  Проверяем: postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64 5/5

Установлены:
  postgresql93-contrib.x86_64 0: 9.3.6-1PGDG.rhel6 postgresql93-server.x86_64 0: 9.3.6-1PGDG.rhel6

Установленная зависимость:
  postgresql93.x86_64 0: 9.3.6-1PGDG.rhel6 postgresql93-libs.x86_64 0: 9.3.6-1PGDG.rhel6
  uuid.x86_64 0: 1.6.1-10.el6

Готово!

          Вот быстрая проверка, что клиент командной строки postgresql, по крайней мере, был успешно установлен:

          [root @ puppetagent01 ~] # psql --version
psql (PostgreSQL) 9.3,6

          Сейчас делаем:

          [root @ puppetmaster tmp] # статус службы postgresql-9.3
postgresql-9.3 остановлен
[root @ puppetmaster tmp] # service postgresql-9.3 initdb # запускает базу данных
Инициализация базы данных: [OK]
[root @ puppetmaster tmp] # chkconfig postgresql-9.3 on # это запускает эту службу во время загрузки
[root @ puppetmaster tmp] #

          Далее вы запускаете службу postgres:

          [root @ puppetmaster tmp] # сервис postgresql-9.3 старт
Запуск службы postgresql-9.3: [OK]

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

          После этого нужно su пользователю postgres:

          Учетная запись «postgres» была автоматически создана во время установки Postgres. Учетная запись «postgres» — это не только учетная запись Linux, но и учетная запись пользователя db, которая также автоматически создается во время установки postgres. Фактически, учетная запись пользователя «postgres» является эквивалентом Postgres «root» в Linux.

          [корень @ puppetmaster tmp] # sudo -u postgres -i
-bash-4.1 $ psql # затем введите командную строку postgresql.
psql (9.3.5)
Введите "help" для получения справки.

postgres = #

          Обратите внимание, что существует связь между учетной записью postgres linux и учетной записью postgres db. То есть, если вы запустите «psql» от имени пользователя postgres linux, то postgres автоматически предполагает, что вы хотите подключиться, используя соответствующую учетную запись пользователя db.

          Обратите внимание на то, что мы использовали утилиту командной строки psql.

          Создайте свою самую первую базу данных PostgreSQL

          Прежде чем мы создадим нашу первую базу данных, давайте сначала рассмотрим список всех баз данных PostgreSQL, которые поставляются с Postgres из коробки, с помощью команды psql «\ l»:

          postgres = # \ l
                                  Список баз данных
   Имя | Владелец | Кодирование | Сортировка | Ctype | Права доступа
----------- + ---------- + ---------- + ------------- + - ----------- + -----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c / postgres +
           | | | | | postgres = CTc / postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c / postgres +
           | | | | | postgres = CTc / postgres
(3 ряда)

          Теперь создаем собственную БД PostgreSQL. Для этого вам нужно сделать это из приглашения bash (пока вы все еще вошли в систему как пользователь postgres), а не из приглашения psql:

          -баш-4.1 $ createdb reviewdb

          В нашем случае мы создали базу данных с именем «reviewdb»

          Обратите внимание, что при желании вы также можете удалить базу данных postgres.

          Подтвердим, что он создан:

          -bash-4.1 $ psql
psql (9.3.5)
Введите "help" для получения справки.

postgres = # \ l
                                  Список баз данных
   Имя | Владелец | Кодирование | Сортировка | Ctype | Права доступа
----------- + ---------- + ---------- + ------------- + - ----------- + -----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 reviewdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c / postgres +
           | | | | | postgres = CTc / postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c / postgres +
           | | | | | postgres = CTc / postgres
(4 ряда)

postgres = #

          Создание учетной записи пользователя PostgreSQL

          Прежде чем мы создадим новую учетную запись пользователя db, мы должны сначала проверить, какая учетная запись пользователя db существует в настоящее время.Проверить это можно двумя способами: запустить запрос sql или использовать параметр «\ du»:

          [root @ puppetagent01 ~] # su - postgres
-bash-4.1 $ psql
psql (9.3.6)
Введите "help" для получения справки.

postgres = # ВЫБРАТЬ имя ролика ИЗ pg_roles;
 Rolname
----------
 Postgres
 геррит
(2 ряда)

postgres = # \ du
                                Список ролей
 Имя роли | Атрибуты | Член
-------------- + ----------------------------------- ------------- + -----------
 gerritdbuser | | {}
 postgres | Суперпользователь, Создание роли, Создание БД, Репликация | {}

postgres = #

          Обратите внимание: дополнительную информацию о параметре «\ du» можно найти так:

          -баш-4.1 $ psql
psql (9.3.6)
Введите "help" для получения справки.

postgres = # help
Вы используете psql, интерфейс командной строки для PostgreSQL.
Введите: \ copyright для условий распространения
       \ h для справки по командам SQL
       \? для помощи с командами psql
       \ g или закончить точкой с запятой для выполнения запроса
       \ q выйти
postgres = # \?
генеральный
  \ copyright показать условия использования и распространения PostgreSQL
  \ g [ФАЙЛ] или; выполнить запрос (и отправить результаты в файл или | конвейер)
  \ gset [PREFIX] выполнить запрос и сохранить результаты в переменных psql
  \ h [NAME] справка по синтаксису команд SQL, * для всех команд
  \ q выйти из psql
  \ watch [SEC] выполнять запрос каждые SEC секунд
..
.
  \ du [+] [ШАБЛОН] список ролей
.
.
.

          В нашем случае мы создали пользователя с именем «gerritdbuser» и установили пароль «admin123», а также предоставили права полного доступа к базе данных «reviewdb».

          Теперь давайте создадим нового пользователя (у которого в нашем случае будет имя пользователя «gerritdbuser»). Для этого нам нужно подключиться к одной из уже существующих баз данных postgres, называемой «template1», для этого:

          -bash-4.1 $ идентификатор
uid = 26 (postgres) gid = 26 (postgres) groups = 26 (postgres)
-баш-4.1 $ psql template1
psql (9.3.5)
Введите "help" для получения справки.

template1 = # СОЗДАТЬ ПОЛЬЗОВАТЕЛЬСКИЙ gerritdbuser С ПАРОЛЕМ 'admin123';
СОЗДАТЬ РОЛЬ
template1 = # ПРЕДОСТАВЛЯЙТЕ ВСЕ ПРИВИЛЕГИИ НА БАЗЕ ДАННЫХ "reviewdb" gerritdbuser;
ГРАНТ
template1 = #

          Теперь проверим, существует ли учетная запись gerritdbuser:

          Это заполнитель.

          Затем нам нужно передать полный контроль созданной нами базе данных reviewdb. Мы сделаем это в следующем разделе.

          Предоставить право полного доступа к БД новой учетной записи пользователя

          Это делается так:

          -баш-4.1 $ id
uid = 26 (postgres) gid = 26 (postgres) groups = 26 (postgres)
-bash-4.1 $ psql шаблон1
psql (9.3.5)
Введите "help" для получения справки.

template1 = # ПРЕДОСТАВЛЯЙТЕ ВСЕ ПРИВИЛЕГИИ НА БАЗЕ ДАННЫХ "reviewdb" gerritdbuser;
ГРАНТ
template1 = #

          Теперь мы можем проверить, что это было успешно, просмотрев последний столбец команды «\ l»:

          Это заполнитель.

          Подключитесь к новой базе данных с новой учетной записью пользователя базы данных

          На этом этапе вы поймете, что когда вы запускаете psql отдельно, то по умолчанию psql будет предполагать, что вы хотите подключиться к базе данных с тем же именем учетной записи db, что и имя учетной записи Linux.Это означает, что psql завершится ошибкой, если вы попытаетесь запустить psql под учетной записью linux, у которой нет эквивалентной учетной записи базы данных с совпадающим именем. Например, мы не создали учетную запись базы данных с именем «root», следовательно,

          На этом этапе вы должны иметь возможность выполнить следующую команду от имени пользователя postgress / root:

          -bash-4.1 $ psql -h 127.0.0.1 -U gerritdbuser -d reviewdb
psql: FATAL: не удалось выполнить аутентификацию идентификатора для пользователя gerritdbuser

          Чтобы исправить это, нам нужно отредактировать следующий файл:

           [данные root @ puppetmaster] # ls / var / lib / pgsql / 9.3 / данные / pg_hba.conf
/var/lib/pgsql/9.3/data/pg_hba.conf

          В этом файле вы найдете следующую строку:

          хост все все 127.0.0.1/32 идент

          Здесь вы просто заменяете «идент» на «доверие» и перезапускаете службу postgres.

          Теперь, если вы повторите вызов psql, вы получите:

          [root @ puppetagent01 ~] # psql -h 127.0.0.1 -U gerritdbuser -d reviewdb
psql (9.3.6)
Введите "help" для получения справки.

reviewdb =>

          Успех!

          Удаленное подключение к серверу db

          Теперь давайте попробуем подключиться, используя вместо этого IP-адрес устройства:

          [root @ puppetagent01 ~] # psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: не удалось подключиться к серверу: в соединении отказано
        Сервер работает на хосте «10.1.172.11» и принимает
        TCP / IP-соединения на порту 5432?
[root @ puppetagent01 ~] #

          Чтобы исправить это, вам необходимо сначала отредактировать следующий файл:

          [корень @ puppetagent01 ~] # vi /var/lib/pgsql/9.3/data/postgresql.conf

          В этом файле у нас есть настройка listen_addresses:

          .
.
# ------------------------------------------------- -----------------------------
# ПОДКЛЮЧЕНИЯ И Аутентификация
# ------------------------------------------------- -----------------------------

# - Настройки соединения -

#listen_addresses = 'localhost' # какой IP-адрес (а) слушать;
..

          раскомментируйте эту строку и установите для нее: «*», то есть:

          .
.
# ------------------------------------------------- -----------------------------
# ПОДКЛЮЧЕНИЯ И Аутентификация
# ------------------------------------------------- -----------------------------

# - Настройки соединения -

#listen_addresses = 'localhost' # какой IP-адрес (а) слушать;
listen_addresses = '*' # какой IP-адрес (а) слушать;
.
.

          Примечание: в моем случае я продублировал эту строку перед ее редактированием.

          Затем перезапустите службу postgres и попробуйте еще раз. Теперь вы должны получить следующее сообщение об ошибке:

          [root @ puppetagent01 ~] # psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: FATAL: нет записи pg_hba.conf для хоста «10.1.172.11», пользователя «gerritdbuser», базы данных «reviewdb», SSL отключен

          Теперь давайте отредактируем наш pg_hba.conf и добавим запись для нашего IP-адреса, которым в данном случае будет «10.1.172.11». Следовательно, мы добавляем следующую строку:

          хозяин все все 10.1.172.11 / 32 траст

          Примечание: это почти то же самое, что и наша предыдущая запись 127.0.0.1.

          Теперь перезапустите службу postgres и попробуйте еще раз:

          [root @ puppetagent01 ~] # psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql (9.3.6)
Введите "help" для получения справки.

reviewdb => \ q
[root @ puppetagent01 ~] #

          Успех !!!

          См. Также: http://dba.stackexchange.com/questions/14740/how-to-use-psql-with-no-password-prompt для повышения безопасности.
          ================================================= ======================

          На клиентской машине:

          
yum install http: // yum.postgres "| grep" клиент "
postgresql93.x86_64: клиентские программы и библиотеки PostgreSQL
[root @ puppetagent03 ~] #

          Итак, давайте, сделаем это сейчас:

          [root @ puppetagent03 ~] # psql --version
-bash: psql: команда не найдена
[root @ puppetagent03 ~] # yum install postgresql93
Настройка процесса установки
Разрешение зависимостей
-> Выполняется проверка транзакции
---> Пакет postgresql93.x86_64 0: 9.3.6-1PGDG.rhel6 будет установлен
-> Зависимость обработки: postgresql93-libs = 9.3.6-1PGDG.rhel6 для пакета: postgresql93-9.3.6-1PGDG.rhel6.x86_64
-> Зависимость обработки: libpq.so.5 () (64 бит) для пакета: postgresql93-9.3.6-1PGDG.rhel6.x86_64
-> Выполняется проверка транзакции
---> Пакет postgresql93-libs.x86_64 0: 9.3.6-1PGDG.rhel6 будет установлен
-> Завершенное разрешение зависимостей

Решенные зависимости

================================================== ================================================== ================================================== ================================================== =======
 Размер репозитория версии пакета Arch
================================================== ================================================== ================================================== ================================================== =======
Установка:
 postgresql93 x86_64 9.3.6-1PGDG.rhel6 pgdg93 1.0 M
Установка для зависимостей:
 postgresql93-libs x86_64 9.3.6-1PGDG.rhel6 pgdg93 191 кБ

Сводка транзакции
================================================== ================================================== ================================================== ================================================== =======
Установить 2 пакета (ов)

Общий размер загрузки: 1.2 млн
Установленный размер: 5,8 м
Это нормально [да / нет]: да
Скачивание пакетов:
(1/2): postgresql93-9.3.6-1PGDG.rhel6.x86_64.rpm | 1.0 МБ 00:01
(2/2): postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64.rpm | 191 кБ 00:00
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------
Всего 163 кБ / с | 1.2 МБ 00:07
Запуск rpm_check_debug
Запуск теста транзакции
Проверка транзакции прошла успешно
Выполняемая транзакция
  Установка: postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64 1/2
  Установка: postgresql93-9.3.6-1PGDG.rhel6.x86_64 2/2
  Проверка: postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64 1/2
  Проверка: postgresql93-9.3.6-1PGDG.rhel6.x86_64 2/2

Установлены:
  postgresql93.x86_64 0: 9.3.6-1PGDG.rhel6

Установленная зависимость:
  postgresql93-libs.x86_64 0: 9.3.6-1PGDG.rhel6

Готово!
[root @ puppetagent03 ~] # psql --version
psql (PostgreSQL) 9.3,6
[root @ puppetagent03 ~] #

          Теперь, если мы попытаемся подключиться, мы получим следующее сообщение об ошибке:

          [root @ puppetagent03 ~] # psql -h 10.1.172.11 -U gerritdbuser -d reviewdb
psql: FATAL: нет записи pg_hba.conf для хоста «10.1.172.13», пользователя «gerritdbuser», базы данных «reviewdb», SSL отключен
[root @ puppetagent03 ~] #

          Это означает, что нашему клиентскому серверу удалось успешно связаться с сервером БД, но сервер БД отказал в соединении.

          Чтобы исправить это, мы еще раз редактируем db-server, pg_hba.conf файл. На этот раз мы вставляем строку, представляющую IP-адрес клиентского сервера. В моем случае IP-адрес моего клиента — «10.1.172.13», поэтому вставьте:

          хост все все 10.1.172.13/32 доверяют

          По сути, это добавляет клиентский сервер в белый список.

          Затем вставьте следующую строку в файл pg_hba.conf:

          pg_hba.conf

          Теперь давайте

          — Вставьте следующую строку

          разместить все все {vm-ip-number} / 32 доверять
хост все все 127.0.0.1 / 32 доверие

          Следующие вещи, которые, я думаю, нам нужно сделать:

          — В файл pg_hba.conf:
          [root @ puppetmaster data] # ls /var/lib/pgsql/9.3/data/pg_hba.conf
          /var/lib/pgsql/9.3/data/pg_hba.conf

          — открывает IP-таблицы для разрешения posgresql:

          iptables -A INPUT -p tcp -s 0/0 --sport 1024: 65535 -d  {db-server-ip-number}  --dport 5432 -m state --state NEW, ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s  {db-server-ip-number}  --sport 5432 -d 0/0 --dport 1024: 65535 -m state --state ESTABLISHED -j ACCEPT

          Обратите внимание: как только postgres найдет совпадение, он прекратит обработку этого файла.Поэтому вам нужно убедиться, что этот файл не содержит совпадений ранее, если да, то закомментируйте это.

          — перезапустите службу postres, чтобы учесть это изменение.

          автоматизация установки и настройки postgresql с помощью puppet

          Все вышеперечисленное можно автоматизировать с помощью puppet which. Для этого у нас есть отдельная статья.

          Примечание:

          http://www.cyberciti.biz/tips/howto-iptables-postgresql-open-port.html

          Полезные ссылки:

          http: // www.postgresql.org/docs/9.3/static/tutorial-createdb.html

          15 практических команд администрирования базы данных PostgreSQL

          http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/

          http://www.postgresql.org/docs/9.3/static/client-authentication.html

          http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

          http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html

          http: // www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/

          http://www.postgresql.org/docs/9.3/static/client-authentication-problems.html

          http://www.postgresql.org/docs/9.3/static/app-psql.html

          http://www.postgresql.org/docs/9.3/static/reference-client.html (это все утилиты командной строки, поставляемые с postgres)

          https://wiki.postgresql.org/wiki/YUM_Installation

          http://www.postgresql.org/download/linux/redhat/

          http: // yum.postgresql.org/repopackages.php

          http://www.postgresql.org/docs/9.3/static/tutorial.html

          PostgreSQL — База данных — Механизмы секретов

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

          См. Документацию по ядру секретов базы данных для дополнительные сведения о настройке механизма секретов базы данных.

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

          »Возможности

          Имя подключаемого модуля Вращение корневых учетных данных Динамические роли Статические роли
          postgresql-database-plugin1073 Да Да 0

          »Настройка

          1. Включить механизм секретов базы данных, если он еще не включен:

              $ секреты хранилища включить базу данных
Успех! Включен механизм секретов базы данных по адресу: database /

            По умолчанию механизм секретов включается при имени механизма.Чтобы включить механизм секретов на другом пути, используйте аргумент -path .

          2. Настройте Vault, указав соответствующий плагин и информацию о подключении:

              $ vault write database / config / my-postgresql-database \
    plugin_name = postgresql-база-плагин \
    allowed_roles = "моя-роль" \
    connection_url = "postgresql: // {{имя пользователя}}: {{пароль}} @localhost: 5432 /" \
    username = "vaultuser" \
    пароль = "vaultpass"

          3. Настройте роль, которая сопоставляет имя в Vault с оператором SQL для выполнения создать учетные данные базы данных:

              $ хранилище записать базу данных / роли / моя-роль \
    db_name = моя база данных postgresql \
    Creation_statements = "СОЗДАТЬ РОЛЬ \" {{name}} \ "С ПАРОЛЕМ ДЛЯ ВХОДА '{{пароль}}' ДЕЙСТВИТЕЛЬНО ДО '{{expiration}}'; \
        GRANT SELECT ДЛЯ ВСЕХ ТАБЛИЦ В СХЕМЕ public TO \ "{{name}} \"; "\
    default_ttl = "1ч" \
    max_ttl = "24 часа"
Успех! Данные, записываемые в: базу данных / роли / мою роль

          »Использование

          После того, как механизм секретов настроен и у пользователя / машины есть токен Vault с надлежащее разрешение, он может генерировать учетные данные.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *