Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Schema Design

graphile.org

Post reply on HN
Print view View original

31–40 of 42 posts

Re: PostgreSQL Schema Design

#31

Post by miles-po »

uhoh-itsmaciek wrote: post #14

>Warning: Be very careful with logging, while we encrypt our passwords here it may be possible that in a query or server log the password will be recorded in plain text! I was glad to see this noted, but surprised this is the extent of the advice and that sending passwords to the database in plaintext is still recommended. Are there more fleshed out best practices around avoiding logging pitfalls of doing this?

Another method, which has worked for me, is to never store passwords in the database at all but in a separate authenticator like Google Auth or AWS Cognito. Then the authenticated username is passed in via signed JWT. The database can perform authorization, but authentication (and authentication method) are kept far away from the data in your database.

Re: PostgreSQL Schema Design

#32

Post by sbuttgereit »

combatentropy wrote: post #29
sbuttgereit wrote: post #25

Earlier quoted context omitted.

There are many good answers here already. As long as you understand that, in PostgreSQL you 1) connect to a single database; 2) you can only query within a single database (normally); and 3) multiple PostgreSQL schemas can exist in a database and that you may query across them, you understand the functionality well enough. So I have a few practices related to PostgreSQL schemas that I like to employ when I'm designin…

Speaking of security and schemas, I just learned about a vulnerability having to do with Postgres's public schema (CVE-2018-1058). It's possible only if you have untrusted users who can create objects, like tables and functions --- which is rare. As an example, someone could make a function in the public schema called "lower" which does something different than the function by the same name in pg_catalog (which lower…

Yep!

This is another reason why I don't favor actually using "public". Aside from issues like this it's in the default search_path and typically you want to leave it there. So when I do create other schemas, I also don't add them to the search_path globally or otherwise. Overall, I find the search_path setting spooky and, while it can save from typing, that saving ain't worth it... even setting it locally or for a session or a transaction... just no.

Re: PostgreSQL Schema Design

#33

Post by mbrameld »

manigandham wrote: post #30
megous wrote: post #11

Earlier quoted context omitted.

> I don't recommend more than one cluster per host. I just mention it as possible. I actually use this as a cheap data backup mechanism. I have a primary database cluster on SSD, and two replicas on two separate harddrives. All running as three postgresql clusters on the same machine. The harddrives run intentionally on different filesystems, so that filesystem bug will not eat all my DB data. (in case someone wants…

This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage? https://github.com/wal-e/wal-e https://github.com/wal-g/wal-g

It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.

Re: PostgreSQL Schema Design

#34

Post by mjard »

mbrameld wrote: post #33
manigandham wrote: post #30

Earlier quoted context omitted.

This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage? https://github.com/wal-e/wal-e https://github.com/wal-g/wal-g

It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.

You never know: https://support.hpe.com/hpesc/public/docDisplay?docId=emr_na...

Re: PostgreSQL Schema Design

#35

Post by sbuttgereit »

sixdimensional wrote: post #28
cryptonector wrote: post #21

Earlier quoted context omitted.

In general , "schema" refers to the design of database elements -- table names, tables' columns and their names and types, function names and prototypes, etc. In some RDBMSes "schema" also refers to a namespace that qualifies type, table, view, materialized table, and function names -- this qualifier is optional, so you don't always see it, but all obtjects' fully qualified names include the schema name. This overloa…

And the confusion/overloading of the term is even worse than this - in Oracle the concept of "schema" overlaps with "user". In other words, owner of the object and namespace of the object overlaps. It makes sense that the owner of an object is like saying the object belongs to a namespace, but coupling user to that takes getting used to in systems that do that. The ANSI SQL standard (I think part 11) describes the fo…

PostgreSQL kinda mimics the oracle way, though it only goes so far. The default definition of PostgreSQL's search_path is:

"$user", public

So if a schema was named the same as the user, you'd automatically have objects available without qualification.

I spent about a fair amount of time working with Oracle and it forces the paradigm of a schema meaning user much more than does PostgreSQL... though with some effort you can make it work somewhat like the logical namespacing capability that PostgreSQL schemas are used for.

Re: PostgreSQL Schema Design

#36

Post by socceroos »

tensor wrote: post #22
awinter-py wrote: post #12

is the PG built-in role system flexible enough to do more sophisticated forms of RBAC? (groups, shares). Is there a performance hit? Have always liked the idea of graphile & wanted to try PG built-in roles, but wasn't sure whether the feature set was robust / whether the DB communicates properly about errors.

Yes, but you need to model it in the database. You don't even need to use the built in role system, you can use a user id that you pass into your database session. Basically, create three tables: user, user_role, role_permission. Each user can have one or more role, and each role has one or more permissions. Permissions could be things like "view_admin_panel" or even granular like "view_project_with_id_5". Then, you…

Yep, I made permissions tables per-entity too with simple `CRUD` access a bit like RWX (it was an API). It's definitely fast enough. I was handling ~5000 concurrent users with queries returning in the ns range. To be fair, I skipped RBAC checks if the entity was marked as 'public'.

Re: PostgreSQL Schema Design

#37

Post by yrro »

uhoh-itsmaciek wrote: post #14

>Warning: Be very careful with logging, while we encrypt our passwords here it may be possible that in a query or server log the password will be recorded in plain text! I was glad to see this noted, but surprised this is the extent of the advice and that sending passwords to the database in plaintext is still recommended. Are there more fleshed out best practices around avoiding logging pitfalls of doing this?

Postgresql has built in crypt and gen_salt functions which may be good enough for your security requirements.

Re: PostgreSQL Schema Design

#38

Post by bzzzt »

mbrameld wrote: post #33
manigandham wrote: post #30

Earlier quoted context omitted.

This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage? https://github.com/wal-e/wal-e https://github.com/wal-g/wal-g

It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.

A power surge caused by a lightning strike would kill your master as well as your backup. Besides that, do you also keep all your WAL logs so you can roll back an accidentally dropped database?

Re: PostgreSQL Schema Design

#39

Post by megous »

bzzzt wrote: post #38
mbrameld wrote: post #33

Earlier quoted context omitted.

It would only fail if the host hardware failed in a way that damaged all three discs, which seems unlikely. It's for data backup, not uptime redundancy.

A power surge caused by a lightning strike would kill your master as well as your backup. Besides that, do you also keep all your WAL logs so you can roll back an accidentally dropped database?

A power surge here where I am may kill the power supply. It's low probability that it would kill everything.

Anyway, I don't fear the lightnings/storms/power surges where I am as much as I do the inevitable failures of the storage devices, or kernel bugs.

This is just a part of layered protection I have. Offline backups are nice, and I have them too, but they are out of sync all the time by definition. So unless absolutely needed, having a real-time synchronized replica is much more prefered.

Re: PostgreSQL Schema Design

#40

Post by megous »

manigandham wrote: post #30
megous wrote: post #11

Earlier quoted context omitted.

> I don't recommend more than one cluster per host. I just mention it as possible. I actually use this as a cheap data backup mechanism. I have a primary database cluster on SSD, and two replicas on two separate harddrives. All running as three postgresql clusters on the same machine. The harddrives run intentionally on different filesystems, so that filesystem bug will not eat all my DB data. (in case someone wants…

This seems complicated and would still fail if something happens to the host hardware itself right? What about projects like wal-e/wal-g that continuously archive to cloud storage? https://github.com/wal-e/wal-e https://github.com/wal-g/wal-g

Looks like an interesting option too.

But it's not really complicated. Making a replica is just a few shell commands and two new systemd service files. It's probably simpler than setting up wal-e, especially if you count in the setup and maintenance costs of those cloud accounts, and the need to keep up with regular payments for the services, and recovery not being as simple as switching to an already configured and uptodate replica is.

Post reply on HN