Mark Phillips wrote:
> On Jan 15, 2019, at 10:14 AM, S. Bob <sbob@quadratum-braccas.com> wrote:
> > PostgreSQL supports schema's as well, a schema in PostgreSQL is a simple namespace,
> > which is a different concept from Oracle but you can accomplish the same thing
> > with a schema, in particular via the "CREATE SCHEMA AUTHORIZATION" approach
>
> Thank you for the suggestion.
>
> My only concern about using pg schemas is the level of separation. Also, I have
> toyed with create schema but, to my ignorant mind, I don’t see the tables and such
> in the “dev schema”.
To emulate Oracle's behavior, create user x and his schema with
CREATE ROLE x LOGIN;
CREATE SCHEMA x AUTHORIZATION x;
You don't have to mess with the search_path, because by default
the schema with the same name as the user is the first on the search_path anyway.
There are no "system privileges" to grant.
To be secure, remove the CREATE privilege on the schema "public":
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com