Remove public schema permission in PostgreSQL database to improve Security

Connect with the postgres server and run the \dn+ command to check schema permission

 sudo -u postgres psql

 \dn+

In PostgreSQL 15+, pg_database_owner controls the public schema. The =U indicates that PUBLIC has USAGE permission. In earlier versions, =UC meant PUBLIC also had CREATE permission.

For review PUBLIC permission on tables following query is used:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC'
AND table_schema = 'public'
LIMIT 10;

Revoke PUBLIC schema permissions for better security

Revoking USAGE from PUBLIC allows only specific users to access the schema.

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

Verify again

Note:  PUBLIC now has no permissions on the schema

This entry was posted in PostgreSQL on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply