1

In the snippet below, the role writer is a member of a superuser role named admin. The 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 admin?

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.

Reproducible example

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

Related questions

Observations

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 writer.

1

SUPERUSER doesn't inherit; it's an attribute of a role rather than a permission.

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute.

See https://www.postgresql.org/docs/current/role-membership.html

2
  • To clarify my understanding: admin can access tables created by the postgres role because its SUPERUSER attribute bypasses permission checks. The writer role doesn't inherit that ability. However, writer can access tables created by admin because table permissions are inherited. – Joe May 8 '20 at 19:01
  • Yes, but don't do it! writer will then be able to do everything admin can because you granted admin to writer. – Colin 't Hart May 8 '20 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.