Создание пользователя postgresql – Иллюстрированный самоучитель по PostgreSQL › Установка PostgreSQL › 10 этапов установки PostgreSQL. Этап 1. Создание пользователя postgres. [страница - 11]

Содержание

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

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

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

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

# TYPE    DATABASE      USER          ADDRESS          METHOD
local     all           postgres                       peer

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

$ sudo -u postgres psql postgres postgres

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

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

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

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

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

export PGPASSWORD=mypasswd

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

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

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

hostname:port:database:username:password

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

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

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

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

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

\du

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

SELECT * FROM pg_user;

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

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

CREATE USER username WITH password 'password';

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

createuser -S -D -R -P username

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

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

ALTER USER username WITH PASSWORD 'password';

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

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

ALTER ROLE username WITH CREATEDB;

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

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

\l

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

psql -l

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

CREATE DATABASE dbname OWNER dbadmin;

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

createdb -O username dbname;

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

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


 GRANT CONNECT ON DATABASE dbname TO dbadmin;

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

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

Check if database exists in postgreSQL using shell

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

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

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

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

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

PostgreSQL. Файл pg_hba.conf.

www.mvoronin.pro

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

1. Установка

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

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


$ sudo apt-get update

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


$ sudo apt-get install postgresql-x.x

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

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


$ sudo apt-cache search postgresql

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

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


$ sudo apt-get install postgresql postgresql-contrib

2. Консоль PostgreSQL

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

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

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


# su - postgres

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


$ psql

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

postgres:


$ sudo -u postgres psql

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

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


postgres=# \q

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


$ exit

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

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

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

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


# CREATE USER username WITH PASSWORD '12345';

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

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

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


# DROP USER username;

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

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

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

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

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


# CREATE DATABASE dbname;

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

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


# DROP DATABASE dbname;

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

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

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

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

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


# GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

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

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

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


# REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

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

karimov.info

PostgreSQL : Документация: 9.6: createuser : Компания Postgres Professional

createuser

createuser — создать новую учётную запись PostgreSQL

Синтаксис

createuser [параметр-подключения…] [параметр…] [имя_пользователя]

Описание

createuser создаёт нового пользователя PostgreSQL, а если точнее — роль. Лишь суперпользователь и пользователи с привилегией CREATEROLE могут создавать новые роли, таким образом, createuser должна запускаться от их лица.

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

createuser — это обёртка для SQL-команды CREATE ROLE. Создание пользователей с её помощью по сути не отличается от выполнения того же действия при обращении к серверу другими способами.

Параметры

createuser принимает следующие аргументы:

имя_пользователя: Задаёт имя создаваемого пользователя PostgreSQL. Это имя должно отличаться от имён всех существующих ролей в данной инсталляции PostgreSQL.
-c номер --connection-limit=номер: Устанавливает максимальное допустимое количество соединений для создаваемого пользователя. По умолчанию ограничение в количестве соединений отсутствует.
-d --createdb: Разрешает новому пользователю создавать базы данных.
-D --no-createdb: Запрещает новому пользователю создавать базы данных. Это поведение по умолчанию.
-e --echo: Вывести команды к серверу, генерируемые при выполнении createuser.
-E --encrypted: Шифровать пароль пользователя, хранимый в базе. Если флаг не указан, то для пароля используется поведение по умолчанию.
-g role --role=role: Указывает роль, к которой будет добавлена текущая роль в качестве члена группы. Допускается множественное использование флага -g.
-i --inherit: Создаваемая роль автоматически унаследует права ролей, в которые она включается. Это поведение по умолчанию.
-I --no-inherit: Роль не будет наследовать права ролей, в которые она включается.
--interactive: Запросить имя для создаваемого пользователя, а также значения для флагов -d/-D, -r/-R, -s/-S, если они явно не указаны в командной строке. До версии PostgreSQL 9.1 включительно это было поведением по умолчанию.
-l --login: Новый пользователь сможет подключаться к серверу (то есть его имя может быть идентификатором начального пользователя сеанса). Это свойство по умолчанию.
-L --no-login: Новый пользователь не сможет подключаться к серверу. (Роль без права входа на сервер тем не менее полезна для управления разрешениями в базе данных.)
-N --unencrypted: Не шифровать пароль, хранимый в базе. Если флаг не указан, то для пароля используется поведение по умолчанию.
-P --pwprompt: Если флаг указан, то createuser запросит пароль для создаваемого пользователя. Если не планируется аутентификация по паролю, то пароль можно не устанавливать.
-r --createrole: Разрешает новому пользователю создавать другие роли, что означает наделение привилегией CREATEROLE.
-R --no-createrole: Запрещает пользователю создавать новые роли. Это поведение по умолчанию.
-s --superuser: Создаваемая роль будет иметь права суперпользователя.
-S --no-superuser: Новый пользователь не будет суперпользователем. Это поведение по умолчанию.
-V --version: Вывести версию createuser и завершить выполнение.
--replication: Создаваемый пользователь будет наделён правом REPLICATION. Это рассмотрено подробнее в документации по CREATE ROLE.
--no-replication: Создаваемый пользователь не будет иметь привилегии REPLICATION. Это рассмотрено подробнее в документации по CREATE ROLE.
-? --help: Вывести помощь по команде createuser.

