Hi! We provide access for our customers to their databases. These databases located on the same postgresql cluster, that's why we denied access to pg_database, pg_roles and others to client's users.
One of our customer uses pg_admin. He can't connect to database by provided username because there are no access to pg_database: 2015-12-15 15:33:18 ERROR : ERROR: permission denied for relation pg_database 2015-12-15 15:33:44 ERROR : Column not found in pgSet: "datlastsysoid" 2015-12-15 15:33:45 ERROR : Column not found in pgSet: datlastsysoid 2015-12-15 15:33:45 ERROR : Column not found in pgSet: oid 2015-12-15 15:33:46 ERROR : Column not found in pgSet: encoding 2015-12-15 15:33:47 ERROR : ERROR: permission denied for relation pg_user 2015-12-15 15:33:47 ERROR : Column not found in pgSet: usecreatedb 2015-12-15 15:33:48 ERROR : Column not found in pgSet: usesuper 2015-12-15 15:33:49 ERROR : Column not found in pgSet: upsince 2015-12-15 15:33:49 ERROR : Column not found in pgSet: confloadedsince 2015-12-15 15:33:50 ERROR : Column not found in pgSet: inrecovery 2015-12-15 15:33:50 ERROR : Column not found in pgSet: replayloc 2015-12-15 15:33:51 ERROR : Column not found in pgSet: receiveloc 2015-12-15 15:33:51 ERROR : Column not found in pgSet: replay_timestamp 2015-12-15 15:33:51 ERROR : Column not found in pgSet: isreplaypaused 2015-12-15 15:33:52 ERROR : ERROR: permission denied for relation pg_roles 2015-12-15 15:33:53 ERROR : Column not found in pgSet: rolcreatedb 2015-12-15 15:33:53 ERROR : Column not found in pgSet: rolcreaterole 2015-12-15 15:33:54 ERROR : ERROR: permission denied for relation pg_database 2015-12-15 15:33:55 ERROR : ERROR: permission denied for relation pg_tablespace 2015-12-15 15:33:56 ERROR : ERROR: permission denied for relation pg_roles 2015-12-15 15:33:58 ERROR : ERROR: permission denied for relation pg_roles pgAdmin version: 1.20.0 Postgresql version: 9.4.2 Postgresql OS: Ubuntu 12.04.3 Server Client OS: Ubuntu desktop 14.10 x64 Does a workaround exist for this situation? How to reproduce: psql -d template1 REVOKE ALL ON DATABASE template1 FROM public; REVOKE ALL ON SCHEMA public FROM public; REVOKE ALL ON pg_user FROM public; REVOKE ALL ON pg_roles FROM public; REVOKE ALL ON pg_group FROM public; REVOKE ALL ON pg_authid FROM public; REVOKE ALL ON pg_auth_members FROM public; REVOKE ALL ON pg_stat_activity FROM public; REVOKE ALL ON pg_database FROM public; REVOKE ALL ON pg_tablespace FROM public; GRANT ALL ON SCHEMA public TO postgres; CREATE DATABASE mydb; psql -d mydb REVOKE ALL ON DATABASE mydb FROM public; CREATE ROLE myuser NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '123'; GRANT USAGE ON SCHEMA public TO myuser; GRANT CONNECT ON DATABASE mydb TO myuser; ALTER DEFAULT PRIVILEGES FOR ROLE mydb IN SCHEMA public GRANT SELECT ON TABLES to myuser; GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuserr; Then connect with pgadmin, maintenance database: mydb, user: myuser. -- Vladimir Yanchenko Suport engineer Naumen -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
