public inbox for [email protected]
help / color / mirror / Atom feedFrom: Asad Ali <[email protected]>
To: somnath som <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: User Privileges Issue
Date: Wed, 4 Sep 2024 14:37:43 +0500
Message-ID: <CAJ9xe=tL62Nx3LX1nJOrpjC-C=z1JzA8k7F1oJgGt6TgUKd93w@mail.gmail.com> (raw)
In-Reply-To: <CABGa5dGsbneuEz6sVtSMSaWK3Mt1hRYiBWLVeb1DYikXSS313A@mail.gmail.com>
References: <CABGa5dGsbneuEz6sVtSMSaWK3Mt1hRYiBWLVeb1DYikXSS313A@mail.gmail.com>
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.
>
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: User Privileges Issue
In-Reply-To: <CAJ9xe=tL62Nx3LX1nJOrpjC-C=z1JzA8k7F1oJgGt6TgUKd93w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox