Re: SELECT has_database_privilege('user01', 'db01', 'connect');

On Thursday, April 28, 2022, William Sescu (Suva) <william(dot)sescu(at)suva(dot)ch>
wrote:

> Hello %
>
> I am kinda confused, if I miss something. I have two questions:
>
> * A new created user should not have the connect privilege per default, if
> the user is not the owner of the db, right?
> * The function has_database_privilege should return false, if I revoke the
> connect privilege, right?
>
> (postgres(at)[local]:55042)[postgres]> CREATE USER user01 ENCRYPTED PASSWORD
> 'user01';
> CREATE ROLE
> (postgres(at)[local]:55042)[postgres]> CREATE DATABASE db01 WITH OWNER =
> postgres;
> CREATE DATABASE
> (postgres(at)[local]:55042)[postgres]> SELECT has_database_privilege('user01',
> 'db01', 'connect');
> has_database_privilege
> ------------------------
> t <= have expected false here
> (1 row)

Not how it works, see default privileges, namely for “public”.

> (postgres(at)[local]:55042)[postgres]> REVOKE CONNECT ON DATABASE db01 FROM
> user01;
> REVOKE
> (postgres(at)[local]:55042)[postgres]> SELECT has_database_privilege('user01',
> 'db01', 'connect');
> has_database_privilege
> ------------------------
> t <= have expected false here even more after the
> revoke statement
> (1 row)
>
> Or do I have some misunderstanding in regards of how it should work?
>

The privilege being found is inherited, you revoked a non-existent grant
which doesn’t do anything. You need to revoke the privilege being
inherited, from “public”.

David J.