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.pear...@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-ow...@postgresql.org [mailto: > pgadmin-support-ow...@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: > > > [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-zh...@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 > > >
