Re: user privileges

[Charles Clavadetscher]

Hello

On 2021-06-21 12:40, Atul Kumar wrote:

Hi,

My question can be silly but I need to ask that if a user is created
without login privilege then what is the use of below command why
don't postgres prompt error on it

CREATE ROLE nolog_user WITH PASSWORD 'pass1';


A user without login privilege is usally called a group and is used for 
bundling privileges that you can grant granting the role to individual 
users. This helps you to keep more order and structure in your DB 
privileges.


You may also have a user that can login and for whatever reason must be 
restricted access for a certain time.


as the user is created with login privileges then what is the use such 
user ?


See above.


Where such users having no login privileges can be used ?


Yes, you use it as a group.

--
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

https://www.swisspug.org

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|Swiss PostgreSQL|
|   Users Group  |
++

Re: About to know the info of foreign table reference used in any of call ,views,functions

[Charles Clavadetscher]

Hi

On 2021-04-27 09:15, Durgamahesh Manne wrote:

Hi Team

By using the system view and function

"I need to get the info of foreign table reference used in any of call
,views,functions"

I found info of views and functions and sprocs that are executed
frequently through application using pg_stat_user_functions view

Please help  for the info i need

Thanks & Regards
Durgamahesh Manne


I am not sure how reliable that is, but as you mention you could query 
the system catalogs.


The definition of views and the code of functions are stored in pg_views 
and pg_proc.
So you can check if those contain the name of the tables you are 
interested in.


You can find a list of the foreign tables using:

SELECT relnamespace::regnamespace AS schema_name,
   relname AS table_name
FROM pg_class
WHERE relkind = 'f';

You can then use this information to query views defintions and function 
bodies:


SELECT schemaname,
   viewname
FROM pg_views
WHERE definition ~ '(schema_name\.)?table_name';

SELECT pronamespace::regnamespace,
   proname
FROM pg_proc
WHERE prosrc ~ '(schema_name\.)?table_name';

schema_name and table_name refer to the result of the first query.

If you have overloaded functions you may need to extract more 
information to identify them correctly, such as the list of parameters. 
The documentation is very helpful in this context.


Hope this helps.

Regards
Charles

--
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

https://www.swisspug.org

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|Swiss PostgreSQL|
|   Users Group  |
++

Re: hstore each() function - returned order??

[Charles Clavadetscher]

Hi

On 2021-03-12 04:46, Brent Wood wrote:

Hi,

 I'm using the following in an SQL :

 select (EACH(value)).key as measurement_key,
   (EACH(value)).value as value from t_reading_hstore;

I'm assuming this will give two columns containing the key/value pairs
in the hstore record.

The docs suggest the order of the keys/values returned is
undetermined. That is a bit ambiguous to me.

The order is not a problem in this case: as long as the keys and
values are returned in the SAME order, what that order is I don't
care.

Just that the key is always returned in the same row as its value.

It does seem to work, at least in my test cases, but I need to know if
that is just luck, or if it is a safe assumption to make.

Can anyone confirm this is a robust assumption for me?


You can order the result by key to have a specific order that remains 
between calls.


select (EACH(value)).key as measurement_key,
   (EACH(value)).value as value from t_reading_hstore
order by (EACH(value)).key;

Bye
Charles



Thanks

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

 [1]

 Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz [1] Facebook [2] LinkedIn [3] Twitter
[4] Instagram [5]

 To ensure compliance with legal requirements and to maintain cyber
security standards, NIWA's IT systems are subject to ongoing
monitoring, activity logging and auditing. This monitoring and
auditing service may be provided by third parties. Such third parties
can access information transmitted to, processed by and stored on
NIWA's IT systems

Links:
--
[1] https://www.niwa.co.nz
[2] https://www.facebook.com/nzniwa
[3] https://www.linkedin.com/company/niwa
[4] https://twitter.com/niwa_nz
[5] https://www.instagram.com/niwa_science


--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PostgreSQL  |
|   Users Group |
|   |
+---+

Re: Table sizes

[Charles Clavadetscher]

Hello

On 2020-09-30 15:54, luis.robe...@siscobra.com.br wrote:

De: "Charles Clavadetscher" 
Para: "luis.roberto" 
Cc: "pgsql-general" 
Enviadas: Quarta-feira, 30 de setembro de 2020 10:46:39
Assunto: Re: Table sizes

Hello

On 2020-09-30 14:11, luis.robe...@siscobra.com.br wrote:

Hi!

I'm trying to use this query to get table sizes, however I'm

getting a

strange error:

select tablename,pg_relation_size(tablename::text)
from pg_tables;

In PG 13:

SQL Error [42P01]: ERROR: relation "sql_implementation_info" does

not

exist

In PG 12:

SQL Error [42P01]: ERROR: relation "sql_parts" does not exist


Try like this:

select schemaname,
tablename,
pg_relation_size((schemaname || '.' || '"' || tablename ||
'"')::regclass)
from pg_tables;

You need to schema qualify the tables. Additionally, if you happen
to
have table names that have a mix of capital and non capital letters
or
contain other characters that might be problematic, you need to
enclose
the table name in double quotes.

Regards
Charles

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PostgreSQL  |
|   Users Group |
|   |
+---+


-

Thanks, this worked.

I wonder though, why calling pg_relation_size('users') work (I don't
need to specify the schema).


Glad to hear that.

The other problem probably depend on your search_path.

You can look at it using (in a psql shell):

show search_path;

Tables that are in your search_path don't need to be schema qualified, 
those that aren't need it.

If you need to, you can change your search_path.

set search_path to ...;

To change it permanently you can use ALTER ROLE:

ALTER ROLE  SET search_path=... ;

Have a look for more information at 
https://www.postgresql.org/docs/current/ddl-schemas.html (chapter 
5.9.3).


Regards
Charles

Re: Table sizes

[Charles Clavadetscher]

Hello

On 2020-09-30 14:11, luis.robe...@siscobra.com.br wrote:

Hi!

I'm trying to use this query to get table sizes, however I'm getting a
strange error:

select tablename,pg_relation_size(tablename::text)
  from pg_tables;

In PG 13:

SQL Error [42P01]: ERROR: relation "sql_implementation_info" does not
exist

In PG 12:

SQL Error [42P01]: ERROR: relation "sql_parts" does not exist


Try like this:

select schemaname,
   tablename,
   pg_relation_size((schemaname || '.' || '"' || tablename || 
'"')::regclass)

from pg_tables;

You need to schema qualify the tables. Additionally, if you happen to 
have table names that have a mix of capital and non capital letters or 
contain other characters that might be problematic, you need to enclose 
the table name in double quotes.


Regards
Charles

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PostgreSQL  |
|   Users Group |
|   |
+---+

Re: Slow SELECT

[Charles Clavadetscher]

On 2020-05-26 11:10, Charles Clavadetscher wrote:

Hello

On 2020-05-26 10:38, Frank Millman wrote:

On 2020-05-26 9:32 AM, Olivier Gautherot wrote:

Hi Frank,

On Tue, May 26, 2020 at 9:23 AM Frank Millman <mailto:fr...@chagford.com>> wrote:


Hi all

I have a SELECT that runs over 5 times slower on PostgreSQL 
compared

with Sql Server and sqlite3. I am trying to understand why.

I have a table that looks like this (simplified) -

CREATE TABLE my_table (
      row_id SERIAL PRIMARY KEY,
      deleted_id INT DEFAULT 0,
      fld_1 INT REFERENCES table_1(row_id),
      fld_2 INT REFERENCES table_2(row_id),
      fld_3 INT REFERENCES table_3(row_id),
      fld_4 INT REFERENCES table_4(row_id),
      tran_date DATE,
      tran_total DEC(21,2)
      );

CREATE UNIQUE INDEX my_table_ndx ON my_table (fld_1, fld_2, 
fld_3,

fld_4, tran_date) WHERE deleted_id = 0;

The table sizes are -
      my_table : 167 rows
      table_1 : 21 rows
      table_2 : 11 rows
      table_3 : 3 rows
      table_4 : 16 rows

Therefore for each tran_date in my_table there are potentially
21x11x3x16 = 11088 rows. Most will be null.

I want to select the row_id for the last tran_date for each of 
those

potential groups. This is my select -

      SELECT (
          SELECT a.row_id FROM my_table a
          WHERE a.fld_1 = b.row_id
          AND a.fld_2 = c.row_id
          AND a.fld_3 = d.row_id
          AND a.fld_4 = e.row_id
          AND a.deleted_id = 0
          ORDER BY a.tran_date DESC LIMIT 1
      )
      FROM table_1 b, table_2 c, table_3 d, table_4 e

Out of 11088 rows selected, 103 are not null.

On identical data, this takes 0.06 sec on SQL Server, 0.04 sec on
sqlite3, and 0.31 sec on PostgreSQL.


SQL Server does a good job at caching data in memory. PostgreSQL does 
too on consecutive calls to the same table. What execution time do 
you get if you issue the query a second time?


My first guess would be to add an index on my_table.tran_date and 
check in EXPLAIN that you don't have a SEQUENTIAL SCAN on that table.


I have looked at the EXPLAIN, but I don't really know what to 
look for.

I can supply it if that would help.

Thanks for any advice.



Thanks Olivier. Unfortunately that did not help.

I was already running the query twice and only timing the second one.

I added the index on tran_date. The timing is the same, and EXPLAIN
shows that it is using a SEQUENTIAL SCAN.

Here is the EXPLAIN -


 Nested Loop  (cost=0.00..64155.70 rows=11088 width=4)
   ->  Nested Loop  (cost=0.00..10.36 rows=528 width=12)
 ->  Nested Loop  (cost=0.00..2.56 rows=33 width=8)
   ->  Seq Scan on table_2 c  (cost=0.00..1.11 rows=11 
width=4)

   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
 ->  Seq Scan on table_3 d  (cost=0.00..1.03 
rows=3 width=4)

 ->  Materialize  (cost=0.00..1.24 rows=16 width=4)
   ->  Seq Scan on table_4 e  (cost=0.00..1.16 rows=16 
width=4)

   ->  Materialize  (cost=0.00..1.31 rows=21 width=4)
 ->  Seq Scan on table_1 b  (cost=0.00..1.21 rows=21 width=4)
   SubPlan 1
 ->  Limit  (cost=5.77..5.77 rows=1 width=8)
   ->  Sort  (cost=5.77..5.77 rows=1 width=8)
 Sort Key: a.tran_date DESC
 ->  Seq Scan on my_table a  (cost=0.00..5.76 rows=1 
width=8)

   Filter: ((fld_1 = b.row_id) AND (fld_2 =
c.row_id) AND (fld_3 = d.row_id) AND (fld_4 = e.row_id) AND
(deleted_id = 0))


Frank


If I see it correct, the query runs sequential scans on all tables,
i.e. table_1 to table_4.
Do you have an index on the referenced keys (row_id) in table_1 to 
table_4?


It happens often that referenced keys are not indexed, leading to poor
execution plans.

Bye
Charles


I noticed later that you have very small tables. This will probably lead 
to a sequential scan althought there is an index in place.


I am not sure if it makes a difference, but what about using explicit 
joins?


SELECT a.row_id FROM my_table a
JOIN b table_1 ON (b.row_id = a.fld_1)
JOIN c table_2 ON (c.row_id = a.fld_2)
JOIN d table_3 ON (d.row_id = a.fld_3)
JOIN e table_4 ON (e.row_id = a.fld_4)
WHERE a.deleted_id = 0
ORDER BY a.tran_date DESC LIMIT 1;

Regards
Charles

Re: Slow SELECT

[Charles Clavadetscher]

Hello

On 2020-05-26 10:38, Frank Millman wrote:

On 2020-05-26 9:32 AM, Olivier Gautherot wrote:

Hi Frank,

On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote:


Hi all

I have a SELECT that runs over 5 times slower on PostgreSQL 
compared

with Sql Server and sqlite3. I am trying to understand why.

I have a table that looks like this (simplified) -

