Thread: has_database_privilege is true?

Hi,

I create a role "test" and it can't (intentionally) login. But why does has_database_privilege() return true if "test"
can'tconnect to the cluster and can't use any database? Is this a bug or am I doing something strange here? Any ideas? 

I'm on PostgreSQL 12.4.

Cheers,
Paul


postgres=# create role "test";
CREATE ROLE
postgres=# \du test
            List of roles
 Role name |  Attributes  | Member of
-----------+--------------+-----------
 test      | Cannot login | {}

postgres=# select has_database_privilege('test', 'postgres', 'connect');
 has_database_privilege
------------------------
 t
(1 row)

postgres=# select has_database_privilege('test', 'db01', 'connect');
 has_database_privilege
------------------------
 t
(1 row)

...

postgres=# select datname from pg_database;
  datname
-----------
 db02
 template0
 template1
 postgres
 db01
(5 rows)

Greetings,

* Paul Förster (paul.foerster@gmail.com) wrote:
> I create a role "test" and it can't (intentionally) login. But why does has_database_privilege() return true if
"test"can't connect to the cluster and can't use any database? Is this a bug or am I doing something strange here? Any
ideas?

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.

Thanks,

Stephen

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

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

Hi Stephen,

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

which I did (see previous post). Thanks very much for clearing that up.

Cheers,
Paul