pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAligning grants and privileges
3+ messages / 2 participants
[nested] [flat]
* Aligning grants and privileges
@ 2024-02-27 20:40 veem v <veema0000@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: veem v @ 2024-02-27 20:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi,
We have two different types of DBA group in current on-premise Oracle
databases, one who deploy code in prod and highest level of privileges
(i.e having write access to the database objects, performing backup
recovery, export/import and performing other database maintenance jobs etc).
and other groups, who only have read only privileges (select catalogue role
i.e access to data dictionary views, run time and historical performance
views along) mainly look into the performance issues and more alligns
towards the functional part of the application , understands the database
design working closely with Application Development team and they have read
only access to the databases. They are expert in reading query execution
path and have privilege to run the explain command, reading sqlmonitor
report, creating profiles, baselines , adding hints to the query by
understanding inefficient execution path, fetching AWR report, tracing sql
queries, gathering object statistics, accessing OEM(oracle enterprise
manager ) to monitor performance. Suggesting appropriate indexes and
partitioning strategies for tables etc.
Now that we are moving few of the applications to Postgres on-premise
database and few to aws RDS postgres. What kind of privileges should we
provide to these two groups? Mainly we don't want the performance guys to
have the elevated privileges. I can understand , readonly access to the
table is something we can give to those users. Apart from that, are there
any specific privileges which we should provide, so as to enable look and
debug into all types of performance issues without any obstruction and at
the same time not giving elevated privileges?
Regards
Veem
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aligning grants and privileges
@ 2024-02-27 20:44 Adrian Klaver <adrian.klaver@aklaver.com>
parent: veem v <veema0000@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Klaver @ 2024-02-27 20:44 UTC (permalink / raw)
To: veem v <veema0000@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 2/27/24 12:40, veem v wrote:
> Hi,
> We have two different types of DBA group in current on-premise Oracle
> databases, one who deploy code in prod and highest level of privileges
> (i.e having write access to the database objects, performing backup
> recovery, export/import and performing other database maintenance jobs etc).
>
> and other groups, who only have read only privileges (select catalogue
> role i.e access to data dictionary views, run time and historical
> performance views along) mainly look into the performance issues and
> more alligns towards the functional part of the application ,
> understands the database design working closely with Application
> Development team and they have read only access to the databases. They
> are expert in reading query execution path and have privilege to run the
> explain command, reading sqlmonitor report, creating profiles, baselines
> , adding hints to the query by understanding inefficient execution path,
> fetching AWR report, tracing sql queries, gathering object statistics,
> accessing OEM(oracle enterprise manager ) to monitor performance.
> Suggesting appropriate indexes and partitioning strategies for tables etc.
>
> Now that we are moving few of the applications to Postgres on-premise
> database and few to aws RDS postgres. What kind of privileges should we
> provide to these two groups? Mainly we don't want the performance guys
> to have the elevated privileges. I can understand , readonly access to
> the table is something we can give to those users. Apart from that, are
> there any specific privileges which we should provide, so as to enable
> look and debug into all types of performance issues without any
> obstruction and at the same time not giving elevated privileges?
Read:
https://www.postgresql.org/docs/current/predefined-roles.html
>
> Regards
> Veem
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aligning grants and privileges
@ 2024-02-28 04:22 veem v <veema0000@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 3+ messages in thread
From: veem v @ 2024-02-28 04:22 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On Wed, 28 Feb, 2024, 2:14 am Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:
> On 2/27/24 12:40, veem v wrote:
> > Hi,
> > We have two different types of DBA group in current on-premise Oracle
> > databases, one who deploy code in prod and highest level of privileges
> > (i.e having write access to the database objects, performing backup
> > recovery, export/import and performing other database maintenance jobs
> etc).
> >
> > and other groups, who only have read only privileges (select catalogue
> > role i.e access to data dictionary views, run time and historical
> > performance views along) mainly look into the performance issues and
> > more alligns towards the functional part of the application ,
> > understands the database design working closely with Application
> > Development team and they have read only access to the databases. They
> > are expert in reading query execution path and have privilege to run the
> > explain command, reading sqlmonitor report, creating profiles, baselines
> > , adding hints to the query by understanding inefficient execution path,
> > fetching AWR report, tracing sql queries, gathering object statistics,
> > accessing OEM(oracle enterprise manager ) to monitor performance.
> > Suggesting appropriate indexes and partitioning strategies for tables
> etc.
> >
> > Now that we are moving few of the applications to Postgres on-premise
> > database and few to aws RDS postgres. What kind of privileges should we
> > provide to these two groups? Mainly we don't want the performance guys
> > to have the elevated privileges. I can understand , readonly access to
> > the table is something we can give to those users. Apart from that, are
> > there any specific privileges which we should provide, so as to enable
> > look and debug into all types of performance issues without any
> > obstruction and at the same time not giving elevated privileges?
>
> Read:
>
> https://www.postgresql.org/docs/current/predefined-roles.html
>
> >
> > Regards
> > Veem
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Thank you so much.
All the reader roles mentioned in the above url looks appropriate for
someone who needs to have high levels read privilege but no write
privilege. Hope same available in RDS too.
Vacuum analyze looks similar to stats gather , will those need special
privilege or the read privilege like pg_read_all_data, pg_read_all_stats,
pg_read_all_setting will be enough to be able to run vaccum on a table?
Is there some facilities to trace a running session and see the trace files
to analyze performance issues. And if any special role needed for that?
Regards
Veem
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-02-28 04:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-27 20:40 Aligning grants and privileges veem v <veema0000@gmail.com>
2024-02-27 20:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 04:22 ` veem v <veema0000@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