Re: 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:

[image: Встроенное изображение 1]

then i press Continue, and get this window:

[image: Встроенное изображение 2]

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.

[image: Встроенное изображение 3]

2015-12-16 15:40 GMT+05:00 Nikolai Zhubr <n-a-zhubr(at)yandex(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>