Re: Connection by user with restricted access to pg_database - Mailing list pgadmin-support

Hello Vladimir,

            If you look at the data in those tables, they relate to what is stored on the PostgreSQL instance.  If PGAdmin can’t read those databases when it fires up, using the username provided then I would assume that it can’t list the databases in PGAdmin (not a PGAdmin developer, but would assume it reads this table when firing up to list all the databases, then when you click on the database it checks your permissions).

 

If you connect via PSQL to the mydb I’m guessing this works fine?

 

If you connect via PSQL and list all databases when under the users login, does it list all databases on the server or error?

 

Try granting those permissions back and try again. 

 

Regards,

Adam Pearson

 

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of ???????? ???????
Sent: 16 December 2015 11:04 AM
To: Nikolai Zhubr
Cc: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Connection by user with restricted access to pg_database

 

Nikolai, thank you for your answer.
 

I expect that pgadmin connects to my database and I can execute some sql queries to get data. But instead I get window with error about permission denied.

When I click OK, I get something like this:

then i press Continue, and get this window:



and it is repeated several times. Then I see my connection, but there no databases, and I can not view tables list and execute some sql.

 

2015-12-16 15:40 GMT+05:00 Nikolai Zhubr <n-a-zhubr@yandex.ru>:

Hello,
16.12.2015 10:04, Владимир Янченко:
[...]

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?

What exactly do you mean by "workaround" here? What do you expect pgadmin to (be able) do?


Regards,
Nikolai

 

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