SQL Error: ОШИБКА: нет прав для изменения параметра "session_replication_role"

[Rodgelius]

Hello, Maxim! There is problem (in subject) that I can't understand. Also I can't find default mechanism of authentication without password (under 'postgres' user)

Here is listing:

root@someserver:~# perl pgcompacttable.pl -U someuser -W somepass -d somedb -t history -v [Fri Aug 18 09:39:02 2017] (somedb) Connecting to database [Fri Aug 18 09:39:02 2017] (somedb) Postgress backend pid: 8163 Wide character in print at pgcompacttable.pl line 187. [Fri Aug 18 09:39:02 2017] (somedb) SQL Error: ОШИБКА: нет прав для изменения параметра "session_replication_role" [Fri Aug 18 09:39:02 2017] (somedb) Database handling interrupt. [Fri Aug 18 09:39:02 2017] (somedb) Disconnecting from database [Fri Aug 18 09:39:02 2017] Processing incomplete: 1 databases left.

Best regards, Vladimir

[alexius2]

There is a method to authenticate without password using -h /path/to/unix/socket/dir (usually /tmp or somthing like /var/run/postgresql) under postgres user. Not very convenient, I agree. For changing session_replication_role you have to be superuser - this setting is used to disable all triggers in session so DB won't have to do additional (and potentially dangerous) work when pgcompacttable performing fake updates.

[Rodgelius]

Please add next to your code, to prevent 'Wide character in print at' error message.

!/usr/bin/perl

use strict;

use utf8;

binmode(STDOUT,':utf8');

[Rodgelius]

Alexius2, Explain please what do you mean here: < quote> For changing session_replication_role you have to be superuser</ quote> Because I run my 'perl pgcompacttable.pl.....' command with root. :) My first message was "....root@someserver:~#...."

[alexius2]

default superuser in db is postgres. so to connect as superuser need to switch to postgres OS user and connect via unix socket or set password to postgres user and connect with -U postgres or set authentication method to trust in pg_hba if it's local/test machine.

[Rodgelius]

Dear Alexius2, Thanks a lot, it works for me! IMHO, this hint should be written in --man. (about -h parameter and socket path) also I was needed to enable pgstattuple, and it was first time I faced with, so please AUTHOR, if you're reading this - add some more info about how to use. Some kind like this: If you're not sure about pgstattuple was installed do this: su - postgres psql \c create extension pgstattuple;

Best regards! Vladimir