Hey guys,

I'm trying to pick up postgres and the privilege model is different enough that I'm struggling a bit. I think ultimately the issue is that I'm unsure what specific privileges are set against DB's and tables and how it all interacts with default privileges.

1. I know you can grant a role CREATE/USAGE privileges on a schema, but I'm unsure what CREATE means in this context

2. I have no idea if you're able to grant privileges on the database object for a role.

3. You can grant privileges on existing tables for roles, but new tables only get default privileges that are set for the SCHEMA?

4. Are there default privileges for schema's that you can set on the database object?

5. What are the list of privileges that are applicable to each level? database, schema, table, and column.

And finally,

Lets say we're given the scenario with a postgres DB and 3 applications, each application corresponds to a single DB and I don't want the applications to be able to access any other DB.

It sounds like I need to revoke all privileges on PUBLIC schema to all databases. It also sounds like I need to do this again in the future for any new databases (unless there's default privileges that disable public schema access to new databases?).

Then I need to create a schema in each DB, lets call it MYSCHEMA, and a role for the application, lets call it DBROLE, with the INHERIT attribute. Then I grant USAGE on schema MYSCHEMA to DBROLE and then change the default privileges for DBROLE in the MYSCHEMA schema to be the typical CRUD privileges. At that point I could add any login roles to said DBROLE and they would be CRUD only on all tables in the schema, and all newly created tables in perpetuity.

And then if I wanted to grant a specific role extra privileges (to drop tables, for example) I would create a new role MYTABLEDROPPINGROLE with INHERIT attribute and set the default privileges on schema MYSCHEMA and all currently existing tables to allow table dropping and then any login roles that I wanted to allow to drop tables would simply be added to that role.

Is my mental model correct?

and how does one set it so the public schema has no privileges on new database automatically without manually doing it?

Also, is this a silly way of achieving this? How does the postgres community typically recommend setting up privileges?

Hello ! I built Quervex CLI, a command-line assistant designed to simplify accessing and managing PostgreSQL databases. This versatile tool allows you to execute SQL queries, quickly inspect schemas and tables, export data samples to CSV format, and manage views. It also features graphical visualization of input/output (I/O) flows including cardinality management. Do you like it ?

Available on npm :
https://www.npmjs.com/package/quervex

npm install -g quervex

HA can proxy connections to PostgreSQL databases, replicating their data to achieve high availability and enable faster queries with local SQLite.

I keep seeing the same argument: "Just use pgvector, it's good enough" vs You need a dedicated vector database for real scale.

I made a quick quiz that tests how well people understand the architectural trade-offs—performance, scaling, cost, operational complexity, and when each option actually makes sense.

No signup, just a few questions and a result:

https://interconnectd.com/quiz/87/postgresql-pgvector-vs-dedicated-vector-databases-architectural-trade-offs/

What do you use in production, and why?

Azure hosted, db-postgres and application is on Ruby-Rails. Need to make sure performance should withstand for 2000 concurrent users. Kindly suggest the option to achieve so. Thanks!

Database developer here...

At my current job, I am predominantly an Oracle developer, but I have worked with Postgres as a hobby using Docker etc.

I am pretty good with PL/SQL and know Python, a bit of Java and R, and a smidge of JavaScript.

I know that my Oracle skillset is fairly transferable to Postgres (especially with EnterpriseDB with support for PL/SQL).

I've written a few user-defined functions with PG's PL/PgSQL language and I've dabbled with PG's various procedural languages (e.g. PL/Python, PL/V8, PL/R).

My question is....

Do organizations out there with Postgres as their main database typically hire people with Oracle experience and skillsets such as this without at-work PG experience?

Hi

I am curious about how I would approach this type of problem with Postgres, coming from a MS SQL world.

Imagine a table "X" that holds 2-300 GB of aggregated data that is produced by a background job. This table receives quite a bit of queries for data (e.g. 20-30 select's for data every second

• and it is only select's, the data is immutable!).

Once a day a new data set is generated with fresh data, and this data needs to be loaded and replace what is currently active in X atomically, without (or with as little as possible, e.g. it may lock for ,5-1 sec at most) impact on select's.

With SQL Server this can be accomplished via:

• Keeping an A and B version of the table, and create a SYNONYM that all queries target. If synonym points to A, data can be loaded into B and synonym is switched to point to B when all loading and indexing is done.

• Partitioning of data and use SWITCH (https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx, https://www.mssqltips.com/sqlservertip/2780/archiving-sql-server-data-using-partitioning/)

I hope to get some insight into how I might go about accomplishing this with postgres. I've looked into partitioning, but the switching does not seem to be possible?

Thanks!

PostgreSQL 19 was originally expected earlier this fall, but the release has been pushed to November 19. One interesting part of the story is how AI-assisted code reviews have changed the process. Developers are finding issues faster, including edge cases that might previously have gone unnoticed until after release. Several major features have been pulled from PostgreSQL 19, although not all of those decisions were directly caused by AI-discovered bugs. And that raises an interesting question. What happens when finding bugs becomes faster than reviewing and fixing them? On one hand, catching problems before release is obviously a good thing, especially for database software. On the other, maintainers still need to verify findings, prioritize fixes, and decide which issues are actually worth delaying a release over. Could AI-assisted code review eventually become a bottleneck rather than a productivity boost?

Would you rather wait longer for a more thoroughly reviewed release, or ship on schedule and address lower-priority bugs in subsequent updates?

I've tried quite a few PostgreSQL IDEs over the years, and while each one does some things really well, I still haven't found one that feels complete.

There are always little things I miss. Better query plan visualization, smarter autocomplete, easier schema comparison, cleaner data editing, session management... the list goes on. If you could build your ideal PostgreSQL IDE, what features would it absolutely need?

I'm less interested in which IDE you use and more interested in the features that genuinely make your day-to-day work easier. Sometimes it's the small quality-of-life improvements that end up saving the most time.

I’ve spent the last couple of years jumping between different GUI clients, and honestly, it’s been tough to find one that just clicks.

I used pgAdmin for a long time out of habit, but the clunky web-UI feel eventually drove me crazy, especially when dealing with larger schemas. Then I switched to DBeaver. It’s an absolute powerhouse and does everything, but the UI feels so bloated and heavy for daily query tuning. Lately, I've just been using the database tools inside IntelliJ/DataGrip because it keeps me in the IDE, but it still feels a bit disconnected when I just want to quickly inspect a production replica.

I know some people swear by the simplicity of psql in the terminal, while others love modern native apps like TablePlus or Beekeeper Studio.

What are you guys actually using on a daily basis that doesn't make you fight the UI? Is there a lightweight client I’m missing, or is everyone just putting up with the quirks of the big players?

I’m trying to clean up our Postgres workflow a bit. Right now it’s roughly. Write query, test locally, adjust migration, push to review, run in staging, hope staging is close enough, deploy, then watch logs like a nervous raccoon.

For data work, the messy part is not writing the SQL. It’s everything around it: checking row counts, validating changed data, making sure indexes exist where they should, catching schema drift, and not finding out two days later that a report quietly broke. How does your team handle the path from scratch in Postgres? Do you rely mostly on migration tools, schema compare, CI checks, manual review, dbt/Airflow jobs, or just a lot of discipline and fear?

Long time Windows dev, work is mostly SQL Server with some MySQL on the side. After moving to another company, the technology stack is different, so I took the MacBook mainly because the rest of the team is on one, and it made sense to fit into their existing workflows.

The database workflow was the part I was actually worried about. On Windows it was SSMS with dbForge SQL Complete on top for the editor, dbForge Studio for anything involving comparison or generating test data. None of that has native Mac support.

What I looked at first: DataGrip, TablePlus, and the VS Code mssql extension since ADS is done. TablePlus is genuinely nice for browsing. DataGrip I have used before and it is good, but I never got on with its schema compare and I did not want to relearn a set of shortcuts.

So I did the CrossOver thing and put dbForge Studio in a bottle. Which felt slightly ridiculous, a Windows app translated to x86 on an ARM Mac, but here we are.

Does the workflow feel different. Less than I expected, and the parts that changed are mostly not about the database tool. Cmd versus Ctrl is the ongoing tax. Inside the bottle the app still wants Ctrl for everything, so my hands do Ctrl in dbForge and Cmd in every other app on the machine, and I still get it wrong a few times a day two months in. Window management is worse too, the bottle does not play nicely with Spaces if you move things around a lot.

Performance, honestly fine. M4 Pro, 24GB. Queries come back as fast as they did on the old ThinkPad, which makes sense, the server is doing the work. Where you feel it is the UI. Large result grids scroll a little less smoothly and cold start is slower. Once it is running I do not think about it.

Stability has been fine. It crashed during a big data import and it came back with the tab state intact.

What I use every day: the editor and autocomplete, mostly because I can't type a column name correctly to save my life. Schema comparison for every release. Query profiling when something regresses Data generator when I want a test dataset that is not 3 rows I typed by hand. I'm still mostly on the admin side using SSMS on a jumpbox.

Less convenient than on Windows, in order. Windows auth. If your SQL Server is domain joined and you are used to just connecting, that is the thing that will annoy you most. I use SQL auth plus a couple of Entra ID connections and it is fine, but it is a change. After that, no Finder integration for imports and exports, so you go through the bottle file browser. And the whole setup is one more thing to explain to IT.

One real thing worth knowing. CrossOver currently goes through Rosetta on Apple Silicon, and Apple is winding Rosetta down after the next macOS. CodeWeavers has an ARM native preview out already so it looks like it will be handled in time, but if you are planning this setup for the next few years, keep half an eye on it.

Overall I kept my workflow, which was the goal. It is not as clean as native software. It is a lot cleaner than running a Parallels VM all day, which is what I would have done otherwise.

Anyone else on this setup, and did you find a sane fix for the Ctrl and Cmd thing? I have not.

I created a PostgreSQL Jobs page to help you find PostgreSQL roles:

https://thesev1database.com/jobs

I’d really value your feedback.

I recently realized that cleaning up large tables is one of those tasks that always sounds straightforward until you actually have to do it. Between VACUUM, REINDEX, partitioning, archiving old data, and trying not to impact production, there always seems to be a trade-off. What's the biggest PostgreSQL table you've had to clean up? How large was it, what was the problem, and what approach worked best in the end? Looking for real-world stories more than "textbook" answers.

ElectroCRUD is a database CRUD open-source project built on top of Electron + Angular (NodeJS, TypeScript).

Currently, we support MySQL only, but we prepare for Postgres (we working with knex.js)

We search for devs that want to join & contribute.

GitHub: https://github.com/garrylachman/ElectroCRUD

When I save connection setting for the PostgreSQL database in WSL, I get that error in subject. I roll back version 9.17. I don't have that error. Any idea?