In this article I will share a few scripts in postgres databases that I think will help you manage your users.
The easiest way to list users is to run the following command.
But it will not give you detailed information.
1 | \du |
With the help of the following script you can see which databases the users have pemission to connect to.
1 2 3 4 5 6 | select pgu.usename as user_name, (select string_agg(pgd.datname, ',' order by pgd.datname) from pg_database pgd where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name from pg_user pgu order by pgu.usename; |
With the help of the following script, you can also list by database basis.
1 2 3 4 5 6 | select pgd.datname as database_name, (select string_agg(pgu.usename, ',' order by pgu.usename) from pg_user pgu where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name from pg_database pgd order by pgd.datname; |
In the above script, you can also query other permissions by typing one of the following permission types instead of connect.
1 2 3 4 5 6 7 8 9 10 11 | SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY or TEMP, EXECUTE, USAGE |
You can see the details of the user permissions in the database with the help of the following script.
1 2 3 4 5 6 7 8 | SELECT grantee ,table_catalog ,table_schema ,table_name ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges FROM information_schema.role_table_grants WHERE grantee != 'postgres' GROUP BY grantee, table_catalog, table_schema, table_name; |
1 | REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name; |
select pgu.usename as user_name,
(select string_agg(pgd.datname, ‘,’ order by pgd.datname)
from pg_database pgd
where has_database_privilege(pgu.usename, pgd.datname, ‘CONNECT’)) as database_name
from pg_user pgu
order by pgu.usename;
———————————————————————————————————————
psql (12) returns the following error:
———————————————————————————————————————
LINE 2: (select string_agg(pgu.usename, ‘,’ order by pgu.usen.