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

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.Pearson@4finance.com
> <mailto:Adam.Pearson@4finance.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@postgresql.org
>     <mailto:pgadmin-support-owner@postgresql.org>
>     [mailto: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
>     <mailto: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:
>
>
>     Встроенное изображение 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@yandex.ru
>     <mailto: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 <mailto:pgadmin-support@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgadmin-support
>
>