Re: PG catalog

[Tom Lane]

"Karki, Sanjay"  writes:
> I need to grant select on privilege in pg_catalog to user so I can connect 
> via Toad Data point ,

Why do you think you need to do that?  Most catalogs have public
select privilege already, and for the ones that don't, there are
very good reasons why not.  I don't know what "Toad Data point"
is, but if it thinks it needs more privilege than is normally
granted, you should be asking very pointed questions about why
and why that shouldn't be considered a security breach.

(Usually we get complaints that the default permissions on the
catalogs are too loose, not too tight.)

regards, tom lane

Re: PG catalog

[David G. Johnston]

On Thursday, May 23, 2024, Karki, Sanjay  wrote:
>
> I need to grant select on privilege in pg_catalog to user so I can connect
> via Toad Data point ,
>
> Users can already select from the tables in pg_catalog, grant able
privileges not required or allowed.  Of course, some specific data is
restricted from non-superusers.

David J.

PG catalog

[Karki, Sanjay]

Hi ,

I need to grant select on privilege in pg_catalog to user so I can connect via 
Toad Data point ,

I tried by
grant select on all tables in schema pg_catalog to group sys;
while connecting as sys.

But it throws me error

grant select on all tables in schema pg_catalog to  sys ;

ERROR: permission denied for table pg_statistic SQL state: 42501


Can I get some help please


Thank you

Sanjay karki



Sanjay Karki

Database Administrator III

ITG

[cid:image001.png@HNMW31RS4NU4.A9W7106ZTAI5]

O: 816-783-8718
M: 816-394-4383
W: www.naic.org

Follow the NAIC on
[cid:image002.png@HNMW31RS4NU4.A9W7106ZTAI5]
 [cid:image003.png@HNMW31RS4NU4.A9W7106ZTAI5]   
[cid:image004.png@HNMW31RS4NU4.A9W7106ZTAI5] 
  
[cid:image005.png@HNMW31RS4NU4.A9W7106ZTAI5] 




--

CONFIDENTIALITY NOTICE

--

This message and any attachments are from the NAIC and are intended only for 
the addressee. Information contained herein is confidential, and may be 
privileged or exempt from disclosure pursuant to applicable federal or state 
law. This message is not intended as a waiver of the confidential, privileged 
or exempted status of the information transmitted. Unauthorized forwarding, 
printing, copying, distribution or use of such information is strictly 
prohibited and may be unlawful. If you are not the addressee, please promptly 
delete this message and notify the sender of the delivery error by e-mail or by 
forwarding it to the NAIC Service Desk at h...@naic.org.

Re: Invalidation pg catalog cache in trigger functions

[Константин Евтеев]

Sorry, I forgot to show version of PostgreSQL:
https://www.postgresql.org/message-id/CAAqA9PQXEmG%3Dk3WpDTmHZL-VKcMpDEA3ZC06Qr0ASO3oTA7bdw%40mail.gmail.com
"Invalidation pg catalog cache in trigger functions"
was detected on "PostgreSQL 9.2.18 on x86_64-unknown-linux-gnu, compiled by
gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit"
Also it can be reproduced on PostgreSQL 9.2.24
For newer versions it is not actual - PostgreSQL 9.3.20, 9.4.15, 9.4.12


But
Issue
https://www.postgresql.org/message-id/20171030125345.1448.24...@wrigleys.postgresql.org

"BUG #14879: Bug connected with table structure modification and trigger
function query plan invalidation"
Is actual for PostgreSQL  9.2.24; 9.3.20; 9.4.12; 9.4.15

In this thread I propose to find out the cause of
Issue
https://www.postgresql.org/message-id/20171030125345.1448.24...@wrigleys.postgresql.org
"BUG #14879: Bug connected with table structure modification and trigger
function query plan invalidation"

--
Konstantin Evteev

Invalidation pg catalog cache in trigger functions

[Константин Евтеев]

There is a bug connected with invalidation pg catalog cache in trigger
functions
Another example of this bug I have already reported [1]

The following bug has been logged on the website:

Bug reference:  14879
Logged by:  Konstantin Evteev
Email address:  konst583(at)gmail(dot)com
PostgreSQL version: 9.4.12
Operating system:Debian GNU/Linux 8 (jessie)
Description:


-- create database tmp;
/*
 CREATE ROLE test_role LOGIN
  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
*/

-- 1) 1st session
psql -U test_role -d tmp

tmp=# create table public.test_tbl(test_id int);
CREATE TABLE
tmp=# DROP SCHEMA if exists _test_schema_1_  cascade;
NOTICE:  schema "_test_schema_1_" does not exist, skipping
DROP SCHEMA

tmp=# CREATE SCHEMA _test_schema_1_
  AUTHORIZATION postgres;
--CREATE SCHEMA

tmp=# alter role test_role set search_path = '_test_schema_1_';
--ALTER ROLE


CREATE OR REPLACE FUNCTION _test_schema_1_.test_func()
  RETURNS void AS
$BODY$
BEGIN
  raise notice 'call test func';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
-- CREATE FUNCTION


CREATE OR REPLACE FUNCTION public.trig()
returns trigger as
$BODY$
BEGIN
perform test_func();
return new;
END;
$BODY$
language plpgsql;
--CREATE FUNCTION




create trigger t_trig before insert or update on public.test_tbl for each
row execute procedure public.trig();
--CREATE TRIGGER



2) 2-nd session
psql -U test_role -d tmp

insert into public.test_tbl (test_id) values (1);
/*
NOTICE:  call test func
INSERT 0 1
*/


3) 1-st session recreate schema 1

DROP SCHEMA if exists _test_schema_1_  cascade;
/*
NOTICE:  drop cascades to function test_func()
DROP SCHEMA
*/

CREATE SCHEMA _test_schema_1_
  AUTHORIZATION postgres;
-- CREATE SCHEMA

CREATE OR REPLACE FUNCTION _test_schema_1_.test_func()
  RETURNS void AS
$BODY$
BEGIN
  raise notice 'call test func';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
-- CREATE FUNCTION


4) 2-nd session

insert into public.test_tbl (test_id) values (2);
/*
ERROR:  function test_func() does not exist
LINE 1: SELECT test_func()
   ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  SELECT test_func()
CONTEXT:  PL/pgSQL function public.trig() line 3 at PERFORM
*/

5) 3-rd session  - new session
psql -U test_role -d tmp
insert into public.test_tbl (test_id) values (3);
/*
NOTICE:  call test func
INSERT 0 1
*/

6)Workaround in any session recreate  trg function - for example in 3rd
session
create or replace function public.trig()
returns trigger as
$BODY$
BEGIN
perform test_func();
return new;
END;
$BODY$
language plpgsql;
--CREATE FUNCTION

7) 2nd session
insert into public.test_tbl (test_id) values (2);
/*
NOTICE:  call test func
INSERT 0 1
*/


[1]
https://www.postgresql.org/message-id/20171030125345.1448.24...@wrigleys.postgresql.org
--
Konstantin Evteev