Re: Connection by user with restricted access to pg_database

Hello,
16.12.2015 14:32, Владимир Янченко:
> Adam,
>
> yes, you're right. I can connect via PSQL, but get error in PSQL, when I
> try to list databases.
>
> That's what I need, because I don't want show all users and all
> databases to our client, who will be connected by this restricted user.
>
> My goal: user CAN NOT view pg_databases and pg_roles, but CAN connect to
> his database via pgadmin and execute sql statements.

I like the idea. Not sure if it is reasonably possible to implement this
and how much effort it would take though. (I never got that deep yet)

Regards,
Nikolai

>
> 2015-12-16 16:10 GMT+05:00 Adam Pearson <Adam(dot)Pearson(at)4finance(dot)com
> <mailto:Adam(dot)Pearson(at)4finance(dot)com>>:
>
> 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(at)postgresql(dot)org
> <mailto:pgadmin-support-owner(at)postgresql(dot)org>
> [mailto:pgadmin-support-owner(at)postgresql(dot)org
> <mailto:pgadmin-support-owner(at)postgresql(dot)org>] *On Behalf Of
> *???????? ???????
> *Sent:* 16 December 2015 11:04 AM
> *To:* Nikolai Zhubr
> *Cc:* pgadmin-support(at)postgresql(dot)org
> <mailto:pgadmin-support(at)postgresql(dot)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:
>
>
> Встроенное изображение 1
>
> then i press Continue, and get this window:
>
> Встроенное изображение 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.
>
> Встроенное изображение 3
>
> 2015-12-16 15:40 GMT+05:00 Nikolai Zhubr <n-a-zhubr(at)yandex(dot)ru
> <mailto: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 <mailto:pgadmin-support(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>
>