Re: Users and object privileges maintenance

On Sat, Feb 17, 2024 at 10:50 PM Lok P <loknath(dot)73(at)gmail(dot)com> 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