[GENERAL] About using IMCS module

[송기훈]

Hi, I'm trying to use imcs module to store table space in memory.
But, It dose not work with 9.6 version anymore.
Releasing 9.6, lwlockassign() function has been deleted, cause of some
issues.

So, What I want to ask you something is that postgresql decide not to
support to imcs module officially (I know imcs module is not updated long
time and not offcially supporting module). And are there other way to store
table space in memory only?

Re: [GENERAL] Audit based on role

[pinker]

Hi,
I personally don't like the solution from
https://wiki.postgresql.org/wiki/Audit_trigger and here are the reasons why:
* it produces a large table where all the changes, from all tables and
schemas go - audit.logged_actions. So when you would like to read it to
check anything it will be super slow
* On audit table are 3 indexes - which slow down the insert process
* All the data are kept in one column - so when comes to any analysis you
need custom functions to do it

Besides: Why there is fillfactor=100? That's the default value for table,
isn't it?

I use a bit different approach:
* every table has got separate table in a audit schema
* audited data are partinioned (usually per month)
* it's much easier to remove old data - just by dropping partition
* data has got exactly the same structure as in original schema

Every od those solution has got pros and cons but I prefer the second one a
lot more.
You can find one of it here: https://github.com/AwdotiaRomanowna/pgaudit



--
View this message in context: 
http://www.postgresql-archive.org/Audit-based-on-role-tp5976507p5976641.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

[Arthur Zakirov]

Hello,

On Mon, Aug 07, 2017 at 04:33:21PM -0700, anand086 wrote:
> 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?
> 

Did you try pgaudit extension?
https://github.com/pgaudit/pgaudit

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ZeosLib database components - uuid inserts using libpq

[Ertan Küçükoğlu]

Hello,

I recently found a bug on open source ZeosLib Database components for
Lazarus (Object Pascal) which is when ZeosLib is used with PostgreSQL
database server. Bug was that library simply removes any uuid column from
select result set. Problem is now solved and current SVN includes fixes (fix
was to first define uuid field type in library then read 16 bytes for uuid
defined columns and finally convert byte information to string
representation for displaying on the result set).

However, my additional tests on latest SNV version of the library resulted
that there is a new bug while inserting values to uuid columns. Provided
uuid value to be inserted is wrong in relevant columns. Considering string
representation of uuid, only last section of the inserted uuid is correct.
Discussing with ZeosLib developers and they are not sure how to pass that
uuid value to libpq while saving. Current questions are;
- Should it be direct string representation or 16 byte raw value?
- If 16 byte raw value, is there any special way to build that 16 bytes from
string representation?

As nobody can be sure on ZeosLib side and I could not find such information
on libpq documents on PostgreSQL web site. I decided to ask here for help.

Thanks & regards,
Ertan Küçükoğlu






-- 
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] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Hi,

I'm coming back to an old thread.  My original issue isn't resolved yet;
this is just to answer your questions below.

Adrian Klaver writes:
> [...]
> I do not build on OS X so this is a bit of a reach for me, still here it 
> goes. From your original post the error was:
> 
> "reating conversions ... FATAL:  could not load library 
> "/opt/bb/170704/lib/postgresql/ascii_and_mic.so": 
> dlopen(/opt/bb/170704/lib/postgresql/ascii_and_mic.so, 10): Symbol not 
> found: _check_encoding_conversion_args
> Referenced from: /opt/bb/170704/lib/postgresql/ascii_and_mic.so
> Expected in: /opt/bb/170704/bin/postgres
>in /opt/bb/170704/lib/postgresql/ascii_and_mic.so"
> 
> You also mentioned you have done 32 bit builds that worked.
I was wrong about that.  Everything was 64 bit.

> Now when I do 64 bit build on Linux the libraries end up in ~/lib64/:
On macOS, everything goes into the same $PREFIX/lib.  A library can be
32bit, or 64bit, or both (a "universal binary").  macOS has a tool
named "lipo" that can create those.  Usually, you get 64bit-only libraries.
Once I realized this (thanks to Tom Lane), I dropped all configure
arguments related to that.

> /usr/local/pgsql/lib64> l ascii_and_mic.so
> -rwxr-xr-x 1 root root 9760 Jun 14 07:32 ascii_and_mic.so*
> 
> Yet in your case I see ~/lib/. Not sure how OS X handles 32bit/64bit, 
> which is where I am reaching. Just wondering if there is cross 
> contamination going on?
No, that's not the problem.  I only have 64 bit stuff.

