Обсуждение: Question about permissions in the Schema

P {margin-top:0;margin-bottom:0;}

Hello,

I have a database with 5 different schemas. Each schema has the same set of tables, but the data is different.

I have created one user to access the data in the tables from one schema only.

The user can access the data from the target schema. For all other schemas it get a permission error except one.

I have specifically run the Revoke command for the schema but still the user can access the data from that schema. I have run the command in PGAdmin. Also DBBeaver does not any permission for the objects in the leaking schema for the user.

What could be wrong and how should I fix it.

I am running PG 17 on Linux. 

Thanks & Regards

Sabyasachi Mukherjee

Dear S. M.,

Even if a role (user) personally doesn't have any permissions to access a schema, memerships in other roles (groups) may allow access.

Check memberships and never, ever grant permissions to individual user roles, but only to non-login (group) roles. This minimises the number of grants and enhances clarity of permissions.

Kind Regards,

Holgger

On Tue, 2025-06-03 at 11:44 +0000, Sabyasachi Mukherjee wrote:
> I have a database with 5 different schemas. Each schema has the same set of tables, but the data is different.
> I have created one user to access the data in the tables from one schema only.
> The user can access the data from the target schema. For all other schemas it get a permission error except one.
> I have specifically run the Revoke command for the schema but still the user can access the
> data from that schema. I have run the command in PGAdmin. Also DBBeaver does not any permission for the objects
> in the leaking schema for the user.
> What could be wrong and how should I fix it.
> I am running PG 17 on Linux. 

To diagnose the problem, look at the permissions on that schema:

  SELECT a.grantor::regrole AS grantor,
         a.grantee::regrole AS grantee,
         a.privilege_type
  FROM pg_namespace AS s
     CROSS JOIN LATERAL aclexplode(s.nspacl) AS a
  WHERE s.nspname = 'schema_name';

Compare that with the user that is granted access and all its groups:

  WITH RECURSIVE myroles AS (
     SELECT current_user::regrole AS r
     UNION ALL
     SELECT m.roleid::regrole
     FROM pg_auth_members AS m
        JOIN myroles ON m.member = myroles.r
  )
  SELECT * FROM myroles;

Yours,
Laurenz Albe