createuser также принимает из командной строки параметры подключения:

-h сервер --host=сервер

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

-p порт --port=порт

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения.

-U имя_пользователя --username=имя_пользователя

Имя пользователя для подключения (не имя создаваемого пользователя).

-w --no-password

Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass, попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль.

-W --password

Принудительно запрашивать пароль перед подключением к базе данных.

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

Переменные окружения

PGHOST PGPORT PGUSER: Параметры подключения по умолчанию

Эта утилита, как и большинство других утилит PostgreSQL, также использует переменные среды, поддерживаемые libpq (см. Раздел 32.14).

Диагностика

В случае возникновения трудностей, обратитесь к CREATE ROLE и psql. Переменные окружения и параметры подключения по умолчанию libpq будут применены при запуске утилиты, это следует учитывать при диагностике.

Примеры

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

$ createuser joe

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

$ createuser --interactive joe
Назначить роль суперпользователем? (y/n) n
Разрешить новой роли создавать базы данных? (y/n) n
Разрешить новой роли создавать другие роли? (y/n) n

Чтобы создать того же пользователя

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

$ createuser -h eden -p 5000 -S -D -R -e joe
CREATE ROLE joe NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Чтобы создать роль joe с правами суперпользователя и предустановленным паролем:

$ createuser -P -s -e joe
Введите пароль для новой роли: xyzzy
Подтвердите ввод пароля: xyzzy
CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

В приведённом примере введённый пароль отображается лишь для отражения сути, на деле же он не выводится на экран. Как видно в выводе журнала команд, пароль зашифрован. Если же указан флаг --unencrypted, то он отобразиться в этом журнале неизменным, а также, возможно, и в других журналах сервера. По этой причине в этой ситуации стоит использовать флаг

-e с особой осторожностью.

postgrespro.ru

Добавление ролей учетных записей в PostgreSQL—Справка