> ldd ascii_and_mic.so
On macOS, there's no ldd.  The equivalent is "otool -L".
It looks like this (line breaks inserted for readability):

  % file /opt/pg/lib/postgresql/ascii_and_mic.so
  /opt/pg/lib/postgresql/ascii_and_mic.so: Mach-O 64-bit bundle x86_64

  % otool -L /opt/pg/lib/postgresql/ascii_and_mic.so
  /opt/pg/lib/postgresql/ascii_and_mic.so:
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0,
  current version 1238.60.2)

BTW, libSystem.B.dylib is a universal binary:

  % file /usr/lib/libSystem.B.dylib
  /usr/lib/libSystem.B.dylib: Mach-O universal binary with 2 architectures:
[x86_64: Mach-O 64-bit dynamically linked shared library x86_64]
[i386: Mach-O dynamically linked shared library i386]
  /usr/lib/libSystem.B.dylib (for architecture x86_64):
Mach-O 64-bit dynamically linked shared library x86_64
  /usr/lib/libSystem.B.dylib (for architecture i386):
Mach-O dynamically linked shared library i386

This 32/64 business isn't the problem, though.

I've found out that everything works if I type the configure/make commands
in my shell (which isn't what I said before, sorry about that), but stops
working if I put them into a Perl script (which is how I automate things
across OSes).  The weird thing is that the commands are absolutely the same,
and environment is the same, too.  Anyway, it seems clear that the error is
on my side.  Given enough time, I will find it and report to you.

Thanks, Rainer
-- 
Email: r...@bb-c.de  +  Telefon: 02448/919126  +  Mobiltelefon: 0172/9593205
Brandt & Brandt Computer GmbH  +  Am Wiesenpfad 6, 53340 Meckenheim
Geschäftsführer: Rainer J.H. Brandt und Volker A. Brandt
Handelsregister: Amtsgericht Bonn, HRB 10513


-- 
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] 64bit initdb failure on macOS 10.11 and 10.12

[Tom Lane]

r...@bb-c.de (Rainer J.H. Brandt) writes:
> This 32/64 business isn't the problem, though.

> I've found out that everything works if I type the configure/make commands
> in my shell (which isn't what I said before, sorry about that), but stops
> working if I put them into a Perl script (which is how I automate things
> across OSes).

Ooooh, that's interesting ...

> The weird thing is that the commands are absolutely the same,
> and environment is the same, too.

I bet not.  We've seen problems with macOS unexpectedly deciding to
filter away inherited environment variables in some situations.
It might be useful to put "env >somefile" into the PG makefile and
compare results between the two ways of invoking it.

regards, tom lane


-- 
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] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Tom Lane writes:
> r...@bb-c.de (Rainer J.H. Brandt) writes:
> > This 32/64 business isn't the problem, though.
> 
> > I've found out that everything works if I type the configure/make commands
> > in my shell (which isn't what I said before, sorry about that), but stops
> > working if I put them into a Perl script (which is how I automate things
> > across OSes).
> 
> Ooooh, that's interesting ...
> 
> > The weird thing is that the commands are absolutely the same,
> > and environment is the same, too.
> 
> I bet not.  We've seen problems with macOS unexpectedly deciding to
> filter away inherited environment variables in some situations.
> It might be useful to put "env >somefile" into the PG makefile and
> compare results between the two ways of invoking it.

Between the configure and make steps, I put it into the top level
GNUmakefile.  Here's the diff:

18c18
< PWD=/private/tmp/buildbot.9365/postgresql-9.6.3
---
> PWD=/tmp/w/postgresql-9.6.3
21c21
< SHLVL=3
---
> SHLVL=2

This subshell level isn't relevant, or is it?
Here's the full output of the manual version:

Apple_PubSub_Socket_Render=/private/tmp/com.apple.launchd.tMcsjxst4H/Render
DISPLAY=/private/tmp/com.apple.launchd.EahVZI82Sz/org.macosforge.xquartz:0
EDITOR=emacsclient
GROUP=people
HOME=/Users/rjhb
HOST=hawk.local
HOSTTYPE=unknown
LANG=en_US.UTF-8
LOGNAME=rjhb
MACHTYPE=x86_64
MAKEFLAGS=
MAKELEVEL=1
MANPATH=/usr/share/man:/Users/rjhb/doc/man:/opt/local/share/man
MFLAGS=
MYDISPLAY=/private/tmp/com.apple.launchd.EahVZI82Sz/org.macosforge.xquartz:0
OSTYPE=darwin
PATH=/usr/bin:/usr/sbin:/bin:/sbin
PWD=/tmp/w/postgresql-9.6.3
SECURITYSESSIONID=186a6
SHELL=/bin/tcsh
SHLVL=2
SSH_AUTH_SOCK=/private/tmp/com.apple.launchd.yAcr2eFkb9/Listeners
TERM=xterm-256color
TERM_PROGRAM=Apple_Terminal
TERM_PROGRAM_VERSION=388.1.1
TERM_SESSION_ID=3BEAA846-F8B4-46AC-9CB6-17ADC709E34F
TMPDIR=/var/folders/75/60nrw1lx53b5jwl1dz7g5v_4z9/T/
USER=rjhb
VENDOR=apple
XPC_FLAGS=0x0
XPC_SERVICE_NAME=0
_=/usr/bin/env
__CF_USER_TEXT_ENCODING=0x3E9:0x0:0x0

Regards, Rainer


-- 
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] 64bit initdb failure on macOS 10.11 and 10.12

