public inbox for [email protected]
help / color / mirror / Atom feedUser Privileges Issue
5+ messages / 5 participants
[nested] [flat]
* User Privileges Issue
@ 2024-08-29 11:52 somnath som <[email protected]>
2024-08-30 11:43 ` Re: User Privileges Issue Kashif Zeeshan <[email protected]>
2024-08-30 11:44 ` Re: User Privileges Issue Muhammad Usman Khan <[email protected]>
2024-09-02 09:51 ` Re: User Privileges Issue Laurenz Albe <[email protected]>
2024-09-04 09:37 ` Re: User Privileges Issue Asad Ali <[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-29 11:52 User Privileges Issue somnath som <[email protected]>
@ 2024-08-30 11:43 ` Kashif Zeeshan <[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-29 11:52 User Privileges Issue somnath som <[email protected]>
@ 2024-08-30 11:44 ` Muhammad Usman Khan <[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-08-29 11:52 User Privileges Issue somnath som <[email protected]>
@ 2024-09-02 09:51 ` Laurenz Albe <[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-08-29 11:52 User Privileges Issue somnath som <[email protected]>
@ 2024-09-04 09:37 ` Asad Ali <[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