Re: Schema/user/role

https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema

On Mon, Mar 20, 2023 at 2:57 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> > david(dot)g(dot)johnston(at)gmail(dot)com wrote:
> >
> >> adaptron(at)comcast(dot)net wrote:
> >>
> >> Is there any good reference to explain the best usage of each of these
> structures. I am coming from Oracle. What is the best analog to Oracle's
> "user".
> >
> > A schema is a namespace mechanism for objects. It has no relationship
> to roles aside from the possibility, if you so choose, to define a schema
> to have the same name as a role, in which case that schema becomes parts of
> that role's default search_path.
> >
> > There is no low-level difference between role and user. A user is a
> role with the login privilege.
>
> I came from Oracle, too. I soon came to see that these facts about PG are
> an improvement on Oracle Database:
>
> — In ORCL, "user" and "role" are distinct notions but in PG they collapse
> into one. This means that the nodes in a PG role hierarchy can all own
> objects. And schemas are among these owned objects.
>
> — In ORCL, "user" and "schema" are 1:1 and so, informally, collapse into a
> single notion. In PG, a role can own many schemas and this can be used to
> advantage as a classification scheme for objects with the same owner.
>
> However, there's more to say.
>
> — "set role" (to a role upon which the current role is a grantee) has to
> be re-learned. For example, it cannot be governed by a required password.
> And it has the same effect on "current_role" (versus "session_role") that a
> "security definer" subprogram has (but with no push-pop notion).
>
> — It's not enough to say, for example, "grant select on table s1.t to r2"
> (when s1.t is owned by, say, r1 and the schema s1 is not owned by r2). You
> have, at a coarser granularity, to also say "grant usage on schema s1 to
> r2". (This is nice because you can prevent r2 from using any of r1's
> objects with just a single "revoke".)
>
> — The "search_path" notion sounds at first to be appealing. And, loosely,
> it makes up for the fact that PG has no synonym notion. However, just as in
> ORCL there's a whole discussion about how nefarious actors can capture a
> synonym with a bogus local object, so is there a similar discussion in PG
> about nefarious misuse of redefining the search path (no privilege governs
> this). This discussion is further complicated by the fact that "pg_temp"
> and "pg_catalog" are inevitably on the search path whether or not you
> mention them (and that when you don't, their positions in the search order
> is surprising). My personal conclusion is that you must always use a
> schema-qualified identifier for all objects in real application code
> (verbosity notwithstanding). This is rather like the ORCL practice never to
> create synonyms and to refer to ORCL-shipped objects as "sys.dbms_output"
> and the like.
>
> — Closely related, a freshly-created database has a "public" schema
> (unless you customize the "template1" database to change this. This is very
> useful for ad hoc testing when you're learning something, But it's a
> nuisance in the database that a serious application uses.
>
> — Having said this, a caveat is probably needed for "pg_catalog" objects
> because even common-or-garden objects like the "+" operator are implemented
> ordinarily via various objects in the "pg_catalog" schema. And the syntax
> for invoking an operator using a schema-qualified identifier is baroque:
>
> select ((2+3) operator(pg_catalog.=) (1+4))::text;
>
> I decided, eventually, to use schema-qualified identifiers for everything
> except for "pg_catalog" objects and always to set the search path thus:
>
> set search_path = pg_catalog, pg_temp;
>
> and especially always to use that list as an attribute in a subprogram's
> source code.
>
>
>
>