JSONB Types, Earth Distance, Dates, Times & Intervals, Authentication | Scaling Postgres 123

In this episode of Scaling Postgres, we discuss working with JSONB types, calculating earth distance, utilizing dates, times & intervals and Postgres authentication.

Content Discussed

• JSONB: A Container of Types
• Recreating YikYak with Postgres.
• Representing Dates, Times and Intervals in PostgreSQL
• How Secure is your Postgres? Part 2: Authentication
• How to SCRAM in Postgres with pgBouncer
• What are PostgreSQL templates?
• pgwatch2 v1.8.0 released
• Barman Cloud – Part 2: Cloud Backup
• Stacey Haysler

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres, we talk about JSON B types, Earth distance,dates, times, intervals and authentication. I'm Kristen Jameson, and this is scaling postgresepisode 123.

[00:00:22] All right, I hope you, your family and coworkers is continue to do well. Our first pieceof content is JSON B, a container of types. This is from Momgn US and it's talking about usingJSON B columns. Now, I found this interesting because he's talking about how types are usedrelative to postgres types and JSON types. And in this example here, he just puts straight valuesinto a JSON B column. So he puts text string, an integer, a boolean, and there's actually afunction called JSON B type above that returns what the type is of that JSON string. Now, Ishould mention there is a thunderstorm going on in case you hear any thunder. So you can usethis function to determine what the actual type is that is stored within that JSON B field. Now, ofcourse, the actual type according to Postgres, is still JSON B for all of these different pieces ofinformation that are being stored. Now, you can convert each of these values to text using thisoperator here, or you could also cast it. And what's interesting is that even though you can castit, also when you're storing data, you need to define it appropriately. So, for example, whenyou're inserting values, when you try to insert a text in a numeric combination, it actually givesyou an error. You actually need to store it as text because that's not a numeric. For example,that's actually a series of text string, whereas this can be interpreted as a numeric type. So hecorrected by changing it to actually an exponential. But you could, if you want to actually storethis value, convert it to text by using double quotes. And then below here, you can see how he'scasting it to a numeric using the double colon. Now, he's just been using values, but he says youcan also apply the same concept to documents as well. So full documents, you can do the samething and get the type JSON B type of to tell you whether it's a string or a number. So I foundthis pretty interesting because there's times I've had to store an unknown set of data andtypically I've just used text strings to do it and had to do the type conversion at the applicationlayer. But having this ability using the JSON B typo function, I might explore different alternativeswhen I need to implement a new solution to this. So if you're interested in this content, definitelycheck out this blog post.

[00:02:47] The next piece of content is Recreating YikYak with postgres. This is fromAdamfallon.com. Basically, he's talking about calculating distances using postgres. And YikYakwas a social network that would show people posting information within a certain distancearound you. So he wanted to implement this using postgres so he set up Postgres, set up adatabase table for Postgres. And in it he used floats to store a latitude and a longitude. Now, hedoes say you can do it as a point, so that's another data type he could have used. But he did itusing two floats. He inserted two posts into the table, storing different latitudes and longitudes.And then from a third location, he posted another one. Now, to do this, he installed twoextensions. So basically, here's post one. Here's post two. And he's saying, let's imagine that weare at this location here. Now, to calculate this, he installed a cube extension and an Earthdistance extension. So this is separate from PostGIS. You can do the same sort of techniqueusing PostGIS. And I've actually seen references that that is potentially more accurate, some ofthe functions they have. But this is a quick and easy way to do it, using these two extensions.And to find the nearby posts, you use this query right here. So he's selecting from Posts, he'sputting in latitude and longitude of the we are here area. And for a certain distance in metersaround this point, it uses a function called ll Two Earth. So latitude, longitude to Earthcoordinates and then uses an Earth box as kind of the bounding area and then uses theContains operator to look through the Posts latitude and longitude, again converting it using thisll Two Earth function. And with that, he gets the two posts that we were expecting. So it's a veryquick and easy way to see locations that are close by to a certain area. And then as a bonus inthe conclusion, he says, of course you can add a Gist index here wit --h a function against the table to get much better performance. So if you're interested in doingcalculations involving Earth distance, definitely check out this blog post.

[00:04:58] The next piece of content is representing dates, times and intervals in PostgreSQL.This is from PG IO. Now, in the first section here, it goes over a I would say pretty opinionatedperspective of what types you should be using and how you should be using them, although theydon't give a lot of justification to why, but just basically say do this, don't do this, et cetera. Now,the main types they advocate you using are dates. So dates not containing a time, timestamp z.So the timestamp, that includes a time zone. So that's pretty much going to be most of your timetracking data type and then an interval. So a duration, for example. And they give an examplehere, one month, three days, for example.

