postgresql 9.6x installers for windows are no longer available

[Ryan Wexler]

Does anyone know what happened to the EDB windows postgresql 9.6 installers?

The host skips from 9.5->10
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Re: Account privileges

[Joe Conway]

On 11/16/21 9:07 PM, Prathima Mulpuri wrote:

Hi all, I need some help regarding Postgres and I have checked and
tried many queries. I am working on RDS Postgres 13 and in the
process of preparing the script for auditing all the user privileges
. As a part of our auditing, I need a script to list down all the
privileges of all the users to each database and if any privilege
that I need is missing, it should automatically execute the grant
/revoke (for example account1 should have only select privileges. If
it is granted with anything else it should revoke the permissions and
if select is not granted it should grant the select privilege) .
This should be done for all the databases in an instance in 1
script. I want to use cursor to list the databases and to run the
check and execute queries using a function or a stored procedure. The
results of the script should be sent to an email.

Please share any ideas and solutions for my requirement.


I am not aware of an existing solution that does all of those things, 
but you could probably build one starting with the "check_access" extension:


https://github.com/CrunchyData/crunchy_check_access

You can see examples of use of the extension here:

https://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1

In particular, you could use check_access to enumerate all privileges 
when in a known-good state, save that output somewhere as the required 
baseline state (e.g. in a text file), and then compare later audit runs 
against that baseline (e.g. using diff).


Automation of remediation is left as an exercise for you ;-)

HTH,

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: How is JSON stored in PG ?

[Laurenz Albe]

On Tue, 2021-11-16 at 10:54 -0500, David Gauthier wrote:
> Hi:
> 
> psql (11.5, server 11.3) on linux
> 
> I'm considering using JSON as a datatype for something I'm working on.  The 
> reasons are...
> 
> 1) the 'metadata' (if you want to call it that) in JSON is very flexible.  
> Doesn't require an alter table or anything like that to change.
> 2) The customers for this data is probably going to be python code.  IOW, 
> they can sort things out in their code after reading the whole JSON 
> file/record.
> 3) Nice array of built-in functions for this datatype.
> 4) There does appear to be the ability to formulate query predicates on the 
> JSON content (just in case)
> 
> But #4 has me a bit worried.  I'm wondering how PG stores this data given 
> that its content can
> be specified in a query predicate.

I don't follow.  There are JSON functions and operators you can use, and then 
there is the
very powerful JSONPATH query language.

> Does PG just store the content in traditional PG tables ?  If so, I can do 
> that myself.
> If not, is there a penalty to be paid at query time if PG needs to get the 
> JSON data, then
> dismantle into a temp table (or something like that) to query.

Yes, it is stored in tables.  But "jsonb" is stored in a binary data structure 
that makes
it fast and efficient to access attributes and values.

I have written up the indications and counter-indications for using JSON here:
https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: postgresql 9.6x installers for windows are no longer available

[Adrian Klaver]

On 11/16/21 21:44, Ryan Wexler wrote:

Does anyone know what happened to the EDB windows postgresql 9.6 installers?


FYI, I also brought this up on the -www  list.



The host skips from 9.5->10 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 






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

Re: postgresql 9.6x installers for windows are no longer available

[Adrian Klaver]

On 11/16/21 21:44, Ryan Wexler wrote:

Does anyone know what happened to the EDB windows postgresql 9.6 installers?


See this post:

https://www.postgresql.org/message-id/CA%2BOCxowLNQeJ-%2BHKCnsQFgAjX642oociM-wq5t%3Dar%2BDdc2m24g%40mail.gmail.com


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

Re: Postgres Equivalent of Oracle Package

[Mladen Gogala]

On 11/16/21 12:23, DAVID ROTH wrote:

One of the nice things about Oracle packages is that the code is loaded and 
global values are set and stored only once per session. This is very useful for 
values that are used repeatedly.

What is the best way of emulating this behavior in Postgresql?




You can create a temporary table with "ON COMMIT PRESERVE ROWS", which 
is visible in a session and cannot cross the session boundary.  Values 
in the table can be queried throughout the session. That is a pretty 
good approximation of the session variables in Oracle.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Account privileges

[Prathima Mulpuri]

Thank you for your suggestions. I want to use pg_cron extension to schedule the 
weekly job to capture  the privileges of all the databases. I cannot use 
node.js as it needs to connect from an EC2 to make the connection. I have do do 
everything inside the RDS only. 

Sent from my iPhone

> On 17 Nov 2021, at 10:01 PM, Joe Conway  wrote:
> 
> On 11/16/21 9:07 PM, Prathima Mulpuri wrote:
>> Hi all, I need some help regarding Postgres and I have checked and
>> tried many queries. I am working on RDS Postgres 13 and in the
>> process of preparing the script for auditing all the user privileges
>> . As a part of our auditing, I need a script to list down all the
>> privileges of all the users to each database and if any privilege
>> that I need is missing, it should automatically execute the grant
>> /revoke (for example account1 should have only select privileges. If
>> it is granted with anything else it should revoke the permissions and
>> if select is not granted it should grant the select privilege) .
>> This should be done for all the databases in an instance in 1
>> script. I want to use cursor to list the databases and to run the
>> check and execute queries using a function or a stored procedure. The
>> results of the script should be sent to an email.
>> Please share any ideas and solutions for my requirement.
> 
> I am not aware of an existing solution that does all of those things, but you 
> could probably build one starting with the "check_access" extension:
> 
> https://github.com/CrunchyData/crunchy_check_access
> 
> You can see examples of use of the extension here:
> 
> https://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
> 
> In particular, you could use check_access to enumerate all privileges when in 
> a known-good state, save that output somewhere as the required baseline state 
> (e.g. in a text file), and then compare later audit runs against that 
> baseline (e.g. using diff).
> 
> Automation of remediation is left as an exercise for you ;-)
> 
> HTH,
> 
> Joe
> -- 
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development