Last active
December 7, 2017 12:26
-
-
Save mattbretl/e9222c3e94f40bb3d38b66e3dfd5c8ec to your computer and use it in GitHub Desktop.
Strange behavior with inherited role permissions in information_schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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