Re: Connection by user with restricted access to pg_database

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.

2015-12-16 16:10 GMT+05:00 Adam Pearson <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] *On Behalf Of *???????? ???????
> *Sent:* 16 December 2015 11:04 AM
> *To:* Nikolai Zhubr
> *Cc:* 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:
>
>
> [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
>
>
>