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 1slmSe-0018zR-54 for pgsql-admin@arkaria.postgresql.org; Wed, 04 Sep 2024 09:38:00 +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 1slmSc-007Chu-Ml for pgsql-admin@arkaria.postgresql.org; Wed, 04 Sep 2024 09:37:59 +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 1slmSc-007CfS-AF for pgsql-admin@lists.postgresql.org; Wed, 04 Sep 2024 09:37:58 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1slmSa-00069S-MJ for pgsql-admin@lists.postgresql.org; Wed, 04 Sep 2024 09:37:58 +0000 Received: by mail-ej1-x62f.google.com with SMTP id a640c23a62f3a-a8a1d78e7b0so70530766b.3 for ; Wed, 04 Sep 2024 02:37:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725442676; x=1726047476; 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=ZKi48RfmiJugzm2HeK5JjAd8bK8qKfgNyBVKJYP6MEM=; b=mQZ+jjAM+jYczmRWiuUZhNvFlxXKnoZRGBtB2E3VrbWkIrVFmwMgKiiTX3dOnicHQ4 T9sCVmYin/gbOaf0DGPCNrCyVF2smt9fRomXNp0XwHTrhSVvW3YEKwhqY7LYRAb0wdAp iAaiBUV5jqJlFrn2Reeyksce/UB6QULsFgbsF+omlTgJxF8anWK5S0aCvgo38J5aWbWD qUcxfzhu7SI4vq0eUOf2MJ6wiO+72fJGtLVcnEC0zJrOWVDm9DJ+qwkoo5z4LP+5HKEL CBr3+HaiO5n/MwsARXbvS/W5N89xPhAzGp5qdf2BU68FJACiVMsDGgLr16T141+PI5UW wseg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725442676; x=1726047476; 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=ZKi48RfmiJugzm2HeK5JjAd8bK8qKfgNyBVKJYP6MEM=; b=ltOafqlEAZdZdxN7YBse4qsrsMpRSelKCvmjD/cWOO54z49EJzWtmdBhXeKQO/iUb8 mZlXtUz0O9fJTni7+tozRReQkcm5Sn7nkOsBVCkrOw4fv60g4YehNKvrKyWaYzMn6EZ0 PIcipTLKUO8FFlP5t5y/J0WWhr1v/C2/KrPKLmYAOpYJA+7ywg4SNwi+dkp7ih0UZeNZ rbT/YA6lvwWm2T72B5YhCyMUmiFOrteOnAkx8088SPFVlkSfRcSA5ohu08l0Azvdi4oU +Zm8r24XUgoO1JaJ30Fqo/fxdPd18zjc35op8BvDTl/vqOVxzayMAC/ZCEDykdT2TfVI +8uA== X-Gm-Message-State: AOJu0YxDs3G0uBbLzb6dmd7bRVfmyrEiTHMMNW2mutMFQ8OVwnfGfzyL M7p+6P2B5eOISqbFBIwcXG5wEZzvnEW1Yk1nDZ/TH0vGbnGQ14nKygcv4na/GVB6H4Kiv7EpA5b T4cr0Hct7pv43ZjD0WyT4bZm1bas= X-Google-Smtp-Source: AGHT+IGkSoasDb/QhhScdaVXygLf1PzlffUsXZsKuN/eLsBh0OJ0XzrJKHfbiULFVnzR6VXeFD6qaFqP3fEBS1plwwI= X-Received: by 2002:a17:907:f7a7:b0:a7a:9a78:4b5a with SMTP id a640c23a62f3a-a8a3f49d7e4mr140321066b.52.1725442675552; Wed, 04 Sep 2024 02:37:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Asad Ali Date: Wed, 4 Sep 2024 14:37:43 +0500 Message-ID: Subject: Re: User Privileges Issue To: somnath som Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000c1bd51062147edb7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c1bd51062147edb7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Somnath, I am sharing how it worked for me. Kindly let me know if you have any questions. *-- Create user * CREATE USER test_user WITH PASSWORD 'test_password'; *-- Grant some basic privileges* GRANT CONNECT ON DATABASE postgres TO test_user; GRANT USAGE ON SCHEMA public TO Test_User; GRANT CREATE ON SCHEMA public TO Test_User; CREATE TABLE public.test_table (id SERIAL PRIMARY KEY, data TEXT); GRANT SELECT, INSERT, UPDATE, DELETE ON public.test_table TO Test_User; *-- Create a sequence & Grant usage and update privileges * CREATE SEQUENCE public.test_seq; GRANT USAGE, SELECT, UPDATE ON SEQUENCE public.test_seq TO Test_User; *-- Create a function*CREATE FUNCTION public.test_function() RETURNS void AS $$ BEGIN RAISE NOTICE 'Test function executed'; END; $$ LANGUAGE plpgsql; *-- Grant execute privilege on the function*GRANT EXECUTE ON FUNCTION public.test_function() TO test_user; *-- Check Table Privileges:* SELECT grantee, table_catalog, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee =3D 'test_user'; grantee | table_catalog | table_schema | table_name | privilege_type -----------+---------------+--------------+------------+---------------- test_user | postgres | public | test_table | INSERT test_user | postgres | public | test_table | SELECT test_user | postgres | public | test_table | UPDATE test_user | postgres | public | test_table | DELETE (4 rows) *-- Check Schema Privileges:* SELECT * FROM information_schema.role_usage_grants WHERE grantee =3D 'test_user'; grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable ----------+-----------+----------------+---------------+-------------+-----= --------+----------------+-------------- postgres | test_user | postgres | public | test_seq | SEQUENCE | USAGE | NO (1 row) *-- Check All Object Privileges:* SELECT * FROM information_schema.table_privileges WHERE grantee =3D 'test_user'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ----------+-----------+---------------+--------------+------------+--------= --------+--------------+---------------- postgres | test_user | postgres | public | test_table | INSERT | NO | NO postgres | test_user | postgres | public | test_table | SELECT | NO | YES postgres | test_user | postgres | public | test_table | UPDATE | NO | NO postgres | test_user | postgres | public | test_table | DELETE | NO | NO (4 rows) Regards, Asad Ali On Fri, Aug 30, 2024 at 4:38=E2=80=AFPM 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. > --000000000000c1bd51062147edb7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Somnath,

