PostgreSQL — различия между версиями

Материал из megapuper
Перейти к: навигация, поиск
 
(не показаны 33 промежуточные версии этого же участника)
Строка 1: Строка 1:
−
Установка на Ubuntu
+
Конфиги<br>
−
# deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
+
postgresql.conf - порт, интерфейсы и тд<br/>
−
  # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
+
pg_hba.conf - конфиг аутентификации пользователей
−
  # apt-get install postgresql-{VERSION}
+
 
 +
 
 +
Формат конфига pg_hba.conf
 +
local database user auth-method [auth-option]
 +
host database user CIDR-address auth-method [auth-option]
 +
hostssl database user CIDR-address auth-method [auth-option]
 +
host database user IP-address IP-mask auth-method [auth-option]
 +
  hostssl database user IP-address IP-mask auth-method [auth-option]
 +
 
 +
1. Первое ключевое слово определяет тип подключения и соответственно тип хостовой записи
 +
'''local''' - для клиентских подключений с того же компьютера, на котором работает сервер
 +
'''host''' - сетевое подключение
 +
'''hostssl''' - для подключений локальных или удаленных хостов к серверу, с использованием ssl протокола, обеспечивающим шифрование всех взаимодействий с сервером.
 +
 
 +
2. Второй параметр database обозначает базы данных, к которым можно подключаться
 +
'''all''' - для разрешения подключения ко всем базам данных
 +
'''sameuser''' - для разрешения подключения только к базе данных имя которой совпадает с именем пользователя
 +
  '''dbname''' - имя базы данных или список баз данных, разделенный запятыми - для разрешения подключения к конкретной базе данных
  
 +
3. Третий определяет пользователей, которым можно подключаться
 +
'''user''' - имена пользователей или групп, которым разрешается подключение с этого компьютера
 +
'''all''' - все пользователи
 +
имя пользователя - имя конкретного пользователя +имя группы - имя группы
 +
список пользователей и групп, разделенный запятыми
  
−
Конфиги
+
4. CIDR-address или IP адрес и IP маска - адрес хоста
−
postgresql.conf - порт, интерфейсы и тд<br/>
+
 
−
pg_hba.conf - конфиг аутентификации пользователей
+
5. Тип аутентификации
 +
peer
 +
trust
 +
reject
 +
md5
 +
crypt
 +
password
 +
krb5
 +
ident
 +
idap
 +
pam
 +
 
 +
6. Опции, в зависимости от типа аутентификации.
  
−
Управление
 
−
pg_ctlcluster 9.4 main stop
 
−
pg_ctlcluster 9.4 main start
 
−
pg_ctlcluster 9.4 main reload
 
  
 
Заходим под пользователем postgres
 
Заходим под пользователем postgres
Строка 38: Строка 68:
 
  postgres=# \d - список таблиц
 
  postgres=# \d - список таблиц
 
  postgres=# \с dbname - выбрать базу
 
  postgres=# \с dbname - выбрать базу
 +
postgres=# \du - список ролей с правами
 
  postgres=# \q - выход
 
  postgres=# \q - выход
 
  postgres=# \h [COMMAND]- вывод помощи
 
  postgres=# \h [COMMAND]- вывод помощи
  
−
  postgres=# create database test_db; - создать базу
+
  postgres=# create database <font color=blue>DATABASE</font>; - создать базу
−
  postgres=# create user test_user with login; - создать пользователя test с возможностью логина
+
  postgres=# create database <font color=blue>DATABASE</font> owner <font color=blue>USER</font>; - создать базу, владельцем которой будет USER
−
  postgres=# create user test_user with password 'qwerty'; - создать пользователя с паролем
+
  postgres=# create user <font color=blue>USER</font> with password 'qwerty'; - создать пользователя с паролем
−
  postgres=# create database test_db owner test_user; - создать базу тест, владельцем которой будет test
+
  postgres=# create user <font color=blue>USER</font> with login; - создать пользователя USER с возможностью логина
−
  postgres=# grant all privileges on database test_db to test_user; - права на базу
+
postgres=# alter database <font color=blue>DATABASE</font> owner to <font color=blue>USER</font>; - сменить владельца базы
−
  postgres=# drop database test_db; - удалить базу
+
postgres=# alter table <font color=blue>PUBLIC.TABLE</font> owner to <font color=blue>USER</font>; - сменить владельца таблицы
 +
  postgres=# grant all privileges on database <font color=blue>DATABASE</font> to <font color=blue>USER</font>; - права на базу
 +
  postgres=# drop database <font color=blue>DATABASE</font>; - удалить базу
 +
postgres=# create role <font color=blue>ROLE_NAME</font>; - создать роль
 +
postgres=# alter role <font color=blue>ROLE_NAME</font> with Superuser; - добавить права для роли
  
 
  # pg_dump <font color=blue>DATABASE</font> > dump.sql - дамп базы
 
  # pg_dump <font color=blue>DATABASE</font> > dump.sql - дамп базы
 +
# pg_dump -U <font color=blue>USER</font> -W <font color=blue>DATABASE</font> > dump.sql - дамп базы, -U это имя пользователя, под которым подключаемся, -W обязывает ввести пароль при подключении
 +
# pg_dump -t <font color=blue>TABLE</font> <font color=blue>DATABASE</font> > dump.sql - дамп таблицы
 +
