Thread: dbuser acess privileges

dbuser acess privileges

From
Durgamahesh Manne
Date:
hi  
 
Respected international pgsql team

pershing=# grant INSERT on public.hyd to ravi;
GRANT
i have granted insert command access to non superuser(ravi)
pershing=> insert into hyd (id,name) values('2','delhi');
INSERT 0 1
here data inserted 


pershing=# grant UPDATE on public.hyd to ravi;
GRANT
i have granted update command access to non superuser(ravi)

pershing=> update public.hyd set id = 3 where name = 'hyderabad';
ERROR:  permission denied for relation hyd
please let me know what is the issue with update command



Re: dbuser acess privileges

From
Ron
Date:
On 4/4/19 5:07 AM, Durgamahesh Manne wrote:
> hi
> Respected international pgsql team
>
> pershing=# grant INSERT on public.hyd to ravi;
> GRANT
> i have granted insert command access to non superuser(ravi)
> pershing=> insert into hyd (id,name) values('2','delhi');
> INSERT 0 1
> here data inserted
>
>
> pershing=# grant UPDATE on public.hyd to ravi;
> GRANT
> i have granted update command access to non superuser(ravi)
>
> pershing=> update public.hyd set id = 3 where name = 'hyderabad';
> ERROR:  permission denied for relation hyd
> please let me know what is the issue with update command

Are there any triggers on public.hyd which modify other tables?

-- 
Angular momentum makes the world go 'round.



Re: dbuser acess privileges

From
Durgamahesh Manne
Date:



On Thu, Apr 4, 2019 at 3:55 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 4/4/19 5:07 AM, Durgamahesh Manne wrote:
> hi
> Respected international pgsql team
>
> pershing=# grant INSERT on public.hyd to ravi;
> GRANT
> i have granted insert command access to non superuser(ravi)
> pershing=> insert into hyd (id,name) values('2','delhi');
> INSERT 0 1
> here data inserted
>
>
> pershing=# grant UPDATE on public.hyd to ravi;
> GRANT
> i have granted update command access to non superuser(ravi)
>
> pershing=> update public.hyd set id = 3 where name = 'hyderabad';
> ERROR:  permission denied for relation hyd
> please let me know what is the issue with update command

Are there any triggers on public.hyd which modify other tables?

--
Angular momentum makes the world go 'round.


Hi
there are no triggers on public.hyd table

Regards
durgamahesh manne
 

Re: dbuser acess privileges

From
Durgamahesh Manne
Date:


On Thu, Apr 4, 2019 at 4:14 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:



On Thu, Apr 4, 2019 at 3:55 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 4/4/19 5:07 AM, Durgamahesh Manne wrote:
> hi
> Respected international pgsql team
>
> pershing=# grant INSERT on public.hyd to ravi;
> GRANT
> i have granted insert command access to non superuser(ravi)
> pershing=> insert into hyd (id,name) values('2','delhi');
> INSERT 0 1
> here data inserted
>
>
> pershing=# grant UPDATE on public.hyd to ravi;
> GRANT
> i have granted update command access to non superuser(ravi)
>
> pershing=> update public.hyd set id = 3 where name = 'hyderabad';
> ERROR:  permission denied for relation hyd
> please let me know what is the issue with update command

Are there any triggers on public.hyd which modify other tables?

--
Angular momentum makes the world go 'round.


Hi
there are no triggers on public.hyd table

Regards
durgamahesh manne
 



 Hi 

i found that there was bug for grant access on update command for non superusers

grant access on update command worked fine on 9.3 version


please i request you to fix grant access bug on update command for nonsupeuser asap in the next pg version 10.8



Regards

durgamahesh manne



RE: dbuser acess privileges

From
Patrick FICHE
Date:

Hi,

 

If I’m not wrong, UPDATE requires SELECT permission as the UPDATE statement needs to read the data to be updated.

So, you should probably add GRANT SELECT and you get it work.

 

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Sent: Thursday, April 4, 2019 12:07 PM
To: pgsql-general@lists.postgresql.org
Subject: dbuser acess privileges

 

hi  

 

Respected international pgsql team

 

pershing=# grant INSERT on public.hyd to ravi;

GRANT

i have granted insert command access to non superuser(ravi)

pershing=> insert into hyd (id,name) values('2','delhi');

INSERT 0 1

here data inserted 

 

 

pershing=# grant UPDATE on public.hyd to ravi;

GRANT

i have granted update command access to non superuser(ravi)

 

pershing=> update public.hyd set id = 3 where name = 'hyderabad';

ERROR:  permission denied for relation hyd

please let me know what is the issue with update command

 

 

 

Attachment

Re: dbuser acess privileges

From
Ron
Date:
You'd think the implicit SELECT perm of that table for the explicit use of UPDATE would be covered by GRANT UPDATE.

On 4/4/19 7:25 AM, Patrick FICHE wrote:

Hi,

 

If I’m not wrong, UPDATE requires SELECT permission as the UPDATE statement needs to read the data to be updated.

