PostgreSQL: шпаргалка (use database, show tables, show users)

Небольшая шпаргалка для начинающих пользователей PostgreSQL, переключится в контекст базы (use database в mysql), список таблиц БД (show tables в mysql) и просмотр пользователей имеющих доступ к базе данных.

Работа с локальным PostgreSQL

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

[root@s ~]# su postgres
bash-4.4$ psql
could not change directory to "/root": Permission denied
psql (10.6)
Type "help" for help.

postgres=#

Список баз данных на сервере (аналог ‘show databases’) —  \l

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 quaded    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 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
(4 rows)

postgres=#

Подключится к базе (аналог ‘use database’) — \c

postgres=# \c quaded
You are now connected to database "quaded" as user "postgres".
quaded=#

Список таблиц в БД (аналог show tables) — \dt

quaded=# \dt
             List of relations
 Schema |       Name        | Type  | Owner
--------+-------------------+-------+-------
 public | alembic_version   | table | quaded
 public | flask_dance_oauth | table | quaded
 public | prices            | table | quaded
 public | product           | table | quaded
 public | user              | table | quaded
(5 rows)

quaded=#

Более подробный список таблиц (с размером таблицы на диске) — \dt+

quaded=# \dt+
                           List of relations
 Schema |       Name        | Type  | Owner |    Size    | Description
--------+-------------------+-------+-------+------------+-------------
 public | alembic_version   | table | quaded | 8192 bytes |
 public | flask_dance_oauth | table | quaded | 16 kB      |
 public | prices            | table | quaded | 8192 bytes |
 public | product           | table | quaded | 16 kB      |
 public | user              | table | quaded | 8192 bytes |
(5 rows)

Посмотреть список записей в таблице можно обычным SQL запросом из контекста БД

select * from products;

Как узнать размер базы данных PostgreSQL?

Войти в интерактивный терминал:

$ sudo -u postgres psql

Выполнить запрос:

# select pg_database_size('base');

В человеческом виде размер базы покажет обёртка pg_size_pretty

SELECT pg_size_pretty( pg_database_size( 'sample_db' ) );

 pg_size_pretty 
----------------
36 GB

Таким же макаром можно посмотреть и размер таблицы (с индексами)

SELECT pg_size_pretty( pg_total_relation_size( 'table' ) );

 pg_size_pretty 
----------------
 6341 MB

Если нужно без индексов, тогда запрос другой:

# SELECT pg_size_pretty( pg_relation_size( 'table' ) );

 pg_size_pretty 
----------------
 1341 MB

Размер всех БД построчно:

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database

Общий размер всех БД одним значением:

select pg_size_pretty(sum(pg_database_size(datname)))
from pg_database

Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена от пользователя postgres — чтобы войти в систему от данного пользователя, вводим:

su - postgres

* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.

Теперь запускаем командную оболочку PostgreSQL:

$ psql -Upostgres template1

* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.

Для просмотра всех пользователей СУБД:

=# select * from pg_user;

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

1. Создание пользователя
а) Добавление новой роли (пользователя) из оболочки SQL:

=# CREATE USER dmosk WITH PASSWORD 'myPassword';

* в примере создана роль dmosk с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

createuser -P dmosk

2. Назначение прав на использование базы данных
Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE "database1" to dmosk;

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

=# \c database1

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "dmosk";

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

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

database1=# \dn

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO "dmosk";

* в данном примере мы даем права на таблицу table1.

3. Настройка файла pg_hba.conf
Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь до него:

=# SELECT current_setting('hba_file');

В ответ мы получим, что-то на подобие:

----------------------------------------- 
/var/lib/pgsql/9.6/data/pg_hba.conf
(1 row)

Выходим из оболочки sql:

=# \q

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

$ exit

Открываем pg_hba.conf:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

Добавляем права на подключение нашему созданному пользователю:

...
# IPv4 local connections:
host    all             dmosk           127.0.0.1/32            md5
...

* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию
host    all             all             127.0.0.1/32            ident.

В файле уже может быть строка на подобие:

host    all      all     127.0.0.1/32 md5

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

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

4. Проверка
Для теста пробуем подключиться к PostgreSQL с помощью созданного пользователя:

psql -U dmosk -d database1 -h 127.0.0.1

* в данном примере мы подключается к локальному серверу 127.0.0.1 под пользователем dmosk и базе database1, на которую мы дали ему права.

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

=# CREATE ROLE "myRole" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

* данной командой создана группа myRole с минимальными правами.

Теперь добавим ранее созданного пользователя dmosk в эту группу:

=# GRANT "myRole" TO dmosk;

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

=# \c database1

и предоставим все права для группы myRole всем таблицам базы database1

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP "myRole";

Редактирование пользователя
1. Смена пароля
Рассмотрим несколько примеров смены пароля пользователя.

Одной командой:

=# ALTER USER postgres PASSWORD 'password'

* в данном примере мы зададим пароль password для пользователя postgres.

С запросов ввода пароля:

=# \password postgres

* после ввода данной команды система потребует дважды ввести пароль для пользователя (в нашем примере, postgres).

Из командной строки Linux:

sudo -u postgres psql -U postgres -d postgres -c "ALTER USER postgres PASSWORD 'password'"

* по сути, мы выполняем также запрос в оболочке sql.

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

=# DROP USER dmosk;

Забрать права:

database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "dmosk";

* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO "dmosk" пишем FROM "dmosk";

Назначение особых прав пользователям PostgreSQL
Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:

=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "dmosk";

* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя dmosk.

Назначение прав для определенной таблицы:

database1=# GRANT ALL PRIVILEGES ON table_users TO "dmosk";

* в данном примере мы предоставим все права на таблицу table_users в базе данных database1;

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

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

=# CREATE USER bkpuser WITH PASSWORD 'bkppasswd';

* мы создадим учетную запись bkpuser с паролем bkppasswd.

Предоставляем права на подключения к базе

=# GRANT CONNECT ON DATABASE database TO bkpuser;

* в данном примере к базе database.

Подключаемся к базе (в нашем примере database):

=# \c database

Даем права на все последовательности в схеме:

=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

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

Графический интерфейс
Иногда проще воспользоваться программой для выставления прав и работы с PostgreSQL. Могу посоветовать приложение pgAdmin. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.