https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
On Mon, Mar 20, 2023 at 2:57 PM Bryn Llewellyn <b...@yugabyte.com> wrote: > > david.g.johns...@gmail.com wrote: > > > >> adapt...@comcast.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. > > > >
