Как работать в pgadmin 4

Как работать в pgadmin 4

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

К примеру, возьмем базу данных test1, которая была создана в прошлой теме (или создадим новую) и добавим в нее таблицу и некоторые начальные данные. Для этого нажмем в правой части окна pgAdmin на базу данных правой кнопкой мыши и в появившемся контекстном меню выберем пункт Query Tool :

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

CREATE TABLE users ( Id SERIAL PRIMARY KEY, Name CHARACTER VARYING(30), Age INTEGER ); INSERT INTO users (Name, Age) VALUES ('Tom', 33);

Фактически весь код разбивается на две части. Первая часть — инструкция CREATE TABLE , которая создает таблицу users с тремя столбцами Id, Name и Age. И вторая часть — инструкция INSERT , которая добавляет в таблицу одну строку.

Чтобы выполнить данные инструкции, нажмем над кодом в панели инструментов на стрелочку. И после этого в выбранную базу данных (test1) будет добавлена таблица users, в которую будет добавлена одна строка.

Впоследствии подобным образом будет выполняться любой другой код SQL к базе данных. Также выбирается нужная база данных, выбирается параметр Query Tool, и далее в поле ввода вводится код SQL, который затем выполняется.

Следует отметить, что для каждой таблицы определяется схема. По умолчанию это схема «public». Поэтому чтобы найти таблицу, нам надо обратиться к узлу базы данных, раскрыть его, далее выбрать подузел Schemas , в нем подузел public (название схема), и далее в нем подузел Tables , который представляет все таблицы, ассоциированные со схемой public:

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

SELECT * FROM users

И внизу программы в поле Data Output мы увидим в табличном представлении те данные, которые ранее были добавлены.

Как работать в pgadmin 4

Для упрощения администрирования на сервере postgresql в базовый комплект установки входит такой инструмент как pgAdmin . Он представляет графический клиент для работы с сервером, через который мы в удобном виде можем создавать, удалять, изменять базы данных и управлять ими. Так, на Windows после установки мы можем найти значок pgAdmin в меню Пуск и запустить его:

После этого нам откроется следующая программа pgAdmin. При открытии также отображится окно для ввода пароля для подключения к серверу Postgres:

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

После успешного логина нам откроется содержимое сервера:

В частности, в узле Databases мы можем увидеть все имеющиеся базы данных. По умолчанию здесь есть только одна база данных — postgres.

Также в правой части мы можем увидеть узел Login/Group Roles , который предназначен для управления пользователями и их ролями.

И третий узел — Tablespaces позволяет управлять местом хранения файлов баз данных.

Теперь создадим свою базу данных. Для этого нажмем правой кнопкой мыши на узел Databases . И далее в контекстном меню выберем Create -> Database.

После этого нам отобразится окно для создания базы данных. Введем название для БД, например, test1 и нажмем на кнопку «Save»:

После этого в древовидном меню слева отобразится содержимое созданной базы данных test1:

Записки IT специалиста

pgAdmin — это приложение с открытым исходным кодом для администрирования и разработки баз данных PostgreSQL которое позволяет выполнять множество задач, начиная от мониторинга и обслуживания и заканчивая выполнением SQL-запросов. pgAdmin 4 можно установить на локальный компьютер, но в наше время, когда доступ может потребоваться в любое время и из любого места, это не очень удобно. Гораздо удобнее установить pgAdmin 4 в режиме сервера и иметь доступ к нему через браузер из любой точки мира. О том как это сделать мы расскажем в данной статье.

Научиться настраивать MikroTik с нуля или систематизировать уже имеющиеся знания можно на углубленном курсе по администрированию MikroTik. Автор курса, сертифицированный тренер MikroTik Дмитрий Скоромнов, лично проверяет лабораторные работы и контролирует прогресс каждого своего студента. В три раза больше информации, чем в вендорской программе MTCNA, более 20 часов практики и доступ навсегда.

Для установки сервера pgAdmin мы будем использовать платформу Linux и дистрибутив Debian 10, при этом также поддерживаются Debian 9 и Ubuntu 16.04 (Xenial), 18.04 (Bionic), 19.10 (Eoan), 20.04 (Focal).

Мы не рекомендуем устанавливать pgAdmin на сервер с PostgreSQL если вы используете версию СУБД отличную от размещенной в репозитории, например, если вы используете PostgreSQL для 1С, потому что pgAdmin имеет в зависимостях ряд библиотек PostgreSQL, и вы с большой долей вероятности столкнетесь с конфликтом версий. Поэтому мы рекомендуем устанавливать pgAdmin на отдельный сервер, лучше всего виртуальную машину или контейнер. Все приведенные ниже команды следует выполнять с правами суперпользователя (root) или через sudo.