[Tom Lane]

r...@bb-c.de (Rainer J.H. Brandt) writes:
> Tom Lane writes:
>> I bet not.  We've seen problems with macOS unexpectedly deciding to
>> filter away inherited environment variables in some situations.
>> It might be useful to put "env >somefile" into the PG makefile and
>> compare results between the two ways of invoking it.

> Between the configure and make steps, I put it into the top level
> GNUmakefile.  Here's the diff:
> 18c18
> < PWD=/private/tmp/buildbot.9365/postgresql-9.6.3
> ---
> > PWD=/tmp/w/postgresql-9.6.3
> 21c21
> < SHLVL=3
> ---
> > SHLVL=2
> This subshell level isn't relevant, or is it?

No, probably not --- so that theory seems like a failure.

> Here's the full output of the manual version:

Hmmm ...

> SHELL=/bin/tcsh

Mine's bash ... I wonder whether that could make a difference here?
I'm pretty sure the PG makefiles aren't set up for csh syntax.
But I can't see how that would make it work in the manual case
and not when going through Perl.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Compiling libpq only on Linux

[Igor Korot]

 Hi, ALL,
Quick question - what is the best way to compile libpq only on Linux?

I just checked and currently my distro (I didn't updated in a long time)
has 9.5 version as current.

Should I grab  it, unpack it and do configure and then make inside
libpq directory
manually? Or there is some other way?

Thank you.


-- 
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] 64bit initdb failure on macOS 10.11 and 10.12

[Vick Khera]

On Tue, Aug 8, 2017 at 12:36 PM, Tom Lane  wrote:

> Hmmm ...
>
> > SHELL=/bin/tcsh
>
> Mine's bash ... I wonder whether that could make a difference here?
> I'm pretty sure the PG makefiles aren't set up for csh syntax.
> But I can't see how that would make it work in the manual case
> and not when going through Perl.
>
>
Shouldn't matter. Any make sub command or exec from perl will by definition
be run by /bin/sh.

Re: [GENERAL] Audit based on role

[Joe Conway]

On 08/07/2017 06:59 PM, Melvin Davidson wrote:
> *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;

The problem with that query is is you have more than one level of
nesting. E.g.:

 Role name  |   Attributes|  Member of
+-+--
 bob| | {joe}
 joe| | {module_dml}
 module_dml | Cannot login| {}

pgopen2017=# 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 = 'joe'
   AND g.rolname = 'module_dml'
 ORDER BY 1, 2;
   group| user | admin | g_super | u_super
+--+---+-+-
 module_dml | joe  | f | f   | f
(1 row)

pgopen2017=# 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 = 'bob'
   AND g.rolname = 'module_dml'
 ORDER BY 1, 2;
 group | user | admin | g_super | u_super
---+--+---+-+-
(0 rows)


Better would be a recursive WITH clause. An example can be seen in the
README.md file here (see the VIEW roletree):

  https://github.com/pgaudit/set_user

