Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk044-006YFR-1m for pgsql-admin@arkaria.postgresql.org; Fri, 30 Aug 2024 11:45:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sk042-002p27-0S for pgsql-admin@arkaria.postgresql.org; Fri, 30 Aug 2024 11:45:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk041-002p1z-Je for pgsql-admin@lists.postgresql.org; Fri, 30 Aug 2024 11:45:14 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sk03y-002F9n-Oh for pgsql-admin@lists.postgresql.org; Fri, 30 Aug 2024 11:45:13 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-2d873dc644dso287430a91.3 for ; Fri, 30 Aug 2024 04:45:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1725018309; x=1725623109; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=k7cNor7b7uPf05RjaXnDOPTyukdxPOkwMqNQpHc41xs=; b=xiFuvTaLu0O5p8eLHg/ByQgzIJKs5djdO43xRTxtPmyY3nWG5MZqlYK8v78liB/9YK +q8fDUusBYSkShLQXuzx/xfQMTVqXP8fG+uQex19/5mya1AC1M54LD/X3r+zZAG1jE20 JcHOVE1BVt5QTerhgVElMTDSJCrcVqjkjILCXMf8qD6sFhfYhtEdIzflltMYGMyrjcgX yajTWPRu/GwepvUh8QjGC/UPkfz87OJNOUSy2itB9oPMUpMLUvK3cFCshSnQRPO3uk3x zCLCC1F6uKj9Ah3cqBeSdeqA+uG26bxn1bY4V6EmzNTDXF3G8JKzGOXksNAbchtwofPD SCHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725018309; x=1725623109; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=k7cNor7b7uPf05RjaXnDOPTyukdxPOkwMqNQpHc41xs=; b=KJNL3S5mHvlDjlCtZXb41cd+AZ38l9eIEvIk03QFb3nO00XFKcEnTOwUh2GcFhKLR8 Z33osmikyOHvl4PZ3tjQcuKF35dB2I1yiitpQncr0vhRBT+LSvK0W4CwJ9jJstuYNKXO 2IZrFw2bB86c1XivhvtB8pRP0IZ4Dl0osyCBxR1ghzIH/0mchxxQSGWymUwDfnJMJjKs QfBVwoDSA7b6XfxNU/PpWdgednoHZupuIR6GRm5+WCtJoG+3rUxrDIt4vhogmFec0xk5 dVGARRO2R5jJwzuQBe+Iq/wx9idbVeheI+DUPkROHyrQaWCPITJJi7epPxDKXVRL5ogG FxqQ== X-Gm-Message-State: AOJu0YyXoaatdQGT5qIz56igkA6lbA7onk0TgiShLporYw3WHhNcv72m iQsGZVRp4P4WJ/Pu7ayGwD3amTbFoJuVTJT1azxhngdrpk0pHRfEkePDwP7dnvUmq1c035tgCNq Lt0gSdPeMmbi66EKdPMqxPjleBJjZrYVaMA2J2g== X-Google-Smtp-Source: AGHT+IFVxhyXm3d78KQgOyzqGMQ/m3mFIiSr5h6GoKZYNAm8w7o7DVygEilosOfrsHMDkv/1uPVo9KqxoJ27YV0pXEs= X-Received: by 2002:a17:90a:e002:b0:2d8:7c53:14d2 with SMTP id 98e67ed59e1d1-2d87c531542mr827667a91.42.1725018309219; Fri, 30 Aug 2024 04:45:09 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Usman Khan Date: Fri, 30 Aug 2024 16:44:57 +0500 Message-ID: Subject: Re: User Privileges Issue To: somnath som Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000008d796b0620e51fbe" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008d796b0620e51fbe Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, You can use the following queries to check privileges. I have tested with my created user 'user01' *Check Role Attributes * postgres=3D# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication postgres-# FROM pg_roles postgres-# WHERE rolname =3D 'user01'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication ---------+----------+------------+---------------+-------------+-----------= --+---------------- user01 | f | t | f | f | t | f (1 row) *Check Database Privileges:* postgres=3D# SELECT datname, postgres-# has_database_privilege('user01', datname, 'CONNECT') AS connect, postgres-# has_database_privilege('user01', datname, 'CREATE') AS create, postgres-# has_database_privilege('user01', datname, 'TEMP') AS temp postgres-# FROM pg_database; datname | connect | create | temp -----------+---------+--------+------ postgres | t | f | t agens | t | f | t template1 | t | f | f template0 | t | f | f (4 rows) *Check Schema Privileges:* postgres=3D# SELECT nspname, postgres-# has_schema_privilege('user01', nspname, 'CREATE') AS create, postgres-# has_schema_privilege('user01', nspname, 'USAGE') AS usage postgres-# FROM pg_namespace; nspname | create | usage --------------------+--------+------- pg_toast | f | f pg_temp_1 | f | f pg_toast_temp_1 | f | f pg_catalog | f | t public | t | t information_schema | f | t (6 rows) On Fri, 30 Aug 2024 at 16:38, somnath som wrote: > We have one user like =E2=80=9CTest_User=E2=80=9D, Can I check what all p= reviliges are > there for =E2=80=9CTest_User=E2=80=9D. > > When running \du+ command then only can see for superuser, others user ar= e > not showing. > > Please provide me command to check what all previliges are there for a > user. > --0000000000008d796b0620e51fbe Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,
You can use the following queries to check privileges. I have tes= ted with my created user 'user01'

