PostgreSQL: Grant CRUD operations only in one database

Question

Is there any way to grant CRUD operations to a user ONLY on a specific database? Let's asume there are 2 databases on my server: database_allowed and database_forbiden

I have created a user on postgreSQL, and granted this privileges:

CREATE ROLE crud_user LOGIN PASSWORD '###';


--Next commands are executed connected to the database
GRANT CONNECT ON DATABASE database_allowed TO crud_user ;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crud_user;

However, after executing the commands, I only get an error:

FATAL: role "crud_user" is not permitted to log in

I know I can execute: ALTER ROLE crud_user WITH LOGIN, but, despite the user can only execute CRUD operations on database_allowed, he can connect both, and see tablenames, etc...

Is there a way to prevent granting login on all databases?

Answer 1

Your crud_user can access the other database because of its PUBLIC permissions:

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). The object owner can, of course, REVOKE both default and expressly granted privileges.

^{(Postgres documentation Privileges)}

So you'll want to run

REVOKE CONNECT ON DATABASE database_forbidden FROM PUBLIC