Re: [GENERAL] Loggingt psql meta-commands

oleg yusim wrote: > Greetings! > > I'm new to PostgreSQL, working on it from the point of view of Cyber Security > assessment. In regards to the here is my question: > > Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, > \z, etc? start psql

Re: [GENERAL] Loggingt psql meta-commands

Andreas, Andrian, Thank you very much for both pieces of information. It was very helpful. Now, let me ask you one more question on the same topic. Is it more granular way to control logging PosgreSQL provides, or I pretty much reduced to choosing between mod and all? The reason I'm asking is

Re: [GENERAL] Loggingt psql meta-commands

Thanks John, I realized that and confirmed in my logs. What I'm trying to determine now, can I only log some SELECT statements, or I should log all of them or none of them. Oleg On Thu, Dec 10, 2015 at 1:40 PM, John R Pierce wrote: > On 12/10/2015 9:58 AM, oleg yusim

Re: [GENERAL] Loggingt psql meta-commands

On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim wrote: > Thanks John, I realized that and confirmed in my logs. What I'm trying to > determine now, can I only log some SELECT statements, or I should log all > of them or none of them. > You can configure this to multiple levels:

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 11:49 AM, oleg yusim wrote: Andreas, Andrian, Thank you very much for both pieces of information. It was very helpful. Now, let me ask you one more question on the same topic. Is it more granular way to control logging PosgreSQL provides, or I pretty much reduced to choosing

Re: [GENERAL] Loggingt psql meta-commands

Scott Mead writes: > On Thu, Dec 10, 2015 at 2:50 PM, oleg yusim wrote: > > Thanks John, I realized that and confirmed in my logs. What I'm trying to > determine now, can I only log some SELECT statements, or I should log all of > them or none >

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 9:58 AM, oleg yusim wrote: I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question: Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc? what the other two gentlemen are

Re: [GENERAL] Loggingt psql meta-commands

2015-12-10 20:49 GMT+01:00 oleg yusim : > Andreas, Andrian, > > Thank you very much for both pieces of information. It was very helpful. > Now, let me ask you one more question on the same topic. Is it more > granular way to control logging PosgreSQL provides, or I pretty

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 09:58 AM, oleg yusim wrote: Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question: Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc? aklaver@killi:~> psql

Re: [GENERAL] Loggingt psql meta-commands

Hmm... let me see if I got it right. I can set log_statement to mod overall, and then modify it as ALTER USER postgres SET log_statement=all; for postgres only? Also... how do we control who can run meta commands? Thanks, Oleg On Thu, Dec 10, 2015 at 2:16 PM, Jerry Sievers

Re: [GENERAL] Loggingt psql meta-commands

On Thu, Dec 10, 2015 at 1:46 PM, oleg yusim wrote: > Hi David, > > Can you, please, give me example? > > ​Not readily...maybe others can. Putting forth specific examples of what you want to accomplish may help. David J.​

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 12:56 PM, oleg yusim wrote: 2) Way to ensure that only superuser can run meta commands, such as \du, \dp, \z Those metacommands only exist in the psql command line tool, so to only restrict access to them, you'd need to modify that tool. however, that wouldn't stop users from

Re: [GENERAL] Loggingt psql meta-commands

On Thu, Dec 10, 2015 at 1:20 PM, oleg yusim wrote: > Also... how do we control who can run meta commands? > ​You cannot do so directly but you can control permissions to the underlying schema that the meta-command queries touch so that attempting to run the meta-command

Re: [GENERAL] Loggingt psql meta-commands

On Thu, Dec 10, 2015 at 1:56 PM, oleg yusim wrote: > So what I want to accomplish is logging queries for roles/privileges with > minimal increasing volume of logs along the way. The idea I got from > responses in this thread so far is: > > 1) Set log_statement on

Re: [GENERAL] Loggingt psql meta-commands

