From: | Paul Förster <paul(dot)foerster(at)gmail(dot)com> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: has_database_privilege is true? |
Date: | 2020-08-23 14:48:56 |
Message-ID: | 6C303FE3-8767-40E0-8BA3-AE5F8AAE85A3@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Stephen,
> On 23. Aug, 2020, at 16:28, Stephen Frost <sfrost(at)snowman(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2020-08-23 14:52:19 | Re: has_database_privilege is true? |
Previous Message | Stephen Frost | 2020-08-23 14:28:34 | Re: has_database_privilege is true? |