pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedmanaging multiple db developers
10+ messages / 5 participants
[nested] [flat]
* managing multiple db developers
@ 2019-01-15 17:43 Mark Phillips <mark.phillips@mophilly.com>
0 siblings, 1 reply; 10+ messages in thread
From: Mark Phillips @ 2019-01-15 17:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
I am wondering how to accomplish a similar arrangement in an postgres cluster.
Thank you,
- Mark Phillips
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 18:07 David G. Johnston <david.g.johnston@gmail.com>
parent: Mark Phillips <mark.phillips@mophilly.com>
0 siblings, 3 replies; 10+ messages in thread
From: David G. Johnston @ 2019-01-15 18:07 UTC (permalink / raw)
To: Mark Phillips <mark.phillips@mophilly.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips
<mark.phillips@mophilly.com> wrote:
>
> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
>
> I am wondering how to accomplish a similar arrangement in an postgres cluster.
I recommend leveraging the fact that PostgreSQL lacks per-instance
licensing fees and have each developer run (more or less)
apt-get install postgresql
on their personal development virtual machine
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 18:14 S. Bob <sbob@quadratum-braccas.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: S. Bob @ 2019-01-15 18:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
https://www.postgresql.org/docs/11/sql-createschema.html
On 1/15/19 11:07 AM, David G. Johnston wrote:
> On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips
> <mark.phillips@mophilly.com> wrote:
>> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
>>
>> I am wondering how to accomplish a similar arrangement in an postgres cluster.
> I recommend leveraging the fact that PostgreSQL lacks per-instance
> licensing fees and have each developer run (more or less)
>
> apt-get install postgresql
>
> on their personal development virtual machine
>
> David J.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 20:16 suresh neravati <suresh.neravati@yahoo.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: suresh neravati @ 2019-01-15 20:16 UTC (permalink / raw)
To: Mark Phillips <mark.phillips@mophilly.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
You can create individual database for each developer. That works as a cluster, but you can specify smaller memory numbers for each developer.
On Tuesday, January 15, 2019, 10:08:05 AM PST, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips
<mark.phillips@mophilly.com> wrote:
>
> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
>
> I am wondering how to accomplish a similar arrangement in an postgres cluster.
I recommend leveraging the fact that PostgreSQL lacks per-instance
licensing fees and have each developer run (more or less)
apt-get install postgresql
on their personal development virtual machine
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 20:29 Mark Phillips <mark.phillips@mophilly.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Mark Phillips @ 2019-01-15 20:29 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Jan 15, 2019, at 10:07 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> I recommend leveraging the fact that PostgreSQL lacks per-instance
> licensing fees and have each developer run (more or less)
Thank you for the suggestion.
That has worked for us to a degree. We ran into situations where the developer had configuration issues and/or the dba was able to determine where “missing” changes came from.
Cheers,
- Mark=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 20:31 Mark Phillips <mark.phillips@mophilly.com>
parent: S. Bob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 10+ messages in thread
From: Mark Phillips @ 2019-01-15 20:31 UTC (permalink / raw)
To: S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin@lists.postgresql.org
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”.
- Mark=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 20:33 Mark Phillips <mark.phillips@mophilly.com>
parent: suresh neravati <suresh.neravati@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mark Phillips @ 2019-01-15 20:33 UTC (permalink / raw)
To: suresh neravati <suresh.neravati@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Jan 15, 2019, at 12:16 PM, suresh neravati <suresh.neravati@yahoo.com> wrote:
>
> You can create individual database for each developer. That works as a cluster, but you can specify smaller memory numbers for each developer.
Thank you. We have used that approach some, but I will revisit the idea with an eye on improving the use of logon and group roles.
- Mark=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 21:52 S. Bob <sbob@quadratum-braccas.com>
parent: Mark Phillips <mark.phillips@mophilly.com>
1 sibling, 1 reply; 10+ messages in thread
From: S. Bob @ 2019-01-15 21:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/15/19 1:31 PM, Mark Phillips wrote:
> On Jan 15, 2019, at 10:14 AM, S. Bob <sbob@quadratum-braccas.com
> <mailto: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”.
>
> - Mark
You'll need to augment setting the schema with setting the search_path,
you can also set a default search_path for a user
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-15 22:13 Mark Phillips <mark.phillips@mophilly.com>
parent: S. Bob <sbob@quadratum-braccas.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mark Phillips @ 2019-01-15 22:13 UTC (permalink / raw)
To: S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin@lists.postgresql.org
On Jan 15, 2019, at 1:52 PM, S. Bob <sbob@quadratum-braccas.com> wrote:
>
> You'll need to augment setting the schema with setting the search_path, you can also set a default search_path for a user
Thank you. I will work with that.
- Mark=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: managing multiple db developers
@ 2019-01-17 09:58 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mark Phillips <mark.phillips@mophilly.com>
1 sibling, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2019-01-17 09:58 UTC (permalink / raw)
To: Mark Phillips <mark.phillips@mophilly.com>; S. Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-01-17 09:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-15 17:43 managing multiple db developers Mark Phillips <mark.phillips@mophilly.com>
2019-01-15 18:07 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-15 18:14 ` S. Bob <sbob@quadratum-braccas.com>
2019-01-15 20:31 ` Mark Phillips <mark.phillips@mophilly.com>
2019-01-15 21:52 ` S. Bob <sbob@quadratum-braccas.com>
2019-01-15 22:13 ` Mark Phillips <mark.phillips@mophilly.com>
2019-01-17 09:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-01-15 20:16 ` suresh neravati <suresh.neravati@yahoo.com>
2019-01-15 20:33 ` Mark Phillips <mark.phillips@mophilly.com>
2019-01-15 20:29 ` Mark Phillips <mark.phillips@mophilly.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox