Thread: Connection by user with restricted access to pg_database

Hi!

We provide access for our customers to their databases. These
databases located on the same postgresql cluster, that's why we denied
access to pg_database, pg_roles and others to client's users.

One of our customer uses pg_admin. He can't connect to database by
provided username because there are no access to pg_database:

2015-12-15 15:33:18 ERROR  : ERROR:  permission denied for relation pg_database
2015-12-15 15:33:44 ERROR  : Column not found in pgSet: "datlastsysoid"
2015-12-15 15:33:45 ERROR  : Column not found in pgSet: datlastsysoid
2015-12-15 15:33:45 ERROR  : Column not found in pgSet: oid
2015-12-15 15:33:46 ERROR  : Column not found in pgSet: encoding
2015-12-15 15:33:47 ERROR  : ERROR:  permission denied for relation pg_user
2015-12-15 15:33:47 ERROR  : Column not found in pgSet: usecreatedb
2015-12-15 15:33:48 ERROR  : Column not found in pgSet: usesuper
2015-12-15 15:33:49 ERROR  : Column not found in pgSet: upsince
2015-12-15 15:33:49 ERROR  : Column not found in pgSet: confloadedsince
2015-12-15 15:33:50 ERROR  : Column not found in pgSet: inrecovery
2015-12-15 15:33:50 ERROR  : Column not found in pgSet: replayloc
2015-12-15 15:33:51 ERROR  : Column not found in pgSet: receiveloc
2015-12-15 15:33:51 ERROR  : Column not found in pgSet: replay_timestamp
2015-12-15 15:33:51 ERROR  : Column not found in pgSet: isreplaypaused
2015-12-15 15:33:52 ERROR  : ERROR:  permission denied for relation pg_roles
2015-12-15 15:33:53 ERROR  : Column not found in pgSet: rolcreatedb
2015-12-15 15:33:53 ERROR  : Column not found in pgSet: rolcreaterole
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?

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

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
>
>

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
>
>

<p dir="ltr">Le 16 déc. 2015 2:11 PM, "Nikolai Zhubr" <<a
href="mailto:n-a-zhubr@yandex.ru">n-a-zhubr@yandex.ru</a>>a écrit :<br /> ><br /> > Hello,<br /> >
16.12.201514:32, Владимир Янченко:<br /> ><br /> >> Adam,<br /> >><br /> >> yes, you're right. I
canconnect via PSQL, but get error in PSQL, when I<br /> >> try to list databases.<br /> >><br /> >>
That'swhat I need, because I don't want show all users and all<br /> >> databases to our client, who will be
connectedby this restricted user.<br /> >><br /> >> My goal: user CAN NOT view pg_databases and pg_roles,
butCAN connect to<br /> >> his database via pgadmin and execute sql statements.<br /> ><br /> ><br /> >
Ilike the idea. Not sure if it is reasonably possible to implement this and how much effort it would take though. (I
nevergot that deep yet)<br /> ><p dir="ltr">Would be quite difficult. And I completely dislike the idea.<p
dir="ltr">Ifyou only need the SQL editor, you can already do this by launching PgAdmin directly with the SQL tool. 

Hello,
16.12.2015 16:37, Guillaume Lelarge:
[...]
>
> Would be quite difficult. And I completely dislike the idea.
>
> If you only need the SQL editor, you can already do this by launching
> PgAdmin directly with the SQL tool.

I'm not sure what you mean by "directly with the SQL tool"?
Anyway. It could be usefull not only for just typing SQL.
Even when access is essentially restricted to some specific database, 
the navigation panel could still be usefull to show objects within that 
specific database and fetch their properties etc. This is done through 
pg_catalog so it should work fine I suppose? I actually can't see what's 
so wrong with this setup (other than someone would obviously have to 
spend some time and effort to develop patches) In fact, that is exactly 
the usage pattern I have with pgadmin most of the time: connect to a 
specific database and _stay_ _within_ that database.


Regards,
Nikolai