# pg_dumpall > dump.sql - дамп всех баз, включая системные
 +
 
  # pg_dump <font color=blue>DATABASE</font> < dump.sql - восстановление базы из дампа
 
  # pg_dump <font color=blue>DATABASE</font> < dump.sql - восстановление базы из дампа
 +
# pg_restore -U <font color=blue>USER</font> -d <font color=blue>DATABASE</font> -1 filename.dump - восстановление базы из дампа
 +
 +
 +
Read-only доступ( postgresql 12)
 +
CREATE ROLE <font color=blue>USER</font> WITH LOGIN PASSWORD 'Test1234';
 +
\connect YourDatabaseName;
 +
 +
GRANT CONNECT ON DATABASE <font color=blue>DATABASE</font> TO <font color=blue>USER</font>;
 +
GRANT USAGE ON SCHEMA public TO <font color=blue>USER</font>;
 +
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <font color=blue>USER</font>;
 +
 +
 +
Read-only доступ(начиная с версии postgresql 14)
 +
create user <font color=blue>USER</font> with password 'qwerty';
 +
grant pg_read_all_data to <font color=blue>USER</font>;
 +
 +
 +
 +
Подключение из консоли
 +
psql -h <font color=blue>HOST</font> -p <font color=blue>PORT</font> -U <font color=blue>USER</font> -d <font color=blue>DATABASE</font>
 +
 +
 +
https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
 +
 +
Полезные запросы<br>
 +
https://habr.com/ru/post/696274/

Текущая версия на 12:22, 11 июля 2024

Конфиги
postgresql.conf - порт, интерфейсы и тд
pg_hba.conf - конфиг аутентификации пользователей


Формат конфига pg_hba.conf 

local database user auth-method [auth-option]
host database user CIDR-address auth-method [auth-option]
hostssl database user CIDR-address auth-method [auth-option]
host database user IP-address IP-mask auth-method [auth-option]
hostssl database user IP-address IP-mask auth-method [auth-option]

1. Первое ключевое слово определяет тип подключения и соответственно тип хостовой записи 

local - для клиентских подключений с того же компьютера, на котором работает сервер
host - сетевое подключение
hostssl - для подключений локальных или удаленных хостов к серверу, с использованием ssl протокола, обеспечивающим шифрование всех взаимодействий с сервером.

2. Второй параметр database обозначает базы данных, к которым можно подключаться 

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

3. Третий определяет пользователей, которым можно подключаться 

user - имена пользователей или групп, которым разрешается подключение с этого компьютера
all - все пользователи
имя пользователя - имя конкретного пользователя +имя группы - имя группы
список пользователей и групп, разделенный запятыми

4. CIDR-address или IP адрес и IP маска - адрес хоста

5. Тип аутентификации 

peer
trust
reject
md5
crypt
password
krb5
ident
idap
pam

6. Опции, в зависимости от типа аутентификации.


Заходим под пользователем postgres 

# sudo -i -u postgres


Подключаемся к постгресу 

# psql

Суперпользователь в PostgreSQL называется postgres. Заводим ему пароль 

postgres=# ALTER USER postgres WITH PASSWORD 'password';

или так 

\password postgres


После этого рихтуем pg_hba.conf и рестартим постгрес 

меняем peer
local   all             postgres                                peer
на md5
local   all             postgres                                md5



postgres=# \c - под каким пользователем и к какой базе подключились
postgres=# \l - список БД
postgres=# \d - список таблиц
postgres=# \с dbname - выбрать базу
postgres=# \du - список ролей с правами
postgres=# \q - выход
postgres=# \h [COMMAND]- вывод помощи

postgres=# create database DATABASE; - создать базу
postgres=# create database DATABASE owner USER; - создать базу, владельцем которой будет USER
postgres=# create user USER with password 'qwerty'; - создать пользователя с паролем
postgres=# create user USER with login; - создать пользователя USER с возможностью логина
postgres=# alter database DATABASE owner to USER; - сменить владельца базы
postgres=# alter table PUBLIC.TABLE owner to USER; - сменить владельца таблицы
postgres=# grant all privileges on database DATABASE to USER; - права на базу
postgres=# drop database DATABASE; - удалить базу
postgres=# create role ROLE_NAME; - создать роль
postgres=# alter role ROLE_NAME with Superuser; - добавить права для роли

# pg_dump DATABASE > dump.sql - дамп базы
# pg_dump -U USER -W DATABASE > dump.sql - дамп базы, -U это имя пользователя, под которым подключаемся, -W обязывает ввести пароль при подключении
# pg_dump -t TABLE DATABASE > dump.sql - дамп таблицы
# pg_dumpall > dump.sql - дамп всех баз, включая системные

# pg_dump DATABASE < dump.sql - восстановление базы из дампа
# pg_restore -U USER -d DATABASE -1 filename.dump - восстановление базы из дампа


Read-only доступ( postgresql 12) 

CREATE ROLE USER WITH LOGIN PASSWORD 'Test1234';
\connect YourDatabaseName;

GRANT CONNECT ON DATABASE DATABASE TO USER;
GRANT USAGE ON SCHEMA public TO USER;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER;


Read-only доступ(начиная с версии postgresql 14) 

create user USER with password 'qwerty';
grant pg_read_all_data to USER;


Подключение из консоли 

psql -h HOST -p PORT -U USER -d DATABASE


https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

Полезные запросы
https://habr.com/ru/post/696274/

Источник — «http://megapuper.ru/index.php?title=PostgreSQL&oldid=160079»