I am sharing how it worked = for me.
Kindly let me know if you have any=C2=A0questions.

-- Create user=C2=A0
CREATE USER test_user WITH= PASSWORD 'test_password';

-- Grant= some basic=C2=A0privileges
GRANT CONNECT ON DATABASE postgre= s TO=C2=A0 test_user;
GRANT USAGE ON SCHEMA public TO Test_User;
GRAN= T CREATE ON SCHEMA public TO Test_User;
CREATE TABLE public.t= est_table (id SERIAL PRIMARY KEY, data TEXT);
GRANT SELECT, I= NSERT, UPDATE, DELETE ON public.test_table TO Test_User;

=
-- Create a sequence=C2=A0&=C2=A0 Grant usage and update privileges
CREATE SEQUENCE public.test_seq;
GRANT US= AGE, SELECT, UPDATE ON SEQUENCE public.test_seq TO Test_User;

-- Create a function
CREATE FUNCTION public.tes= t_function() RETURNS void AS $$
BEGIN
=C2=A0 =C2=A0 RAISE NOTICE '= ;Test function executed';
END;
$$ LANGUAGE plpgsql;

-- = Grant execute privilege on the function
GRANT EXECUTE ON FUNCTION pu= blic.test_function() TO test_user;

-- Check= Table Privileges:
SELECT grantee, table_catalog, table_schem= a, table_name, privilege_type FROM information_schema.role_table_grants WHE= RE grantee =3D 'test_user';
=C2=A0 grantee =C2=A0| table_catalog= | table_schema | table_name | privilege_type
-----------+--------------= -+--------------+------------+----------------
=C2=A0test_user | postgre= s =C2=A0 =C2=A0 =C2=A0| public =C2=A0 =C2=A0 =C2=A0 | test_table | INSERT=C2=A0test_user | postgres =C2=A0 =C2=A0 =C2=A0| public =C2=A0 =C2=A0 =C2= =A0 | test_table | SELECT
=C2=A0test_user | postgres =C2=A0 =C2=A0 =C2= =A0| public =C2=A0 =C2=A0 =C2=A0 | test_table | UPDATE
=C2=A0test_user |= postgres =C2=A0 =C2=A0 =C2=A0| public =C2=A0 =C2=A0 =C2=A0 | test_table | = DELETE
(4 rows)

-- Check Schema Pri= vileges:
SELECT * FROM information_schema.role_usage_grants W= HERE grantee =3D 'test_user';
=C2=A0grantor =C2=A0| =C2=A0grante= e =C2=A0| object_catalog | object_schema | object_name | object_type | priv= ilege_type | is_grantable
----------+-----------+----------------+------= ---------+-------------+-------------+----------------+--------------
= =C2=A0postgres | test_user | postgres =C2=A0 =C2=A0 =C2=A0 | public =C2=A0 = =C2=A0 =C2=A0 =C2=A0| test_seq =C2=A0 =C2=A0| SEQUENCE =C2=A0 =C2=A0| USAGE= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| NO
(1 row)

-- Check All Object Privileges:
SELECT * FROM infor= mation_schema.table_privileges WHERE grantee =3D 'test_user';
= =C2=A0grantor =C2=A0| =C2=A0grantee =C2=A0| table_catalog | table_schema | = table_name | privilege_type | is_grantable | with_hierarchy
----------+-= ----------+---------------+--------------+------------+----------------+---= -----------+----------------
=C2=A0postgres | test_user | postgres =C2= =A0 =C2=A0 =C2=A0| public =C2=A0 =C2=A0 =C2=A0 | test_table | INSERT =C2=A0= =C2=A0 =C2=A0 =C2=A0 | NO =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | NO
=C2= =A0postgres | test_user | postgres =C2=A0 =C2=A0 =C2=A0| public =C2=A0 =C2= =A0 =C2=A0 | test_table | SELECT =C2=A0 =C2=A0 =C2=A0 =C2=A0 | NO =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | YES
=C2=A0postgres | test_user | postgres = =C2=A0 =C2=A0 =C2=A0| public =C2=A0 =C2=A0 =C2=A0 | test_table | UPDATE =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | NO =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | NO
= =C2=A0postgres | test_user | postgres =C2=A0 =C2=A0 =C2=A0| public =C2=A0 = =C2=A0 =C2=A0 | test_table | DELETE =C2=A0 =C2=A0 =C2=A0 =C2=A0 | NO =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | NO
(4 rows)

= Regards,
Asad Ali

On Fri, Aug 30, 2024 at 4:38= =E2=80=AFPM 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.

--000000000000c1bd51062147edb7--