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.
PostgreSQL is a "cluster" of databases. Like the PDBs introduced in Oracle 11 (or 12?) years ago.
There's a single "entry-point" of host[:port] to the "cluster", but you must always connect to a particular DB of that cluster.
But the USER (ROLE) and password (if any) you authenticate with is the same for any DB of that "cluster".
But you of course can connect only to DBs you have explicit access to. Just beware that before 15, the built-in PUBLIC
role, which all ROLEs are implicitly members of, had implicit access to newly created DBs.
The "scope" of ROLEs is kinda reversed between Oracle and PostgreSQL.
In Oracle, USERs are PDB (database) scoped by default, but can be "global", at the CDB level instead.
In PostgreSQL, USERs are "global" by default (i.e. across all databases).
And the database-specific ones exist, but are discouraged (and considered esoteric?)
(do note my Oracle experience has bit-rotted, since years old now)
"DBAs" in PostgreSQL are ROLEs with the SUPERUSER privilege. (e.g. the built-in postgres ROLE)
USERs are ROLEs with the LOGIN privilege.
ROLEs used solely for managing GRANTs to objects (e.g. tables) are sometimes called GROUPs,
but they are just ROLEs in the end. (and are typically NOLOGIN)
An important concept to understand in PostgreSQL is whether a ROLE is INHERIT or not.
I.e. whether membership (of a role A) in a role B gives implicit access to the GRANTs of B to A (INHERIT on A),
or whether one must SET ROLE B explicitly (NOINHERIT on A). Or the reverse, I confuse it all the time! :)
In PostgreSQL 16+, INHERIT became more granular, at the ROLE *membership* level.
To create ROLEs, a ROLE must have the CREATEROLE privilege. (separate from the stronger-still SUPERUSER privilege).
But once again, v16 brings important changes (restrictions) to CREATEROLE. Which complicates things.
(but are likely more inline with Oracle restrictions I kinda remember in that same area)
I refer you to the excellent PostgreSQL documentation for the rest. Less detailed than the Oracle one,
but still very dense (every word matters) and complete (you just often overlook things on the first reading, in my experience).
For the rest, PostgreSQL experts here can complement (or correct) the above.
I recommend you do your homework, and ask more specific questions,
as your open-ended one is less likely to get good answers I'm afraid.
As parting thoughts, let me add that I enjoy PostgreSQL more than Oracle. And libpq way more than OCI.
One final area of difference is that Oracle is battery-included (but at what cost?), while PostgreSQL is not.
There are tons of extensions, but that also leads to fragmentation, and when one must deal with Cloud-Managed PostgreSQL,
or PostgreSQL "clones" since you mention RDS, the list of extensions that "intersect" them all is quite restrictive.
Good luck with PostgreSQL. --DD
PS: I also refer you to this good and up-to-date PDF on ROLEs (recently posted on this list)
from Bruce Momjian from EDB: https://momjian.us/main/writings/pgsql/user.pdf