So, you should probably add GRANT SELECT and you get it work.

 

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Sent: Thursday, April 4, 2019 12:07 PM
To: pgsql-general@lists.postgresql.org
Subject: dbuser acess privileges

 

hi  

 

Respected international pgsql team

 

pershing=# grant INSERT on public.hyd to ravi;

GRANT

i have granted insert command access to non superuser(ravi)

pershing=> insert into hyd (id,name) values('2','delhi');

INSERT 0 1

here data inserted 

 

 

pershing=# grant UPDATE on public.hyd to ravi;

GRANT

i have granted update command access to non superuser(ravi)

 

pershing=> update public.hyd set id = 3 where name = 'hyderabad';

ERROR:  permission denied for relation hyd

please let me know what is the issue with update command

 

 

 


--
Angular momentum makes the world go 'round.
Attachment

RE: dbuser acess privileges

From
Patrick FICHE
Date:

Here is the extract of documentation relative to GRANT UPDATE

 

UPDATE

Allows UPDATE of any column, or the specific columns listed, of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege. For sequences, this privilege allows the use of the nextval and setval functions. For large objects, this privilege allows writing or truncating the object.

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, April 4, 2019 2:50 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: dbuser acess privileges

 

You'd think the implicit SELECT perm of that table for the explicit use of UPDATE would be covered by GRANT UPDATE.

On 4/4/19 7:25 AM, Patrick FICHE wrote:

Hi,

 

If I’m not wrong, UPDATE requires SELECT permission as the UPDATE statement needs to read the data to be updated.

So, you should probably add GRANT SELECT and you get it work.

 

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Sent: Thursday, April 4, 2019 12:07 PM
To: pgsql-general@lists.postgresql.org
Subject: dbuser acess privileges

 

hi  

 

Respected international pgsql team

 

pershing=# grant INSERT on public.hyd to ravi;

GRANT

i have granted insert command access to non superuser(ravi)

pershing=> insert into hyd (id,name) values('2','delhi');

INSERT 0 1

here data inserted 

 

 

pershing=# grant UPDATE on public.hyd to ravi;

GRANT

i have granted update command access to non superuser(ravi)

 

pershing=> update public.hyd set id = 3 where name = 'hyderabad';

ERROR:  permission denied for relation hyd

please let me know what is the issue with update command

 

 

 

 

--
Angular momentum makes the world go 'round.

Attachment

Re: dbuser acess privileges

From
Durgamahesh Manne
Date:

  

From: Durgamahesh Manne <maheshpostgres9@gmail.com
Sent: Thursday, April 4, 2019 12:07 PM
To: pgsql-general@lists.postgresql.org
Subject: dbuser acess privileges

 

hi  

 

Respected international pgsql team

 

pershing=# grant INSERT on public.hyd to ravi;

GRANT

i have granted insert command access to non superuser(ravi)

pershing=> insert into hyd (id,name) values('2','delhi');

INSERT 0 1

here data inserted 

 

 

pershing=# grant UPDATE on public.hyd to ravi;

GRANT

i have granted update command access to non superuser(ravi)

 

pershing=> update public.hyd set id = 3 where name = 'hyderabad';

ERROR:  permission denied for relation hyd

please let me know what is the issue with update command




On Thu, Apr 4, 2019 at 5:55 PM Patrick FICHE <Patrick.Fiche@aqsacom.com> wrote:

Hi,

 

If I’m not wrong, UPDATE requires SELECT permission as the UPDATE statement needs to read the data to be updated.

So, you should probably add GRANT SELECT and you get it work.

 

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg

 

 

 


 Hi  Patrick Fiche  as per your info

Grant access on update command is worked  AFTER I DID EXECUTE THIS   GRANT SELECT ON TABLE(hyd)  for non superuser in 10.6

 
But

 in 9.3 version    Grant access on update command is worked  EVEN I DID NOT EXECUTE THIS   GRANT SELECT ON TABLE(hyd)  for non superuser 


Regards

Durgamahesh Manne

Attachment

Re: dbuser acess privileges

From
Tom Lane
Date:
Durgamahesh Manne <maheshpostgres9@gmail.com> writes:
> On Thu, Apr 4, 2019 at 5:55 PM Patrick FICHE <Patrick.Fiche@aqsacom.com>
> wrote:
>> If I’m not wrong, UPDATE requires SELECT permission as the UPDATE
>> statement needs to read the data to be updated.

>  in 9.3 version    Grant access on update command is worked  EVEN I DID NOT
> EXECUTE THIS   GRANT SELECT ON TABLE(hyd)  for non superuser

I don't think so.

regression=# create table t1 (f1 int, f2 int);
CREATE TABLE
regression=# create user joe;
CREATE ROLE
regression=# grant update on table t1 to joe;
GRANT
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> update t1 set f1 = 1;
UPDATE 0
regression=> update t1 set f1 = 1 where f2 = 3;
ERROR:  permission denied for relation t1
regression=> select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.25 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)


It's acted that way for a very very long time.

            regards, tom lane