In the snippet below, the role
writer is a member of a superuser role named
admin role can insert rows into a table but the
writer role cannot insert rows into the same table. Why does the
writer role not inherit permissions from the superuser role
CREATE TABLE test_check_roles( id SERIAL NOT NULL ); CREATE ROLE admin SUPERUSER LOGIN; -- Insert succeeds as the admin role. SET ROLE admin; INSERT INTO test_check_roles (id) VALUES (DEFAULT); -- Insert fails as the writer role with: -- ERROR: permission denied for table test_check_roles CREATE ROLE writer LOGIN INHERIT; GRANT admin TO writer; SET ROLE writer; INSERT INTO test_check_roles (id) VALUES (DEFAULT);
Based on the documentation below (Postgres 12 - Role Membership), I expected
writer to have all privileges of the
admin user since the writer role has the INHERIT tag.
Member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles.
docker run --name pg-test -d --rm \ --env POSTGRES_HOST_AUTH_METHOD=trust \ -p 54321:5432 \ postgres:12.2 psql -h localhost -p 54321 -U postgres -d postgres \ -c "CREATE TABLE test_check_roles( id SERIAL NOT NULL )" \ -c "CREATE ROLE admin SUPERUSER LOGIN" \ -c "SET ROLE admin" \ -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)" \ -c "CREATE ROLE writer LOGIN INHERIT" \ -c "GRANT admin TO writer" \ -c "SET ROLE writer" \ -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)" docker stop pg-test
- Postgres role permission problem: Related to
ALTER DEFAULT PRIVILEGES.
- Why is a new user allowed to create a table? - Has a good answer describing that privileges aren't hierarchial.
If I run
CREATE TABLE as the admin role instead of the postgres role, the example works. It seems the permission are somewhere lost between
postgres owning the table, the
admin having superuser, and
writer being a member of admin. I don't follow why a table created by the
postgres role is accessible by
admin by not by