From: | Владимир Янченко <xooyanoox(at)gmail(dot)com> |
---|---|
To: | pgadmin-support(at)postgresql(dot)org |
Subject: | Connection by user with restricted access to pg_database |
Date: | 2015-12-16 07:04:30 |
Message-ID: | CAAH6kD1CNBdybCuAkRQnGFsWFeTcu3v8xgjTaZ4rdkSKXfesYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolai Zhubr | 2015-12-16 10:23:07 | Re: pgAdmin III - Crash on Startup |
Previous Message | Peter Morrissey | 2015-12-16 02:41:22 | pgAdmin III - Crash on Startup |