Confused by the default privilege

Поиск

Список

Период

Сортировка

От	孙冰
Тема	Confused by the default privilege
Дата	15 июня 2021 г. 17:13:54
Msg-id	CA+czfDXX=ZE-KXW7Vruwd5k9zKx76kd1QMzYDU2akR10g+_+LA@mail.gmail.com обсуждение исходный текст
Ответы	Re: Confused by the default privilege (孙冰 <subi.the.dream.walker@gmail.com>)
Список	pgsql-hackers

Дерево обсуждения

Hi,

My use case is to create an isolated interface schema consisting of only views and functions (possibly many schemas, for multi-tenancy or multi-version), which has the minimal access exposure. To reduce the mental and maintenance burden, I am inclined to create one role per interface schema, instead of creating separate roles for the owner and the user. As a consequence, the default privileges must be revoked from the owner. Explicit revocation works just fine, except that it requires repetitive and forgettable statements for each object in the schema.

The default privileges come to rescue. It mostly works, despite a bit of confusion to me.

The ending contents are some experiments and demonstrations. To sum up, I have to either leave some non-critical privileges (e.g., trigger, references) by the default privilege mechanism or manually revoke all privileges, to stop the owner having all the default privileges. Plus, the first alternative is not applicable to functions because there is only one privilege for functions (execute).

To me, it is confusing and less intuitive. Or is there something I miss?

TL;DR

Revoking all default privileges is effectively equivalent to revoking nothing, because an empty string of access privileges is handled as 'default'.

Maybe 'NULL' for 'default', and '' (empty string) means nothing?

Regards.

------------------------------------------------------------------------------------------

drop owned by owner;
drop role if exists owner, guest;

create role owner;
create role guest;

drop schema if exists s;
create schema if not exists s authorization owner;

DROP OWNED DROP ROLE CREATE ROLE CREATE ROLE DROP SCHEMA CREATE SCHEMA

1. tables

1.1. no-op

set role to owner;
create or replace view s.v1 as select 1;

\dp+ s.v1

Schema	Name	Type	Access privileges	Column privileges	Policies
s	v1	view

select * from information_schema.role_table_grants where table_name='v1';

grantor	grantee	table_catalog	table_schema	table_name	privilege_type	is_grantable	with_hierarchy
owner	owner	postgres	s	v1	INSERT	YES	NO
owner	owner	postgres	s	v1	SELECT	YES	YES
owner	owner	postgres	s	v1	UPDATE	YES	NO
owner	owner	postgres	s	v1	DELETE	YES	NO
owner	owner	postgres	s	v1	TRUNCATE	YES	NO
owner	owner	postgres	s	v1	REFERENCES	YES	NO
owner	owner	postgres	s	v1	TRIGGER	YES	NO

set role to owner;
select * from s.v1;

?column?
1

1.2. default privilege: `revoke all from owner`

alter default privileges for user owner revoke all on tables from owner;
\ddp+

Owner	Schema	Type	Access privileges
owner		table

set role to owner;
create or replace view s.v2 as select 1;

\dp+ s.v2

Schema	Name	Type	Access privileges	Column privileges	Policies
s	v2	view

select * from information_schema.role_table_grants where table_name='v2';

grantor	grantee	table_catalog	table_schema	table_name	privilege_type	is_grantable	with_hierarchy
owner	owner	postgres	s	v2	INSERT	YES	NO
owner	owner	postgres	s	v2	SELECT	YES	YES
owner	owner	postgres	s	v2	UPDATE	YES	NO
owner	owner	postgres	s	v2	DELETE	YES	NO
owner	owner	postgres	s	v2	TRUNCATE	YES	NO
owner	owner	postgres	s	v2	REFERENCES	YES	NO
owner	owner	postgres	s	v2	TRIGGER	YES	NO

set role to owner;
select * from s.v2;

?column?
1

1.3. default privilege: `revoke all but one from owner`

alter default privileges for user owner revoke all on tables from owner;
alter default privileges for user owner grant trigger on tables to owner;
\ddp+

Owner	Schema	Type	Access privileges
owner		table	owner=t/owner

set role to owner;
create or replace view s.v3 as select 1;

\dp+ s.v3

Schema	Name	Type	Access privileges	Column privileges	Policies
s	v3	view	owner=t/owner

select * from information_schema.role_table_grants where table_name='v3';

grantor	grantee	table_catalog	table_schema	table_name	privilege_type	is_grantable	with_hierarchy
owner	owner	postgres	s	v3	TRIGGER	YES	NO

set role to owner;
select * from s.v3;

ERROR:  42501: permission denied for view v3
LOCATION:  aclcheck_error, aclchk.c:3461

1.4. manual `revoke all from owner`

alter default privileges for user owner revoke all on tables from owner;
\ddp+

Owner	Schema	Type	Access privileges
owner		table

set role to owner;
create or replace view s.v4 as select 1;

\dp+ s.v4

Schema	Name	Type	Access privileges	Column privileges	Policies
s	v4	view

select * from information_schema.role_table_grants where table_name='v4';

grantor	grantee	table_catalog	table_schema	table_name	privilege_type	is_grantable	with_hierarchy
owner	owner	postgres	s	v4	INSERT	YES	NO
owner	owner	postgres	s	v4	SELECT	YES	YES
owner	owner	postgres	s	v4	UPDATE	YES	NO
owner	owner	postgres	s	v4	DELETE	YES	NO
owner	owner	postgres	s	v4	TRUNCATE	YES	NO
owner	owner	postgres	s	v4	REFERENCES	YES	NO
owner	owner	postgres	s	v4	TRIGGER	YES	NO

set role to owner;
select * from s.v4;

?column?
1

So far, the situation is identical to s.v2.

set role to owner;
revoke all on table s.v4 from owner;

\dp+ s.v4

Schema	Name	Type	Access privileges	Column privileges	Policies
s	v4	view

select * from information_schema.role_table_grants where table_name='v4';

grantor

grantee

table_catalog

table_schema

table_name

privilege_type

is_grantable

with_hierarchy

set role to owner;
select * from s.v4;

ERROR:  42501: permission denied for view v4
LOCATION:  aclcheck_error, aclchk.c:3461

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

Предыдущее

От: Julien Rouhaud
Дата: 15 июня 2021 г., 17:07:32
Сообщение: Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

Следующее

От: 孙冰
Дата: 15 июня 2021 г., 17:19:46
Сообщение: Re: Confused by the default privilege

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

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