Re: managing multiple db developers

Mark Phillips wrote:
> On Jan 15, 2019, at 10:14 AM, S. Bob <sbob(at)quadratum-braccas(dot)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