Re: User documentation vs Official Docs

[Pavel Stehule]

Hi

2018-08-10 21:00 GMT+02:00 Bruce Momjian :

> On Fri, Jul 20, 2018 at 05:31:40PM -0700, Adrian Klaver wrote:
> > JD sit down, I am going to agree with you:) The documentation as it
> stands
> > is very good, though it requires some fore knowledge to successfully
> > navigate. On pages with a lot of content it often is not evident, to
> many,
> > that there are actually examples at the bottom of the page. Also that the
> > exceptions to the rules are called out there also. The general concept of
> > presenting a task, writing a procedure to accomplish the task and
> pointing
> > to the documentation that covers the procedure would be a helpful
> addition.
> > It would be nice to point to something like that in a post rather then
> > continually rebuilding the explanation every time a new user hits the
> list.
> > Looking at the link posted upstream:
>
> I am jumping in late here, but I do have some thoughts on this topic.
> To me, there are three levels of information presentation:
>
> 1.  Task-oriented documents
> 2.  Exhaustive technical documentation/manuals
> 3.  Concept-level material
>
> I think we call agree that the Postgres documentation does very well
> with #2, and we regularly get complements for its quality.
>
> For #1, this is usually related to performing a task without requiring a
> full study of the topic.  For example, if I need iptables rules to block
> a sunrpc attack, or use NFS over ssh, I really want some commands that I
> can study and adjust to the task --- I don't want to study all the
> features of these utilities to get the job done.  This is an area the
> docs don't cover well, but our blogs and wikis do.
>
> For #3, this is mostly covered by books.  This topic requires a lot of
> explanation and high-level thinking.  We have some of that in our docs,
> but in general books probably do this better.
>

I wrote lot of documentation related to plpgsql and some other, but
unfortunately it is in Czech language. It is free, so it can be freely
transalated

Here are links - on the page is a possibility to set google translator

https://postgres.cz/wiki/Jak_nepou%C5%BE%C3%ADvat_PL/pgSQL,_p%C5%99%C3%ADpadn%C4%9B_PL/SQL,_a_dal%C5%A1%C3%AD_fat%C3%A1ln%C3%AD_chyby
https://postgres.cz/wiki/PL/pgSQL

Regards

Pavel


> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +  Ancient Roman grave inscription +
>
>

Re: How to revoke privileged from PostgreSQL's superuser

[Rui DeSousa]

> On Aug 10, 2018, at 7:21 PM, Benedict Holland  
> wrote:
> 
> Oh this is an easy one. There are simpler solutions for PITR. Also, a PITR is 
> a very specific case of a database use, if it even uses one. Generally 
> speaking, you would not want to keep encrypted data within a database. There 
> simply isn't a need for it. Just use a file or a folder. You can't do 
> anything that you would normally do with a database if you can't read or 
> access any of your objects. It would just be a table of binary objects 
> without names, possibly access or creation dates depending on the level of 
> paranoia. Literally, you would have an int column and a binary object column. 
> What can you honestly do with that? You can't even link it to other objects. 
> It has no relational structure, hense the question. If there isn't a 
> relationship to anything then a relational database wouldn't really help 
> anything. 
> 
> Also, I would probably keep the encryption key within the database anyway. 
> Otherwise, your objects could get permanently lost making the whole thing 
> moot in the first place. 
> 
> Look, you either trust your DBAs or you don't. If you don't trust them, why 
> are they your DBA? This is like writing unit tests for unit tests or having 
> even higher levels of privilege than a superuser. It's turtles all the way 
> down.
> 
> ~Ben
> 
> 
> On Fri, Aug 10, 2018 at 4:12 PM, Rui DeSousa  > wrote:
> 
> 
>> On Aug 6, 2018, at 10:45 AM, Bear Giles > > wrote:
>> 
>> then it's reasonable to ask if storing the information in a relational 
>> database is the best approach.
> 
> Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the 
> database. What about PITR, how would that be handled?  You basically would 
> have to reimplement things the RDBMS system gives you for free by storing it 
> outside the database.  Don’t forget it's called a management system for a 
> reason.
> 

