PG Phriday: Taking Postgres for GRANTed

Not every database-backed application needs to be locked down like Fort Knox. Sometimes there are even roles that leverage blanket access to large swathes of available data, if not every table, simply for auditing or monitoring purposes. Normally this would require quite a bit of preparation or ongoing privilege management, but Postgres came up with a unique solution starting with version 14: predefined roles. This topic comes up relatively frequently in Postgres chats like Discord, Slack, and IRC.

Getting Settled

The dust has mostly settled and we’re finally unpacking in the new house. Since it’s Memorial Day, I’m taking some time to rest and gather my thoughts to chronicle this whole affair. There’s still a lot to do, but hopefully the most grueling elements have passed. In Closing Our closing date for the house was May 17th, meaning we could take possession later that day. It would be a good opportunity to get a second look to refresh our memories and drop some emergency supplies off to make sure there was room for the cat carriers on Monday.

PG Phriday: On the Move

Sometimes when we have an existing database and schema full of tables, there comes a time when we need to perform a migration. Maybe it’s because we want to move to or from a cloud service. Perhaps we have a small database and want to do a quick version upgrade via dump / restore. Whatever the reason, we may decide to clean up some technical debt while we’re making the transition.

Many Postgres experts recommend against creating objects in the public schema. This is the default schema that exists in nearly all Postgres databases, and there are often implicit grants that could make our objects available in unexpected scenarios. It’s also a cluttered namespace if all tables, views, functions, etc., are created there by default. Using it is sloppy and makes future data or operation segregation much more difficult.

So how can we move a bunch of existing stuff out of the public schema safely?

PG Phriday: Papa's Got a Brand New RAG

Remember this guy? Robo-Postgres returns! AI is all anyone talks about these days, isn’t it? Even when it comes to databases like Postgres, devs are finding new and innovative ways to leverage LLMs in everyday use cases. Can you really blame them though? This is an exciting new technology that will transform how we work and potentially society as a whole once it finally matures. We even covered building a crude RAG app a few short weeks ago.

On the Move

It’s no secret that Jen and I have been thinking of moving out of Macomb for a while now. The change in jobs has given me a much-needed kick in the pants to reorganize my life in general. Those interest rates aren’t going any lower until at least 2025, so there’s no time like the present. So we’ve been on the hunt. But where to go? I want to abscond from Illinois on pure principle, but given the amount of family in the area, I can bide my time.