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.pear...@4finance.com
<mailto: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>
[mailto: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
<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-zh...@yandex.ru
<mailto: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 <mailto:pgadmin-support@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
