QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Started by Osahon Oduwarealmost 9 years ago6 messages

oduwareosahon@gmail.com

almost 9 years ago

Hi All,

I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY
privilege on all tables in a schema as shown below:

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]

Next, I test this by trying to UPDATE a column in a table (same schema as
above) with pgAdmin/psql and this works fine by giving a response that the
user has no permission - 'ERROR: permission denied for relation
<table_name>.'

Next, I connect with the same user in QGIS and add a layer from the same
table (same schema as above). I open the attribute table for the layer,
turn on editing mode (by clicking on the pencil-like icon), and edit the
same field/column above. To my surprise, the edit was saved successfully
without any permission error prompt.

Next, I check the value of the field/column (same table/schema as above) in
pgAdmin/psql and it is having the new (edited) value from QGIS. This is
rather strange as it seems QGIS is bypassing the permissions set for the
same user in the PostgreSQL/PostGIS database.

I will be glad if someone can help me unravel this mystery.

Giuseppe Broccolo

giuseppe.broccolo@2ndquadrant.it

almost 9 years ago

In reply to: Osahon Oduware (#1)

Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hi Osahon,

2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon@gmail.com>:

Hi All,

I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY
privilege on all tables in a schema as shown below:

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]

I'd have done this as followed:

REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC;
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name];
GRANT [role_name] TO [user_name];

Next, I test this by trying to UPDATE a column in a table (same schema as
above) with pgAdmin/psql and this works fine by giving a response that the
user has no permission - 'ERROR: permission denied for relation
<table_name>.'

Next, I connect with the same user in QGIS and add a layer from the same
table (same schema as above). I open the attribute table for the layer,
turn on editing mode (by clicking on the pencil-like icon), and edit the
same field/column above. To my surprise, the edit was saved successfully
without any permission error prompt.

Next, I check the value of the field/column (same table/schema as above)
in pgAdmin/psql and it is having the new (edited) value from QGIS. This is
rather strange as it seems QGIS is bypassing the permissions set for the
same user in the PostgreSQL/PostGIS database.

I will be glad if someone can help me unravel this mystery.

Check which user is used the first time you connect to the database through
QGIS, and if you switch the user to [user_name] in a second moment. I'm
wondering if you are keeping some privileges from a previous session.

All the best,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

Osahon Oduware

oduwareosahon@gmail.com

almost 9 years ago

In reply to: Giuseppe Broccolo (#2)

Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hi Giuseppe,

Thanks for the response. I have provided the GRANTS and other PostgreSQL
setup scripts below as it includes what you have suggested:

ROLE
---------
CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE NOREPLICATION;

USER
----------
CREATE USER <username> WITH PASSWORD '<password>'

REVOKES
----------------
REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;

GRANTS
-------------
GRANT CONNECT ON DATABASE <database_name> TO <role_name>;
GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;
GRANT <role_name> TO <username>;

I cannot but wonder why these privileges are working when tested in
pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database.

On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo <
giuseppe.broccolo@2ndquadrant.it> wrote:

Show quoted text

Hi Osahon,

2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon@gmail.com>:

Hi All,

I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY
privilege on all tables in a schema as shown below:

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]

I'd have done this as followed:

REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC;
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name];
GRANT [role_name] TO [user_name];

Next, I test this by trying to UPDATE a column in a table (same schema as
above) with pgAdmin/psql and this works fine by giving a response that the
user has no permission - 'ERROR: permission denied for relation
<table_name>.'

Next, I connect with the same user in QGIS and add a layer from the same
table (same schema as above). I open the attribute table for the layer,
turn on editing mode (by clicking on the pencil-like icon), and edit the
same field/column above. To my surprise, the edit was saved successfully
without any permission error prompt.

Next, I check the value of the field/column (same table/schema as above)
in pgAdmin/psql and it is having the new (edited) value from QGIS. This is
rather strange as it seems QGIS is bypassing the permissions set for the
same user in the PostgreSQL/PostGIS database.

I will be glad if someone can help me unravel this mystery.

Check which user is used the first time you connect to the database
through QGIS, and if you switch the user to [user_name] in a second moment.
I'm wondering if you are keeping some privileges from a previous session.

All the best,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

John Scalia

jayknowsunix@gmail.com

almost 9 years ago

In reply to: Osahon Oduware (#3)

Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

While I do not know QGIS, I'm wondering if it's similar to some of our
applications where they always use the same system login for the database
while each user provides a unique login to the application. Have you ever
set log_connections in your postgresql.conf file? That would show you which
user is connecting during your attempts, and they might very well be
something you're not expecting. As far as I know, there is no way for any
application to bypass PostgreSQL's internal security model.

On Fri, Mar 17, 2017 at 11:43 AM, Osahon Oduware <oduwareosahon@gmail.com>
wrote:

Show quoted text

Hi Giuseppe,

Thanks for the response. I have provided the GRANTS and other PostgreSQL
setup scripts below as it includes what you have suggested:

ROLE
---------
CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE NOREPLICATION;

USER
----------
CREATE USER <username> WITH PASSWORD '<password>'

REVOKES
----------------
REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;

GRANTS
-------------
GRANT CONNECT ON DATABASE <database_name> TO <role_name>;
GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;
GRANT <role_name> TO <username>;

I cannot but wonder why these privileges are working when tested in
pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database.

On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo <giuseppe.broccolo@
2ndquadrant.it> wrote:

Hi Osahon,

2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon@gmail.com>:

Hi All,

I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY
privilege on all tables in a schema as shown below:

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]