Установка и настройка pgAdmin 4

Для начала установим пакет gnupg для работы с цифровыми подписями:

apt install gnupg

Затем перейдем в домашнюю директорию, скачаем и установим публичный ключ для репозитория pgAdmin:

cd
wget https://www.pgadmin.org/static/packages_pgadmin_org.pub
apt-key add packages_pgadmin_org.pub

Теперь создадим файл со списком источников пакетов и внесем туда запись о репозитории pgAdmin:

echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list

Затем обновим список пакетов и установим сервер pgAdmin для работы в web-режиме:

apt update
apt install pgadmin4-web

По окончании установки запустите скрипт начальной настройки сервера:

/usr/pgadmin4/bin/setup-web.sh

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

На этом установка и первичная настройка закончена, для доступа к серверу наберите в браузере http://имя_или_ip_сервера/pgadmin4. Если все сделано правильно, то вы увидите страницу входа.

После авторизации вы попадете в привычную среду pgAdmin, можете подключать свои сервера и работать с базами данных. Инструмент поддерживает любые версии PostgreSQL и платформы установки (Windows, Linux и т.п.). В тестовых целях мы подключили сборку PostgreSQL 10 от 1С на платформе Linux и PostgreSQL 11 от Postgres Pro установленную на Windows Server.

Настраиваем SSL с самоподписанным сертификатом

Вроде бы все хорошо, все настроено и работает. Но есть одно но! По умолчанию pgAdmin 4 работает по незащищенному протоколу HTTP, что в начале третьего десятилетия 21-го века неправильно, даже если вы работаете только в пределах локальной сети. Тем более что в случае несанкционированного доступа некто получит полный доступ ко всем вашим базам данных.

Как быть? Если у вас есть собственное доменное имя, то можно настроить работу с сертификатами от Let’s Encrypt, подробнее читайте в нашей инструкции: Настраиваем Apache для работы по HTTPS (SSL) с сертификатами Let’s Encrypt

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

Для создания такого сертификата выполните команду:

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout /etc/ssl/private/pgadm4-selfsigned.key -out /etc/ssl/certs/pgadm4-selfsigned.crt

На что здесь следует обратить внимание? На ключ -days, который указывает срок действия сертификата в днях, в нашем случае мы указали 3560 дней или 10 лет,

В процессе генерации сертификата вам потребуется ответить на ряд вопросов, в большинстве их них можно указать произвольные данные и только в поле Common Name следует указать IP-адрес сервера или его FQDN-имя.

Затем откроем файл /etc/apache2/sites-available/default-ssl.conf в котором найдем и приведем к следующему виду две строки:

SSLCertificateFile /etc/ssl/certs/pgadm4-selfsigned.crt
SSLCertificateKeyFile /etc/ssl/private/pgadm4-selfsigned.key

После чего создадим файл с настройками SSL:

touch /etc/apache2/conf-available/ssl.conf 

И внесем в него следующие строки:

SSLProtocol all -SSLv3 -TLSv1 -TLSv1.1
SSLCipherSuite ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384
SSLHonorCipherOrder off
SSLSessionTickets off

SSLUseStapling On
SSLStaplingCache "shmcb:logs/ssl_stapling(32768)"

Мы не будем в рамках данной статьи подробно разбирать эти параметры, они достаточно сложны, но для получения актуальных настроек SSL вы всегда можете воспользоваться ресурсом ssl-config.mozilla.org, рекомендуемый уровень настроек — Intermediate. Указанный выше блок получен именно оттуда.

Сохраняем изменения и включаем нужные модули, конфигурации и виртуальные хосты:

a2enmod ssl
a2enconf ssl
a2ensite default-ssl

Проверяем конфигурацию Apache на ошибки:

apachectl -t

И перезапускаем веб-сервер:

systemctl reload apache2

Теперь подключимся к серверу pgAdmin 4 через браузер с явным указанием защищенного протокола https://имя_или_ip_сервера/pgadmin4:

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

