Re: has_database_privilege is true?

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