Hi Stephen,
> On 23. Aug, 2020, at 16:28, Stephen Frost <sfrost@snowman.net> 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
