Re: Grant read-only access to exactly one database amongst many

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Grant read-only access to exactly one database amongst many
Дата
Msg-id CAKFQuwa303ijRqh9fQy12pxwqVvjAAhxo0gxV4OnLB2sYWUdOw@mail.gmail.com
обсуждение исходный текст
Ответ на Grant read-only access to exactly one database amongst many  (Graham Leggett <minfrin@sharp.fm>)
Ответы Re: Grant read-only access to exactly one database amongst many  (Graham Leggett <minfrin@sharp.fm>)
Список pgsql-hackers
Дерево обсуждения
On Sun, Feb 4, 2024 at 5:04 PM Graham Leggett <minfrin@sharp.fm> wrote:
Hi all,

I have a postgresql 15 instance with two databases in it, and I have a need to grant read-only access to one of those databases to a given user.

To do this I created a dedicated role for readonly access to the database db1:

CREATE ROLE "dv_read_db1"
GRANT CONNECT ON DATABASE db1 TO dv_read_db1

This grant is basically pointless since by default all roles can connect everywhere via the PUBLIC pseudo-role.  You need to revoke that grant, or even alter it being given out by default.

 
Trouble is, I can create tables in db1 which is write access.

Since in v15 PUBLIC also gets CREATE on the public schema.

I can also connect to db2 (bad),

See my comment regarding the pointless grant in a default setup.

and I can enumerate the tables in db2 (bad),

Connect privilege grants reading all catalog data by design.


I appears the mechanism I am using above has insecure side effects.

It has, from your expectation, insecure defaults which you never changed.  We changed public schema in v16 but the ease-of-use database connecting remains.

David J.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Grant read-only access to exactly one database amongst many
Следующее
От: Sutou Kouhei
Дата:
Сообщение: meson: catalog/syscache_ids.h isn't installed