Несмотря на ряд серьезных ошибок все достаточно неплохо. Первая ошибка сообщает нам, что имя сертификата не соответствует нашему узлу, что действительно так, сертификат мы выпустили для FQDN debian-pgadm4.interface31.lab, а обращаемся к серверу по IP-адресу 192.168.233.142. Вторая ошибка предупреждает о том, что сертификат выпущен центром сертификации (CA), который не является доверенным. Но так как мы знаем кто именно выпустил данный сертификат и что указанный IP-адрес совпадает с FQDN сертификата, то смело игнорируем эти ошибки.

Следующие пункты сообщают нам о том, что соединение использует протокол TLS 1.3, прямую секретность на базе 128-битной эллиптической кривой Curve25519 и шифрование AES128-GCM, также на странице нет незащищенных элементов. Это очень неплохой результат, соответствующий современным требованиям к безопасности.

После того, как мы убедились, что наш сервер нормально работает по защищенному протоколу, настроим автоматическую переадресацию с HTTP на HTTPS. Откроем файл /etc/apache2/sites-available/000-default.conf и в пределах секции VirtualHost внесем следующие строки:

RewriteEngine On
RewriteCond % off
RewriteRule (.*) https://%%

Подключим необходимые модули:

a2enmod rewrite

И перезапустим веб-сервер:

systemctl reload apache2

После чего убедимся, что все запросы к нашему серверу автоматически перенаправляются на защищенную версию. Как видим, настроить pgAdmin 4 в режиме сервера достаточно несложно, получив удобный и безопасный инструмент для управления вашими базами данных.

Научиться настраивать MikroTik с нуля или систематизировать уже имеющиеся знания можно на углубленном курсе по администрированию MikroTik. Автор курса, сертифицированный тренер MikroTik Дмитрий Скоромнов, лично проверяет лабораторные работы и контролирует прогресс каждого своего студента. В три раза больше информации, чем в вендорской программе MTCNA, более 20 часов практики и доступ навсегда.

Помогла статья? Поддержи автора и новые статьи будут выходить чаще:

Или подпишись на наш Телеграм-канал:

Установка PostgreSQL на Windows

Это статья об установке PostgreSQL. Настройка связи с r_keeper 7 описана в отдельной статье.

Установка PostgreSQL

1. Скачайте установщик на сайте: https://www.postgresql.org/download/windows/
   На момент написания статьи последней версией базы данных PostgreSQL была 13.3. В процессе установки м ы использовали 64- разрядную платформу, поэтому в данной инструкции была выбрана версия для Windows x86-64.
   
2. Запустите .exe файл и пройдите основные шаги по установке.
   Почти все шаги уже имеют предзаполненные значения. Без необходимости их не нужно менять:
   • Укажите путь для установки.
     Расположение сервера не имеет значения, если только нет конкретных причин для изменения.
   • Укажите компоненты базы данных.
     По умолчанию выбраны все.
   • Выберите путь для хранимых данных.
     Расположение базы данных тоже не имеет значения.
   • Выберите пароль для супер-пользователя postgres.
     Например: postgres.
   • Выберите порт для устанавливаемой копии сервера PostgreSQL.
     По умолчанию: 5432.
   • Выберите локализацию базы данных. По умолчанию это просто Default locale.
     В дальнейшем при использовании Unicode локализация не будет имеет значения для хранимых данных. Но от локализации зависят названия, например, дней недели и месяцев.
3. Установщик предложит посмотреть на выбранные параметры.
4. Проверьте и начните установку.
5. Stack Builder пока не требуется запускать. Уберите галочку и нажмите Finish.
   

Запуск pgAdmin

1. В папке PostgreSQL 13 выберите и запустите приложение pgAdmin 4.
   
2. Откроется окно запуска.
   
3. Задайте пароль для административной панели, например, postgres.
4. Нажмите на кнопку OK.
   
   Произойдет подключение к серверу по указанному при установке паролю. После чего появится одна предустановленная база данных postgres.
   
5. Создайте свою базу данных.

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

1. Выберите стандартную базу данных postgres и нажмите на нее правой кнопкой мыши.
2. В появившемся меню нажмите на пункт Query Tool.

   В центральной части программы откроется поле для ввода кода SQL.

3. В это поле введите следующий код с именем новой базы данных:

create database pgs;

Нажмите на кнопку выполнения кода .

После этого появится сообщение о том, что создана база данных pgs.

Чтобы ее увидеть, правой кнопкой мыши нажмите на узел Databases и в контекстном меню выберите Refresh.

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

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

create user username password 'pgspgs'; grant all on database pgs to username; alter user username with createrole;

select * from pg_shadow

При закрытии pgAdmin 4 сохраните сделанные изменения в существующую или новую папку.