Adrian, What I hope to achieve is to meet this requirement from Database SRG: *Review DBMS documentation to verify that audit records can be produced when privileges/permissions/role memberships are retrieved.* To do that I would need to enable logging of such commands as \du, \dp, \z. At the

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

On Thu, Dec 10, 2015 at 7:38 AM, Corradini, Carlos wrote: > I have a Java application that must read a > data provided by two ( 2 ) cursors returned by a function stored in a > database. I know to retrieve data if the function have one ( 1 ) cursor, but > with

Re: [GENERAL] Loggingt psql meta-commands

Adrian, You seemed to be familiar with the STIG world, so how about V-ID from Database SRG? I'm looking into STIG ID: SRG-APP-91-DB-66 right now. Now, I do not really think it is a tall order, since the requirement speaks about explicit calls for privilege/permission/role membership

Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

On 12/10/2015 05:38 AM, Corradini, Carlos wrote: Dear Gurus : First let me say hello from Buenos Aires, Argentina. I took this emails addresses from internet ( page www.postgresql.org ) Now I will try to explain which is my problem

[GENERAL] regexp_replace question / help needed

Hello, I am running into a problem and need some pointers on regexp_replace - I can't seem to find an answer in any of the online resources. I have a string (like 40,000 with different length and number of components) of them in a field named "externalurl". I need to replace the final "/" of the

Re: [GENERAL] Loggingt psql meta-commands

So what I want to accomplish is logging queries for roles/privileges with minimal increasing volume of logs along the way. The idea I got from responses in this thread so far is: 1) Set log_statement on postgresql.conf to 'mod' 2) Raise log_statement to 'all' but only for postgres superuser What

[GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

Dear Gurus : First let me say hello from Buenos Aires, Argentina. I took this emails addresses from internet ( page www.postgresql.org ) Now I will try to explain which is my problem ( excuse my poor level of English, please ). I have a Java application

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 12:56 PM, oleg yusim wrote: So what I want to accomplish is logging queries for roles/privileges with minimal increasing volume of logs along the way. The idea I got from responses in this thread so far is: 1) Set log_statement on postgresql.conf to 'mod' 2) Raise log_statement to

Re: [GENERAL] Loggingt psql meta-commands

Hi David, Can you, please, give me example? Thanks, Oleg On Thu, Dec 10, 2015 at 2:25 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Dec 10, 2015 at 1:20 PM, oleg yusim wrote: > >> Also... how do we control who can run meta commands? >> > > ​You

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 01:36 PM, oleg yusim wrote: Adrian, What I hope to achieve is to meet this requirement from Database SRG: So some aspect of this: https://www.stigviewer.com/stig/database_security_requirements_guide/ Can you be more specific? /Review DBMS documentation to verify that audit

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 2:03 PM, Adrian Klaver wrote: So some aspect of this: https://www.stigviewer.com/stig/database_security_requirements_guide/ thats a rather insane bunch of requirements. Reads like a wish list by academic security researchers. for instance

Re: [GENERAL] Loggingt psql meta-commands

Adrian, I used all those settings you suggested already, and I will suggest to use logrotate or syslog on top of it, so no more than day worth of log would be kept in the system. Still, I view it as a big drawback. Do you know of any third party tools (log monitoring/analyze software, you

Re: [GENERAL] Loggingt psql meta-commands

On Thu, Dec 10, 2015 at 02:52:38PM -0800, John R Pierce wrote: > thats a rather insane bunch of requirements. Reads like a wish list by > academic security researchers. Well, I don't know. Might be a wish list by insurance adjusters who want to minimise liability. At least in the United

[GENERAL] connections not getting closed on a replica

Hello, I'm trying to figure out why we had a build up of connections on our streaming replica. We're running postgres 9.3.5 on the master and 9.3.10 on the replica, linux 3.2.0 on both, disks in raid10. Here are some graphs to illustrate what happened:

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 03:02 PM, oleg yusim wrote: Adrian, I used all those settings you suggested already, and I will suggest to use logrotate or syslog on top of it, so no more than day worth of log would be kept in the system. Still, I view it as a big drawback. Do you know of any third party tools

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 02:13 PM, oleg yusim wrote: Adrian, You seemed to be familiar with the STIG world, so how about V-ID from No, I am just familiar with how search engines work:) Database SRG? I'm looking into STIG ID: SRG-APP-91-DB-66 right now. Now, I do not really think it is a tall

