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

21–30 of 42 posts

Re: PostgreSQL Schema Design

#21

Post by cryptonector »

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 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 overloading of the word can cause confusion, naturally, but once you understand it it's easy enough to keep it straight.

"Let me show you my schema" -> generic sense of the word.

"Utilities live in the 'util' schema, while the business logic lives in the 'public' schema" -> the second sense of the word given above.

Re: PostgreSQL Schema Design

#22

Post by tensor »

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 can create a row level security policy that does the right look up in these tables. I've not run this in a production system yet, but did successfully build out a proof of concept that worked. Performance seemed reasonable.

Re: PostgreSQL Schema Design

#24

Post by quietbritishjim »

uhoh-itsmaciek wrote: post #13
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.

Indeed, this means that it is logically impossible to connect to postgres and create a database, at least through a normal connection. But you can create a schema with the CREATE SCHEMA statement.

Re: PostgreSQL Schema Design

#25

Post by sbuttgereit »

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

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 designing a PostgreSQL database. I tend to work in enterprise business systems with larger database structures than many here I think, so to be sure, what I typically do isn't for everyone, but maybe it'll help you better understand the spot where this concept lives.

First, most database objects, like tables and functions, must live in some schema in PostgreSQL. By default the schema is 'public'. I actually avoid using the public schema in favor of using schemas I create. The reason I do this is because, some extensions and such will also define objects in the public schema and I don't want to confuse stuff from third parties with stuff I manage. By always creating at least one clearly dedicated schema for the objects I create, I know what software I'm managing vs. just got thrown into the dumping ground. I do this on all size databases I create.

If the database is sufficiently complex, I may create different schemas for different "modules" that I define in the software. It helps me to understand, in the database, where the boundaries are. For example, I work with an off-the-shelf ERP system. When I create extensions to this system, I will create a new database schema to hold the various tables and database functions required.

I may use PostgreSQL schemas to logically delineate different security concerns; I'll usually do this in conjunction with different authorization roles that have schema level permissions. I do this more often when there's need to define a database function "API" to the database. I'll put the data into a data schema, but then I'll create, say, two additional schemas... one to hold private/internal database functions and another to hold the "public" facing API. I can then have those applications/integrations that should always use the database function driven API to use a database role which only gives them access to functions defined in that schema. I don't mean to suggest that this is a "sufficient" security mechanism, but is part of a broader strategy of security in depth.

Anyway, some ideas to go along with the definitions.

Re: PostgreSQL Schema Design

#26

Post by uhoh-itsmaciek »

combatentropy wrote: post #18
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 m…

You're right that it's probably not much harder, but the current Postgres role is tied to the session, so you'll either be limited in the number of concurrent users (Postgres does not do great with high connection counts) or juggling SET ROLE around your connection pooling (which sounds dicey plus is just a lot of extra statements).

Re: PostgreSQL Schema Design

#27

Post by uhoh-itsmaciek »

quietbritishjim wrote: post #24
uhoh-itsmaciek wrote: post #13

Earlier quoted context omitted.

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.

Indeed, this means that it is logically impossible to connect to postgres and create a database, at least through a normal connection. But you can create a schema with the CREATE SCHEMA statement.

You can create databases via sql [1]. It's just that after you run the statement, you're still connected to the old database and you need to disconnect in order to connect to the new one.

[1]: https://www.postgresql.org/docs/current/sql-createdatabase.h...

Re: PostgreSQL Schema Design

#28

Post by sixdimensional »

cryptonector wrote: post #21
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 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 formal definition of schema.

Re: PostgreSQL Schema Design

#29

Post by combatentropy »

sbuttgereit wrote: post #25
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"?

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 lowercases strings). When other users call

  select lower(col) from table
they would be calling public.lower instead of pg_catalog.lower. This is just one example. They could do this for any commonly used function in pg_catalog.

The solution is one of:

   drop schema public;
   revoke create on schema public from public;
   alter role all set search_path = "$user";
That last one you could do in postgresql.conf instead.

--- https://www.postgresql.org/docs/current/ddl-schemas.html#DDL...

Re: PostgreSQL Schema Design

#30

Post by manigandham »

megous wrote: post #11
combatentropy wrote: post #10

Earlier quoted context omitted.

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…

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

Post reply on HN