Создание пользователя PostgreSQL с правами Read Only

Thank you for reading this post, don't forget to subscribe!

Для доступа к одной из баз данных, потребовалось создать пользователя с правами readonly, решение для PostgreSQL 9.6+ было найдено на stackoverflow


<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> Read_Only_User  <span class="hljs-keyword">WITH</span> ENCRYPTED <span class="hljs-keyword">PASSWORD</span> <span class="hljs-string">'STRONG_PASSWORD'</span>;
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">to</span> Read_Only_User;
<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">PRIVILEGES</span> <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">TABLES</span> <span class="hljs-keyword">TO</span> Read_Only_User;

<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">CONNECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">DATABASE</span> DATABASE_TO_READ <span class="hljs-keyword">to</span> Read_Only_User;

\c DATABASE_TO_READ

<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">PRIVILEGES</span> <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">TABLES</span> <span class="hljs-keyword">TO</span> Read_Only_User;
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">to</span> Read_Only_User; 
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">ALL</span> SEQUENCES <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">TO</span> Read_Only_User;
<span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">ALL</span> <span class="hljs-keyword">TABLES</span> <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">public</span> <span class="hljs-keyword">TO</span> Read_Only_User;

CREATE USER Read_Only_User WITH ENCRYPTED PASSWORD 'STRONG_PASSWORD';

GRANT USAGE ON SCHEMA public to Read_Only_User;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read_Only_User;

GRANT CONNECT ON DATABASE DATABASE_TO_READ to Read_Only_User;

\c DATABASE_TO_READ

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO Read_Only_User;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;

DATABASE_TO_READ — база данных, куда нужно дать доступ на чтение
Read_Only_User — пользователь используемый для доступа
STRONG_PASSWORD — пароль пользователя Read_Only_User

Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Centos

Создание пользователя PostgreSQL с правами Read Only

https://github.com/midnight47/