With that logic then you should use flat files for encrypted data and 
unencrypted data.  It’s what was done many moons ago; and its unstructured 
haphazard approach gave rise to RDBMS systems.

You cannot say that encrypted data does not belong in a RDBMS system… that is 
just false.  Hell, I’ve stored blobs in a RDMBS system which could have easily 
been stored in a different system if need be.  It’s a design choice and what 
fits the application and budget needs.

Encrypting sensitive information and storing in the database is a valid use 
case.  It may be only a few columns that are encrypted or a complete document 
(blob); there is no need to increase complexity just to move those columns out 
of the database.

Re: How to revoke privileged from PostgreSQL's superuser

[Benedict Holland]

Oh this is an easy one. There are simpler solutions for PITR. Also, a PITR
is a very specific case of a database use, if it even uses one. Generally
speaking, you would not want to keep encrypted data within a database.
There simply isn't a need for it. Just use a file or a folder. You can't do
anything that you would normally do with a database if you can't read or
access any of your objects. It would just be a table of binary objects
without names, possibly access or creation dates depending on the level of
paranoia. Literally, you would have an int column and a binary object
column. What can you honestly do with that? You can't even link it to other
objects. It has no relational structure, hense the question. If there isn't
a relationship to anything then a relational database wouldn't really help
anything.

Also, I would probably keep the encryption key within the database anyway.
Otherwise, your objects could get permanently lost making the whole thing
moot in the first place.

Look, you either trust your DBAs or you don't. If you don't trust them, why
are they your DBA? This is like writing unit tests for unit tests or having
even higher levels of privilege than a superuser. It's turtles all the way
down.

~Ben


On Fri, Aug 10, 2018 at 4:12 PM, Rui DeSousa  wrote:

>
>
> On Aug 6, 2018, at 10:45 AM, Bear Giles  wrote:
>
> then it's reasonable to ask if storing the information in a relational
> database is the best approach.
>
>
> Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the
> database. What about PITR, how would that be handled?  You basically would
> have to reimplement things the RDBMS system gives you for free by storing
> it outside the database.  Don’t forget it's called a management system for
> a reason.
>

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Dimitri Maziuk]

On 08/10/2018 02:49 PM, Christoph Berg wrote:

> plv8 will work for anyone willing to go through that. It's just not
> feasible to support it from a packager perspective.

Hm. Gotta wonder if running this:
https://github.com/clkao/docker-postgres-plv8/blob/master/10-2/Dockerfile
and copying plv8.so out of the resulting image is the easy way out.

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: How to revoke privileged from PostgreSQL's superuser

[Rui DeSousa]

> On Aug 6, 2018, at 10:45 AM, Bear Giles  wrote:
> 
> then it's reasonable to ask if storing the information in a relational 
> database is the best approach.

Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the 
database. What about PITR, how would that be handled?  You basically would have 
to reimplement things the RDBMS system gives you for free by storing it outside 
the database.  Don’t forget it's called a management system for a reason.

Re: How to revoke privileged from PostgreSQL's superuser

[Benedict Holland]

The short answer I will provide from my experience is that you can't do it.
Your DBA will have access to just about anything across all tables and
databases.

The longer answer are ones that others have pointed out. If a DBA should be
restricted from tables, they probably shouldn't be your DBA. Your DBA will
likely be the one responsible, for example, for backing up all of the
databases on a server. That requires read access and understanding concepts
about secure backups of sensitive data. It is also possible that they are
running backups as their own user rather than postgres. If you don't want
DBAs to access your data you really do not want that data to not have
backups.

I also would take Bruce's comment with a massive grain of salt. Everything
that everyone does on a database is logged somewhere assuming proper
logging. Now do you have the person-power to go through gigs of plain text
logs to find out if someone is doing something shady... that is a question
for your management team. Also, if you suspect someone of doing something
shady, you should probably revoke their admin rights.

~Ben


On Fri, Aug 10, 2018 at 3:41 PM, Bruce Momjian  wrote:

> On Mon, Aug  6, 2018 at 06:19:55AM -0700, David G. Johnston wrote:
> > On Monday, August 6, 2018,  wrote:
> >
> >
> > 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.
> >
> >
> > User then needs to encrypt data prior to storing it.  Superuser can still
> > access the data but would be challenged to make sense of it,
>
> Keep in mind DBAs can often remove data with little detection, unless
> you are using some kind of block chain, which itself can force
> serialized data access, slowing things down.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +  Ancient Roman grave inscription +
>
>

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Christoph Berg]

Re: Bruce Momjian 2018-08-10 <20180810194407.ge7...@momjian.us>
> Wow, OK.  That's bad news.  So PL/v8 is no longer a viable stored
> procedure language?

It is bad news, the plv8 upstream is very pleasant to work with.

But now building plv8 means building v8 first, which means something
like downloading and building the whole chrome toolchain. That's 30 GB
of stuff, including binary blobs from the internet.

plv8 will work for anyone willing to go through that. It's just not
feasible to support it from a packager perspective.

Christoph

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Bruce Momjian]

On Fri, Aug 10, 2018 at 09:41:44PM +0200, Christoph Berg wrote:
> Re: Bruce Momjian 2018-08-10 <20180810192205.gc7...@momjian.us>
> > Uh, who is building PL/v8 currently, and for what operating systems?  No 
> > one?
> 
> No one is likely correct.

Wow, OK.  That's bad news.  So PL/v8 is no longer a viable stored
procedure language?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Christoph Berg]

Re: Bruce Momjian 2018-08-10 <20180810192205.gc7...@momjian.us>
> Uh, who is building PL/v8 currently, and for what operating systems?  No one?

No one is likely correct.

Christoph

Re: How to revoke privileged from PostgreSQL's superuser

[Bruce Momjian]

On Mon, Aug  6, 2018 at 06:19:55AM -0700, David G. Johnston wrote:
> On Monday, August 6, 2018,  wrote:
> 
> 
> 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.
> 
> 
> User then needs to encrypt data prior to storing it.  Superuser can still
> access the data but would be challenged to make sense of it,

Keep in mind DBAs can often remove data with little detection, unless
you are using some kind of block chain, which itself can force
serialized data access, slowing things down.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: Who and How is responsible for released installations packages and 3rd party packs? (e.g. on https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/)

[Bruce Momjian]

On Fri, Aug  3, 2018 at 10:30:51AM +0200, Pavel Raiskup wrote:
> On Friday, August 3, 2018 8:08:55 AM CEST Devrim Gündüz wrote:
> > On Thu, 2018-08-02 at 16:26 +0300, Alexandru Lazarev wrote:
> > > What about 3rd party libraries like plv8 - Who and How (based on which
> > > criteria, which versions) build RPM and upload them there?
> >
> > Latest versions of PL/v8 does not build on RHEL/Fedora anymore, at least 
> > from
> > the package build point of view.
> 
> Yes, packaging of plv8 is pretty complicated.  If one decided to ship RPM
> package with plv8, it would mean maintenance of whole v8 language - which
> is incredibly complicated (incompatible changes all the time, backporting
> security fixes, etc.).
> 
> That's the reason why plv8 (and even v8 runtime) becomes dropped from Linux
> distributions.

Uh, who is building PL/v8 currently, and for what operating systems?  No one?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: User documentation vs Official Docs

[Bruce Momjian]

On Fri, Jul 20, 2018 at 05:31:40PM -0700, Adrian Klaver wrote:
> JD sit down, I am going to agree with you:) The documentation as it stands
> is very good, though it requires some fore knowledge to successfully
> navigate. On pages with a lot of content it often is not evident, to many,
> that there are actually examples at the bottom of the page. Also that the
> exceptions to the rules are called out there also. The general concept of
> presenting a task, writing a procedure to accomplish the task and pointing
> to the documentation that covers the procedure would be a helpful addition.
> It would be nice to point to something like that in a post rather then
> continually rebuilding the explanation every time a new user hits the list.
> Looking at the link posted upstream:

I am jumping in late here, but I do have some thoughts on this topic. 
To me, there are three levels of information presentation:

1.  Task-oriented documents
2.  Exhaustive technical documentation/manuals
3.  Concept-level material

I think we call agree that the Postgres documentation does very well
with #2, and we regularly get complements for its quality.

For #1, this is usually related to performing a task without requiring a
full study of the topic.  For example, if I need iptables rules to block
a sunrpc attack, or use NFS over ssh, I really want some commands that I
can study and adjust to the task --- I don't want to study all the
features of these utilities to get the job done.  This is an area the
docs don't cover well, but our blogs and wikis do.

For #3, this is mostly covered by books.  This topic requires a lot of
explanation and high-level thinking.  We have some of that in our docs,
but in general books probably do this better.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: Audit management

[dangal]

Achilleas thanks for your answer
A query, the only possible way out is the postgres log?
I would like to be able to throw the audit output to a different place so
that I can not access the file



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

Re: Audit management

[Achilleas Mantzios]

On 10/08/2018 17:01, dangal wrote:

Dear, I would like to ask you to see what you recommend
I manage a production database with a nominated user with the same
permissions as the postgres user SUPERUSER INHERIT CREATEDB CREATEROLE
REPLICATION
The client is asking us for an audit about this nominated user, could you
recommend any extension or form that you believe is the best solution,
I am currently testing pgaudit

Pgaudit. That's what we use. It seems to be a really nice tool.




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



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Audit management

[dangal]

Dear, I would like to ask you to see what you recommend
I manage a production database with a nominated user with the same
permissions as the postgres user SUPERUSER INHERIT CREATEDB CREATEROLE
REPLICATION
The client is asking us for an audit about this nominated user, could you
recommend any extension or form that you believe is the best solution,
I am currently testing pgaudit



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

Re: multiple conflict targets

[Adrian Klaver]

On 08/10/2018 03:12 AM, Torsten Förtsch wrote:

Hi,

if I read https://www.postgresql.org/docs/9.6/static/sql-insert.html 
correctly there can be only one conflict target or none at all.


What if I have a table with 3 unique constraints? If the first one is 
violated by the insert I want to do nothing. If the 2nd is violated some 
fields should be updated. And violation of the 3rd one should raise an 
error.


Can that be expressed?


Yes as a trigger and associated function.



Thanks,
Torsten



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

Re: select version() with internal number version?

[Luca Ferrari]

On Thu, Aug 9, 2018 at 3:51 PM Tom Lane  wrote:
> Also, if you're using libpq, see PQserverVersion() which
> (a) avoids a round trip to the server, and (b) works further back
> than server_version_num, though that issue is probably academic
> to most folk at this point (server_version_num appeared in 8.2).

Thanks but server_version_num is what I was looking for, since I need
to get the version number from outside PostgreSQL in foreign languages
(python, perl, etc).
I don't believe going back than 8.2 is a metter in this case, since
this is for supporting tools that should not...ehm...support any more
EOL versions.

Luca

Re: Postgres - search for value throughout many tables?

[Daniel Verite]

Adrien NAYRAT wrote:

> On 08/08/2018 04:09 PM, czezz wrote:
> > Hi everyone,
> > I want to aks if anyone knows is there a way to search for specific 
> > "value" throughout list of tables OR all tables in databse?
> > 
> > Cheers,
> > czezz
> 
> Hello,
> 
> Maybe this article by Daniel could help you : 
> https://blog-postgresql.verite.pro/2017/06/06/global-search.html
> 
> Unfortunately it is not translated, you have to play with a translate tool.

You may also get the code and README from here:
https://github.com/dverite/postgresql-functions/tree/master/global_search


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

multiple conflict targets

[Torsten Förtsch]

Hi,

if I read https://www.postgresql.org/docs/9.6/static/sql-insert.html
correctly there can be only one conflict target or none at all.

What if I have a table with 3 unique constraints? If the first one is
violated by the insert I want to do nothing. If the 2nd is violated some
fields should be updated. And violation of the 3rd one should raise an
error.

Can that be expressed?

Thanks,
Torsten

Re: Postgres - search for value throughout many tables?