I'd have done this as followed:

REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC;
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name];
GRANT [role_name] TO [user_name];

Next, I test this by trying to UPDATE a column in a table (same schema
as above) with pgAdmin/psql and this works fine by giving a response that
the user has no permission - 'ERROR: permission denied for relation
<table_name>.'

Next, I connect with the same user in QGIS and add a layer from the same
table (same schema as above). I open the attribute table for the layer,
turn on editing mode (by clicking on the pencil-like icon), and edit the
same field/column above. To my surprise, the edit was saved successfully
without any permission error prompt.

Next, I check the value of the field/column (same table/schema as above)
in pgAdmin/psql and it is having the new (edited) value from QGIS. This is
rather strange as it seems QGIS is bypassing the permissions set for the
same user in the PostgreSQL/PostGIS database.

I will be glad if someone can help me unravel this mystery.

Check which user is used the first time you connect to the database
through QGIS, and if you switch the user to [user_name] in a second moment.
I'm wondering if you are keeping some privileges from a previous session.

All the best,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

Giuseppe Broccolo

giuseppe.broccolo@2ndquadrant.it

almost 9 years ago

In reply to: John Scalia (#4)

Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hi all,

2017-03-17 17:09 GMT+01:00 John Scalia <jayknowsunix@gmail.com>:

While I do not know QGIS, I'm wondering if it's similar to some of our
applications where they always use the same system login for the database
while each user provides a unique login to the application. Have you ever
set log_connections in your postgresql.conf file? That would show you which
user is connecting during your attempts, and they might very well be
something you're not expecting. As far as I know, there is no way for any
application to bypass PostgreSQL's internal security model.

Exactly, so I repost here my initial question:

On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo <

giuseppe.broccolo@2ndquadrant.it> wrote:

Check which user is used the first time you connect to the database
through QGIS, and if you switch the user to [user_name] in a second moment.
I'm wondering if you are keeping some privileges from a previous session.

Regards,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

Osahon Oduware

oduwareosahon@gmail.com

almost 9 years ago

In reply to: John Scalia (#4)

Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hi John,

Thanks for your response. From my experience as a Software Developer, I
don't think QGIS can logon to my database/schema/table with a dedicated
user as they need authentication to do so. What you described about using a
dedicated user in applications is only possible because that user was
created in the database server and granted necessary privileges/permissions.

On Fri, Mar 17, 2017 at 5:09 PM, John Scalia <jayknowsunix@gmail.com> wrote:

Show quoted text

While I do not know QGIS, I'm wondering if it's similar to some of our
applications where they always use the same system login for the database
while each user provides a unique login to the application. Have you ever
set log_connections in your postgresql.conf file? That would show you which
user is connecting during your attempts, and they might very well be
something you're not expecting. As far as I know, there is no way for any
application to bypass PostgreSQL's internal security model.

On Fri, Mar 17, 2017 at 11:43 AM, Osahon Oduware <oduwareosahon@gmail.com>
wrote:

Hi Giuseppe,

Thanks for the response. I have provided the GRANTS and other PostgreSQL
setup scripts below as it includes what you have suggested:

ROLE
---------
CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE NOREPLICATION;

USER
----------
CREATE USER <username> WITH PASSWORD '<password>'

REVOKES
----------------
REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;

GRANTS
-------------
GRANT CONNECT ON DATABASE <database_name> TO <role_name>;
GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;
GRANT <role_name> TO <username>;

I cannot but wonder why these privileges are working when tested in
pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database.

On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo <
giuseppe.broccolo@2ndquadrant.it> wrote:

Hi Osahon,

2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon@gmail.com>:

Hi All,

I created a "Read-only" User in PostgreSQL via a Role with "SELECT"
ONLY privilege on all tables in a schema as shown below:

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]

I'd have done this as followed:

REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC;
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name];
GRANT [role_name] TO [user_name];

Next, I test this by trying to UPDATE a column in a table (same schema
as above) with pgAdmin/psql and this works fine by giving a response that
the user has no permission - 'ERROR: permission denied for relation
<table_name>.'

Next, I connect with the same user in QGIS and add a layer from the
same table (same schema as above). I open the attribute table for the
layer, turn on editing mode (by clicking on the pencil-like icon), and edit
the same field/column above. To my surprise, the edit was saved
successfully without any permission error prompt.

Next, I check the value of the field/column (same table/schema as
above) in pgAdmin/psql and it is having the new (edited) value from QGIS.
This is rather strange as it seems QGIS is bypassing the permissions set
for the same user in the PostgreSQL/PostGIS database.

I will be glad if someone can help me unravel this mystery.

Check which user is used the first time you connect to the database
through QGIS, and if you switch the user to [user_name] in a second moment.
I'm wondering if you are keeping some privileges from a previous session.

All the best,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it