From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Sabyasachi Mukherjee <mukherjee(dot)sabyasachi(at)outlook(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Question about permissions in the Schema |
Date: | 2025-06-03 13:36:04 |
Message-ID: | 08b36ebfe3f64ce29f51bfcb661fc7917d942709.camel@cybertec.at |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Radoulov, Dimitre | 2025-06-04 12:30:45 | vacuumlo Permission Discrepancy Between Prod and Dev on AWS Aurora PostgreSQL |
Previous Message | Holger Jakobs | 2025-06-03 12:02:06 | Re: Question about permissions in the Schema |