Re: [HACKERS] pg_monitor role

[Thomas Reiss]

Le 20/02/2017 à 12:48, Dave Page a écrit :
> Further to the patch I just submitted
> (https://www.postgresql.org/message-id/CA%2BOCxow-X%3DD2fWdKy%2BHP%2BvQ1LtrgbsYQ%3DCshzZBqyFT5jOYrFw%40mail.gmail.com)
> I'd like to propose the addition of a default role, pg_monitor.
> 
> The intent is to make it easy for users to setup a role for fully
> monitoring their servers, without requiring superuser level privileges
> which is a problem for many users working within strict security
> policies.
> 
> At present, functions or system config info that divulge any
> installation path related info typically require superuser privileges.
> This makes monitoring for unexpected changes in configuration or
> filesystem level monitoring (e.g. checking for large numbers of WAL
> files or log file info) impossible for non-privileged roles.
> 
> A similar example is the restriction on the pg_stat_activity.query
> column, which prevents non-superusers seeing any query strings other
> than their own.
> 
> Using ACLs is a problem for a number of reasons:
> 
> - Users often don't like their database schemas to be modified
> (cluttered with GRANTs).
> - ACL modifications would potentially have to be made in every
> database in a cluster.
> - Using a pre-defined role minimises the setup that different tools
> would have to require.
> - Not all functionality has an ACL (e.g. SHOW)
> 
> Other DBMSs solve this problem in a similar way.
> 
> Initially I would propose that permission be granted to the role to:
> 
> - Execute pg_ls_logdir() and pg_ls_waldir()
> - Read pg_stat_activity, including the query column for all queries.
> - Allow "SELECT pg_tablespace_size('pg_global')"
> - Read all GUCs
> 
> In the future I would also like to see us add additional roles for
> system administration functions, for example, a backup operator role
> that would have the appropriate rights to make and restore backups.
> 
> Comments?

Hello,

That's something really useful. Some customers would like to use a
non-privileged user to connect their monitoring.

I've come to a set of hacks to give such features to a particular
customer, but that remains a hack. But this only works if the monitoring
tool does not prefix explicitly each view or functions with schema
pg_catalog.

I'm really looking forward such feature. Let me know if I can help in
some way.

Regards


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Thomas Reiss]

Hello,

Here's a small docpatch to fix two typos in the new documentation.

Regards,
Thomas


Le 11/03/2016 07:19, Amit Kapila a écrit :
On Fri, Mar 11, 2016 at 12:28 AM, Robert Haas > wrote:

>
>
> Committed with some further editing.  In particular, the way you
> determined whether we could safely access the tranche information for
> any given ID was wrong; please check over what I did and make sure
> that isn't also wrong.
>

There are few typos which I have tried to fix with the attached 
patch.  Can you tell me what was wrong with the way it was done in patch?



@@ -4541,9 +4542,10 @@ AbortSubTransaction(void)
*/
LWLockReleaseAll();
+pgstat_report_wait_end();
+pgstat_progress_end_command();
AbortBufferIO();
UnlockBuffers();
-pgstat_progress_end_command();
/* Reset WAL record construction state */
XLogResetInsertion();
@@ -4653,6 +4655,9 @@ AbortSubTransaction(void)
*/
XactReadOnly = s->prevXactReadOnly;
+/* Report wait end here, when there is no further possibility of wait */
+pgstat_report_wait_end();
+
RESUME_INTERRUPTS();
 }

AbortSubTransaction() does call pgstat_report_wait_end() twice, is 
this intentional? I have kept it in the end because there is a chance 
that in between API's can again set the state to wait and also by that 
time we have not released buffer pins and heavyweight locks, so not 
sure if it makes sense to report wait end at that stage.  I have 
noticed that in WaitOnLock(), on error the wait end is set, but now 
again thinking on it, it seems it will be better to set it in 
AbortTransaction/AbortSubTransaction at end.  What do you think?



With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com 




diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index ec5328e..199f38a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -657,7 +657,7 @@ postgres   27093  0.0  0.0  30096  2752 ?Ss   11:34   0:00 postgres: ser
 
  
   Lock: The backend is waiting for a heavyweight lock.
-  Heayweight locks, also known as lock manager locks or simply locks,
+  Heavyweight locks, also known as lock manager locks or simply locks,
   primarily protect SQL-visible objects such as tables.  However,
   they are also used to ensure mutual exclusion for certain internal
   operations such as relation extension.  wait_event will
@@ -965,7 +965,7 @@ postgres   27093  0.0  0.0  30096  2752 ?Ss   11:34   0:00 postgres: ser
 
  LWLockTranche
  clog
