Subject: User Privileges Issue

From: somnath som <[email-masked]>
Date: Thu, 29 Aug 2024 17:22:05 +0530

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.

---

From: Kashif Zeeshan <[email-masked]>
Date: Fri, 30 Aug 2024 16:43:36 +0500

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

---

From: Muhammad Usman Khan <[email-masked]>
Date: Fri, 30 Aug 2024 16:44:57 +0500

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)

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 02 Sep 2024 11:51:43 +0200

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.

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

---

From: Asad Ali <[email-masked]>
Date: Wed, 4 Sep 2024 14:37:43 +0500

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