[Adrien NAYRAT]

On 08/08/2018 04:09 PM, czezz wrote:

Hi everyone,
I want to aks if anyone knows is there a way to search for specific 
"value" throughout list of tables OR all tables in databse?


Cheers,
czezz


Hello,

Maybe this article by Daniel could help you : 
https://blog-postgresql.verite.pro/2017/06/06/global-search.html


Unfortunately it is not translated, you have to play with a translate tool.

Regards,

timestamp arithmetics in C function

[Lutz Gehlen]

Hello all,

I am trying to implement a C function that accepts a date ("date" in 
the sense of a type of information, not a postgres datatype) as 
parameter (among others) and returns a certain point in time. (The 
background is to calculate the time of dawn and dusk at the given 
date.) Ideally, I would like to accept a timestamp value and return 
another timestamp as result. I have implemented the function, but I 
would like to ask advice on whether my implementation is the 
recommended way to achieve this.

To get started - since this is my first attempt at a C function in 
postgres - I implemented a function that accepts the date as three 
separate int32 values for year, month, and day and returns the time 
of dawn as a float8 for the minutes since midnight (this is what the 
implemented algorithm internally returns, anyway):


PG_FUNCTION_INFO_V1(dawn_utc);

Datum dawn_utc(PG_FUNCTION_ARGS) {
  float8 lat  = PG_GETARG_FLOAT8(0);
  float8 lon  = PG_GETARG_FLOAT8(1);
  int32  year = PG_GETARG_INT32(2);
  int32  month= PG_GETARG_INT32(3);
  int32  day  = PG_GETARG_INT32(4);
  float8 solar_depression = PG_GETARG_FLOAT8(5);

  // postgres-independent computation goes here
  float8 dawn_utc = calc_dawn_utc
(lat, lon, year, month, day, solar_depression);

  PG_RETURN_FLOAT8(dawn_utc);
}


This works fine. However, it would be more convenient if the function 
would accept a date or timestamp value and return a timestamp. So I 
modified the first part of the function like this, based on code 
snippets I found in the postgres source code:


PG_FUNCTION_INFO_V1(dawn_utc);

Datum dawn_utc(PG_FUNCTION_ARGS) {
  float8 lat  = PG_GETARG_FLOAT8(0);
  float8 lon  = PG_GETARG_FLOAT8(1);
  Timestamp timestamp = PG_GETARG_TIMESTAMP(2);
  float8 solar_depression = PG_GETARG_FLOAT8(3);

  struct pg_tm tt;
  struct pg_tm *tm = &tt;
  fsec_t   fsec;

  if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 errmsg("timestamp out of range")));

  // postgres-independent computation goes here
  float8 dawn_utc = calc_dawn_utc
(lat, lon, tm->tm_year, tm->tm_mon, tm->tm_mday, 
solar_depression;


For the second part of the function, I now have to add the 
calculated number of minutes to the date portion of the timestamp 
variable. One has to be aware that depending on the geographic 
location dawn_utc can possibly be negative or larger than 1440 (i.e. 
24h). I am not sure whether I should construct an interval value 
from the number of minutes and add that to the timestamp. I have not 
figured out how to do this, but decided to calculate a new timestamp 
in a more fundamental way:


  tm->tm_sec  = 0;
  tm->tm_min  = 0;
  tm->tm_hour = 0;
  Timestamp result;
  if (tm2timestamp(tm, 0, NULL, &result) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 errmsg("timestamp out of range")));

#ifdef HAVE_INT64_TIMESTAMP
  /* timestamp is microseconds since 2000 */
  result += dawn_utc * USECS_PER_MINUTE;
#else
  /* timestamp is seconds since 2000 */
  result += dawn_utc * (double) SECS_PER_MINUTE;
#endif

  PG_RETURN_TIMESTAMP(result);


Again this code is based on what I found in the source code. It 
seems to work correctly (at least on my development machine), but I 
am wondering whether this is a safe and recommended way to achieve 
this result or whether it is considered bad practice to manipulate a 
timestamp on such fundamental level.

Thank you for your advice and best wishes,
Lutz