Preventing users from altering default privileges on PostgreSQL

Question

I'm testing database permissions on PostgreSQL and i'm trying to prevent a common user (readuser) from executing an 'ALTER DEFAULT PRIVILEGES' statement. However i cannot find a way to revoke this specific permission, and couldn't find anything about it on documentation.

I started a local PostgreSQL 11.2 instance, removed connect permisssions, created a database testdb and revoked table creation on the public schema.

revoke connect on database postgres from public;

create database testdb with template template0 --lc_collate "pt_BR.utf8" lc_ctype "pt_BR.utf8";

revoke connect on database testdb from public;

\c :database

revoke all on schema public from public;
grant all on schema public to postgres;

create schema private;

After that, I created a user with read permissions only:

create user readuser
       with nosuperuser
            nocreatedb
            nocreaterole
            noreplication
            login
            encrypted password 'testpassword';

grant connect
   on database testdb
   to readuser;

Then create a schema testschema and granted read permissions on it's tables:

grant usage
   on schema testschema
   to readuser;

grant select
   on all tables
   in schema testschema
   to readuser;

Even though i only set read permissions on all schemas and tables, the 'readuser' user can still perform 'alter default privileges' query without a permission error:

alter default privileges in schema testschema grant select on tables to readuser;
ALTER DEFAULT PRIVILEGES

I would like some help on preventing a user from altering it's default privileges, so that it cannot mess up permissions for tables created in the future.

William William · Accepted Answer · 2019-07-19T20:02:15

Try this by revoking the EXECUTE from the role postgres that granted the default privilege of execute to readuser

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA testschema REVOKE EXECUTE ON FUNCTIONS FROM readuser;

Preventing users from altering default privileges on PostgreSQL

1 Answers