Message-ID: <08b36ebfe3f64ce29f51bfcb661fc7917d942709.camel@cybertec.at>
Subject: Re: Question about permissions in the Schema
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Sabyasachi Mukherjee <mukherjee.sabyasachi@outlook.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 03 Jun 2025 15:36:04 +0200
In-Reply-To: 
 <PAXP194MB1230AFF50B791AD42BAF6EFE8C6DA@PAXP194MB1230.EURP194.PROD.OUTLOOK.COM>
References: 
	<PAXP194MB1230AFF50B791AD42BAF6EFE8C6DA@PAXP194MB1230.EURP194.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/08b36ebfe3f64ce29f51bfcb661fc7917d942709.camel%40cybertec.at>
Precedence: bulk

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 schema=
s it get a permission error except one.
> I have specifically run the Revoke command for the schema but still the u=
ser can access the
> data from that schema. I have run the command in PGAdmin. Also DBBeaver d=
oes 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.=C2=A0

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 =3D '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 =3D myroles.r
  )
  SELECT * FROM myroles;

Yours,
Laurenz Albe