"""
Name: create_database_user.py
Description: Provide connection information to a database user.
Type create_database_user.py -h or create_database_user.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy
import os
import optparse
import sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 release")

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("--auth", dest="Account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
parser.add_option ("--utype", dest="user_type", type ="choice", choices=['DATABASE_USER', 'OPERATING_SYSTEM_USER'], default='DATABASE_USER', help="Authentication type options (case-sensitive):  DATABASE_USER, OPERATING_SYSTEM_USER.  Default=DATABASE_USER")
parser.add_option ("-u", dest="dbuser", type="string", default="", help="database user name")
parser.add_option ("-p", dest="dbuser_pwd", type="string", default="", help="database user password")
parser.add_option ("-r", dest="role", type="string", default="", help="role to be granted to the user")
parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	database = options.Database.lower()	
	account_authentication = options.Account_authentication.upper()
	dbms_admin = options.Dbms_admin
	dbms_admin_pwd = options.Dbms_admin_pwd
	dbuser = options.dbuser
	dbuser_pwd = options.dbuser_pwd	
	tablespace = options.Tablespace
	user_type = options.user_type
	role = options.role

	
	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
	
	if( database_type ==""):	
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified."))
		parser.print_help()
		sys.exit(3)		
	
	if(database_type == "SQL_SERVER"):
		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication"))
			sys.exit(3)
		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
			print("\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication..."))
	else:		
		if( dbuser.lower() == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "Database user must be specified."))
			sys.exit(3)		
		if( dbms_admin == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!"))
			sys.exit(3)

	if ( user_type == "DATABASE_USER" and (dbuser =="" or dbuser_pwd =="")):
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "To create database authenticated user, user name and password must be specified!"))
		parser.print_help()
		sys.exit(3)	

	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print("\n" + product_license + " license found!" + "  Creating a user in an enterprise geodatabase or database requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license.")
		sys.exit("Re-authorize ArcGIS before creating a database user.")
	else:
		print("\n" + product_license + " license available!  Continuing to create...")
		arcpy.AddMessage("+++++++++")

	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + dbms_admin   

	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)

	try:
		print("\nCreating Database Connection File...\n")
		# Process: Create Database Connection File...
		# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
		#arcpy.CreateDatabaseConnection_management(temp , Connection_File_Name, database_type, instance, database, account_authentication, dbms_admin, dbms_admin_pwd, "TRUE")
		arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=dbms_admin, password=dbms_admin_pwd, save_user_pass="TRUE")
	        for i in range(arcpy.GetMessageCount()):
			if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("\n+++++++++")
				arcpy.AddMessage("Exiting!!")
				arcpy.AddMessage("+++++++++\n")
				sys.exit(3)            
			else:
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("+++++++++\n")

		print("Creating database user...\n")
		arcpy.CreateDatabaseUser_management(input_workspace=Connection_File_Name_full_path, user_authentication_type=user_type, user_name=dbuser, user_password=dbuser_pwd, role=role, tablespace_name=tablespace)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print("\n")
		parser.print_help()   
		parser.exit(2)

desktop.arcgis.com

PostgreSQL не может создать пользователя

Проблема: я могу создавать пользователей или базы данных из оболочки (bash, OSX), но не postgres cli. Из bash я не получаю подтверждения в случае успеха.

Если я пытаюсь создать CREATE ROLE в psql, тогда я не получаю ответа и не генерирует никаких ошибок. Если я попытаюсь создать createuser из bash, то в случае успеха он ничего не сообщает о возврате, если он не увенчался успехом, тогда он генерирует ошибку: «имя пользователя роли уже существует».

Example: 

Yunti-# CREATE ROLE testuser 
Yunti-# \du 
          List of roles 
Role name |     Attributes     | Member of 
-----------+------------------------------------------------+----------- 
Yunti  | Superuser, Create role, Create DB, Replication | {} 
anything |            | {} 
monkey |            | {} 

Yunti-# CREATE DATABASE testdb 
Yunti-# \l 
           List of databases 
    Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+-------------+-------------+------------------- 
Yunti  | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | 
postgres | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | 
template0 | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti   + 
      |  |   |    |    | Yunti=CTc/Yunti 
template1 | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti   + 
      |  |   |    |    | Yunti=CTc/Yunti 
test  | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | 
test5  | Yunti | UTF8  | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows) 

Yunti-#

Аналогичная вещь случается при использовании createdb.

Как создать пользователей и базы данных в postgres cli? И это нормально, чтобы не получить ответа на большинство команд postgres в bash?

информация: пользователи и их привилегии:

Yunti-# \du 
          List of roles 
Role name |     Attributes     | Member of 
-----------+------------------------------------------------+----------- 
Yunti  | Superuser, Create role, Create DB, Replication | {} 
anything |            | {} 
monkey |            | {}

stackoverrun.com

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

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

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

Каждому пользователю PostgreSQL назначается внутренний системный идентификатор (sysid) и пароль, хотя пароль не всегда обязателен для подключения (это зависит от файла конфигурации pg_hba.conf, о котором говорилось в главе 8). Системный идентификатор связывает объекты базы данных с владельцем (так называется пользователь, которому разрешено предоставлять и отзывать права доступа к объекту).

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

В PostgreSQL по умолчанию создается один суперпользователь с именем postgres. Все остальные пользователи создаются этим суперпользователем или другим, созданным позднее.

Просмотр списка пользователей

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

Таблица 10.1. Структура таблицы pg_shadow.

Поле	Тип
usename	name
usesysid	integer
usecreatedb	boolean
usetrace	boolean
usesuper	boolean
usecatupd	boolean
passwd	text
valuntil	abstime

Основное различие между данными, содержащимися в таблицах pg_user и pg_shadow, заключается в том, что в pg_user отсутствует значение в поле passwd (оно заменяется несколькими звездочками). Эта мера безопасности гарантирует, что обычный пользователь не сможет получить несанкционированный доступ к чужим паролям.

В поле usename хранится имя системного пользователя, которое представляет собой уникальную строку. Существование одноименных пользователей не допускается, потому что пользователи являются глобальными объектами базы данных. В поле usesysid хранится уникальный целый идентификатор, связанный с пользователем. Поля usecreatedb и usesuper содержат информацию о привилегиях, которые могут присваиваться пользователю при создании (см. следующий подраздел).

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

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

Первый способ основан па выполнении команды SQL CREATE USER в клиенте PostgreSQL (например, psql) после прохождения аутентификации. Второй способ, основанный на использовании программы createuser, более удобен для системного администратора, поскольку все действия выполняются одной командой без взаимодействия с клиентом PostgreSQL.

samoychiteli.ru

Установка и настройка PostgreSQL 10 на Linux Ubuntu Server | Info-Comp.ru

Привет! Материал сегодня будет посвящен рассмотрению процесса установки СУБД PostgreSQL 10 на серверную операционную систему Linux Ubuntu Server, а также первоначальной настройки PostgreSQL 10, для того чтобы можно было ее использовать, например, в сети своей организации.

Другими словами, сейчас мы с Вами реализуем сервер баз данных на базе Linux Ubuntu Server и PostgreSQL 10, который будет иметь базовую настройку. Если Вас интересует реализация подобного сервера только на базе операционной системы CentOS 7.1, то можете ознакомиться с материалом «Установка PostgreSQL 9.4 на CentOS 7.1», в нем мы как раз рассматривали данный процесс.

Так как PostgreSQL 10 – это новая версия данной системы управления базами данных, то начать предлагаю с краткого рассмотрения новых возможностей 10 версии.

Примечание! 10 версия PostgreSQL была актуальна на момент написания статьи, на текущий момент доступны новые версии.

Что нового в PostgreSQL 10?

Начиная с PostgreSQL 10, меняется схема нумерации версий, это вызвано тем, что раньше выходило множество минорных версий (например, 9.x), многие из которых на самом деле вносили значительные изменения не соответствующие минорным, теперь мажорные версии будут нумероваться 10, 11, 12, а минорные 10.1, 10.2, 11.1 и так далее.

Основные нововведения:

Логическая репликация с использованием публикации и подписки — теперь возможно осуществлять репликацию отдельных таблиц на другие базы, это реализовывается с помощью команд CREATE PUBLICATION и CREATE SUBSCRIPTION;
Декларативное партиционирование таблиц – в PostgreSQL 10 добавился специальный синтаксис для партиционирования, который позволяет легко создавать и поддерживать таблицы с интервальной или списочной схемой партиционирования;
Улучшенный параллелизм запросов – другими словами, появилась дополнительная оптимизация запроса, для того чтобы пользователь получал данные быстрей;
Аутентификация пароля на основе SCRAM-SHA-256 – добавился новый метод аутентификации, который является более безопасным, чем метод с использованием MD5;
Quorum Commit для синхронной репликации – теперь администратор может указать что, если какое-либо количество реплик подтвердило, что внесено изменение в базу данных, данное изменение можно считать надёжно зафиксированным;
Значительные общие улучшения производительности;
Улучшенный мониторинг и контроль.

Более детально обо всех нововведениях можете почитать на официальном сайте – PostgreSQL 10.

Установка PostgreSQL 10 на Linux Ubuntu Server 16.04

Как Вы уже, наверное, поняли рассматривать процесс установки и соответственно настройки PostgreSQL 10 мы будем на примере версии Ubuntu Server 16.04, так как эта версия имеет долгосрочную поддержку и на текущий момент является актуальной среди LTS версий.

Шаг 1

Установку и настройку PostgreSQL необходимо осуществлять с правами суперпользователя, поэтому давайте сразу переключимся на пользователя root. Для этого вводим sudo -i (или sudo su) и жмем Enter.

Шаг 2

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

   
   apt-cache search postgresql-10

Как видим, в Ubuntu Server 16.04 10 версии PostgreSQL нет, поэтому нам нужно подключить необходимый репозиторий, в котором присутствует PostgreSQL 10. Если у Вас более новая версия Ubuntu Server и в стандартных репозиториях есть 10 версия PostgreSQL, то дополнительный репозиторий Вам подключать не нужно, т.е. данный шаг Вы пропускаете.

Для подключения репозитория нам необходимо создать специальный файл с адресом нужного репозитория. Адреса для каждой версии Ubuntu разные, поэтому если у Вас версия Ubuntu не 16.04, то уточнить адрес Вы можете на официальном сайте PostgreSQL на странице загрузке – вот она.

После перехода на страницу выбираете версию Ubuntu, после чего у Вас отобразится адрес нужного репозитория.

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

   
   sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

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

   
   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \apt-key add -

Далее обновляем список пакетов.

   
   apt-get update

И еще раз проверяем наличие пакета с PostgreSQL 10.

   
   apt-cache search postgresql-10

Теперь нужный пакет у нас есть, и мы можем переходить к установке PostgreSQL 10.

Шаг 3

Для установки PostgreSQL 10 пишем следующую команду.

   
   apt-get -y install postgresql-10

По окончанию процесса установки проверяем, запущен ли сервер PostgreSQL.

   
   systemctl status postgresql

Как видим, PostgreSQL 10 установился и работает.

Базовая настройка PostgreSQL 10 в Linux Ubuntu Server

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

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

После установки, к серверу PostgreSQL мы можем подключиться только с помощью системного пользователя postgres, причем без пароля. Для этого переключаемся на пользователя postgres (учетная запись в Ubuntu создана автоматически во время установки PostgreSQL).

   
   su - postgres

Запускаем psql — это консоль управления PostgreSQL.

   
   psql

Сначала зададим пароль для пользователя postgres.

   
   \password postgres

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

   
    create user testuser with password '123456';

где, testuser – это имя пользователя, ‘123456’ – это его пароль.

Далее давайте создадим базу данных.

   
   create database testdb;

где, testdb – это имя новой базы данных.

Теперь давайте дадим права на управление БД нашему новому пользователю.

   
   grant all privileges on database testdb to testuser;

Все готово, выходим из консоли.

\q

Для проверки, давайте подключимся к PostgreSQL от имени нового пользователя, на предложение о вводе пароля вводим пароль от новой учетной записи.

   
   psql -h localhost testdb testuser

Работает. Для выхода снова набираем \q.

\q

Для переключения обратно на root вводим exit.

   
   exit

Разрешаем подключение к PostgreSQL по сети

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

Для этого открываем файл postgresql.conf, например редактором nano.

   
   nano /etc/postgresql/10/main/postgresql.conf

Находим следующую строку.

   
   #listen_addresses = 'localhost'

и заменяем на (вместо звездочки Вы в случае необходимости указываете IP адрес нужного интерфейса).

   
   listen_addresses = '*'

Сохраняем изменения сочетанием клавиш CTRL+O и подтверждаем нажатием Enter, затем просто закрываем редактор nano сочетанием клавиш CTRL+X.

Теперь давайте разрешим подключение из сети 10.0.2.0/24 с методом аутентификации md5. Для этого открываем файл pg_hba.conf

   
   nano /etc/postgresql/10/main/pg_hba.conf

Ищем вот такие строки.

И вносим следующие изменения (если IPv6 Вы не будете использовать, то можете закомментировать соответствующие строки знаком #).

Где, 10.0.2.0/24 адрес сети, из которой будет происходить подключение к текущему серверу PostgreSQL. Сохраняем изменения сочетанием клавиш CTRL+O, подтверждаем нажатием Enter и закрываем редактор nano сочетанием клавиш CTRL+X.

Перезапускаем PostgreSQL

   
   systemctl restart postgresql

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