Re: Schema/user/role - Mailing list pgsql-general

> david.g.johnston@gmail.com wrote:
>
>> adaptron@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
thebest 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
defaultsearch_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
rolehierarchy 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
schemasand 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
governedby 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
schemas1 is not owned by r2). You have, at a coarser granularity, to also say "grant usage on schema s1 to r2". (This
isnice 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
synonymnotion. However, just as in ORCL there's a whole discussion about how nefarious actors can capture a synonym
witha bogus local object, so is there a similar discussion in PG about nefarious misuse of redefining the search path
(noprivilege governs this). This discussion is further complicated by the fact that "pg_temp" and "pg_catalog" are
inevitablyon the search path whether or not you mention them (and that when you don't, their positions in the search
orderis surprising). My personal conclusion is that you must always use a schema-qualified identifier for all objects
inreal application code (verbosity notwithstanding). This is rather like the ORCL practice never to create synonyms and
torefer 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
changethis. This is very useful for ad hoc testing when you're learning something, But it's a nuisance in the database
thata 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
operatorusing 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
setthe 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.