Обсуждение: Users and object privileges maintenance
Hello All,
We were having past experience in Oracle and are newly getting moved to postgres database. In there we have schema which alternately also called as Users and the super user was sys/system through which the dev team never login but only DBA does. And DBA used to create some functional users to which write access is provided to the table/view when they are created through Grant command. These functional users are used by applications to do the DML etc. Other users are given read only privileges only so as to not do DML but just view the data.
Again these privileges are not given directly to the objects but given through roles for better maintenance and control purposes. We had some users also which were not supposed to see some sensitive attribute, so we created views(by excluding the sensitive column) on top of those and only gave the read-only access to the views but not to the underlying tables.
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 Or any different strategy is needed. And if it's different in RDS Postgres as compared to opensource one? If any document to follow for the same.
We were having past experience in Oracle and are newly getting moved to postgres database. In there we have schema which alternately also called as Users and the super user was sys/system through which the dev team never login but only DBA does. And DBA used to create some functional users to which write access is provided to the table/view when they are created through Grant command. These functional users are used by applications to do the DML etc. Other users are given read only privileges only so as to not do DML but just view the data.
Again these privileges are not given directly to the objects but given through roles for better maintenance and control purposes. We had some users also which were not supposed to see some sensitive attribute, so we created views(by excluding the sensitive column) on top of those and only gave the read-only access to the views but not to the underlying tables.
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 Or any different strategy is needed. And if it's different in RDS Postgres as compared to opensource one? If any document to follow for the same.
Regards
Lok
On Sat, Feb 17, 2024 at 10:50 PM Lok P <loknath.73@gmail.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
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@gmail.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 similarway 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
On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
1. Schemas and users are not tied together, they are orthoginal concepts. Just like operating
system users and directories (and indeed all other databases).
Forgot about that one! OTOH, you could say PostgreSQL has tied USERs and ROLEs, while ORACLE didn't :)
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.
Personally I find that confusing. I wouldn't mind schema objects all belonging to the one owner.
Or being to enforce that, as an opt-in option. Not sure what's the benefits of different owners for a schemas objects are.
> 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.
To be fair, these days, anyone should use https://oracle.github.io/odpi/, not OCI directly.
And at the time, OCCI was lagging behind OCI, but maybe it doesn't anymore. We're C++ here, not C.
Also, as a C++ dev, I use higher level wrappers, easier, type-safe, just as efficient. My own in both cases.
Once you've encapsulated your hard-earned knowledge of the low-level API, you forget about it, good or bad.
But when you get a crash in OCI, it's much harder to diagnose it, it being so pointer based and closed source...
Plus with the protocol being OSS and documented, one can always write a libpq alternative, be it in Go, Rust, JS/TS,
or C++ using ASIO (POC at https://github.com/anarthal/postgres-asio), to fit the client environment better. --DD
On 18.02.2024 15:19, Dominique Devienne wrote:
On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: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.Personally I find that confusing. I wouldn't mind schema objects all belonging to the one owner.Or being to enforce that, as an opt-in option. Not sure what's the benefits of different owners for a schemas objects are.
The situation is even more confusing :-) Roles with membership in object owner role acts as an owner. For example they can modify or drop object or grant/revoke access to object.
-- Pavel Luzanov Postgres Professional: https://postgrespro.com
On Sun, Feb 18, 2024 at 3:27 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote:
On 18.02.2024 15:19, Dominique Devienne wrote:On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: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.Personally I find that confusing. I wouldn't mind schema objects all belonging to the one owner.Or being [able] to enforce that, as an opt-in option. Not sure what's the benefits of different owners for a schemas objects are.The situation is even more confusing :-) Roles with membership in object owner role acts as an owner. For example they can modify or drop object or grant/revoke access to object.
Well, membership in a role mean you can "become that role", no? Thus this seems logical,
and not confusing to me, that you can act as the owner, since you SET ROLE to the owner.
Or am I missing something else? --DD
On 18.02.2024 17:40, Dominique Devienne wrote:
Well, membership in a role mean you can "become that role", no? Thus this seems logical,and not confusing to me, that you can act as the owner, since you SET ROLE to the owner.
They may acts as the owner even without explicit SET ROLE to the owner. It wasn't obvious to me when I came to postgres from oracle.
-- Pavel Luzanov Postgres Professional: https://postgrespro.com
On 2/18/24 02:12, Dominique Devienne wrote: > On Sat, Feb 17, 2024 at 10:50 PM Lok P <loknath.73@gmail.com > <mailto:loknath.73@gmail.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. > More information on a couple of points I have found trips people up on occasion. > > "DBAs" in PostgreSQL are ROLEs with the SUPERUSER privilege. (e.g. the > built-in postgres ROLE) This is a convention not a rule. More specifically it is the name of the OS user that runs initdb. It can be overridden by using -U <some_name) with initdb. I bring it up because this causes confusion, in particular with the folks using the Homebrew and Postgres.app packages found here: https://www.postgresql.org/download/macosx/ In those cases the default superuser generally is the name of the OS user that downloaded and installed the package. > > USERs are ROLEs with the LOGIN privilege. Again a convention not a rule. This harks back to the days(v 8.0-) when Postgres had groups and users. Per https://www.postgresql.org/docs/current/sql-createuser.html "CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE." It is entirely possible to CREATE USER ... WITH NOLOGIN ... -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Feb 18, 2024 at 4:33 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote:
On 18.02.2024 17:40, Dominique Devienne wrote:Well, membership in a role mean you can "become that role", no? Thus this seems logical,and not confusing to me, that you can act as the owner, since you SET ROLE to the owner.They may acts as the owner even without explicit SET ROLE to the owner. It wasn't obvious to me when I came to postgres from oracle.
Well, that depends on INHERIT on the ROLE, and since v16 on the membership GRANT, probably.
But that's a good point, worth mentioning indeed. As I wrote, understanding INHERIT is important. --DD
On Sun, Feb 18, 2024, 11:35 Dominique Devienne <ddevienne@gmail.com> wrote:
On Sun, Feb 18, 2024 at 4:33 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote:On 18.02.2024 17:40, Dominique Devienne wrote:Well, membership in a role mean you can "become that role", no?
Since v16, no. There is now a grant option that controls this capability.
Thus this seems logical,and not confusing to me, that you can act as the owner, since you SET ROLE to the owner.They may acts as the owner even without explicit SET ROLE to the owner. It wasn't obvious to me when I came to postgres from oracle.Well, that depends on INHERIT on the ROLE, and since v16 on the membership GRANT, probably.
Prior to v16 only the attribute mattered. Since v16 only the membership option matters.
David J.
On 18.02.2024 15:19, Dominique Devienne wrote:
...
...
...
...
But you of course can connect only to DBs you have explicit access to. Just beware that before 15, the built-in PUBLICrole, which all ROLEs are implicitly members of, had implicit access to newly created DBs.
It's still the same concerning default database CONNECT privilege for PUBLIC.
You must confuse with the fact that v15+ removesPUBLICcreation permission on thepublicschema
Regards
Gilles