pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSchema/user/role
4+ messages / 4 participants
[nested] [flat]
* Schema/user/role
@ 2023-03-20 16:12 DAVID ROTH <adaptron@comcast.net>
0 siblings, 1 reply; 4+ messages in thread
From: DAVID ROTH @ 2023-03-20 16:12 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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".
Thanks
Dave Roth
> On 03/20/2023 10:15 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
>
> Real-time CDC is the difficult part. ora2pg (using views) can do a static migration. No coding (unless you consider clever use of bash to modify config files to be coding). I used it to migrate a 7TB db to Postgresql.
>
> https://ora2pg.darold.net/
>
> On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq <inzamam.shafiq@hotmail.com mailto:inzamam.shafiq@hotmail.com > wrote:
>
> > > Hi,
> >
> > Hope everyone is fine.
> >
> > Can someone help or guide regarding Open Source tools for Oracle to PostgreSQL migration with real time CDC. along with this is there any possibility to change the structure of the database? Let me explain a little more,
> >
> > We have an Oracle DB which is around 1TB and we want to migrate to PostgreSQL that have a new table structure, so we want to perform data transformation and real time CDC from Oracle to PostgreSQL. Do we have any good open source tool to achieve this with No Coding involved.??
> >
> > Thanks.
> >
> > Regards,
> >
> > Inzamam Shafiq
> > Sr. DBA
> >
> >
> > >
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema/user/role
@ 2023-03-20 17:22 David G. Johnston <david.g.johnston@gmail.com>
parent: DAVID ROTH <adaptron@comcast.net>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-03-20 17:22 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Mar 20, 2023 at 9:13 AM DAVID ROTH <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.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema/user/role
@ 2023-03-20 18:57 Bryn Llewellyn <bryn@yugabyte.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bryn Llewellyn @ 2023-03-20 18:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: DAVID ROTH <adaptron@comcast.net>; pgsql-general list <pgsql-general@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Schema/user/role
@ 2023-03-20 19:35 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Bryn Llewellyn <bryn@yugabyte.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2023-03-20 19:35 UTC (permalink / raw)
To: pgsql-general list <pgsql-general@lists.postgresql.org>
https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
On Mon, Mar 20, 2023 at 2:57 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> > 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.
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-03-20 19:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-20 16:12 Schema/user/role DAVID ROTH <adaptron@comcast.net>
2023-03-20 17:22 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-20 18:57 ` Bryn Llewellyn <bryn@yugabyte.com>
2023-03-20 19:35 ` Ron Johnson <ronljohnsonjr@gmail.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