Hi Stephen, > On 23. Aug, 2020, at 16:28, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > > The role attribute system (where you see 'cannot login') is largely > independent from the GRANT system (which is what has_database_privilege > is checking). Both are required for a user to log in. I see. So I need to postgres=# revoke all privileges on database "postgres", "db01", "db02" from public; REVOKE and then select something like: postgres=# select postgres-# c.datname, postgres-# b.rolname, postgres-# ( postgres(# b.rolcanlogin and postgres(# has_database_privilege(b.rolname, c.datname, 'connect') postgres(# ) as use_db postgres-# from postgres-# pg_catalog.pg_roles b, postgres-# pg_catalog.pg_database c postgres-# where postgres-# not c.datistemplate and postgres-# c.datname != 'postgres' and postgres-# b.rolname ~ '^xxx-' postgres-# order by postgres-# 2, 1, 3; datname | rolname | use_db ---------+---------+-------- db01 | xxx-a | f db02 | xxx-a | f db01 | xxx-b | t db02 | xxx-b | f db01 | xxx-c | f db02 | xxx-c | f db01 | xxx-d | f db02 | xxx-d | f db01 | xxx-e | f db02 | xxx-e | f (10 rows) to get it right? At least the result look like expected now. Thanks very much. Cheers, Paul
