#4702 PostgreSQL 9.5 pg_shadow deprecated

[Mohamad Akhras]

pg_shadow retired and deprecated from v9.5 forever

Webmin postgreSQL needs update, below error appers when try to see the postgreSQL users:
"SQL select usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil from pg_shadow failed : ERROR: column "usecatupd" does not exist LINE 1: select usename,usesysid,usecreatedb,usesuper,usecatupd,passw... ^"

[Jamie Cameron]

So what table replaced it?

[Andrew R]

pg_user is a publicly readable view on pg_shadow: http://www.postgresql.org/docs/9.5/static/view-pg-shadow.html

And yes, Webmin does not work on Postgresql 9.5 anymore.

[Jamie Cameron]

Does the pg_user file have the same columns as pg_shadow?
I don't have a PostgreSQL 9.5 system to test on currently.

[Paul Treadaway]

9.5: -

CREATE OR REPLACE VIEW pg_shadow AS
SELECT pg_authid.rolname AS usename,
pg_authid.oid AS usesysid,
pg_authid.rolcreatedb AS usecreatedb,
pg_authid.rolsuper AS usesuper,
pg_authid.rolreplication AS userepl,
pg_authid.rolbypassrls AS usebypassrls,
pg_authid.rolpassword AS passwd,
pg_authid.rolvaliduntil::abstime AS valuntil,
s.setconfig AS useconfig
FROM pg_authid
LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid
WHERE pg_authid.rolcanlogin;

CREATE OR REPLACE VIEW pg_user AS
SELECT pg_shadow.usename,
pg_shadow.usesysid,
pg_shadow.usecreatedb,
pg_shadow.usesuper,
pg_shadow.userepl,
pg_shadow.usebypassrls,
'**'::text AS passwd,
pg_shadow.valuntil,
pg_shadow.useconfig
FROM pg_shadow;

==========

9.3: -

CREATE OR REPLACE VIEW pg_shadow AS
SELECT pg_authid.rolname AS usename,
pg_authid.oid AS usesysid,
pg_authid.rolcreatedb AS usecreatedb,
pg_authid.rolsuper AS usesuper,
pg_authid.rolcatupdate AS usecatupd,
pg_authid.rolreplication AS userepl,
pg_authid.rolpassword AS passwd,
pg_authid.rolvaliduntil::abstime AS valuntil,
s.setconfig AS useconfig
FROM pg_authid
LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid
WHERE pg_authid.rolcanlogin;

CREATE OR REPLACE VIEW pg_user AS
SELECT pg_shadow.usename,
pg_shadow.usesysid,
pg_shadow.usecreatedb,
pg_shadow.usesuper,
pg_shadow.usecatupd,
pg_shadow.userepl,
'**'::text AS passwd,
pg_shadow.valuntil,
pg_shadow.useconfig
FROM pg_shadow;

==========

So usecatupd has gone, and usebypassrls is new.

[Jamie Cameron]

The next Webmin release will fix this.

[Paul Treadaway]

Thanks!

[Ed Yin]

well the webmin update does not work on my ubuntu version so heres the solution to this problem:

go to :
/usr/share/webmin/postgresql#
and open:
vi (edtior)./postgresql-lib.pl
go to line :
$pg_shadow_cols = "usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil";
replace usecatupd with usebypassrls

Done

no need to wait for webmin updates just solve the problem in the current version !!!!!
and now it has status --> closed-fixed

[Jamie Cameron]

Ed - which PostgreSQL version are you running?

[Ed Yin]

Well Jamie is was running 9.3 in Webmin 1.7 since the new 1.810 would not run at my Ordoid C2 ubuntu (arm64) server at first. But since i formatted and retried yesterday on a older kernel of ubuntu server i could install the newer version of Webmin and also postgresql.

i just checked it and using PostgreSQL version 9.5.3 (With schemas) now.

[Jamie Cameron]

Does it show version 9.5.3 on the main page of Webmin's PostgreSQL module? And do you still have the problem with this version?