Then you can do something like:
SELECT
  ro.rolname,
  ro.rolcanlogin,
  ro.rolparents
FROM roletree ro
WHERE 'module_dml' = ANY (rolparents);
 rolname | rolcanlogin |rolparents
-+-+--
 bob | t   | {module_dml,joe}
 joe | t   | {module_dml}
(2 rows)


> On Mon, Aug 7, 2017 at 8:05 PM, Joe Conway wrote:
>  ALTER USER whomever SET log_statement = mod;

> 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.

And in turn, this can be done like so:

SELECT * FROM pg_db_role_setting
WHERE setrole IN (SELECT ro.roloid FROM roletree ro
  WHERE 'module_dml' = ANY (rolparents));
 setdatabase | setrole | setconfig
-+-+---
(0 rows)

DO $$
 DECLARE
  username text;
 BEGIN
  FOR username IN
   SELECT ro.rolname FROM roletree ro
   WHERE 'module_dml' = ANY (rolparents) LOOP
EXECUTE 'ALTER USER ' || username || ' SET log_statement = mod';
  END LOOP;
 END
$$;

SELECT * FROM pg_db_role_setting
WHERE setrole IN (SELECT ro.roloid FROM roletree ro
  WHERE 'module_dml' = ANY (rolparents));
 setdatabase | setrole |  setconfig
-+-+-
   0 |  150929 | {log_statement=mod}
   0 |  150930 | {log_statement=mod}
(2 rows)

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] Compiling libpq only on Linux

[Peter Eisentraut]

On 8/8/17 14:49, Igor Korot wrote:
> Quick question - what is the best way to compile libpq only on Linux?
> Should I grab  it, unpack it and do configure and then make inside
> libpq directory
> manually?

Pretty much yes.

> Or there is some other way?

You could look for updated distribution packages, even if they are not
directly from your vendor.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

[David G. Johnston]

Hey all, looking for thoughts on a feature request:

I run quite a few queries, using psql, that are intended for exceptional
situations.  When there are no results, which is expected, I still get the
table header and basic frame showing up in the output.  The option I'd like
is to be able to suppress the output of the empty table (and header if
there is one) or possibly substitute the empty table with user-supplied
text.

Thinking something that is used like \g

SELECT * FROM (VALUES (1)) vals (v) WHERE v = 0 \ghideifempty

[SQL] \galtifempty 'No values matching 0 in vals'

The names are descriptive, not suggestions...

David J.

Re: [GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

[Melvin Davidson]

On Tue, Aug 8, 2017 at 9:16 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> Hey all, looking for thoughts on a feature request:
>
> I run quite a few queries, using psql, that are intended for exceptional
> situations.  When there are no results, which is expected, I still get the
> table header and basic frame showing up in the output.  The option I'd like
> is to be able to suppress the output of the empty table (and header if
> there is one) or possibly substitute the empty table with user-supplied
> text.
>
> Thinking something that is used like \g
>
> SELECT * FROM (VALUES (1)) vals (v) WHERE v = 0 \ghideifempty
>
> [SQL] \galtifempty 'No values matching 0 in vals'
>
> The names are descriptive, not suggestions...
>
> David J.
>

>The option I'd like is to be able to suppress the output of the empty
table (and header if there is one)
*Have you looked at the TUPLES ONLY option?*

*-t* *--tuples-only*

*Turn off printing of column names and result row count footers, etc. This
is equivalent to the \t command.*
*https://www.postgresql.org/docs/9.4/static/app-psql.html
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Any thoughts on making a psql meta-command "hide (or show alt text) if no results"?

[David G. Johnston]

On Tue, Aug 8, 2017 at 6:25 PM, Melvin Davidson 
wrote:

>
> *​H​ave you looked at the TUPLES ONLY option?*
>
> *-t* *--tuples-only*
>
> *Turn off printing of column names and result row count footers, etc. This
> is equivalent to the \t command.*
> *https://www.postgresql.org/docs/9.4/static/app-psql.html
> *
>
>
​Hadn't pondered it for this usage.  Now that I have I'd say having the
column names be visible for those few times the query returns results would
be nice.​  So, close but not quite.

I eventually saw, in one of the three descriptions for "tuples only" where
the caption is explicitly noted as being hidden in this mode. In my
proposed feature the caption, like the table it is attached to, would be
visible only conditionally.

David J.