Postgresql – Default Postgres privileges not showing on \ddp

postgresql-9.5

As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using:

CREATE USER foo WITH PASSWORD 'foo';
\connect bar;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo;

and I can connect to DB with no issues. Then I taught I must have connection privilege in the default privilege set, since I didn't grant connect on database explicitly.

When I do \ddp I get 0 rows, and as far as I understood it is a way of checking default privileges per this page.

Can someone help me understand how connecting to the DB is permitted when I haven't explicitly allowed it?

Best Answer

According to the documentation, PostgreSQL does the following for PUBLIC:

PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains).

This implies that to prevent a new user to be able to connect to any database following must be run for each database:

revoke connect on database <db_name> from public;
Related Question