CREATE TABLE my_table (
      row_id SERIAL PRIMARY KEY,
      deleted_id INT DEFAULT 0,
      fld_1 INT REFERENCES table_1(row_id),
      fld_2 INT REFERENCES table_2(row_id),
      fld_3 INT REFERENCES table_3(row_id),
      fld_4 INT REFERENCES table_4(row_id),
      tran_date DATE,
      tran_total DEC(21,2)
      );

CREATE UNIQUE INDEX my_table_ndx ON my_table (fld_1, fld_2, fld_3,
fld_4, tran_date) WHERE deleted_id = 0;

The table sizes are -
      my_table : 167 rows
      table_1 : 21 rows
      table_2 : 11 rows
      table_3 : 3 rows
      table_4 : 16 rows

Therefore for each tran_date in my_table there are potentially
21x11x3x16 = 11088 rows. Most will be null.

I want to select the row_id for the last tran_date for each of 
those

potential groups. This is my select -

      SELECT (
          SELECT a.row_id FROM my_table a
          WHERE a.fld_1 = b.row_id
          AND a.fld_2 = c.row_id
          AND a.fld_3 = d.row_id
          AND a.fld_4 = e.row_id
          AND a.deleted_id = 0
          ORDER BY a.tran_date DESC LIMIT 1
      )
      FROM table_1 b, table_2 c, table_3 d, table_4 e

Out of 11088 rows selected, 103 are not null.

On identical data, this takes 0.06 sec on SQL Server, 0.04 sec on
sqlite3, and 0.31 sec on PostgreSQL.


SQL Server does a good job at caching data in memory. PostgreSQL does 
too on consecutive calls to the same table. What execution time do you 
get if you issue the query a second time?


My first guess would be to add an index on my_table.tran_date and 
check in EXPLAIN that you don't have a SEQUENTIAL SCAN on that table.


I have looked at the EXPLAIN, but I don't really know what to look 
for.

I can supply it if that would help.

Thanks for any advice.



Thanks Olivier. Unfortunately that did not help.

I was already running the query twice and only timing the second one.

I added the index on tran_date. The timing is the same, and EXPLAIN
shows that it is using a SEQUENTIAL SCAN.

Here is the EXPLAIN -


 Nested Loop  (cost=0.00..64155.70 rows=11088 width=4)
   ->  Nested Loop  (cost=0.00..10.36 rows=528 width=12)
 ->  Nested Loop  (cost=0.00..2.56 rows=33 width=8)
   ->  Seq Scan on table_2 c  (cost=0.00..1.11 rows=11 
width=4)

   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
 ->  Seq Scan on table_3 d  (cost=0.00..1.03 rows=3 
width=4)

 ->  Materialize  (cost=0.00..1.24 rows=16 width=4)
   ->  Seq Scan on table_4 e  (cost=0.00..1.16 rows=16 
width=4)

   ->  Materialize  (cost=0.00..1.31 rows=21 width=4)
 ->  Seq Scan on table_1 b  (cost=0.00..1.21 rows=21 width=4)
   SubPlan 1
 ->  Limit  (cost=5.77..5.77 rows=1 width=8)
   ->  Sort  (cost=5.77..5.77 rows=1 width=8)
 Sort Key: a.tran_date DESC
 ->  Seq Scan on my_table a  (cost=0.00..5.76 rows=1 
width=8)

   Filter: ((fld_1 = b.row_id) AND (fld_2 =
c.row_id) AND (fld_3 = d.row_id) AND (fld_4 = e.row_id) AND
(deleted_id = 0))


Frank


If I see it correct, the query runs sequential scans on all tables, i.e. 
table_1 to table_4.
Do you have an index on the referenced keys (row_id) in table_1 to 
table_4?


It happens often that referenced keys are not indexed, leading to poor 
execution plans.


Bye
Charles

Re: FDW and RLS

[Charles Clavadetscher]

Hello

On 2020-05-25 15:50, Laurenz Albe wrote:

On Fri, 2020-05-22 at 08:02 -0500, Ted Toth wrote:

Will RLS be applied to data being retrieved via a FDW?


ALTER FOREIGN TABLE rp_2019 ENABLE ROW LEVEL SECURITY;
ERROR:  "rp_2019" is not a table

Doesn't look good.

Yours,
Laurenz Albe


Actually it does work if you set the policy on the source table and 
access it using the user defined in the user mappings on the foreign 
table on the remote server.


Server 1:

charles@kofdb.archivedb.5432=# \d public.test_fdw_rls
 Table "public.test_fdw_rls"
  Column  |  Type   | Collation | Nullable | Default
--+-+---+--+-
 id   | integer |   |  |
 content  | text|   |  |
 username | text|   |  |
Policies:
POLICY "kofadmin_select" FOR SELECT
  TO kofadmin
  USING ((username = ("current_user"())::text))

kofadmin@kofdb.archivedb.5432=> \dp public.test_fdw_rls
 Access privileges
 Schema | Name | Type  |Access privileges| Column 
privileges |   Policies

+--+---+-+---+--
 public | test_fdw_rls | table | charles=arwdDxt/charles+|   
| kofadmin_select (r):+
|  |   | kofadmin=arwd/charles   |   
|   (u): (username = ("current_user"())::text)+
|  |   | |   
|   to: kofadmin


charles@kofdb.archivedb.5432=# SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;


charles@kofdb.archivedb.5432=# SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;

 current_user
--
 charles
(1 row)

 id | content  | username
+--+--
  1 | Text for charles | charles
  1 | Access from fdw via user fdwsync | fdwsync
(2 rows)

charles@kofdb.archivedb.5432=# set role fdwsync ;
SET
charles@kofdb.archivedb.5432=> SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls;

 current_user
--
 fdwsync
(1 row)

 id | content  | username
+--+--
  1 | Access from fdw via user fdwsync | fdwsync
(1 row)

On the server accessing the table via FDW:

kofadmin@kofdb.t-archivedb.5432=> \deu+
List of user mappings
   Server   | User name | FDW options
+---+-
 kofdb_prod | kofadmin  | (password 'mysecret', "user" 'fdwsync')

kofadmin@kofdb.t-archivedb.5432=> SELECT CURRENT_USER; SELECT * FROM 
public.test_fdw_rls ;

 current_user
--
 kofadmin
(1 row)

 id | content  | username
+--+--
  1 | Access from fdw via user fdwsync | fdwsync
(1 row)

Regards
Charles

Re: How to get the OID of a view

[Charles Clavadetscher]

Hello
--

> On 22.05.2020, at 18:15, stan  wrote:
> 
> I am trying to write a query to return the names, and data types of all the
> columns in a view. It has been pointed out to me that the best approach
> would be using pg_catalog. OK, so I found pg_view, which I can get the names
> of a the views from and pg_attribute which can give me the column names,
> but it looks like i need to join this on OID, and pg_table does not have
> that data.
> 
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>-- Benjamin Franklin
> 
> 

You find the oid of the views in pg_catalog.pg_class (relkind 'v').

Regards
Charles

Re: Exportacion por lotes

[Charles Clavadetscher]

Hola



---
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

Tel: +41-79-345 18 88
-
> On 03.03.2020, at 20:21, Hernan Jesus Gonzalez Carmona 
>  wrote:
> 
> Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me acabo 
> de inscribir en esta lista de correo y desde ya me disculpo si en este 
> mensaje violo alguna normativa de la lista de correo pero necesito ayuda que 
> me apura mucho.
> 
> Quien me podria ayudar con información respecto de como exportar una consulta 
> en distintos archivos según una condición determinada y que cada archivo 
> tenga el nombre de dicha condición, es decir, si tengo una tabla con 100 
> registros y uno de los campos tiene un dominio de 4 valor distintos, necesito 
> generar 4 archivos cada uno con nombre de cada valor posible de dicho campo, 
> y que la suma de los registros de los 4 archivos sea 100
> 
> ¿me explico?
> 
> Desde ya muy agradecido por vuestro tiempo
> 
> HJGC

Hay una lista de correo en español.
En esta el idioma en uso es el inglés.

About your question.
You can export data to a file using copy or \copy (if you work fro a remote 
client). You can use a select statement that gives you the exact result that 
you need.

For example. Let's assume that you have a table (e.g. t) with a column that you 
want to use for grouping (e.g. group_c). This is basically your use case. Then 
you can do the following in psql:

\copy (select * from t where group_c = 'some value') to filename.csv csv header 
delimiter ';' null ''

The exact syntax is on the postgresql page.

Unfortunately I have no access right now to a PC and can't check for links. I 
will catch that up later if no one else responds.

Regards
Charles

Re: Help : Removal of leading spaces in all the columns of a table

[Charles Clavadetscher]

CCing the list.

Hello

On 2020-02-12 14:30, Pete Yunker wrote:

Shouldn’t the replacement string in regexp_replace be a single space
instead of a 0-length string?


Yes, correct.

SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', ' ', 'g') FROM test;
 regexp_replace

 ABC
 ABC
 A B C
 A B C
(4 rows)

The 3rd row was not modified correctly in my previous example.
Thank you for pointing out.

And to get back to the OP I saw that he wants a statement for all 
columns - I assume of a table.


In this case it is possible to generate the statement using the system 
catalogs.


I modified the test table to illustrate this.

\d test
 Table "public.test"
 Column |   Type| Collation | Nullable | Default
+---+---+--+-
 txt| text  |   |  |
 i  | integer   |   |  |
 txt2   | character varying |   |  |

select * from test;
  txt   | i |   txt2
+---+---
 abc| 1 |   de   f
 abc| 2 | d ef
   ab c | 3 |d  e f
 a b c  | 4 | def
(4 rows)

Now create the statement replacing 'test' with the name of the table 
that must be processed.