Re: [GENERAL] regexp_replace question / help needed

On 10 December 2015 at 10:56, Christopher Molnar wrote: > Hello, > > I am running into a problem and need some pointers on regexp_replace - I > can't seem to find an answer in any of the online resources. > > I have a string (like 40,000 with different length and number of >

Re: [GENERAL] Loggingt psql meta-commands

oleg yusim writes: > What I hope to achieve is to meet this requirement from Database SRG: > *Review DBMS documentation to verify that audit records can be produced > when privileges/permissions/role memberships are retrieved.* > To do that I would need to enable logging of

Re: [GENERAL] Loggingt psql meta-commands

Thanks Tom, I get what you are saying and that seems to be final at this stage. I will write pg_audit down, though. Oleg On Thu, Dec 10, 2015 at 4:41 PM, Tom Lane wrote: > oleg yusim writes: > > What I hope to achieve is to meet this requirement from

Re: [GENERAL] Loggingt psql meta-commands

John, I can answer that - Oracle and MS SQL do, or at least there were able to convince DISA that they do (STIGs for them are present here: http://iase.disa.mil/stigs/Pages/a-z.aspx). That actually benefits those products greatly - from the point of view of security they, once hardened, meet

Re: [GENERAL] Loggingt psql meta-commands

On 12/10/2015 02:58 PM, oleg yusim wrote: > John, > > I can answer that - Oracle and MS SQL do, or at least there were able to > convince DISA that they do (STIGs for them are present here: > http://iase.disa.mil/stigs/Pages/a-z.aspx). That actually benefits those > products greatly - from the

[GENERAL] Loggingt psql meta-commands

Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my question: Is it a way to enable logging for psql prompt meta-commands, such as \du, \dp, \z, etc? Thanks, Oleg

Re: [GENERAL] [PostgreSQL+SOCI+BOOST] Compile error in core\exchange-traits.h with PG and SOCI on Windows

On 12/10/2015 06:55 AM, Lawry, Brian wrote: Hi. This isn’t really a PostgreSQL question but I’ve gotten no response to the following issue posted on the SOCI-users list. I’m posting here in the hope that someone has Did you see this response?:

[GENERAL] [PostgreSQL+SOCI+BOOST] Compile error in core\exchange-traits.h with PG and SOCI on Windows

Hi. This isn’t really a PostgreSQL question but I’ve gotten no response to the following issue posted on the SOCI-users list. I’m posting here in the hope that someone has experience building SOCI for PostgreSQL with Boost type support on Windows. Configuration: Using SOCI 3.2.3, Boost

Re: [GENERAL] [PostgreSQL+SOCI+BOOST] Compile error in core\exchange-traits.h with PG and SOCI on Windows

On 12/10/2015 06:55 AM, Lawry, Brian wrote: Hi. This isn’t really a PostgreSQL question but I’ve gotten no response to the following issue posted on the SOCI-users list. I’m posting here in the hope that someone has experience building SOCI for PostgreSQL with Boost type support on Windows.

Re: [GENERAL] [PostgreSQL+SOCI+BOOST] Compile error in core\exchange-traits.h with PG and SOCI on Windows

"Lawry, Brian" writes: > This isn’t really a PostgreSQL question but I’ve gotten no response to > the following > issue posted on the SOCI-users list. I’m posting here in the hope that > someone has > experience building SOCI for PostgreSQL with Boost type support