Re: User Privileges Issue - Mailing list pgsql-admin

CREATE USER test_user WITH PASSWORD 'test_password';

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 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;

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)

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,