Вход в оболочку psql
Создать пользователя и базу
Создать readonly пользователя
Права доступа на схему и её таблицы
Узнать размер базы
Закрыть подключения к базе
Схемы
Дамп конкретной схемы
Выполнить скрипт:


Вход в оболочку для работы с СУБД:

$ sudo -u postgres psql

Для выхода из оболочки служит команда \q.


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

# CREATE USER new_user WITH password 'qwerty';
# CREATE DATABASE new_database OWNER=new_user;
# GRANT ALL PRIVILEGES ON DATABASE new_database TO new_user;

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

# ALTER USER new_user WITH PASSWORD 'qwerty1';

Создать readonly пользователя:

# CREATE USER ro_user WITH password 'qwerty';
# ALTER ROLE "ro_user" WITH LOGIN;
# GRANT CONNECT ON DATABASE new_database TO ro_user;
# \c database
database=# GRANT USAGE ON SCHEMA public TO ro_user;

доступ к отдельным объектам внутри схемы:

database=# GRANT SELECT ON public.table1 TO ro_user;
database=# GRANT SELECT ON public.table2 TO ro_user;

доступ к схеме и всем объектам схемы (если есть ещё схемы):

database=# GRANT USAGE ON SCHEMA my_schema TO ro_user;
database=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA my_schema TO ro_user;
database=# GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO ro_user;
database=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO ro_user;

Cтрока ниже дает полный доступ ко всем вновь создаваемым таблицам в схеме:

database=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user;

Узнать размер базы:

# select pg_database_size('base');

Закрыть подключения к базе:
Версия 9.1 и ниже

# SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'ИМЯ БАЗЫ' AND procpid <> pg_backend_pid();

Версия 9.2+

# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'ИМЯ БАЗЫ' AND pid <> pg_backend_pid();

Схемы:
- создать схему:

database# CREATE SCHEMA my_schema;

- список схем:

# \connect database
# \dn

- выбрать схему:

database=# SET search_path to my_schema,public;

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

- посмотреть права на схему:

database=# \z

- дать доступ к схеме:

database=# GRANT USAGE ON SCHEMA my_schema,my_schema1 TO my_role;
database=# GRANT SELECT (или USAGE) ON ALL SEQUENCES IN SCHEMA my_schema TO my_role;
database=# GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO my_role;

- удалить доступ:

database=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA "my_schema" FROM "my_role";

Дамп конкретной схемы:

$ pg_dump -U postgres --schema=ИМЯ_СХЕМЫ -f ИМЯ_ФАЙЛА.sql ИМЯ_БАЗЫ

или

$ pg_dump -U postgres -n ИМЯ_СХЕМЫ -f ИМЯ_ФАЙЛА.sql ИМЯ_БАЗЫ

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

$psql -U postgres -d ИМЯ_БАЗЫ -f ИМЯ_ФАЙЛА