Search Postgresql Archives

RE: Remove default privilege from DB

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

 

From: Durumdara [mailto:durumdara@xxxxxxxxx]
Sent: Donnerstag, 15. Februar 2018 12:41
To: Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>
Cc: Postgres General <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: Remove default privilege from DB

 

Dear Charles!

 

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>:

Hi

 

From: Durumdara [mailto:durumdara@xxxxxxxxx]
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General <pgsql-general@xxxxxxxxxxxxxx>
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@db.localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

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

(0 rows)

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

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

(0 rows)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles@db.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@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype |       defaclacl

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

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

(1 row)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

         Default access privileges

Owner | Schema | Type | Access privileges

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

(0 rows)

 

charles@db.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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux