Re: User Privileges Issue

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 <som(dot)somnath16(at)gmail(dot)com> 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.
>