By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create role the_user;
create table game(
id int primary key,
is_finished boolean,
points numeric
);
insert into game values (1,true,1.1);
grant all on table game to the_user;
CREATE ROLE
CREATE TABLE
INSERT 0 1
GRANT
set role the_user;
select * from game;
update game set is_finished=false returning *;
update game set is_finished=true,points=1.2 returning *;
SET
| id | is_finished | points |
|---|---|---|
| 1 | t | 1.1 |
SELECT 1
| id | is_finished | points |
|---|---|---|
| 1 | f | 1.1 |
UPDATE 1
| id | is_finished | points |
|---|---|---|
| 1 | t | 1.2 |
UPDATE 1
set role postgres;
revoke all on game from the_user;
grant select,delete,insert,references(id,is_finished,points)on game to the_user;
grant update(id,is_finished)on game to the_user;--`update` on points not granted
SET
REVOKE
GRANT
GRANT
set role the_user;
update game set is_finished=false returning *;
SET
| id | is_finished | points |
|---|---|---|
| 1 | f | 1.2 |
UPDATE 1
update game set is_finished=false,points=1.3 returning *;
ERROR: permission denied for table game