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 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.