[00:05:45] So these are generally the data types that you're going to want to use, working withdates and times. Now, actually, depending upon your application framework, I've also seen thecase of using the timestamp without the time zone, although a lot of Postgres users advocatenot using that. Some application frameworks have time zone capabilities built in and thereforethe database just stores everything in UTC time and the conversion happens in the application.But of course it's up to you to determine how best you would like to do that implementation. Andthen it goes through and shows you different ways you can use these different data types. So forexample, you can convert what is now to a date and add a certain number of days, or determinehow many days from now. A certain day is how many days till Christmas, the ten longestcourses in a table. And then they talk about the timestamp type and how that can be used. Andalso specifically talking about the differences between a transaction timestamp statementtimestamp and the clock timestamp. So the transaction one indicates the timestamp at the startof the transaction. Times statement timestamp is the timestamp at the start of the statement thatyou're in. And then Clock timestamp gives you the actual system clock. And lastly it coversinterval types and how you can work with those. Then they follow up looking on how you canextract different date time components from timestamps, as well as doing time zone conversionsfor working with dates and times. So if you're interested in a post to learn more about dates,times and intervals, definitely check out this post from PG IO.

[00:07:21] The next piece of content is how secure is your postgres? Part Two authentication.This is from the enterprisedb.com blog. In this section they're talking about authentication andprimarily that's done through the Pghba.com file. So it has different entries within that file thatwhen a client connection is attempted. This file is reviewed and the first matching entry is what'sused for authentication purposes. So for example, this is using a local connection, connecting toa particular database for a particular user. And they're going to use Scram authentication. In thisnext example, they're using a host based access to connect via a particular IP address to aspecific database for a specific user from a specific client IP. And they're going to be using MDFive authentication. Now of course, what you are generally going to use postgres is Host SSL,so requiring SSL connections to the database server or the host GSS ENC, which is a GSS APIencrypted connection. So generally those are the two you're going to want to use. You can ofcourse also use local connections if you're connecting to the database for administrative tasks.And then the next section here, they cover the different authentication types. So Trust isbasically there's no protections at all if you're using Host and the IP matches, you can get in,there's no password or anything required.

[00:08:47] They talk about peer and identification. Peer is local connections that matches upusers. So if the user in the database matches the user name, the system it grants you access.Ident is the same concept on a network level. So that is definitely not as secure, so you want toprobably pretty much avoid that. These are the password authentication methods MD Five andScrum. And as right now, everyone's advocating moving towards scram that is supported in themost recent versions of Postgres. You could also choose to do an external authenticatio --n system using Kerberos or LDAP, as well as use certificates. And it describes how you canset up certificates to be able to authenticate connections between clients and servers. And lastly,they follow up talking about the authentication timeout you may want to set in yourpostgresql.com file, as well as a contrib module you could add off delay. That minimizes howquickly someone can try password attempts against your PostgreSQL database server. So ifyou're interested in learning more about authentication and PostgreSQL, definitely check out thispost from EnterpriseDB.

[00:09:55] The next piece of content is how to scram in postgres with PG Bouncer. And this isfrom Crunchydata.com, and it explains mostly how scram works, but it actually doesn't show youhow to set it up. So like it doesn't give the commands and the steps to run through, but explainshow it works, how PG Bouncer kind of handles it, and the advantages of using scrum overprevious authentication methods. So if you want to learn more about how scram works with PGBouncer, definitely check out this post.

[00:10:26] The next piece of content are what are PostgreSQL templates? And this is fromSuperbase IO, and they're talking about postgres database templates. Now by default, when youcreate a cluster, you're going to see a template zero database and a template one database. Sowhenever you create a new database, it uses the contents of template one to make a copy ofthat to create your new database. So you can make changes to template one. And then everydatabase you create from that point forward will have any changes you've made to say, objectsyou've added to that database. Now template zero is basically a backup in case some bigproblem happens to template one. So basically never make changes to this, but you can use itto create a new template one if you need to. And actually they advocate not making changes totemplate one, but what they advocate doing is creating a custom template database. And whatyou would do is create a new database, set it up the way you want with all the objects added orchanges you want to be able to make it a template. And then you execute this command alterdatabase with the new template name with is template True. Then at that point when you createa new database to choose your new template, you say create database with the new nametemplate and then your template name and it will create that new database using your customtemplate. So if you want to learn more about templates in PostgreSQL, definitely check out thisblog post.

[00:11:54] The next piece of content is PG watch two version 1.8.0 released. This is fromCybertechn Postgresql.com, and this is a monitoring tool for Postgres, and it has a new release.The highlights for it are support for PG Pool Two, PostgreSQL 13 and Timescale DB MetricStorage. So if you're interested in PG Watch Two or looking for a monitoring solution, definitelycheck out this post from Cybertechnposgreskool.com.

[00:12:23] The next piece of content is Barman cloud part Two cloud Backup Now, we coveredthe first part of this post in the previous episode of Scaling Postgres, talking about the Barmanwall archive, and it was basically a way to send the wall archive to a cloud destination providersuch as Amazon's S Three. Well, this shows you how to actually do the cloud backup part. So ifyou're interested in that and use Barman, check out this post from Secondquadrant.com.

[00:12:51] And the last piece of content is the PostgreSQL Person of the Week is StaceyHassler. So if you're interested in learning more about Stacey and her contributions toPostgreSQL, definitely check out this blog post that does it. For this episode of Scaling Postgres,you can get links to all the content mentioned in the show notes be sure to head over toScalingposgres.com, where you can sign up to receive weekly notifications of each episode, oryou can subscribe via YouTube or itunes. Thanks. --