User can always connect to the database [POSTGRES]

Question

I connect with the user "Postgres" and I create one user:

postgres=# CREATE DATABASE db_example ;
postgres=# CREATE USER user WITH PASSWORD 'user123';
postgres=# REVOKE CONNECT ON DATABASE db_example FROM user;
REVOKE
postgres=# \q

later, I try to connect with the user "user" to "db_example":

root@debian:/home/debian# psql -d db_example -U user -h localhost

psql (9.4.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

db_example=>

Why can I connect to the user "user" to "db_example"?

Thanks for the help and good day.

Possible duplicate of [Why new user in PostgreSQL can connect to all databases](http://stackoverflow.com/questions/6884020/why-new-user-in-postgresql-can-connect-to-all-databases) — cske, Nov 22 '16 at 15:22

score 1 · Accepted Answer · answered Nov 22 '16 at 15:28

That is because PUBLIC (i.e. everybody) has that privilege by default.

To remove that, run

REVOKE CONNECT ON DATABASE db_example FROM PUBLIC;

Run \l+ to see the privileges currently set on the databases. An empty setting means the default value: the owner can do everything, and PUBLIC can connect.

User can always connect to the database [POSTGRES]

1 Answers1