SELECT format($$UPDATE %I.%I SET (%s) = (%s)$$,
  c.relnamespace::REGNAMESPACE,
  c.relname,
  string_agg(a.attname, ', '),
  string_agg(format($$regexp_replace(upper(trim(%s)),'[ 
]{2,}', ' ', 'g')$$, a.attname), ', '))

FROM pg_catalog.pg_attribute a,
 pg_catalog.pg_class c
WHERE a.attrelid = c.oid
AND a.atttypid::regtype IN ('text','varchar')
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relname = 'test'
GROUP BY c.relnamespace::regnamespace,
   c.relname;

And execute the resulting statement.

UPDATE public.test SET (txt, txt2) = (regexp_replace(upper(trim(txt)),'[ 
]{2,}', ' ', 'g'), regexp_replace(upper(trim(txt2)),'[ ]{2,}', ' ', 
'g'));


Content of the table after the update.

select * from test;
  txt  | i | txt2
---+---+---
 ABC   | 1 | DE F
 ABC   | 2 | D E F
 A B C | 3 | D E F
 A B C | 4 | DEF
(4 rows)

Regards
Charles



On Feb 12, 2020, at 8:23 AM, Charles Clavadetscher 
 wrote:


Hello

On 2020-02-12 13:42, srikkanth wrote:

Hi Team,
How can i write the syntax to remove the leading spaces on a table 
for

all the columns.
Also, want to know how to do the all words of all the columns in
capital along with removing of leading\excessive\trailing spaces at a
time.
Can you please help me out, let me know in case of any inputs.


You may combine existing functions:

CREATE TABLE test (txt TEXT);

INSERT INTO test VALUES ('abc'),('abc'),('  ab c'),('a 
b c');


SELECT * FROM test;
 txt

abc
abc
  ab c
a b c
(4 rows)

Now, assuming that "excessive" spaces means that there must be at most 
one between words:


SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', '', 'g') FROM test;
regexp_replace

ABC
ABC
AB C
A B C
(4 rows)

first you remove all leading and trailing spaces (trim).
Then you transform all letters to upper case (upper).
Finally you remove all spaces but one between the words 
(regexp_replace with '[ ]{2,}' meaning 2 or more spaces to be replaced 
with empty string '' for all occurrences in the string. 'g' means 
global).


The order of the calls is not really relevant for the result.

Use UPDATE test SET txt instead of a select if you want to update your 
table at once.

I usually prefer to see the result before I act on the data ;-)

Bye
Charles


Thanks,
Srikanth B


--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Re: Help : Removal of leading spaces in all the columns of a table

[Charles Clavadetscher]

Hello

On 2020-02-12 13:42, srikkanth wrote:

Hi Team,

How can i write the syntax to remove the leading spaces on a table for
all the columns.

Also, want to know how to do the all words of all the columns in
capital along with removing of leading\excessive\trailing spaces at a
time.

Can you please help me out, let me know in case of any inputs.


You may combine existing functions:

CREATE TABLE test (txt TEXT);

INSERT INTO test VALUES ('abc'),('abc'),('  ab c'),('a b 
c');


SELECT * FROM test;
  txt

 abc
 abc
   ab c
 a b c
(4 rows)

Now, assuming that "excessive" spaces means that there must be at most 
one between words:


SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', '', 'g') FROM test;
 regexp_replace

 ABC
 ABC
 AB C
 A B C
(4 rows)

first you remove all leading and trailing spaces (trim).
Then you transform all letters to upper case (upper).
Finally you remove all spaces but one between the words (regexp_replace 
with '[ ]{2,}' meaning 2 or more spaces to be replaced with empty string 
'' for all occurrences in the string. 'g' means global).


The order of the calls is not really relevant for the result.

Use UPDATE test SET txt instead of a select if you want to update your 
table at once.

I usually prefer to see the result before I act on the data ;-)

Bye
Charles


Thanks,

Srikanth B


--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Re: POLL: Adding transaction status to default psql prompt

[Charles Clavadetscher]

Please answer +1 if you want or don't mind seeing transaction status by
default in psql or -1 if you would prefer to keep the current default.


+1

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Re: Trigger

[Charles Clavadetscher]

Hello

On 2019-10-11 12:59, Sonam Sharma wrote:

Can someone please help me in how to list all the triggers with their
respective tables and the trigger body


You can use the catalogs:

SELECT t.tgname, t.tgrelid::regclass, t.tgfoid::regprocedure as 
function_name, pg_get_functiondef(t.tgfoid) as function_body FROM 
pg_trigger t WHERE NOT tgisinternal;


There is additional information in the table such as when the trigger 
fire, etc.
Have a look at: 
https://www.postgresql.org/docs/12/catalog-pg-trigger.html


Another way is querying the information schema:

SELECT * FROM information_schema.triggers;

https://www.postgresql.org/docs/12/infoschema-triggers.html
However you won't get there the function body.

Regards
Charles

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PostgreSQL  |
|   Users Group |
|   |
+---+

Re: Variable constants ?

[Charles Clavadetscher]

On 2019-08-16 14:50, Rich Shepard wrote:

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:

Another way to keep a history is using a daterange instead of two 
columns for start and end date. Something like


create table labor_rate_mult (
 rate   real primary_key,
 validity   daterange not null
)


Charles,

Just out of curiosity, what is the range for a rate that is still 
current?

Does it change every day?

Regards,

Rich


Hi Rich

That would be a range with an empty upper bound. Let's say that the rate 
is valid since 2019-08-14 then the range would look like


[2019-08-14,)

A query to find the current rate would look like:

SELECT rate
FROM labor_rate_mult
WHERE validity @> CURRENT_DATE;

Here you can find documentation on the range types (cool stuff I 
believe):


https://www.postgresql.org/docs/11/rangetypes.html

Regards
Charles

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Neugasse 84
CH – 8005 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PostgreSQL  |
|   Users Group |
|   |
+---+

Re: Variable constants ?

[Charles Clavadetscher]

On 2019-08-15 23:27, Rich Shepard wrote:

On Thu, 15 Aug 2019, stan wrote:

I need to put a few bossiness constants, such as a labor rate 
multiplier
in an application. I am adverse to hard coding these things. The best 
plan
i have come up with so far is to store them in a table, which would 
have

only 1 row, and a column for each needed constant.

Anyone have a better way to do this?

Failing a better way is there some way I can limit this table to only
allow one row to exist?


Stan,

I've resolved similar issues with changing regulatory agency staff. For 
your

application(s) I suggest a table like this:

create table labor_rate_mult (
  rate  real primary_key,
  start_datedate not null,
  end_date  date
)

This provides both a history of labor rate multipliers and the ability 
to

select either the most current one or a previous one.

If other factors affect the rate, add attribute columns for them.

Regards,

Rich


Another way to keep a history is using a daterange instead of two 
columns for start and end date. Something like


create table labor_rate_mult (
  rate  real primary_key,
  validity  daterange not null
)

This makes it easier to manage and avoid e.g. overlappings.

Regards
Charles

RE: Potentially undocumented behaviour change in Postgres 11 concerning OLD record in an after insert trigger

[Charles Clavadetscher]

Hello

 

From: Kristjan Tammekivi  
Sent: Freitag, 4. Januar 2019 11:46
To: pgsql-gene...@postgresql.org
Subject: Potentially undocumented behaviour change in Postgres 11 concerning 
OLD record in an after insert trigger

 

Hi,

 

I've noticed a change in the behaviour in triggers / hstores in Postgres 11.1 
when compared to Postgres 10.5.

The following won't work on Postgres 10.5 but in Postgres 11.1 it works just 
fine:

 

CREATE EXTENSION hstore;

CREATE TABLE _tmp_test1 (id serial PRIMARY KEY, val INTEGER);
CREATE TABLE _tmp_test1_changes (id INTEGER, changes HSTORE);

CREATE FUNCTION test1_trigger ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
BEGIN
INSERT INTO _tmp_test1_changes (id, changes) VALUES (NEW.id, hstore(OLD) - 
hstore(NEW));
RETURN NEW;
END
$BODY$;

CREATE TRIGGER table_update AFTER INSERT OR UPDATE ON _tmp_test1
FOR EACH ROW EXECUTE PROCEDURE test1_trigger();

 

INSERT INTO _tmp_test1 (val) VALUES (5);

ERROR:  record "old" is not assigned yet

DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

CONTEXT:  SQL statement "INSERT INTO _tmp_test1_changes (id, changes) VALUES 
(NEW.id, hstore(OLD) - hstore(NEW))"

PL/pgSQL function test1_trigger() line 3 at SQL statement

 

I couldn't find anything about this in the release notes 
(https://www.postgresql.org/docs/11/release-11.html), but maybe I just didn't 
know what to look for.

 

I doubt that this works on any PG version for INSERT.

 

According to the documentation:

 

https://www.postgresql.org/docs/10/plpgsql-trigger.html and 
https://www.postgresql.org/docs/11/plpgsql-trigger.html

 

OLD: Data type RECORD; variable holding the old database row for UPDATE/DELETE 
operations in row-level triggers. This variable is unassigned in 
statement-level triggers and for INSERT operations.

 

Regards

Charles

RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Charles Clavadetscher]

> -Original Message-
> From: Achilleas Mantzios 
> Sent: Montag, 19. November 2018 15:43
> To: pgsql-general@lists.postgresql.org
> Subject: Re: pgconf eu 2018 slides entry missing from
> https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
> ns
> 
> On 19/11/18 3:27 μ.μ., Stephen Frost wrote:
> > Greetings,
> >
> > * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote:
> >>
> https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018
> >>
> >> As mentioned there, the slides are linked, as long as they have been
> delivered by the speakers, in the talk descriptions in the schedule.
> > I'm not sure what the point of duplicating that information on to the
> > wiki is..?
> 
> 
> It is nice to have a single source to all slides from all conferences, so
> at least a link to the conference page should be there on the wiki.

[>] It is since this morning.

> 
> 
> >
> > Seems like what we should do here is add a feature to the conference
> > system where a particular URL will produce a list of talks with links
> > to slides, if uploaded.  That shouldn't be hard to do if someone is
> > interested and knows a little python/django- the code is here:
> >
> > https://git.postgresql.org/gitweb/?p=pgeu-website.git;a=summary
> >
> > Patches very much accepted. :)
> >
> > Thanks!
> >
> > Stephen
> 
> 
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 

RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

[Charles Clavadetscher]

Hello Achilleas

> -Original Message-
> From: Achilleas Mantzios 
> Sent: Sonntag, 18. November 2018 12:23
> To: pgsql-gene...@postgresql.org
> Subject: pgconf eu 2018 slides entry missing from
> https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
> ns
> 
> Hello,
> 
> The pgconf eu 2018 entry is missing from
> https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio
> ns
> .
> 
> I am afraid the more time passes since the event the harder to get
> people post their slides there, so pls update this ASAP.
> 

[>] I made an entry in the wiki:

https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018

As mentioned there, the slides are linked, as long as they have been delivered 
by the speakers, in the talk descriptions in the schedule.

Bye
Charles

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Charles Clavadetscher (SwissPUG)]

An addition


On 01.10.2018 05:18:15, Charles Clavadetscher (SwissPUG) 
 wrote:
Hello

On 01.10.2018 05:00:02, Carl Sverre  wrote:
Thank you for the detailed report Charles. I think you may be missing the 
“returning id” clause in the insert. Can you verify it works when you use 
“returning id”? Thanks!
[Charles] : You are right:

testuser@charles.localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, 
i.e. Adrian's assumption is probably correct.

Regards
Charles
[Charles] : In the RLS documentation 
(https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html):

"To specify which rows are visible or modifiable according to a policy, an 
expression is required that returns a Boolean result. This expression will be 
evaluated for each row prior to any conditions or functions coming from the 
user's query."

I assume that the returning clause is essentially a condition or function from 
the user query or it is implemented as such. Therefore the policy is applied 
prior to it.
The most knowlegdable person on this list on the RLS topic is Stephen Frost. He 
may shed light on the matter.

Regards
Charles

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Charles Clavadetscher (SwissPUG)]

Hello

On 01.10.2018 05:00:02, Carl Sverre  wrote:
Thank you for the detailed report Charles. I think you may be missing the 
“returning id” clause in the insert. Can you verify it works when you use 
“returning id”? Thanks!
[Charles] : You are right:

testuser@charles.localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, 
i.e. Adrian's assumption is probably correct.

Regards
Charles

On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) 
mailto:clavadetsc...@swisspug.org]> wrote:

Hello

On 30.09.2018 23:31:32, Adrian Klaver mailto:adrian.kla...@aklaver.com]> wrote:
On 9/30/18 1:13 PM, Carl Sverre wrote:
> Thanks for the initial results. Can you check that you are not using
> super permissions and are enabling row security when running the test?
> Super ignores row security.

Yeah, big oops on my part, I was running as superuser. Running as
non-superuser resulted in the failure you see. I tried to get around
this with no success. My suspicion is that the new row in b is not
visible to the returning(SELECT) query in a until after the transaction
completes. Someone with more knowledge on this then I will have to
confirm/deny my suspicion.


>
> Also yes, I forgot to add the policy names, sorry about that.
> On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > wrote:
>
> Hello

--
Adrian Klaver
adrian.kla...@aklaver.com [mailto:adrian.kla...@aklaver.com]

[Charles] : I also made the first test as super. However I still don't get any 
errors when executing the test query as non superuser.

The user is not superuser:

testuser@charles.localhost=> SELECT CURRENT_USER;
 current_user
--
 testuser
(1 row)

testuser@charles.localhost=> \du testuser
           List of roles
 Role name | Attributes | Member of
---++---
 testuser  |            | {}

The table privileges show that RLS is enabled and that testuser has SELECT and 
INSERT privilege on both tables. This is not related to RLS but simple 
precondition for the test:

testuser@charles.localhost=> \d a
                Table "public.a"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |
