On Thursday, April 28, 2022, William Sescu (Suva) <
william.sescu@suva.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@[local]:55042)[postgres]> CREATE USER user01 ENCRYPTED PASSWORD 'user01';
CREATE ROLE
(postgres@[local]:55042)[postgres]> CREATE DATABASE db01 WITH OWNER = postgres;
CREATE DATABASE
(postgres@[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@[local]:55042)[postgres]> REVOKE CONNECT ON DATABASE db01 FROM user01;
REVOKE
(postgres@[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.