public inbox for [email protected]help / color / mirror / Atom feed
Question about permissions in the Schema 3+ messages / 3 participants [nested] [flat]
* Question about permissions in the Schema @ 2025-06-03 11:44 Sabyasachi Mukherjee <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Sabyasachi Mukherjee @ 2025-06-03 11:44 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about permissions in the Schema @ 2025-06-03 12:02 Holger Jakobs <[email protected]> parent: Sabyasachi Mukherjee <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Holger Jakobs @ 2025-06-03 12:02 UTC (permalink / raw) To: [email protected] Am 03.06.25 um 13:44 schrieb Sabyasachi Mukherjee: > 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 -- Holger Jakobs, Bergisch Gladbach ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about permissions in the Schema @ 2025-06-03 13:36 Laurenz Albe <[email protected]> parent: Sabyasachi Mukherjee <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Laurenz Albe @ 2025-06-03 13:36 UTC (permalink / raw) To: Sabyasachi Mukherjee <[email protected]>; Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-03 13:36 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-03 11:44 Question about permissions in the Schema Sabyasachi Mukherjee <[email protected]> 2025-06-03 12:02 ` Holger Jakobs <[email protected]> 2025-06-03 13:36 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox