public inbox for [email protected]help / color / mirror / Atom feed
User Privileges Issue 5+ messages / 5 participants [nested] [flat]
* User Privileges Issue @ 2024-08-29 11:52 somnath som <[email protected]> 0 siblings, 4 replies; 5+ messages in thread From: somnath som @ 2024-08-29 11:52 UTC (permalink / raw) To: Pgsql-admin <[email protected]> We have one user like “Test_User”, Can I check what all previliges are there for “Test_User”. 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User Privileges Issue @ 2024-08-30 11:43 Kashif Zeeshan <[email protected]> parent: somnath som <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Kashif Zeeshan @ 2024-08-30 11:43 UTC (permalink / raw) To: somnath som <[email protected]>; +Cc: Pgsql-admin <[email protected]> Hi Somnath Use the system catalog tables to get the information you need, for a reference please check the below link. https://stackoverflow.com/questions/40759177/postgresql-show-all-the-privileges-for-a-concrete-user Thanks Kashif Zeehsan On Fri, Aug 30, 2024 at 4:38 PM somnath som <[email protected]> wrote: > We have one user like “Test_User”, Can I check what all previliges are > there for “Test_User”. > > 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. > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User Privileges Issue @ 2024-08-30 11:44 Muhammad Usman Khan <[email protected]> parent: somnath som <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Muhammad Usman Khan @ 2024-08-30 11:44 UTC (permalink / raw) To: somnath som <[email protected]>; +Cc: Pgsql-admin <[email protected]> Hi, You can use the following queries to check privileges. I have tested with my created user 'user01' *Check Role Attributes * postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication postgres-# FROM pg_roles postgres-# WHERE rolname = 'user01'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication ---------+----------+------------+---------------+-------------+-------------+---------------- user01 | f | t | f | f | t | f (1 row) *Check Database Privileges:* postgres=# 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=# 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 <[email protected]> wrote: > We have one user like “Test_User”, Can I check what all previliges are > there for “Test_User”. > > 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. > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User Privileges Issue @ 2024-09-02 09:51 Laurenz Albe <[email protected]> parent: somnath som <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Laurenz Albe @ 2024-09-02 09:51 UTC (permalink / raw) To: somnath som <[email protected]>; Pgsql-admin <[email protected]> On Thu, 2024-08-29 at 17:22 +0530, somnath som wrote: > We have one user like “Test_User”, Can I check what all previliges are there for “Test_User”. > 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. There is nothing like that built into PostgreSQL. Perhaps the pg_permissions extension can help: https://github.com/cybertec-postgresql/pg_permissions Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: User Privileges Issue @ 2024-09-04 09:37 Asad Ali <[email protected]> parent: somnath som <[email protected]> 3 siblings, 0 replies; 5+ messages in thread From: Asad Ali @ 2024-09-04 09:37 UTC (permalink / raw) To: somnath som <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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 = '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 = '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 = '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 PM somnath som <[email protected]> wrote: > We have one user like “Test_User”, Can I check what all previliges are > there for “Test_User”. > > 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. > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-04 09:37 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-29 11:52 User Privileges Issue somnath som <[email protected]> 2024-08-30 11:43 ` Kashif Zeeshan <[email protected]> 2024-08-30 11:44 ` Muhammad Usman Khan <[email protected]> 2024-09-02 09:51 ` Laurenz Albe <[email protected]> 2024-09-04 09:37 ` Asad Ali <[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