Re: User Privileges Issue

[Asad Ali <asadalinagri@xxxxxxxxx>]

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.somnath16@xxxxxxxxx> 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.