Fwd: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions - Mailing list pgadmin-support

If you created with superuser, it will have access to everything in the instance by default. Your example below does not include the SUPERUSER key word; however, as mine does below:

create user operator with password 'xxxxxxx' superuser createdb inherit login createrole;

 

From the documentation (https://www.postgresql.org/docs/9.2/static/sql-createrole.html):

SUPERUSER
NOSUPERUSER

These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. If not specified, NOSUPERUSER is the default.

 

If you have any questions, please feel free to contact me.

Tim Hearne
CAST / Flex Force Application DBA
Principal DBA

Centralized Development
AT&T Services, Inc.

PROPRIETARY INFORMATION
The Information contained herein is for use only by authorized employees of AT&T, and authorized

Affiliates of AT&T, and is not for general distribution within or outside the respective companies

 

From: Osahon Oduware [mailto:oduwareosahon@gmail.com]
Sent: Friday, March 17, 2017 8:01 AM
To: HEARNE, TIMOTHY S <th1618@att.com>
Subject: Re: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

 

Hi,

 

Thanks for your prompt response. The user was created with the postgres user (superuser) as below:

 

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

 

 

On Fri, Mar 17, 2017 at 3:56 PM, HEARNE, TIMOTHY S <th1618@att.com> wrote:

Did you create the user with SUPERUSER or some other elevated privilege?  Since you did not include the script for the user, it is hard to determine the exact root cause.

If you have any questions, please feel free to contact me.

Tim Hearne
CAST / Flex Force Application DBA
Principal DBA

Centralized Development
AT&T Services, Inc.
e-mail:  timothy.hearne@att.com
http://intranet.att.com/its/cdtworx/content.cfm/home/

Agile Bronze Certified

AT&T CAST team mailbox:  g01494@att.com

PROPRIETARY INFORMATION
The Information contained herein is for use only by authorized employees of AT&T, and authorized

Affiliates of AT&T, and is not for general distribution within or outside the respective companies

 

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Osahon Oduware
Sent: Friday, March 17, 2017 7:46 AM
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

 

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.