Skip to content

Instantly share code, notes, and snippets.

@mattbretl
Last active December 7, 2017 12:26
Show Gist options
  • Save mattbretl/e9222c3e94f40bb3d38b66e3dfd5c8ec to your computer and use it in GitHub Desktop.
Save mattbretl/e9222c3e94f40bb3d38b66e3dfd5c8ec to your computer and use it in GitHub Desktop.
Strange behavior with inherited role permissions in information_schema
-- With this schema in place..
-- https://github.com/postgraphql/postgraphql/blob/master/examples/forum/schema.sql
-- try running the following:
set role forum_example_postgraphql;
select distinct privilege_type from information_schema.role_table_grants where table_name = 'person';
-- privilege_type
------------------
-- DELETE
-- SELECT
-- UPDATE
--(3 rows)
-- OK
set role postgres;
create role person_creator_parent noinherit;
create role person_creator noinherit;
grant person_creator_parent to forum_example_postgraphql;
grant person_creator to person_creator_parent;
grant insert on table forum_example.person TO person_creator;
set role forum_example_postgraphql;
select distinct privilege_type from information_schema.role_table_grants where table_name = 'person';
-- privilege_type
------------------
-- DELETE
-- SELECT
-- UPDATE
--(3 rows)
-- OK
set role postgres;
alter role person_creator with inherit;
set role forum_example_postgraphql;
select distinct privilege_type from information_schema.role_table_grants where table_name = 'person';
-- privilege_type
------------------
-- DELETE
-- SELECT
-- UPDATE
--(3 rows)
set role postgres;
alter role person_creator_parent with inherit;
set role forum_example_postgraphql;
select distinct privilege_type from information_schema.role_table_grants where table_name = 'person';
-- OK
-- privilege_type
------------------
-- DELETE
-- SELECT
-- UPDATE
--(3 rows)
-- Hmmm, I expected to see INSERT here
set role person_creator_parent;
select distinct privilege_type from information_schema.table_privileges where table_name = 'person';
-- privilege_type
------------------
-- INSERT
--(1 row)
-- OK
set role forum_example_postgraphql;
select distinct privilege_type from information_schema.role_table_grants where table_name = 'person';
-- privilege_type
------------------
-- DELETE
-- INSERT
-- SELECT
-- UPDATE
--(4 rows)
-- Wait, now it works!?
-- Reset
set role postgres;
revoke all privileges on forum_example.person from person_creator;
revoke all privileges on forum_example.person from person_creator_parent;
drop role person_creator;
drop role person_creator_parent;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment