Re: [GENERAL] Audit based on role
*You can tweak the following query to help you determine if your user is a
member of role/group 'module_dml'.*
*Then you can use it in a trigger function that does the logging.SELECT
g.rolname as group, u.rolname as user, r.admin_option as
admin, g.rolsuper as g_super, u.rolsuper as u_super FROM
pg_auth_members r JOIN pg_authid g ON (r.roleid = g.oid) JOIN pg_authid
u ON (r.member = u.oid) WHERE u.rolname = '{your_user}' AND g.rolname =
'module_dm;' ORDER BY 1, 2;*
On Mon, Aug 7, 2017 at 8:05 PM, Joe Conway wrote:
> On 08/07/2017 04:47 PM, anand086 wrote:
> > Only Insert/Update/Delete sqls are to be audited.
>
> You could get close to what you want, I think, by setting log_statement
> = mod for the users of interest, e.g. by doing:
>
> ALTER USER whomever SET log_statement = mod;
>
> See:
>
> https://www.postgresql.org/docs/9.6/static/runtime-
> config-logging.html#GUC-LOG-STATEMENT
>
> Note: "mod logs all ddl statements, plus data-modifying statements such
>as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM."
>
> Caveat: You would have to do this per user in that group. However you
> could write a query against the system catalogs though to loop through
> the members of the group and execute this statement against each one.
> Maybe rerun it periodically.
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
[GENERAL] Ora2pg estimation accuracy
Hi All, I am working on getting an estimation for large scale migration from oracle to postgresql. The challenge I am facing is to prove how accurate the cost/man-days estimation is,given by Ora2pg tool. Could please help me with the answer if anyone has gone through similar experience? Your help and reply would be of great help to me. Regards, Venkat
[GENERAL] the "PgJDBC driver is not an executable" message
Well you know the old saying: "the road to hell is paved with good intentions"! So an attempt was made to help people who download the JDBC driver and then try to "run" it: https://github.com/pgjdbc/pgjdbc/pull/112/files But here's a fun side effect: If you add a .jar to an Eclipse project that has a main(), Eclipse adds a new run configuration with that class as the main class, and further makes that the current selected config, automatically and silently. Took me a little bit to figure that one out... (because I'm a novice Eclipse user...) So, is it worth adding some explanatory text? It was really annoying to suddenly start getting this message when I never had any intention of "running" the JDBC driver ;-) -- Scott Ribe scott_r...@killerybtes.com (303) 722-0567 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [General] - Ora2PG estimation accuracy
Hi All, I am working on getting an estimation for large scale migration from oracle to postgresql. The challenge I am facing is to prove how accurate the cost/man-days estimation is,given by Ora2pg tool. Could please help me with the answer if anyone has gone through similar experience? Your help and reply would be of great help to me. Regards, Venkat
Re: [GENERAL] Audit based on role
On 08/07/2017 04:47 PM, anand086 wrote: > Only Insert/Update/Delete sqls are to be audited. You could get close to what you want, I think, by setting log_statement = mod for the users of interest, e.g. by doing: ALTER USER whomever SET log_statement = mod; See: https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#GUC-LOG-STATEMENT Note: "mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM." Caveat: You would have to do this per user in that group. However you could write a query against the system catalogs though to loop through the members of the group and execute this statement against each one. Maybe rerun it periodically. HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Audit based on role
Only Insert/Update/Delete sqls are to be audited. -- View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5976509.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Audit based on role
On 8/7/2017 4:33 PM, anand086 wrote: We are running Postgres 9.6.3 version and have requirement, where we want to audit any DML action performed by a user whose has module_dml role granted. What would be the best way to do that? I was thinking to write something likehttps://wiki.postgresql.org/wiki/Audit_trigger, but I am not sure on how to integrate "user whose has module_dml role granted" into the function. Instead of trigger is there any better way to achieve this? DML as in select/insert/update/delete ? or did you mean DDL as in CREATE/ALTER TABLE, etc ? -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Audit based on role
Hi, I am postgres newbie. We are running Postgres 9.6.3 version and have requirement, where we want to audit any DML action performed by a user whose has module_dml role granted. What would be the best way to do that? I was thinking to write something like https://wiki.postgresql.org/wiki/Audit_trigger, but I am not sure on how to integrate "user whose has module_dml role granted" into the function. Instead of trigger is there any better way to achieve this? Regards, Anand -- View this message in context: http://www.postgresql-archive.org/Audit-based-on-role-tp5976507.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redacting JSONB
On 08/07/2017 12:08 PM, Paul Jones wrote: Is there some way to redact JSONB fields? This is essentially implementing "row-level" security on JSONB fields. Could possibly be done with a view or a function. MongoDB and MarkLogic both do this. Yeah I imagine you would use a view or function and only give access to the json data via that view or function. Pretty standard way of handling things like this. JD PJ -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://pgconf.us * Unless otherwise stated, opinions are my own. * -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Redacting JSONB
Is there some way to redact JSONB fields? This is essentially implementing "row-level" security on JSONB fields. Could possibly be done with a view or a function. MongoDB and MarkLogic both do this. PJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
