Re: has_database_privilege is true? - Mailing list pgsql-general

Greetings,

* Paul Förster (paul.foerster@gmail.com) wrote:
> > 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

If you want has_database_privilege() to return that the user doesn't
have access due to missing GRANT privileges, yes.

An alternative would be to query against pg_roles and check the
'rolcanlogin' privilege/column.  Again, a user has to have both in order
to be able to actually log in.

Thanks,

Stephen