Postgres Pro
Downloads
Home   >   mailing lists

Thread: User Privileges Issue

User Privileges Issue

From
somnath som
Date:

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.

Re: User Privileges Issue

From
Kashif Zeeshan
Date:
Hi Somnath

Use the system catalog tables to get the information you need, for a reference please check the below link.


Thanks
Kashif Zeehsan

On Fri, Aug 30, 2024 at 4:38 PM somnath som <som.somnath16@gmail.com> 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.

Re: User Privileges Issue

From
Muhammad Usman Khan
Date:

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 <som.somnath16@gmail.com> 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.

Re: User Privileges Issue

From
Laurenz Albe
Date:
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

Re: User Privileges Issue

From
Asad Ali
Date:
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@gmail.com> 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.