On Sun, 2024-02-18 at 11:12 +0100, Dominique Devienne wrote: > On Sat, Feb 17, 2024 at 10:50 PM Lok P <loknath.73@xxxxxxxxx> wrote: > > We were having past experience in Oracle and are newly getting moved to postgres database. [...] > > So I just wanted to understand if these grants and privileges for objects/users are given and maintained in a similar way in postgres database > > Yes they are. Having done that move from Oracle to PostgreSQL, here are a few pointers. > > The one big difference between Oracle and PostgreSQL is that any user can see all catalogs (dictionaries), > and they can know about any objects in the DB, even when they don't themselves have access to them. > There's no DBA_, USER_, ALL_ views over the catalogs, to hide metadata of objects you don't have access to. > Beside that, the access controls are basically the same, ROLE and GRANT/REVOKE based. Three more additions concerning the fundamental differences (without claiming completeness): 1. Schemas and users are not tied together, they are orthoginal concepts. Just like operating system users and directories (and indeed all other databases). 2. In PostgreSQL, there is the important concept of ownership, which is not tied to the schema. The owner is the user who created the object. 3. Different from Oracle, functions are executable by PUBLIC by default, and run with the privileges of the invoker. > As parting thoughts, let me add that I enjoy PostgreSQL more than Oracle. And libpq way more than OCI. That goes without saying. I have never seen an API as terrible as OCI. As an aside, IBM has re-implemented the OCI API for DB2. I am sure that led to serial quitting and mental illness among IBM's developers. Yours, Laurenz Albe
