RE: Remove default privilege from DB

Hi

From: Durumdara [mailto:durumdara(at)gmail(dot)com]
Sent: Donnerstag, 15. Februar 2018 12:41
To: Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Remove default privilege from DB

Dear Charles!

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org> >:

Hi

From: Durumdara [mailto:durumdara(at)gmail(dot)com <mailto:durumdara(at)gmail(dot)com> ]
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General <pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >
Subject: Remove default privilege from DB

Hello!

I need to remove default privileges from a Database.

After that some of them remains.

Default access privileges

Owner | Schema | Type | Access privileges

------------+--------+----------+-------------------

postgres | | function | =X/postgres

postgres | | sequence |

postgres | | table |

postgres | | type | =U/postgres

suser | | function | =X/suser

suser | | sequence |

suser | | table |

suser | | type | =U/suser

How to completely remove the last items?

Could you send me one example?

I assume

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

Bye

Charles

After that:

Default access privileges

Owner | Schema | Type | Access privileges

------------+--------+----------+-------------------

suser | | function |

suser | | sequence |

suser | | table |

suser | | type |

(4 rows)

What are they?

I am a bit puzzled. I checked the documentation and execute on function is the hard wired default privilege.

https://www.postgresql.org/docs/current/static/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC. 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 USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges.

So after revoking it from public you should actually get an ACL like {suser=X/suser} and the entry for the grants should make it disapper.

Example:

charles(at)db(dot)localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

-------+--------+------+-------------------

(0 rows)

charles(at)db(dot)localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

------------+-----------------+---------------+-----------

(0 rows)

charles(at)db(dot)localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles(at)db(dot)localhost=# \ddp

Default access privileges

Owner | Schema | Type | Access privileges

----------+--------+----------+---------------------

charlesc | | function | charlesc=X/charlesc

(1 row)

Now only user charlesc can execute (new) functions created by himself. This is the most typical use case when restricting access to self-made functions.

charles(at)db(dot)localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

------------+-----------------+---------------+-----------------------

25269137 | 0 | f | {charlesc=X/charlesc}

(1 row)

charles(at)db(dot)localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles(at)db(dot)localhost=# \ddp

Default access privileges

Owner | Schema | Type | Access privileges

-------+--------+------+-------------------

(0 rows)

charles(at)db(dot)localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

------------+-----------------+---------------+-----------

(0 rows)

Now again. everybody can execute functions created by charlesc.

What version of PostgreSQL are you using?
And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

again?

Regards
Charles

select * from pg_default_acl

24629;0;"r";"{}"

24629;0;"S";"{}"

24629;0;"f";"{}"

24629;0;"T";"{}"

24629 = suser | namespace 0 = none in document

Hmmm... It's very strange for me. I don't find any point which links this user to this database.

Do you have any idea?

Thanks

dd