Policies (forced row security enabled):
    POLICY "a_insert" FOR INSERT
      WITH CHECK (true)
    POLICY "a_select" FOR SELECT
      USING ((EXISTS ( SELECT b.id [http://b.id]
   FROM b
  WHERE (a.id [http://a.id] = b.id [http://b.id]
Triggers:
    reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE 
reprohandler()

testuser@charles.localhost=> \dp a
                                         Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges |          
 Policies
+--+---+-+---+--
 public | a    | table | charles=arwdDxt/charles+|                   | a_select 
(r):               +
        |      |       | testuser=ar/charles     |                   |   (u): 
(EXISTS ( SELECT b.id [http://b.id]+
        |      |       |                         |                   |    FROM 
b                   +
        |      |       |                         |                   |   WHERE 
(a.id [http://a.id] = b.id [http://b.id])))     +
        |      |       |                         |                   | a_insert 
(a):               +
        |      |       |                         |                   |   (c): 
true

testuser@charles.localhost=> \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |

testuser@charles.localhost=> \dp b
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies
+--+---+-+---+--
 public | b    | table | charles=arwdDxt/charles+|                   |
        |      |       | testuser=ar/charles     |                   |

And now the test:

testuser@charles.localhost=> SELECT * FROM a;
 id

(0 rows)

testuser@charles.localhost=> SELECT * FROM b;
 id

(0 rows)

testuser@charles.localhost=> INSERT INTO a VALUES ('fails');
NOTICE:  inside trigger handler
INSERT 0 1
testuser@charles.localhost=> SELECT * FROM a;
  id
---
 fails
(1 row)

testuser@charles.localhost=> SELECT * FROM b;
  id
---
 fails
(1 row)

Version of PG:
testuser@charles.localhost=> SELECT version();
                          version

 PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

Regards
Charles

--

Carl Sverre

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Charles Clavadetscher (SwissPUG)]

Hello

On 30.09.2018 23:31:32, Adrian Klaver  wrote:
On 9/30/18 1:13 PM, Carl Sverre wrote:
> Thanks for the initial results. Can you check that you are not using
> super permissions and are enabling row security when running the test?
> Super ignores row security.

Yeah, big oops on my part, I was running as superuser. Running as
non-superuser resulted in the failure you see. I tried to get around
this with no success. My suspicion is that the new row in b is not
visible to the returning(SELECT) query in a until after the transaction
completes. Someone with more knowledge on this then I will have to
confirm/deny my suspicion.


>
> Also yes, I forgot to add the policy names, sorry about that.
> On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > wrote:
>
> Hello

--
Adrian Klaver
adrian.kla...@aklaver.com

[Charles] : I also made the first test as super. However I still don't get any 
errors when executing the test query as non superuser.

The user is not superuser:

testuser@charles.localhost=> SELECT CURRENT_USER;
 current_user
--
 testuser
(1 row)

testuser@charles.localhost=> \du testuser
           List of roles
 Role name | Attributes | Member of
---++---
 testuser  |            | {}

The table privileges show that RLS is enabled and that testuser has SELECT and 
INSERT privilege on both tables. This is not related to RLS but simple 
precondition for the test:

testuser@charles.localhost=> \d a
                Table "public.a"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |
Policies (forced row security enabled):
    POLICY "a_insert" FOR INSERT
      WITH CHECK (true)
    POLICY "a_select" FOR SELECT
      USING ((EXISTS ( SELECT b.id
   FROM b
  WHERE (a.id = b.id
Triggers:
    reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE 
reprohandler()

testuser@charles.localhost=> \dp a
                                         Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges |          
 Policies
+--+---+-+---+--
 public | a    | table | charles=arwdDxt/charles+|                   | a_select 
(r):               +
        |      |       | testuser=ar/charles     |                   |   (u): 
(EXISTS ( SELECT b.id+
        |      |       |                         |                   |    FROM 
b                   +
        |      |       |                         |                   |   WHERE 
(a.id = b.id)))     +
        |      |       |                         |                   | a_insert 
(a):               +
        |      |       |                         |                   |   (c): 
true

testuser@charles.localhost=> \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |

testuser@charles.localhost=> \dp b
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies
+--+---+-+---+--
 public | b    | table | charles=arwdDxt/charles+|                   |
        |      |       | testuser=ar/charles     |                   |

And now the test:

testuser@charles.localhost=> SELECT * FROM a;
 id

(0 rows)

testuser@charles.localhost=> SELECT * FROM b;
 id

(0 rows)

testuser@charles.localhost=> INSERT INTO a VALUES ('fails');
NOTICE:  inside trigger handler
INSERT 0 1
testuser@charles.localhost=> SELECT * FROM a;
  id
---
 fails
(1 row)

testuser@charles.localhost=> SELECT * FROM b;
  id
---
 fails
(1 row)

Version of PG:
testuser@charles.localhost=> SELECT version();
                          version

 PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

Regards
Charles

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

[Charles Clavadetscher (SwissPUG)]

Hello


On 29.09.2018 20:24:45, Adrian Klaver  wrote:
On 9/28/18 11:35 PM, Carl Sverre wrote:
> *Context*
> I am using row-level security along with triggers to implement a pure
> SQL RBAC implementation. While doing so I encountered a weird behavior
> between INSERT triggers and SELECT row-level security policies.
>
> *Question*
> I have posted a very detailed question on StackOverflow here:
> https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s
>
> For anyone who is just looking for a summary/repro, I am seeing the
> following behavior:
>
> CREATE TABLE a (id TEXT);
> ALTER TABLE a ENABLE ROW LEVEL SECURITY;
> ALTER TABLE a FORCE ROW LEVEL SECURITY;
>
> CREATE TABLE b (id TEXT);
>
> CREATE POLICY ON a FOR SELECT
> USING (EXISTS(
>     select * from b where a.id = b.id
> ));
>
> CREATE POLICY ON a FOR INSERT
> WITH CHECK (true);
>
> CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
> BEGIN
>     RAISE NOTICE USING MESSAGE = 'inside trigger handler';
>     INSERT INTO b (id) VALUES (NEW.id);
>     RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER reproTrigger BEFORE INSERT ON a
> FOR EACH ROW EXECUTE PROCEDURE reproHandler();
>
> INSERT INTO a VALUES ('fails') returning id;
> NOTICE:  inside trigger handler
> ERROR:  new row violates row-level security policy for table "a"
>
> Rather than the error, I expect that something along these lines should
> occur instead:
>
> 1. A new row ('fails') is staged for INSERT
> 2. The BEFORE trigger fires with NEW set to the new row
> 3. The row ('fails') is inserted into b and returned from the trigger
> procedure unchanged
> 4. The INSERT's WITH CHECK policy true is evaluated to true
> 5. The SELECT's USING policy select * from b where a.id =
> b.id is evaluated.  *This should return true due to step 3*
> 6. Having passed all policies, the row ('fails') is inserted in table
> 7. The id (fails) of the inserted row is returned
>
> If anyone can point me in the right direction I would be extremely thankful.

When I tried to reproduce the above I got:

test=# CREATE POLICY ON a FOR SELECT
test-# USING (EXISTS(
test(# select * from b where a.id = b.id
test(# ));
ERROR: syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR SELECT
^
test=#
test=# CREATE POLICY ON a FOR INSERT
test-# WITH CHECK (true);
ERROR: syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR INSERT

Changing your code to:

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

CREATE POLICY a_select ON a FOR SELECT
USING (EXISTS(
select * from b where a.id = b.id
));

CREATE POLICY a_insert ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE USING MESSAGE = 'inside trigger handler';
INSERT INTO b (id) VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

Resulted in:

test=# INSERT INTO a VALUES ('fails') returning id;
NOTICE: inside trigger handler
id
---
fails
(1 row)

INSERT 0 1
test=# select * from a;
id
---
fails
(1 row)


>
> Carl Sverre
>
> http://www.carlsverre.com


--
Adrian Klaver
adrian.kla...@aklaver.com

[Charles] : I did the same test with PG version 10 on Windows and PG 9.6.2 on 
Linux (RedHat) with exactly the same result.

db=# INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
  id
---
 fails
(1 row)

INSERT 0 1
db=# select * from a;
  id
---
 fails
(1 row)

db=# select * from b;
  id
---
 fails
(1 row)

Regards
Charles

Re: Executing a Function with an INSERT INTO command fails

[Charles Clavadetscher]

Hi

> On 29.08.2018, at 19:45, TalGloz  wrote:
> 
> 
>> This is very strange, even if I comment all the loops in the function and
>> leave only the INSERT INTO command the insert still doesn't happen.
> 
> Now If I execute the function locally in a query window like this: 
> 
> SELECT public.seal_diff_benchmark_pgsql('YW55IGNhcm5hbCBwbGVhc3VyZQ==')
> 
> Then the INSERT INTO command fires and values are being inserted into the
> public.runtime_benchmark table. But when I execute the command form a Client
> code (C++) on a different pc using the libpqxx library, then everything else
> in the function works except the  INSERT INTO command. I use the right
> credentials since everything else in the function works perfectly.
> 
> So why is this happening?
> 
> Tal
> 

Tom's assumption is possibly correct. If the client does not have autocommit 
set then you need to explicitly commit the transaction.

> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 

RE: Executing a Function with an INSERT INTO command fails

[Charles Clavadetscher]

Hi

> -Original Message-
> From: TalGloz [mailto:glozman...@gmail.com]
> Sent: Mittwoch, 29. August 2018 13:22
> To: pgsql-gene...@postgresql.org
> Subject: RE: Executing a Function with an INSERT INTO command fails
> 
> Charles Clavadetscher wrote
> > Do you get any error?
> 
> The function executes perfectly and does what it's supposed to except of the 
> INSERT INTO part. I don't get any
> errors.
> 
> 
> > Does the select deliver any result at all?
> 
> Yes, booth SELECT deliver everything they supposed to.
> 
> 
> > If yes, is there maybe already a trigger on table
> > public.runtime_benchmark?
> 
> I didn’t crate any triggers for the table manually and I don't think they are 
> crated automatically.
> 
> 
> > If not, is there maybe a policy on either public.nyc2015_09_enc or
> > public.runtime_benchmark?
> 
> There are no security policy on any of them, at least I didn't set any while 
> creating the tables with PgAdmin4.
> I use the tables on a local server for testing purposes only.
> 
> 
> > If not, could you provide more information on the table?
> 
> What kind of information should I provide?

The point is to try to reproduce the problem. So the table definition as 
delivered by \d would be a good starting point. Or the CREATE TABLE generated 
by pgAdmin.

While I was having a closer look at the function I noticed that you call 
another function to populate the sealArray.

In order to try to reproduce the error I did:

Create public.nyc2015_09_enc on assumptions and populate it with some data.

CREATE TABLE public.nyc2015_09_enc
(
  id INTEGER,
  "Pickup_longitude" TEXT,
  "Dropoff_longitude" TEXT
);

INSERT INTO public.nyc2015_09_enc VALUES (1,'47.0','8.0');
INSERT INTO public.nyc2015_09_enc VALUES (2,'49.0','8.5');

SELECT * FROM public.nyc2015_09_enc;
 id | Pickup_longitude | Dropoff_longitude
+--+---
  1 | 47.0 | 8.0
  2 | 49.0 | 8.5
(2 rows)

Create public.runtime_benchmark based on assuptions.

CREATE TABLE public.runtime_benchmark
(
  test_number INTEGER,
  column_names TEXT,
  execution_time TEXT,
  operation_type TEXT,
  seal_or_sql TEXT
);

Create your function. The sealArray creation is modified, because I don't know 
how the function public.seal_diff_benchmark is defined.

CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams CHARACTER 
VARYING)
RETURNS SETOF TEXT
AS $outputVar$
DECLARE
tempVar1 CHARACTER VARYING;
tempVar2 CHARACTER VARYING;
  outputVar text;
sealArray TEXT[];
outputArray TEXT[];
BEGIN
FOR i IN 1..2 LOOP
SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, 
tempVar2 FROM public.nyc2015_09_enc WHERE id=i;
--sealArray := (SELECT public.seal_diff_benchmark(tempVar1, 
tempVar2, sealparams));
sealArray := ARRAY[tempVar1, tempVar2, sealparams];
outputArray[i] := sealArray[1];

INSERT INTO public.runtime_benchmark (test_number, 
column_names, execution_time, operation_type, seal_or_sql) VALUES (1, 
'Pickup_longitude, Dropoff_longitude', sealArray[2], 'sub', 'seal');

END LOOP;

FOREACH outputVar IN ARRAY outputArray LOOP
RETURN NEXT outputVar;
END LOOP;
END;
$outputVar$ LANGUAGE plpgsql;

Is there any reason for the loop 1..2?

And test it.

SELECT * FROM public.runtime_benchmark ;
 test_number | column_names | execution_time | operation_type | seal_or_sql
-+--+++-
(0 rows)

SELECT * FROM seal_diff_benchmark_pgsql('0.12');
 seal_diff_benchmark_pgsql
---
 47.0
 49.0
(2 rows)

SELECT * FROM public.runtime_benchmark ;
 test_number |column_names | execution_time | 
operation_type | seal_or_sql
-+-+++-
   1 | Pickup_longitude, Dropoff_longitude | 8.0| sub   
 | seal
   1 | Pickup_longitude, Dropoff_longitude | 8.5| sub   
 | seal
(2 rows)

Unfortunately I am not able to reproduce the problem, but maybe with the table 
and functions definitions, as well as the 2 data rows that are selected in the 
function, is that easier to analyze.

Regards
Charles

> Best regards,
> Tal
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

RE: Executing a Function with an INSERT INTO command fails

[Charles Clavadetscher]

Hi

> -Original Message-
> From: TalGloz [mailto:glozman...@gmail.com]
> Sent: Mittwoch, 29. August 2018 11:12
> To: pgsql-gene...@postgresql.org
> Subject: Executing a Function with an INSERT INTO command fails
> 
> Hello,
> 
> I have this function that executes a C extention function in it and returns a 
> SETOF TEXT
> 
> CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams
> CHARACTER VARYING) RETURNS SETOF TEXT AS $outputVar$
> DECLARE
>   tempVar1 CHARACTER VARYING;
>   tempVar2 CHARACTER VARYING;
> outputVar text;
>   sealArray TEXT[];
>   outputArray TEXT[];
> BEGIN
>   FOR i IN 1..2 LOOP
>   SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, 
> tempVar2 FROM public.nyc2015_09_enc
> WHERE id=i;
>   sealArray := (SELECT public.seal_diff_benchmark(tempVar1, 
> tempVar2, sealparams));
>   outputArray[i] := sealArray[1];
> 
>   INSERT INTO public.runtime_benchmark (test_number, 
> column_names, execution_time, operation_type,
> seal_or_sql) VALUES (1, 'Pickup_longitude, Dropoff_longitude', sealArray[2], 
> 'sub', 'seal');
> 
>   END LOOP;
> 
>   FOREACH outputVar IN ARRAY outputArray LOOP
>   RETURN NEXT outputVar;
>   END LOOP;
> END;
> $outputVar$ LANGUAGE plpgsql;
> 
> Inside the first FOR LOOP... I've an INSERT INTO... command but nothing gets 
> inserted into the
> public.runtime_benchmark table. Executing the INSERT INTO command separately 
> works without any problems so why
> doesn't it work inside my PostgreSQL function?
> 
> The user executing the function above with the INSERT command is alsow the 
> owner of the public.runtime_benchmark
> table. Do I have to create a trigger function for the 
> public.runtime_benchmark table to be able to insert into
> it using some other function? Or is there a simpler way to modify the 
> function above to achieve my goal?

No. It is possible to use a normal function.
Do you get any error?

Does the select deliver any result at all?

If yes, is there maybe already a trigger on table public.runtime_benchmark?

If not, is there maybe a policy on either public.nyc2015_09_enc or 
public.runtime_benchmark?

If not, could you provide more information on the table?

Regards
Charles

> Best regards,
> Tal
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

RE: extracting the sql for a function

[Charles Clavadetscher]

Hello

> -Original Message-
> From: Paul Tilles [mailto:paul.til...@noaa.gov]
> Sent: Donnerstag, 23. August 2018 19:18
> To: pgsql-gene...@postgresql.org
> Subject: extracting the sql for a function
> 
> Using postgres Version 9.5
> 
> I can extract the sql for a table using
> 
> pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql
> 
> Is there something equivalent for extracting the sql for a function from the 
> database?
> 
> 
> Paul Tilles
> 

I don't know if there is but you can get the function definition:

SELECT * FROM pg_get_functiondef('functionname'::regproc);

or

SELECT * FROM pg_get_functiondef('functionname(list of parameters data 
types)'::regprocedure);

If you want to export you may use the -t and -o switches.

Regards
Charles

Re: JSONB filed with default JSON from a file

[Charles Clavadetscher]

Hi

---
Charles Clavadetscher
Neugasse 84
CH - 8005 Zürich

Tel: +41-79-345 18 88
-

> On 13.08.2018, at 19:40, mrcasa bengaluru  wrote:
> 
> All,
> 
> I'm new to JSONB datatype. We would like to store a nested JSON file in this 
> field. Since the JSON is nested, we wanted to create JSON with default value 
> from an external JSON file.
> 
> My address table looks like,
> 
> CREATE TABLE address (
>   id  CHAR(36) UNIQUE NOT NULL,
>   address JSONB NOT NULL
> );
> 
> For example, the default JSON will look like,
> 
> $ cat address_default.json
> 
> {
>   "address": {
> "address1": "175 N Street",
> "address2": "Timabktu",
> "location": [
>   {
> "city": "Utopia",
> "geolocation": [
>   {
> "lat": "12.345",
> "long": "12.1234"
>   }
> ],
> "state": "Nowhere"
>   }
> ],
> "zip": "96001"
>   }
> }
> 
> 
> How do I make the address_default.json as the default JSON value for the 
> address column?
> 

I assume that you could declare the column as

address jsonb not null default 'your json here'::jsonb;

I did not try it, but this is what you would do with other data types.

Regards
Charles

RE: How to revoke privileged from PostgreSQL's superuser

[Charles Clavadetscher]

Hello

 

From: bejita0...@yahoo.co.jp [mailto:bejita0...@yahoo.co.jp] 
Sent: Montag, 6. August 2018 11:49
To: pgsql-ad...@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: How to revoke privileged from PostgreSQL's superuser

 

Hello,

 

I am a newbie DBA.

 

I have a request for revoking the access to user's data from DBA-user.

I think the request is right because users should be the only ones can access 
their data.

But DBA-user also need full access to the other data? It means that DBA-user 
also needs to be a superuser.

 

So I conclude the request that how to revoke privileged from superuser in 
postgres.

 

As my knowledge, the superuser in PostgreSQL bypasses all the permission check.

So that, there is no way to do it in PostgreSQL, is that right?

 

Yes, superuser have access to everything. The main question is why should a 
superuser be restricted in her access to an object?

I think that this is more a question of designing your user and groups in a 
clean way.

 

In theory you could restrict access using a before trigger, but this is not a 
good idea and is quite expensive.

You could also use a policy for the superuser with the clause using(false), but 
this also is not really a good idea and may become
very confusing.

 

In either way, this would not be a good solution.

 

Maybe, if you clarify better what is your purpose, there might be more useful 
answers and approaches.

 

Regards

Charles

 

Is there some DBAs are faced with this before?

 

 

Thanks,

--

bejita

RE: how to install pgcrypto

[Charles Clavadetscher]

CCing list.

 

From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] 
Sent: Freitag, 3. August 2018 17:20
To: 'Ze Victor Harry' 
Subject: RE: how to install pgcrypto

 

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com] 
Sent: Freitag, 3. August 2018 17:11
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >
Subject: Re: how to install pgcrypto

 

hello again  Charles Clavadetscher 

how many hours does it take to complete ant fresh_install

it has been 4 hrs since i have started it but it is still not finished.I am 
using fast broadband internet(25 MB/s).is something wrong or is usually like 
that?



 

 

Well, to install PostgreSQL itself goes quickly. I can’t tell you exactly how 
long, but it is a matter of minutes.

In the screenshot that you sent, it looks like you are downloading a file, 
probably with data used by the appication installed in c:\dspace\webapps.

I assume that this is what is taking time, depending on how big the data is.

 

You may ask the developers of the web application on that. This is hardly a 
database question.

 

BTW: do not top post when responding on the list and, since you are starting a 
new topic (the pgcrypto installation of the subject is solved), you should 
start a new thread, so that other people may look at it.

 

Regards

Charles

 

On Fri, Aug 3, 2018 at 2:19 PM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com 
<mailto:assayab...@gmail.com> ] 
Sent: Freitag, 3. August 2018 12:53
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >
Subject: Re: how to install pgcrypto

 

 




Thanx  Charles Clavadetsche


 I am installing dspace in windows 10 and I created the extension by going to 
OBJECT->CREATE->EXTENSION and created pgcrypto successfully that is what I have 
been asking and it is working fine. am I welcome to ask further questions on 
the installation though i am new to this kind of installation?

 

Of course. That’s the reason of existence of this mailing list.

 

On Fri, Aug 3, 2018 at 12:21 PM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com 
<mailto:assayab...@gmail.com> ] 
Sent: Freitag, 3. August 2018 10:54
To: pgsql-gene...@postgresql.org <mailto:pgsql-gene...@postgresql.org> 
Subject: how to install pgcrypto

 

hello again I have a small problem here can someone tell me briefly how to do 
it? I am getting this error 


 
<https://stackoverflow.com/questions/45408745/when-i-give-ant-fresh-install-it-gives-error-postgresql-pgcrypto-extension-ins>
 When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' extension 
installed /up to date? False (not installed) Create extension pgcrypto.


I have tried to look up for solutions and they all say I have to run a command 

# Login to your "dspace" database as a superuser

psql --username=postgres dspace

 

With this command on the linux shell your run psql, which is the PostgreSQL 
client that always is shipped with the server.

In the psql shell you can then run the create extension command as below.

You may do this in PgAdmin (a graphical UI for Windows), too.

 

The whole point is that you must first log into the database as a superuser 
before you install the extension.

 

Hope this helps.

Bye

Charles

 

# Enable the pgcrypto extension on this database

CREATE EXTENSION pgcrypto;

 but where I got confused is from where do I run these  <http://commands.is/> 
commands.is it from cmd? or in pgadmin query tool? I need clearly articulated  
steps

 

 

 

RE: how to install pgcrypto

[Charles Clavadetscher]

Hi

 

From: Ze Victor Harry [mailto:assayab...@gmail.com] 
Sent: Freitag, 3. August 2018 10:54
To: pgsql-gene...@postgresql.org
Subject: how to install pgcrypto

 

hello again I have a small problem here can someone tell me briefly how to do 
it? I am getting this error 


 

 When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' extension 
installed /up to date? False (not installed) Create extension pgcrypto.


I have tried to look up for solutions and they all say I have to run a command 

# Login to your "dspace" database as a superuser

psql --username=postgres dspace

 

With this command on the linux shell your run psql, which is the PostgreSQL 
client that always is shipped with the server.

In the psql shell you can then run the create extension command as below.

You may do this in PgAdmin (a graphical UI for Windows), too.

 

The whole point is that you must first log into the database as a superuser 
before you install the extension.

 

Hope this helps.

Bye

Charles

 

# Enable the pgcrypto extension on this database

CREATE EXTENSION pgcrypto;

 but where I got confused is from where do I run these   
commands.is it from cmd? or in pgadmin query tool? I need clearly articulated  
steps

 

RE: Read only to schema

[Charles Clavadetscher]

Hello Jakek

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Samstag, 28. Juli 2018 14:13
To: Melvin Davidson ; pgsql-gene...@postgresql.org >> 
PG-General Mailing List 
Subject: Re: Read only to schema

 

Hi Guys,

 

thank you for such advances replies and help ! You are the best!

 

I have found out that this command:

 

GRANT ALL PRIVILAGES ON ALL TABLES IN SCHEMA schema_name TO role_name; 

 

Be aware that granting all privilege is not necessarily the best approach. You 
are granting e.g. a user the permission to create triggers on your table that 
can perform actions transparently or to truncate the table.

If this is really what you want and need, well, go for it. Otherwise restrict 
it to the privileges the user really needs, e.g.

 

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ...

 

Regards.

Charles

 

is working for me. 

 

I will read more and try to use it properly! 

 

Thank you very much, 

Best,

Jacek 

 

 

 

pt., 27 lip 2018 o 17:55 Melvin Davidson mailto:melvin6...@gmail.com> > napisał(a):

Jacek, 
you appear to be confused on how security works in PostgreSQL.

Please review the options in 

 

https://www.postgresql.org/docs/10/static/sql-grant.html


and read the Description that follows on that page

As owner or superuser, you can
GRANT CONNECT {CREATE} ON DATABASE;
GRANT USAGE {CREATE} ON SCHEMA schema_name TO role_name;
GRANT SELECT ON TABLE table_name TO role_name;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

What you want is probably the last of these.

If you are still confused, you should probably purchase 
or download 

PostgreSQL Administration Essentials
Chapter 3: Users and Permissions

from https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials 
<https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials_list%5BAvailable%5D=Available===>
 _list%5BAvailable%5D=Available===

 

On Fri, Jul 27, 2018 at 11:31 AM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi

 

Please notice that in this mailing list it is not customary to top post.

 

From: Łukasz Jarych [mailto:jarys...@gmail.com <mailto:jarys...@gmail.com> ] 
Sent: Freitag, 27. Juli 2018 12:19
To: Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >; pgsql-gene...@postgresql.org 
<mailto:pgsql-gene...@postgresql.org>  >> PG-General Mailing List 
mailto:pgsql-gene...@postgresql.org> >
Subject: Re: Read only to schema

 

Hi,

 

ok i hate this. 

 

SELECT datname, datacl FROM pg_database where datname = 'AccessLog'; 

 



\What does it mean?

 

The ACL (Access Control List) contains all the information about which role can 
do what on that object, in this case a database and which role granted the 
privileges.

 

You can find detailed information in the documentation:

https://www.postgresql.org/docs/10/static/sql-grant.html

 

An ACL is an array of aclitem, which again is basically a key/value pair with 
an equal sign between the key and the value.

In this context the key is the grantee, i.e. the role being granted a privilege.

The value is the list of privileges (see list following the link above) granted 
to that role, followed by a slash (/) and the role that granted the privilege.

The list of acronyms is a.o. in the link I gave above. Here for your 
convenience:

 

rolename= -- privileges granted to a role

= -- privileges granted to PUBLIC

 

r -- SELECT ("read")

w -- UPDATE ("write")

a -- INSERT ("append")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNECT

T -- TEMPORARY

  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)

* -- grant option for preceding privilege

 

/ -- role that granted this privilege

 

If the key is empty, it means that the privilege is granted to public, i.e. 
every role.

 

Example:

 

admin=CTc/postgres means that role postgres granted Connect (c), Create (C) and 
Temporary (T) to role admin.

 

Notice that the granting role may differ from the one you are using, if you are 
acting as superuser:

 

“If a superuser chooses to issue a GRANT or REVOKE command, the command is 
performed as though it were issued by the owner of the affected object. In 
particular, privileges granted via such a command will appear to have been 
granted by the object owner. (For role membership, the membership appears to 
have been granted by the containing role itself.)”

 

If the ACL is empty (NULL) then the predefined default privileges apply. >From 
the doc:

 

“PostgreSQL grants default privileges on some types of objects to PUBLIC. No 
privileges are granted to PUBLIC by default on tables, table columns, 
sequences, for

RE: Read only to schema

[Charles Clavadetscher]

Hello Jacek

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Samstag, 14. Juli 2018 11:55
To: clavadetsc...@swisspug.org; pgsql-gene...@postgresql.org >> PG-General 
Mailing List 
Subject: Re: Read only to schema

 

Thank you very much Charles! 

 

Awesome knowledge, thank you!

 

I will test it and let you know if it is working like a charm (i am on vacation 
now and without access to postgresql).

 

I am wondering with one thing: 

 

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

 

This will allow to create, drop, isnert and delete? All ddl and dml commands? 

Or should i use GRANT ALL ON SCHEMA PUBLIC TO jaryszek ?

 

No. There are 2 privileges that you can grant on a schema:

USAGE: Allows to use objects in that schema.

CREATE: Allows to create and destroy objects in that schema.

 

None of those says anything about which privileges users have within the 
schema. You need to define additionally privileges (e.g. select, insert, 
update, delete) on the objects within the schema.

 

So in order to get access to a table public.test a user must have:

 

USAGE on schema public AND SELECT (or whatever) on the table itself. If any of 
those is missing the user will not be able to access the table.

 

GRANT ALL is generally a bad idea, althought on schemas there is not much you 
can do wrong.

It doesn’t hurt if you add USAGE (which would be included in ALL along CREATE) 
for user jaryszek, but it is not necessary, because public (= any user) was 
only revoked CREATE (s. example in last mail). That means public still has 
USAGE on schema public and obviously you are on one of “any user”.

 

I hope I could explain that somehow.

If you still have questions just get back on the list.

 

Bye

Charles

 

Best,

Jacek 

 

sob., 14 lip 2018 o 08:23 Charles Clavadetscher mailto:clavadetsc...@swisspug.org> > napisał(a):

Hello

 

From: Łukasz Jarych [mailto:jarys...@gmail.com <mailto:jarys...@gmail.com> ] 
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-gene...@postgresql.org <mailto:pgsql-gene...@postgresql.org>  >> 
PG-General Mailing List mailto:pgsql-gene...@postgresql.org> >
Subject: Re: Read only to schema

 

I found something like this:

 

CREATE ROLE readonly_user

   WITH LOGIN

   ENCRYPTED PASSWORD '1234'

 

ALTER ROLE readonly_user

SET search_path to 

public

 

 GRANT CONNECT

ON DATABASE "TestDb"

TO readonly_user;

  

 GRANT USAGE

ON SCHEMA public

TO readonly_user;

  

GRANT USAGE

ON ALL SEQUENCES  -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...

IN SCHEMA public

TO readonly_user;

  

 GRANT SELECT

ON ALL TABLES  -- Alternatively: ON TABLE table1, view1, table2 ...

IN SCHEMA public

TO readonly_user;

 

Question is how to give this user opposite access? I mean give him access to 
all functionalities like inserting, deleting, creating tables and staff like 
this. 

 

I mean i want to assign user "jaryszek" to this read_only role and after 
changing schema i want to give user "jaryszek" all credentials. 

 

Best,

Jacek 

 

 

You can change your readonly_user to NOINHERIT and GRANT the role to jaryszek.

When you then want to act as readonly_user you set the role explicitly.

 

Here basically:

 

Revoke create from public, so that only granted users will be able to create or 
drop objects.

REVOKE CREATE ON SCHEMA PUBLIC FROM public;

 

Create the role as group (nologin) and without implicit inheritance of 
privileges.

CREATE ROLE readonly_user NOINHERIT NOLOGIN;

 

Your normal user should be able to create tables.

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

 

Add your user to the readonly_user group.

GRANT readonly_user TO jaryszek;

 

Now when you log in as jaryszek you can create table add data, etc.

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT SESSION_USER, 
CURRENT_USER;

session_user | current_user

--+--

jaryszek | jaryszek

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => CREATE TABLE 
public.test (a INTEGER);

CREATE TABLE

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => INSERT INTO public.test 
VALUES (1);

INSERT 0 1

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT * FROM 
public.test;

a

---

1

(1 row)

 

Now let’s set up the permissions of readonly_user.

 

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO readonly_user;

 

When you want to act as readonly_user you set explicitly that role.

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SET ROLE readonly_user ;

SET

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT SESSION_USER, 
CURRENT_USER;

session_user | current_user

--+---

jaryszek | readonly_user

(1 row)

 

After this all privileges will be checked against readonly_user.

RE: Read only to schema

[Charles Clavadetscher]

An addition to my previous post (marked as [addition]).

 

From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] 
Sent: Samstag, 14. Juli 2018 08:23
To: 'Łukasz Jarych' ; pgsql-gene...@postgresql.org
Subject: RE: Read only to schema

 

Hello

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-gene...@postgresql.org <mailto:pgsql-gene...@postgresql.org>  >> 
PG-General Mailing List mailto:pgsql-gene...@postgresql.org> >
Subject: Re: Read only to schema

 

I found something like this:

 

CREATE ROLE readonly_user

   WITH LOGIN

   ENCRYPTED PASSWORD '1234'

 

ALTER ROLE readonly_user

SET search_path to 

public

 

 GRANT CONNECT

ON DATABASE "TestDb"

TO readonly_user;

  

 GRANT USAGE

ON SCHEMA public

TO readonly_user;

  

GRANT USAGE

ON ALL SEQUENCES  -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...

IN SCHEMA public

TO readonly_user;

  

 GRANT SELECT

ON ALL TABLES  -- Alternatively: ON TABLE table1, view1, table2 ...

IN SCHEMA public

TO readonly_user;

 

Question is how to give this user opposite access? I mean give him access to 
all functionalities like inserting, deleting, creating tables and staff like 
this. 

 

I mean i want to assign user "jaryszek" to this read_only role and after 
changing schema i want to give user "jaryszek" all credentials. 

 

Best,

Jacek 

 

 

You can change your readonly_user to NOINHERIT and GRANT the role to jaryszek.

When you then want to act as readonly_user you set the role explicitly.

 

Here basically:

 

Revoke create from public, so that only granted users will be able to create or 
drop objects.

REVOKE CREATE ON SCHEMA PUBLIC FROM public;

 

Create the role as group (nologin) and without implicit inheritance of 
privileges.

CREATE ROLE readonly_user NOINHERIT NOLOGIN;

 

Your normal user should be able to create tables.

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

 

Add your user to the readonly_user group.

GRANT readonly_user TO jaryszek;

 

Now when you log in as jaryszek you can create table add data, etc.

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT SESSION_USER, 
CURRENT_USER;

session_user | current_user

--+--

jaryszek | jaryszek

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => CREATE TABLE 
public.test (a INTEGER);

CREATE TABLE

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => INSERT INTO public.test 
VALUES (1);

INSERT 0 1

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT * FROM 
public.test;

a

---

1

(1 row)

 

Now let’s set up the permissions of readonly_user.

 

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO readonly_user;

 

[addition]

If you want that privileges are granted automatically then you can set up 
default privileges:

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE jaryszek IN SCHEMA 
public GRANT SELECT ON TABLES TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

   Default access privileges

  Owner   | Schema | Type  | Access privileges

--++---+---

jaryszek | public | table | =r/jaryszek

(1 row)

 

You are now connected to database "db" as user "jaryszek".

jaryszek@db.localhost=> CREATE TABLE public.test2 (a INTEGER);

CREATE TABLE

jaryszek@db.localhost=> INSERT INTO public.test2 VALUES (34);

INSERT 0 1

 

Through the default privilege setting everybody, including your readonly_user 
can read from the table.

If you prefer you may restrict the default setting only to your read user.

 

jaryszek@db.localhost=> SET ROLE readonly_user ;

SET

jaryszek@db.localhost=> SELECT * FROM public.test2;

a



34

(1 row)

 

Regards

Charles

[end of addition]

 

When you want to act as readonly_user you set explicitly that role.

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SET ROLE readonly_user ;

SET

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT SESSION_USER, 
CURRENT_USER;

session_user | current_user

--+---

jaryszek | readonly_user

(1 row)

 

After this all privileges will be checked against readonly_user. That means:

 

You can read from tables, but you cannot modify data or change/create tables.

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => SELECT * FROM 
public.test;

a

---

1

(1 row)

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => INSERT INTO public.test 
VALUES (2);

ERROR:  permission denied for relation test

 

jaryszek@db.localhost <mailto:jaryszek@db.localhost> => CREATE TABLE 
public.test2 (a INTEGER);

ERROR:  permission denied for schema public

LINE 1: CREATE TABLE public.test2 (a INTEGER);

 

When you want to get back

RE: Read only to schema

[Charles Clavadetscher]

Hello

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-gene...@postgresql.org >> PG-General Mailing List 

Subject: Re: Read only to schema

 

I found something like this:

 

CREATE ROLE readonly_user

   WITH LOGIN

   ENCRYPTED PASSWORD '1234'

 

ALTER ROLE readonly_user

SET search_path to 

public

 

 GRANT CONNECT

ON DATABASE "TestDb"

TO readonly_user;

  

 GRANT USAGE

ON SCHEMA public

TO readonly_user;

  

GRANT USAGE

ON ALL SEQUENCES  -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...

IN SCHEMA public

TO readonly_user;

  

 GRANT SELECT

ON ALL TABLES  -- Alternatively: ON TABLE table1, view1, table2 ...

IN SCHEMA public

TO readonly_user;

 

Question is how to give this user opposite access? I mean give him access to 
all functionalities like inserting, deleting, creating tables and staff like 
this. 

 

I mean i want to assign user "jaryszek" to this read_only role and after 
changing schema i want to give user "jaryszek" all credentials. 

 

Best,

Jacek 

 

 

You can change your readonly_user to NOINHERIT and GRANT the role to jaryszek.

When you then want to act as readonly_user you set the role explicitly.

 

Here basically:

 

Revoke create from public, so that only granted users will be able to create or 
drop objects.

REVOKE CREATE ON SCHEMA PUBLIC FROM public;

 

Create the role as group (nologin) and without implicit inheritance of 
privileges.

CREATE ROLE readonly_user NOINHERIT NOLOGIN;

 

Your normal user should be able to create tables.

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

 

Add your user to the readonly_user group.

GRANT readonly_user TO jaryszek;

 

Now when you log in as jaryszek you can create table add data, etc.

 

jaryszek@db.localhost=> SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--+--

jaryszek | jaryszek

 

jaryszek@db.localhost=> CREATE TABLE public.test (a INTEGER);

CREATE TABLE

jaryszek@db.localhost=> INSERT INTO public.test VALUES (1);

INSERT 0 1

jaryszek@db.localhost=> SELECT * FROM public.test;

a

---

1

(1 row)

 

Now let’s set up the permissions of readonly_user.

 

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO readonly_user;

 

When you want to act as readonly_user you set explicitly that role.

 

jaryszek@db.localhost=> SET ROLE readonly_user ;

SET

jaryszek@db.localhost=> SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--+---

jaryszek | readonly_user

(1 row)

 

After this all privileges will be checked against readonly_user. That means:

 

You can read from tables, but you cannot modify data or change/create tables.

 

jaryszek@db.localhost=> SELECT * FROM public.test;

a

---

1

(1 row)

 

jaryszek@db.localhost=> INSERT INTO public.test VALUES (2);

ERROR:  permission denied for relation test

 

jaryszek@db.localhost=> CREATE TABLE public.test2 (a INTEGER);

ERROR:  permission denied for schema public

LINE 1: CREATE TABLE public.test2 (a INTEGER);

 

When you want to get back to your normal role then use

 

jaryszek@db.localhost=> RESET ROLE;

RESET

jaryszek@db.localhost=> INSERT INTO public.test VALUES (2);

INSERT 0 1

 

The idea is to put all permissions in (group) roles and then impersonate the 
role that you need setting it explicitly.

 

I hope this helps.

Bye

Charles

 

 

pt., 13 lip 2018 o 12:58 Łukasz Jarych mailto:jarys...@gmail.com> > napisał(a):

Maybe read-only view?

 

Best,

Jacek 

 

pt., 13 lip 2018 o 07:00 Łukasz Jarych mailto:jarys...@gmail.com> > napisał(a):

Hi Guys,

 

Yesterday i tried all day to figure out system to read only schemas. 

 

I want to :

 

1. Create user who can login (user: jaryszek)

2. Create role who can read only data (only watching tables) (role: readonly)

3, Create role who can read all data (inserting, deleting, altering, dropping) 
(role: readall)

 

What sqls should i use for this? 

What grants should i add?

 

And now i am logged as jaryszek

 

I want to grant myself role read only to schema public (when owner is 
postgres). 

I want to review tables as views only,

After work i want to grant myself role readall to schema public. 

 

It is possible? 

Or possible workaround ? 

 

Best,

Jacek

RE: Read only to schema

[Charles Clavadetscher]

Hello

 

From: Łukasz Jarych [mailto:jarys...@gmail.com] 
Sent: Freitag, 13. Juli 2018 07:00
To: pgsql-gene...@postgresql.org >> PG-General Mailing List 

Subject: Read only to schema

 

Hi Guys,

 

Yesterday i tried all day to figure out system to read only schemas. 

 

I want to :

 

Here is a try. I did not test it all, but it should go in this direction.

 

1.  Create user who can login (user: jaryszek)

 

CREATE ROLE jaryszek LOGIN;

\password jaryszek

 

2.  Create role who can read only data (only watching tables) (role: 
readonly)

 

Suppose you have a schema xyz

 

GRANT USAGE ON SCHEMA xyz;

GRANT SELECT ON ALL TABLES IN SCHEMA xyz TO jaryzsek;

 

3, Create role who can read all data (inserting, deleting, altering, dropping) 
(role: readall)

 

CREATE ROLE candoall LOGIN;

\password candoall

 

GRANT USAGE, CREATE ON SCHEMA xyz;

GRANT SELECT, INSERT, UPDATE DELETE ON ALL TABLES IN SCHEMA xyz TO candoall;

 

For schema:

USAGE: allows users to see or modify contents of tables in the schema

CREATE: allows users to create new objects in the schema

 

For tables:

SELECT without other privileges: user can only read from tables

INSERT, UPDATE, DELETE: Well, that shoud be obvious

 

If you have other objects (sequences, functions, etc) you may need to add other 
privileges.

 

What sqls should i use for this? 

What grants should i add?

 

And now i am logged as jaryszek

 

I want to grant myself role read only to schema public (when owner is 
postgres). 

 

Schema public has a default privilege setting that grants basically everything 
to everybody (public). So you must first revoke all those privileges from publc.

 

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO jaryszek;

 

You may want to change the default privilege settings for schema public. It 
would help you to read about which default privileges are set and how to change 
them:

 

https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html

 

Regards

Charles

 

I want to review tables as views only,

After work i want to grant myself role readall to schema public. 

 

It is possible? 

Or possible workaround ? 

 

Best,

Jacek

RE: How to remove elements from array .

[Charles Clavadetscher]

Hi

 

From: Brahmam Eswar [mailto:brahmam1...@gmail.com] 
Sent: Freitag, 6. Juli 2018 09:50
To: pgsql-general ; pgsql-hack...@postgresql.org
Subject: How to remove elements from array .

 

Hi ,

 

I tried to use array_remove to remove elements from an array but it's saying 
function doesn't exist . I'm able to use other array functions.

 

1) Capture the results with multiple columns into array . 

2)  if ay results exist then loop through an array to find out the record with 
col1='Y'

3)  If col1='Y' then get the respective value of Col2 (10) and delete the 
similar records of col2 if exist.

 

Col1Col2 

 Y 10

 N 20

N  10 

 

Need to delete record1 and record3.To delete the array records i'm using 
array_remove but it says doesn't exist.

 

Version pgadmin4 .

 

 

 

 

 

 

Snippet :-

 

CREATE or REPLACE FUNCTION FUNC1

(

  << List of elements >>

) AS $$

 

DECLARE



  TEST_CODES record1 ARRAY;

  TEMP_REF_VALUE VARCHAR(4000);



BEGIN

IS_VALID := 'S';



  SELECT ARRAY 

 (SELECT ROW(Col1,Col2,COl3,Col4) ::record1

  FROM table1  INTO TEST_CODES

  

  IF array_length(TEST_CODES, 1) > 0 THEN

  

  FOR indx IN array_lower(TEST_CODES, 
1)..array_upper(TEST_CODES, 1) LOOP

   IF TEST_CODES[indx].COL1 = 'Y' THEN

  TEMP_REF_VALUE:=TEST_CODES[indx].Col2;

  TEST_CODES := 
array_remove(TEST_CODES,TEMP_REF_VALUE);

  END IF;

 END Loop;

END IF; 




 

-- 

Thanks & Regards,
Brahmeswara Rao J.

 

I am not so in clear why you are using arrays in a function for that.

A solution with SQL would be:

 

CREATE TABLE tst (

  col1 text,

  col2 integer

);

 

INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);

 

SELECT * FROM tst;

 

col1 | col2

--+--

Y|   10

N|   20

N|   10

(3 rows)

 

DELETE FROM tst t

USING (SELECT * FROM tst

   WHERE col1 = 'Y') AS x

WHERE t.col2 = x.col2;

 

SELECT * FROM tst;

 

col1 | col2

--+--

N|   20

(1 row)

 

Regards

Charles

Re: Not able to update some rows in a table

[Charles Clavadetscher]

> On 02.07.2018, at 17:59, Marco Fochesato  wrote:
> 
> 
>> 
>> I would suggest writing a self-contained script that creates the table, 
>> inserts a single record, and updates that record.  Present that for 
>> consideration along with a description or capture of the results of running 
>> the script on your machine.
> 
> But not all the records fail the update... 
> 
> 

Do you have a trigger or a rule intercepting the update? You may see this with 
\d in a console.

Re: Not able to update some rows in a table

[Charles Clavadetscher]

>> On 02.07.2018, at 17:59, Marco Fochesato  wrote:
>> 
>> 
>>> 
>>> I would suggest writing a self-contained script that creates the table, 
>>> inserts a single record, and updates that record.  Present that for 
>>> consideration along with a description or capture of the results of running 
>>> the script on your machine.
>> 
>> But not all the records fail the update...
> 
> Do you have a trigger or a rule intercepting the update? You may see this 
> with \d in a console.

RE: Question on disk contention

[Charles Clavadetscher]

Hi Melvin

 

From: Melvin Davidson [mailto:melvin6...@gmail.com] 
Sent: Donnerstag, 31. Mai 2018 15:53
To: Charles Clavadetscher 
Cc: pgsql-generallists.postgresql.org 
Subject: Re: Question on disk contention

 

 

 

On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher 
mailto:clavadetsc...@swisspug.org> > wrote:

Hi Melvin

As an answer to a previous post you wrote:

"Also, your main problem is that when you have two exact same queries executing 
at the same time, they will cause contention in
the disk, and neither one will make much progress."

Could you elaborate a little more on the meaning of "contention in the disk"?
What is it that happens?

Thank you and have a good day.
Regards
Charles




>Could you elaborate a little more on the meaning of "contention in the disk"?
>What is it that happens? 


 

To simplify, you have two users/jobs, both wanting the exact same information. 
So the system instructs the disk to get 

that information from the disk, which causes the disk head to "seek" to the 
position of the first eligible row and 

continues positioning to other eligible rows. Now the job is not exclusive, so 
the system temporarily switches to the

other job, which causes the disk to go back to the first row and work from 
there. The switching back and forth continues,

so that instead of one job finishing quickly, they both have to take turns 
waiting for needed information. That takes

a lot longer,

 

Try this, Select a table that has a lot of rows, ideally 1M+. Then start a 
query with a WHERE clause and see how long

it takes. Then submit the same query from 5 separate connections simultaneously 
and see how long that takes.

 

Thank you very much for your answer.

Regards

Charles

 


-- 

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only! 

Question on disk contention

[Charles Clavadetscher]

Hi Melvin

As an answer to a previous post you wrote:

"Also, your main problem is that when you have two exact same queries executing 
at the same time, they will cause contention in
the disk, and neither one will make much progress."

Could you elaborate a little more on the meaning of "contention in the disk"?
What is it that happens?

Thank you and have a good day.
Regards
Charles

RE: RE: How do I select composite array element that satisfy specific conditions.

[Charles Clavadetscher]

Hello

 

From: a [mailto:372660...@qq.com] 
Sent: Mittwoch, 23. Mai 2018 14:23
To: Charles Clavadetscher <clavadetsc...@swisspug.org>; pgsql-general 
<pgsql-gene...@postgresql.org>
Subject: Re: RE: How do I select composite array element that satisfy specific 
conditions.

 

Thanks for your reply...

 

Honestly I do not use java and don't really know json. All I understand is that 
it is a text format that allow some customization.

 

Java and JSON are not really related. For a simple description of JSON: 
http://www.json.org

 

However, as long as it can solve my problem, I'm happy to learn it.

 

now I do have a complex structure of data to store. what I'm aiming at is to:

 

1, orgnize the data so that it has hierarchy and structrues for people to 
operate.

 

2, all updates, insertion, will be recorded (including who, when, for what 
reason and which element changed from what to what).

currently I wrote a C trigger to dynamically disassemble the complex structure 
and compare them one by one and generate a string
that printing out every change along with the update user info.

 

since my amount of data are not that big and the trigger is written in C, the 
final efficient is considerablly accepted. Now my
question would be if json would be helpful on creating a relative efficient 
mechanism on that..

 

I assume that it is possible and much easier, but this would require more 
knowledge on the data that you want to pack in the json
structure. Besides that, a basic question would be if it even necessary at all 
to have such a complex structure. In many cases a
simpler design is more efficient.

 

The best thing would be to have a look at how JSON works and decide for 
yourself, if it helps in your case. Creating new types and
aggregating them in array sounds like an overkill, but I may be mistaken.

 

For example your original example in JSONB could look like this:

 

[

  {

"x": 1,

"y": 2

  },

  {

"x": 3,

"y": 4

  }

]

 

In the database:

 

CREATE t (a JSONB);

INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');

SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';

 

e

--

{"x": 3, "y": 4}

(1 row)

 

For completeness. The answer to your original question is:

 

SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;

 

  ay

---

(3,4)

(1 row)

 

Regards

Charles

 

---Original---

From: "Charles Clavadetscher"<clavadetsc...@swisspug.org 
<mailto:clavadetsc...@swisspug.org> >

Date: Wed, May 23, 2018 19:29 PM

To: "'pgsql-general'"<pgsql-gene...@postgresql.org 
<mailto:pgsql-gene...@postgresql.org> >;"'a'"<372660...@qq.com
<mailto:372660...@qq.com> >;

Subject: RE: How do I select composite array element that satisfy specific 
conditions.


Hi

> -Original Message-
> From: a [mailto:372660...@qq.com]
> Sent: Mittwoch, 23. Mai 2018 11:43
> To: pgsql-general <pgsql-gene...@postgresql.org 
> <mailto:pgsql-gene...@postgresql.org> >
> Subject: How do I select composite array element that satisfy specific 
> conditions.
> 
> Hi, say if I have composite type and table
> 
> create type A as(
>  x float8,
>  y float8
> );
> 
> create table B(
>  Ay A[]
> );
> 
> insert into B
> values(array[
>  (1,2)::A,
>  (3,4)::A]
> );
> 
> How could I select the element of Ay that satisfy x=3??
> 
> Thank you so much!!
> 
> Shore

I did not really follow this thread, so I am not in clear, why you want to 
complicate your life that much.
You create a custom data type and then use it in an array in a column. A 
complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic 
very much like a school assignment.

Bye
Charles

RE: help to query json column

[Charles Clavadetscher]

Hello

> -Original Message-
> From: Arup Rakshit [mailto:aruprakshit1...@outlook.com]
> Sent: Mittwoch, 7. März 2018 05:41
> To: pgsql-general@lists.postgresql.org
> Subject: help to query json column
> 
> Hi,
> 
> I would like to select only rows where signature has a non null value. My 
> json looks like :
> 
> {
>   "carInspection": {
> "damages": [
>   {
> "x": 68.670309653916,
> "y": 44.08014571949,
> "errors": [
>   {
> "code": "BR",
> "description": "Gebrochen"
>   },
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 40.8014571949,
> "y": 50.273224043716,
> "errors": [
>   {
> "code": "BR",
> "description": "Gebrochen"
>   },
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 48.269581056466,
> "y": 37.340619307832,
> "errors": [
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 49.180327868852,
> "y": 15.482695810565,
> "errors": [
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   }
> ],
> "layoutURL": "default",
> "signature1": " 0 1000 

Re: How to avoid trailing zero (after decimal point) for numeric type column

[Charles Clavadetscher]

Hello

> On 28.02.2018, at 13:33, pkashimalla  wrote:
> 
> Hello Team,
> 
> We have recently migrated our database from Oracle
> And there are few of my tables with numeric column type.
> 
> In below example
> I did insertion from java program with below code snippet
> 
> Double object = 10.0;
> String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES
> (?)";
> selectPrepareStmt.setObject(1, object,Types.NUMERIC);
> int count = selectPrepareStmt.executeUpdate();
> 
> it inserted like this.
> /
> select id from blob_test_table;
> 
> id
> numeric
> -
> 10.0/
> 
> 
> In this case, when a decimal point is equal to 0 then,  I don't want to see
> the precision and the value in the column should just 10
> 
> And If I execute code,
> 
> Double object = 10.5801
> String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES
> (?)";
> selectPrepareStmt.setObject(1, object,Types.NUMERIC);
> int count = selectPrepareStmt.executeUpdate();
> 
> Now ,the value in the column should be 10.5801 as the precision is greater
> than ZERO
> 
> Because of this, the migrated data (from Oracle) is without PRECISION ZERO
> and the new data which is being inserted is with PRECISION ZERO. 
> 
> /
> select id from blob_test_table;
> 
> id
> numeric
> -
> 10.0
> 10
> 11
> 11.0
> /
> 
> Is there a possible setting in PostgreSQL server to achieve this?
> 

I think that you should set the colomn type to real or double precision:

select 10.0::double precision;
10

select 10.5801::double precision;
10.5801

Regards
Charles

> FYI - 
> 
> Oracle's NUMBER column type is handling it as I expected.
> I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL
> 
> 
> 
> Thanks,
> Praveen
> 
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 

RE: Remove default privilege from DB

[Charles Clavadetscher]

Hi

 

From: Durumdara [mailto:durumd...@gmail.com] 
Sent: Donnerstag, 15. Februar 2018 12:41
To: Charles Clavadetscher <clavadetsc...@swisspug.org>
Cc: Postgres General <pgsql-gene...@postgresql.org>
Subject: Re: Remove default privilege from DB

 

Dear Charles!

 

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetsc...@swisspug.org 
<mailto:clavadetsc...@swisspug.org> >:

Hi

 

From: Durumdara [mailto:durumd...@gmail.com <mailto:durumd...@gmail.com> ] 
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General <pgsql-gene...@postgresql.org 
<mailto:pgsql-gene...@postgresql.org> >
Subject: Remove default privilege from DB

 

Hello!

 

I need to remove default privileges from a Database.

After that some of them remains.

 

 Default access privileges

   Owner| Schema |   Type   | Access privileges

++--+---

 postgres   || function | =X/postgres

 postgres   || sequence |

 postgres   || table|

 postgres   || type | =U/postgres

 suser|| function | =X/suser

 suser|| sequence |

 suser|| table|

 suser|| type | =U/suser

 

How to completely remove the last items? 

Could you send me one example?

 

I assume

 

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

 

Bye

Charles

 

 

After that:

 

 Default access privileges

   Owner| Schema |   Type   | Access privileges

++--+---

 suser || function |

 suser || sequence |

 suser || table|

 suser || type |

(4 rows)


What are they?



I am a bit puzzled. I checked the documentation and execute on function is the 
hard wired default privilege.

https://www.postgresql.org/docs/current/static/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC. No 
privileges are granted to PUBLIC by default on tables, table columns, 
sequences, foreign data wrappers, foreign servers, large objects, schemas, or 
tablespaces. For other types of objects, the default privileges granted to 
PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) 
privileges for databases; EXECUTE privilege for functions; and USAGE privilege 
for languages and data types (including domains). The object owner can, of 
course, REVOKE both default and expressly granted privileges.

So after revoking it from public you should actually get an ACL like 
{suser=X/suser} and the entry for the grants should make it disapper.

Example:

 

charles@db.localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

---++--+---

(0 rows)

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

+-+---+---

(0 rows)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE 
EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

 Default access privileges

  Owner   | Schema |   Type   |  Access privileges

--++--+-

charlesc || function | charlesc=X/charlesc

(1 row)

 

Now only user charlesc can execute (new) functions created by himself. This is 
the most typical use case when restricting access to self-made functions.

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype |   defaclacl

+-+---+---

   25269137 |   0 | f | {charlesc=X/charlesc}

(1 row)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE 
ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

 Default access privileges

Owner | Schema | Type | Access privileges

---++--+---

(0 rows)

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

+-+---+---

(0 rows)

 

Now again. everybody can execute functions created by charlesc.

What version of PostgreSQL are you using?
And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

again?

Regards
Charles

select * from pg_default_acl

24629;0;"r";"{}"

24629;0;"S";"{}"

24629;0;"f";"{}"

24629;0;"T";"{}"

 

24629 = suser | namespace 0 = none in document

Hmmm... It's very strange for me. I don't find any point which links this user 
to this database.

 

Do you have any idea?

 

Thanks

  dd

 

 

Re: How do I get rid of database test-aria

[Charles Clavadetscher]

Hi

> On 13.02.2018, at 21:38, Sherman Willden  wrote:
> 
> development platform: HP Compaq 6710b
> postgresql 9.6
> Operating System: Ubuntu 17.10
> 
> It probably doesn't matter since there is nothing in the database. I don't 
> remember  how I created the database. It appears that postgresql does not 
> like a dash in the database name. I have not tried to create a table under 
> the database. When I try to drop the database I get the following..
> 
> date_time=# DROP DATABASE test-aria;
> ERROR:  syntax error at or near "-"
> LINE 1: DROP DATABASE test-aria;
> 

Does
DROP DATABASE "test-aria";
work?

Regards
Charles

> Thanks;
> 
> Sherman

RE: Remove default privilege from DB

[Charles Clavadetscher]

Hi

 

From: Durumdara [mailto:durumd...@gmail.com] 
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General 
Subject: Remove default privilege from DB

 

Hello!

 

I need to remove default privileges from a Database.

After that some of them remains.

 

 Default access privileges

   Owner| Schema |   Type   | Access privileges

++--+---

 postgres   || function | =X/postgres

 postgres   || sequence |

 postgres   || table|

 postgres   || type | =U/postgres

 suser|| function | =X/suser

 suser|| sequence |

 suser|| table|

 suser|| type | =U/suser

 

How to completely remove the last items? 

Could you send me one example?

 

I assume

 

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

 

Bye

Charles

 

Thank you for it!

 

Best regards

  DD

 

 

RE: Set role dynamically from proc

[Charles Clavadetscher]

Hello

 

From: Durumdara [mailto:durumd...@gmail.com] 
Sent: Mittwoch, 22. November 2017 14:56
To: pgsql-gene...@postgresql.org
Subject: Set role dynamically from proc

 

Hello!

 

May you know the way how to set role dynamically.

 

DO 

$$

DECLARE act_dbowner varchar(100);

BEGIN

 

SELECT u.usename into act_dbowner FROM pg_database d

JOIN pg_user u ON (d.datdba = u.usesysid)

WHERE d.datname = (SELECT current_database());

raise notice 'DB owner: %', act_dbowner;

 

set role to act_dbowner; --  THIS LINE



END

$$;

 

-

 

ERROR:  role "act_dbowner" does not exist

CONTEXT:  SQL statement "set role to act_dbowner"

PL/pgSQL function inline_code_block line 10 at SQL statement

 

I try to use $act_dbowner, but it have no effect.

 

It seems that the user does not exist:

 

CREATE OR REPLACE FUNCTION set_role()

RETURNS VOID

AS $$

BEGIN

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

  SET ROLE blabla;

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

END;

$$ LANGUAGE plpgsql;

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  role "blabla" does not exist

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

 

db=> CREATE ROLE blabla;

CREATE ROLE

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  permission denied to set role "blabla"

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

 

db=> GRANT blabla TO kofadmin;

GRANT ROLE

 

Now it works:

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

NOTICE:  CURRENT_USER: blabla

[...]

 

Regards

Charles

 

Thank you for your help!

 

Best wishes

   dd