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

11–20 of 42 posts

Re: PostgreSQL Schema Design

#11

Post by megous »

combatentropy wrote: post #10
nemothekid wrote: post #2

One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?

In PostgreSQL, the hierarchy is: host > cluster > database > schema > object. By host, I mean a server. A host can have many database clusters. Usually it has just one. To have more than one, you would have to have more than one PostgreSQL instance running, each listening on a separate port. The default port is 5432. I don't recommend more than one cluster per host. I just mention it as possible. A cluster can have m…

> 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 to suggest raid ;))

Re: PostgreSQL Schema Design

#12

Post by awinter-py »

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.

Re: PostgreSQL Schema Design

#13

Post by uhoh-itsmaciek »

nemothekid wrote: post #2

One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?

You got some good answers already, but one thing to note is that a Postgres connection is always to a single database. So you can query across multiple schemas from the same connection, but not multiple databases.

Re: PostgreSQL Schema Design

#14

Post by uhoh-itsmaciek »

>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?

Re: PostgreSQL Schema Design

#15

Post by ComputerGuru »

edoceo wrote: post #9
ComputerGuru wrote: post #6

Earlier quoted context omitted.

It is perfectly fine to have a one-to-one mapping between schema and database, thereby treating them as interchangeable. So far as I know, by convention the schema will be the “database name” and the database name would just be “public”.

You have it backwards. Database is named "foo" and the default schema in PG is called "public". PG uses a schema search path. When you select from table you're really selecting from public.table. Cross schema queries like SELECT public.client.id, billing.ledger.amount FROM public.client JOIN billing.leger ON ....

Sorry, yes. Thanks for the correction!

Re: PostgreSQL Schema Design

#16

Post by vbezhenar »

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?

Hash password in your application. Probably it's not compatible with that postgraphile thing, it's more general advice.

Re: PostgreSQL Schema Design

#17

Post by ComputerGuru »

vbezhenar wrote: post #16
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?

Hash password in your application. Probably it's not compatible with that postgraphile thing, it's more general advice.

And if you’re using a language that supports it, pass passwords around in a custom data type/wrapper around string that does not leak its contents when your platform equivalent of .toString() is called (and override the JSON or slog conversion interface).

Re: PostgreSQL Schema Design

#18

Post by combatentropy »

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.

I've wanted to try this too. I think speed should be fine. Roles are saved in system tables.

Traditionally the applicaiton handles authentication and authorization. Then it connects to the database as a generic user account, with access to everything. It lets the signed-in user do only the things that he should, through carefully crafted queries.

If instead you registered each end user as a database user, then that makes certain things easier (and perhaps other things harder). For one thing, the current user is available as a variable, current_user. You could create database views that use that variable, instead of having to feed it into each query as a parameter (a small convenience, I admit). You could make the current_user the default value for columns like created_by. You could update columns like last_edited_by purely through triggers. In general, you could write more of your logic in pure SQL, instead of a tight coupling of SQL and your application.

If you're not used to doing it this way, it feels dangerous, and rightly so. But don't think it's actually harder than securing it in application code, just less familiar. A new user in PostgreSQL has no rights, only what is granted through commands.

For ease of maintenance, you can gather users into groups. They are both called roles. A role that has a login is generally a user. You can add role to another role, though, and so the second role acts like a group. Then you can grant and revoke rights to the group, instead of having to issue commands to change the rights one user at a time. There is even a function to help check role membership, pg_has_role.

Re: PostgreSQL Schema Design

#19

Post by jimktrains2 »

nemothekid wrote: post #2

One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?

Siblings have answered the question, but I'll add that the terminology comes from the sql standard, I don't believe it's postgres specific.
Post reply on HN