Search code examples
postgresqldatabase-administration

Revoke CONNECT permission to new DB by default

this is happening on newly created Postgres 13 instance on GCP's Cloud SQL.

I would like to disallow users to CONNECT to newly created databases. So I am modifying template database:

REVOKE ALL ON DATABASE template1 FROM public;

Then, I create new Database:

CREATE DATABASE mydb TEMPLATE template1;

Then I am checking CONNECT permissions for existing user:

SELECT datname
FROM pg_database
WHERE has_database_privilege('someuser', datname, 'CONNECT')

    datname
---------------
 cloudsqladmin
 postgres
 template0
 mydb

So as you can see someuser has CONNECT permission to mydb.

Why is this happening? How can I prevent users to connect to newly created databases?

Solution

  • The permissions on a database are not copied from the template database during CREATE DATABASE. Rather, all new databases have the same default permissions: CONNECT and TEMP for PUBLIC, and all permissions for the owner.

    There is no way to change that default, short of modifying the PostgreSQL code.