Check Role Attri= butes=C2=A0=C2=A0
postgres=3D# SELECT rolname, rolsuper, rolinherit,= rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
postgres-# FROM= pg_roles
postgres-# WHERE rolname =3D 'user01';
=C2=A0rolnam= e | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rol= replication
---------+----------+------------+---------------+---------= ----+-------------+----------------
=C2=A0user01 =C2=A0| f =C2=A0 =C2=A0= =C2=A0 =C2=A0| t =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| f =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | f =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | t =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f
(1 row)

Check Database Privileges:
postgres=3D# SELECT = datname,
postgres-# =C2=A0 =C2=A0 =C2=A0 =C2=A0has_database_privilege(&= #39;user01', datname, 'CONNECT') AS connect,
postgres-# =C2= =A0 =C2=A0 =C2=A0 =C2=A0has_database_privilege('user01', datname, &= #39;CREATE') AS create,
postgres-# =C2=A0 =C2=A0 =C2=A0 =C2=A0has_da= tabase_privilege('user01', datname, 'TEMP') AS temp
post= gres-# FROM pg_database;
=C2=A0 datname =C2=A0| connect | create | temp =
-----------+---------+--------+------
=C2=A0postgres =C2=A0| t =C2= =A0 =C2=A0 =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| t
=C2=A0agens =C2=A0 =C2=A0 = | t =C2=A0 =C2=A0 =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| t
=C2=A0template1 | t= =C2=A0 =C2=A0 =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| f
=C2=A0template0 | t = =C2=A0 =C2=A0 =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| f
(4 rows)
=
Check Schema Privileges:
post= gres=3D# SELECT nspname,
postgres-# =C2=A0 =C2=A0 =C2=A0 =C2=A0has_schem= a_privilege('user01', nspname, 'CREATE') AS create,
post= gres-# =C2=A0 =C2=A0 =C2=A0 =C2=A0has_schema_privilege('user01', ns= pname, 'USAGE') AS usage
postgres-# FROM pg_namespace;
=C2=A0= =C2=A0 =C2=A0 nspname =C2=A0 =C2=A0 =C2=A0 | create | usage
----------= ----------+--------+-------
=C2=A0pg_toast =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| f
=C2=A0pg_temp_1 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| f =C2=A0 =C2=A0 =C2=A0| f
=C2=A0pg_toast_temp_1 =C2=A0 = =C2=A0| f =C2=A0 =C2=A0 =C2=A0| f
=C2=A0pg_catalog =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | f =C2=A0 =C2=A0 =C2=A0| t
=C2=A0public =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | t =C2=A0 =C2=A0 =C2=A0| t
=C2=A0information_schema |= f =C2=A0 =C2=A0 =C2=A0| t
(6 rows)


On Fr= i, 30 Aug 2024 at 16:38, somnath som <som.somnath16@gmail.com> wrote:

We have one user like =E2=80=9CTest_User=E2=80=9D, Can I che= ck what all previliges are there for =E2=80=9CTest_User=E2=80=9D.

When running \du+ command then only can see for superuser, others user are not showing.

Please provide me command to check what all previliges are there for a user.

--0000000000008d796b0620e51fbe--