- Waiting for I/O on a clog (transcation status) buffer.
+ Waiting for I/O on a clog (transaction status) buffer.
 
 
  commit_timestamp

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Casting issues with domains

[Thomas Reiss]

Le 11/12/2014 00:46, Tom Lane a écrit :
 Kevin Grittner kgri...@ymail.com writes:
 Tom Lane t...@sss.pgh.pa.us wrote:
 As far as that goes, I think the OP was unhappy about the performance
 of the information_schema views, which in our implementation do exactly
 that so that the exposed types of the view columns conform to the SQL
 standard, even though the underlying catalogs use PG-centric types.

 I don't believe that that's the only reason why the performance of the
 information_schema views tends to be sucky, but it's certainly a reason.
 
 Is that schema too edge case to justify some functional indexes
 on the cast values on the underlying catalogs? (I'm inclined to
 think so, but it seemed like a question worth putting out
 there)
 
 We don't support functional indexes on system catalogs, so whether it'd
 be justified is sorta moot.  On the whole though I'm inclined to agree
 that the information_schema views aren't used enough to justify adding
 overhead to system-catalog updates, even if the pieces for that all
 existed.
 
 Or, since these particular domains are known, is there any sane way
 to special-case these to allow the underlying types to work?
 
 I don't particularly care for a kluge solution here.
 
 I notice that recent versions of the SQL spec contain the notion of a
 distinct type, which is a user-defined type that is representationally
 identical to some base type but has its own name, and comes equipped with
 assignment-grade casts to and from the base type (which in PG terms would
 be binary-compatible casts, though the spec doesn't require that).
 It seems like this might be intended to be the sort of zero cost type
 alias we were talking about, except that the SQL committee seems to have
 got it wrong by not specifying the cast-to-base-type as being implicit.
 Which ISTM you would want so that operators/functions on the base type
 would apply automatically to the distinct type.  But perhaps we could
 extend the spec with some option to CREATE TYPE to allow the cast to come
 out that way.
 
 Or in short, maybe we should try to replace the domains used in the
 current information_schema with distinct types.

That's interesting and could easily solve the problem.

To give some context, for some reason, Drupal queries the
information_schema views before displaying some pages.
As our customer has many tables (approx 6 tables, organised à la
Oracle with one schema per database user). Thus, the seq scan against
pg_class takes ~50ms and the very same one without the cast takes less
than 1ms.

There is an example of query used :
SELECT column_name, data_type, column_default
  FROM information_schema.columns
 WHERE table_schema = 'one_schema'
   AND table_name = 'one_table'
   AND ( data_type = 'bytea'
 OR ( numeric_precision IS NOT NULL
   AND column_default::text LIKE '%nextval%' )
 );

Regards



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Casting issues with domains

[Thomas Reiss]

Le 08/12/2014 16:18, Tom Lane a écrit :
 Thomas Reiss thomas.re...@dalibo.com writes:
 postgres=# explain select * from test2 where a='toto';
 QUERY PLAN
 --
  Seq Scan on test1  (cost=0.00..1693.00 rows=500 width=5)
Filter: (((a)::tstdom)::text = 'toto'::text)
 (2 lignes)
 
 As you can see, a is casted to tstdom then again to text. This casts
 prevents the optimizer to choose an index scan to retrieve the data. The
 casts are however strictly equivalent and should be not prevent the
 optimizer to use indexes.
 
 No, they are not equivalent.  The optimizer can't simply drop the
 cast-to-domain, because that cast might result in a runtime error due
 to a domain CHECK constraint violation.  (This is true even if no such
 constraint exists at planning time, unfortunately.  If we had a
 mechanism to force replanning at ALTER DOMAIN ADD CONSTRAINT, maybe the
 no-constraints case could be handled better, but we don't; and adding
 one would also imply adding more locks around domain usage, so it's not
 all that attractive to do it.)
 
 The short answer is that SQL domains are not zero-cost type aliases.
 Perhaps there would be value in having a feature that *is* a a zero-cost
 alias, but it wouldn't be a domain.

I agree regarding the feature for zero-cost aliases. It would ease
access on the catalog done via the information_schema for example.

Thanks for your answer. There's some room for improvement for sure, but
it not as easy as it seems.

Regards,
Thomas



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Casting issues with domains

[Thomas Reiss]

Hello all,


We experienced some casting issues with domains. We experienced the
problem while querying the information_schema btw, but here is a simpler
test case :

postgres=# create table test1 (a text);
CREATE TABLE
postgres=# insert into test1 select generate_series(1,10);
INSERT 0 10
postgres=# create index idx1 on test1(a);
CREATE INDEX
postgres=# analyze test1 ;
ANALYZE;
postgres=# explain select * from test1 where a = 'toto';
  QUERY PLAN
---
 Index Only Scan using idx1 on test1  (cost=0.29..8.31 rows=1 width=5)
   Index Cond: (a = 'toto'::text)
(2 lignes)

Now we create a tstdom domain and cast the a column to tstdom in the
view definition :
postgres=# create domain tstdom as text;
CREATE DOMAIN
postgres=# create view test2 as select a::tstdom from test1 ;
CREATE VIEW
postgres=# explain select * from test2 where a='toto';
QUERY PLAN
--
 Seq Scan on test1  (cost=0.00..1693.00 rows=500 width=5)
   Filter: (((a)::tstdom)::text = 'toto'::text)
(2 lignes)

As you can see, a is casted to tstdom then again to text. This casts
prevents the optimizer to choose an index scan to retrieve the data. The
casts are however strictly equivalent and should be not prevent the
optimizer to use indexes.

Also, the same problem appears in the information_schema views, as every
object names are casted to information_schema.sql_identifier. Even if
this domain is declared as name, no index will be used because of this cast.

Shouldn't the planner simplify the casts when it's possible ?


Regards,
Thomas


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Small doc patch for pg_replication_slots

[Thomas Reiss]

Le 01/05/2014 04:56, Robert Haas a écrit :
 On Tue, Apr 29, 2014 at 5:39 AM, Thomas Reiss thomas.re...@dalibo.com wrote:
 You can find attached a small patch to fix the pg_replication_slots
 documentation. The slot_type and plugin are not in the appropriate
 order, slot_name and plugin have a wrong type and xmin appears two times.
 
 Without the patch, the description of catalog_xmin is:
 
   entryThe literalxmin/literal, or oldest transaction ID, that this
   slot forces to be retained in the system catalogs. /entry
 
 With the patch it's:
 
   entryThe oldest transaction that this slot needs the database to
   retain.  literalVACUUM/literal cannot remove catalog tuples deleted
   by any later transaction.
 
 That's only one word different from the language for xmin, which
 doesn't seem quite right.  Committed after fixing that.
 
 ...Robert
 

Thanks

-- 
Thomas Reiss
Consultant Dalibo
http://dalibo.com - http://dalibo.org


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Small doc patch for pg_replication_slots

[Thomas Reiss]

Hello,

You can find attached a small patch to fix the pg_replication_slots
documentation. The slot_type and plugin are not in the appropriate
order, slot_name and plugin have a wrong type and xmin appears two times.

Regards
-- 
Thomas Reiss

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 415a3bc..d8c41d9 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5252,23 +5252,23 @@
 tbody
  row
   entrystructfieldslot_name/structfield/entry
-  entrytypetext/type/entry
+  entrytypename/type/entry
   entry/entry
   entryA unique, cluster-wide identifier for the replication slot/entry
  /row
 
  row
-  entrystructfieldslot_type/structfield/entry
-  entrytypetext/type/entry
+  entrystructfieldplugin/structfield/entry
+  entrytypename/type/entry
   entry/entry
-  entryThe slot type - literalphysical/ or literallogical//entry
+  entryThe basename of the shared object containing the output plugin this logical slot is using, or null for physical slots./entry
  /row
 
  row
-  entrystructfieldplugin/structfield/entry
+  entrystructfieldslot_type/structfield/entry
   entrytypetext/type/entry
   entry/entry
-  entryThe basename of the shared object containing the output plugin this logical slot is using, or null for physical slots./entry
+  entryThe slot type - literalphysical/ or literallogical//entry
  /row
 
  row
@@ -5305,7 +5305,7 @@
  /row
 
  row
-  entrystructfieldxmin/structfield/entry
+  entrystructfieldcatalog_xmin/structfield/entry
   entrytypexid/type/entry
   entry/entry
   entryThe oldest transaction that this slot needs the database to
@@ -5315,14 +5315,6 @@
  /row
 
  row
-  entrystructfieldcatalog_xmin/structfield/entry
-  entrytypexid/type/entry
-  entry/entry
-  entryThe literalxmin/literal, or oldest transaction ID, that this
-  slot forces to be retained in the system catalogs. /entry
- /row
-
- row
   entrystructfieldrestart_lsn/structfield/entry
   entrytypepg_lsn/type/entry
   entry/entry

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers