9 ms·

PostgreSQL beginner guide

238 points by lukasbar 6y ago

wilsonrocks 6y ago
I get that this is for learning purposes, but doesn't this tutorial start by making your database insecure?
- orev 6y ago
  If you need remote access to the DB, then you need remote access. Most people in a normal setup would not have the database server ports directly exposed to the Internet, but if you have the perspective that all servers are in the cloud, then you would also be expected to know how to keep them secure. But making it listen on an external IP does not inherently make it insecure, as long as you also have other firewall and user account controls.
  - MaxBarraclough 6y ago
    I don't know much about Postgres specifically, but what I'm seeing in the tutorial does seem insecure. > If you need remote access to the DB, then you need remote access That's not a defence for teaching insecure practices. It's possible to configure Postgres for secure remote access. Using md5 for password security seems like a red flag, and sure enough, the Postgres docs remind us that md5 should no longer be considered secure. [0] I don't think there's any verification of the server's fingerprint, either. There's really no excuse here, as there's a quick and easy way to do it: SSH tunnels. [1] > Most people in a normal setup would not have the database server ports directly exposed to the Internet Not a safe assumption. Instances on Linode and, iirc, Digital Ocean, are not behind a cloud firewall, all ports are wide open to the Internet. (I think that's a bad move on the part of Linode and Digital Ocean, but that's not the point.) > if you have the perspective that all servers are in the cloud, then you would also be expected to know how to keep them secure If you already knew how to securely configure Postgres, you wouldn't need the tutorial. > making it listen on an external IP does not inherently make it insecure, as long as you also have other firewall and user account controls You still need to configure the crypto properly. [0] https://www.postgresql.org/docs/current/auth-password.html https://www.postgresql.org/docs/current/auth-password.html [1] https://www.postgresql.org/docs/current/ssh-tunnels.html https://www.postgresql.org/docs/current/ssh-tunnels.html
smashah 6y ago
Related side note/question. I was on a project recently where postgres was used with a NodeJS JavaScript server app. It seems a bit backwards. Like you have the flexibility of a loosely typed server app but all the annoyance of a RDBMS. It just made development a nightmare. The 'strictness' if the system was just backwards. Surely NoSQL + typescript is better for development (and potentially for performance). Thoughts?
- maxmunzel 6y ago
  I’d be interested, which use case of yours would be restricted by Postgres‘ strict typing. All my experiences were wonderful, especially its performance and powerful features like rule rewriting. Also PostgREST is brilliant.
  - how_gauche 6y ago
    I've done this before (postgREST) for internal apps and it's amazing. You can POST a JSON file to the front end from a shell script with curl and have it show up on a Grafana dashboard immediately. I've used this in the past for setting up automated quality tests for machine learning pipelines -- the pipeline runs automatically and you get the results in a central place, so you can see how your quality metrics are trending over time. Given that postgREST exists, I don't see any reason to talk to postgres directly from a JS app.
- Sebb767 6y ago
  As someone who works on a business app using CouchDB+Typescript, I can tell you that (a) you will also hit the edge cases of NoSQL, like transactions and joins, and it will not be that awesome either and (b) at some point you'll realize that you'll need to do schema validation still. Frontend bugs, possibly malicious clients and protected attributes (i.e. 'disabled' should only be set by an admin) will force you to write a lot of validation anyway. It definitely allows for faster iteration, though. You can deploy a prototype or change very quickly without caring about this, which is pretty nice. But you need to keep track of that and pay the debt if you use the prototype. Overall, I think it would not give or take much if we'd be using PostgreSQL+some JSON column for general data instead of CouchDB. You just need to know your stack and its drawbacks and work with them.
autotune 6y ago
Can anyone recommend an “advanced” PG guide? I’m looking to learn all the ins and outs of Postgres.
- throwaway8941 6y ago
  http://www.interdb.jp/pg/ http://www.interdb.jp/pg/ https://www.postgresql.org/docs/current/internals.html https://www.postgresql.org/docs/current/internals.html
- golergka 6y ago
  The official documentation is pretty damn good. Most of the time when I needed to learn something about Postgres, it had all the answers I needed, and in rare occasions it didn't, I used Stack Overflow to figure out what other part of the manual I needed to read.
- akerro 6y ago
  Checks posts of this author https://habr.com/en/users/erogov/posts/ https://habr.com/en/users/erogov/posts/ he doesn't write often, but it's advanced technical topic, I find it super useful and well explained.
2mol 6y ago
So this is cool, and can serve as a good cheatsheet for beginners. However, the question where I struggled the most: how the hell do I set up a secure Postgres instance on some cloud VM or server? (I _really_ love those 2.50 bucks per month instances) I spent a fair amount of time reading up on it, but it's still really easy to make a mistake in your `pg_hba.conf` or somehwere else. I remember disabling password based login, and just authenticating over SSH, and my server still got compromised after a day or two. 100% my fault - I think it was related to `COPY FROM/TO` being able to run arbitrary commands because I didn't understand that `postgres` is regarded as a superuser by the database. I've been using managed services like Heroku Postgres and RDS ever since. Point is: it would be of huge value to have a clear (but complete) overview of how to configure a Postgres instance so that it's secure in the basic sense. If that isn't possible (or very hard) then please let's collectively tell beginners to just use managed instances.
- williamjackson 6y ago
  I launched the Postgres Docker image on a VPS several years ago, published the port on the open Internet, and ... used strong passwords? I still haven’t been compromised. Have I done something wrong?
  - 2mol 6y ago
    Who knows! That's my struggle with security, unless you have good monitoring in place it's not even trivial to notice problems. In my case I was only made aware of the compromised VM because my hosting provider sent me very stern email about my server's IP netscanning their entire fricking address range.
    - rfoo 6y ago
      How about try setting up a box with exactly same steps that you would do for your database instance except actually installing the database? Yes, please include all the steps that you may consider "irrelevant without a database installation", they may well be important. Then see if it got compromised.
  - 6y ago
hliyan 6y ago
The very first sentence: "By default after instalation and creting database cluster PostgreSQL will listner only on localhost." Bit worried about the quality of the content, based on the quality of the proofreading.
- sigzero 6y ago
  My guess is that English is a second language for the author.
  - marton78 6y ago
    It's unlikely that someone who can form longer sentences still struggles with the orthography of common words.
    - posix_me_less 6y ago
      Based on what? People misspell all the time.
    - 1tCKV3QfIo 6y ago
      Go be pretentious elsewhere.
- jmcqk6 6y ago
  >Bit worried about the quality of the content, based on the quality of the proofreading. I don't know why this feeling persists. The ability to proofread or write free of spellings is a skill completely unrelated to the underlying content. Complaining about the spelling just seems childish at this point. We live in a world full of mistakes and problems. Dealing with life despite of them is a fundamental life skill.
  - ramraj07 6y ago
    There's definitely a middle ground here. I have been in academic contexts where every sentence has to be absolute perfection before we submit for publication. Thats overkill, for sure, but there is merit to proofreading as a sign of legitimacy of the argument itself. Some dude literally wrote this text and didn't even re-read the post before publishing. Clearly, he's either the greatest expert at technological things that it's second nature to him even more than LANGUAGE, or he just did a shoddy job on both sides. Unless I see the name to be Linus Torvalds or something, I'm just going to assume this post is low quality in general.
avthar 6y ago
This is a good beginners' resource. For those looking for similar Postgres related resources, I've found this handy Postgres Cheat Sheet [1] to be really useful. [1] https://postgrescheatsheet.com https://postgrescheatsheet.com
devwastaken 6y ago
Is there a good way to embed postgres in applications? I've preferred sqlite, but would like to just use postgres everywhere, but requiring users to install it as a service and maintain that globally just makes it unreliable for embedding.
- tuatoru 6y ago
  No.

238 points by lukasbar 6y ago

wilsonrocks 6y ago

I get that this is for learning purposes, but doesn't this tutorial start by making your database insecure?

orev 6y ago
If you need remote access to the DB, then you need remote access. Most people in a normal setup would not have the database server ports directly exposed to the Internet, but if you have the perspective that all servers are in the cloud, then you would also be expected to know how to keep them secure. But making it listen on an external IP does not inherently make it insecure, as long as you also have other firewall and user account controls.
- MaxBarraclough 6y ago
  I don't know much about Postgres specifically, but what I'm seeing in the tutorial does seem insecure. > If you need remote access to the DB, then you need remote access That's not a defence for teaching insecure practices. It's possible to configure Postgres for secure remote access. Using md5 for password security seems like a red flag, and sure enough, the Postgres docs remind us that md5 should no longer be considered secure. [0] I don't think there's any verification of the server's fingerprint, either. There's really no excuse here, as there's a quick and easy way to do it: SSH tunnels. [1] > Most people in a normal setup would not have the database server ports directly exposed to the Internet Not a safe assumption. Instances on Linode and, iirc, Digital Ocean, are not behind a cloud firewall, all ports are wide open to the Internet. (I think that's a bad move on the part of Linode and Digital Ocean, but that's not the point.) > if you have the perspective that all servers are in the cloud, then you would also be expected to know how to keep them secure If you already knew how to securely configure Postgres, you wouldn't need the tutorial. > making it listen on an external IP does not inherently make it insecure, as long as you also have other firewall and user account controls You still need to configure the crypto properly. [0] https://www.postgresql.org/docs/current/auth-password.html https://www.postgresql.org/docs/current/auth-password.html [1] https://www.postgresql.org/docs/current/ssh-tunnels.html https://www.postgresql.org/docs/current/ssh-tunnels.html

smashah 6y ago

Related side note/question. I was on a project recently where postgres was used with a NodeJS JavaScript server app. It seems a bit backwards. Like you have the flexibility of a loosely typed server app but all the annoyance of a RDBMS. It just made development a nightmare. The 'strictness' if the system was just backwards. Surely NoSQL + typescript is better for development (and potentially for performance). Thoughts?

maxmunzel 6y ago
I’d be interested, which use case of yours would be restricted by Postgres‘ strict typing. All my experiences were wonderful, especially its performance and powerful features like rule rewriting. Also PostgREST is brilliant.
- how_gauche 6y ago
  I've done this before (postgREST) for internal apps and it's amazing. You can POST a JSON file to the front end from a shell script with curl and have it show up on a Grafana dashboard immediately. I've used this in the past for setting up automated quality tests for machine learning pipelines -- the pipeline runs automatically and you get the results in a central place, so you can see how your quality metrics are trending over time. Given that postgREST exists, I don't see any reason to talk to postgres directly from a JS app.
Sebb767 6y ago
As someone who works on a business app using CouchDB+Typescript, I can tell you that (a) you will also hit the edge cases of NoSQL, like transactions and joins, and it will not be that awesome either and (b) at some point you'll realize that you'll need to do schema validation still. Frontend bugs, possibly malicious clients and protected attributes (i.e. 'disabled' should only be set by an admin) will force you to write a lot of validation anyway. It definitely allows for faster iteration, though. You can deploy a prototype or change very quickly without caring about this, which is pretty nice. But you need to keep track of that and pay the debt if you use the prototype. Overall, I think it would not give or take much if we'd be using PostgreSQL+some JSON column for general data instead of CouchDB. You just need to know your stack and its drawbacks and work with them.

autotune 6y ago

Can anyone recommend an “advanced” PG guide? I’m looking to learn all the ins and outs of Postgres.

throwaway8941 6y ago
http://www.interdb.jp/pg/ http://www.interdb.jp/pg/ https://www.postgresql.org/docs/current/internals.html https://www.postgresql.org/docs/current/internals.html
golergka 6y ago
The official documentation is pretty damn good. Most of the time when I needed to learn something about Postgres, it had all the answers I needed, and in rare occasions it didn't, I used Stack Overflow to figure out what other part of the manual I needed to read.
akerro 6y ago
Checks posts of this author https://habr.com/en/users/erogov/posts/ https://habr.com/en/users/erogov/posts/ he doesn't write often, but it's advanced technical topic, I find it super useful and well explained.

2mol 6y ago

So this is cool, and can serve as a good cheatsheet for beginners. However, the question where I struggled the most: how the hell do I set up a secure Postgres instance on some cloud VM or server? (I _really_ love those 2.50 bucks per month instances) I spent a fair amount of time reading up on it, but it's still really easy to make a mistake in your `pg_hba.conf` or somehwere else. I remember disabling password based login, and just authenticating over SSH, and my server still got compromised after a day or two. 100% my fault - I think it was related to `COPY FROM/TO` being able to run arbitrary commands because I didn't understand that `postgres` is regarded as a superuser by the database. I've been using managed services like Heroku Postgres and RDS ever since. Point is: it would be of huge value to have a clear (but complete) overview of how to configure a Postgres instance so that it's secure in the basic sense. If that isn't possible (or very hard) then please let's collectively tell beginners to just use managed instances.

williamjackson 6y ago
I launched the Postgres Docker image on a VPS several years ago, published the port on the open Internet, and ... used strong passwords? I still haven’t been compromised. Have I done something wrong?
- 2mol 6y ago
  Who knows! That's my struggle with security, unless you have good monitoring in place it's not even trivial to notice problems. In my case I was only made aware of the compromised VM because my hosting provider sent me very stern email about my server's IP netscanning their entire fricking address range.
  - rfoo 6y ago
    How about try setting up a box with exactly same steps that you would do for your database instance except actually installing the database? Yes, please include all the steps that you may consider "irrelevant without a database installation", they may well be important. Then see if it got compromised.
- 6y ago

hliyan 6y ago

The very first sentence: "By default after instalation and creting database cluster PostgreSQL will listner only on localhost." Bit worried about the quality of the content, based on the quality of the proofreading.

sigzero 6y ago
My guess is that English is a second language for the author.
- marton78 6y ago
  It's unlikely that someone who can form longer sentences still struggles with the orthography of common words.
  - posix_me_less 6y ago
    Based on what? People misspell all the time.
  - 1tCKV3QfIo 6y ago
    Go be pretentious elsewhere.
jmcqk6 6y ago
>Bit worried about the quality of the content, based on the quality of the proofreading. I don't know why this feeling persists. The ability to proofread or write free of spellings is a skill completely unrelated to the underlying content. Complaining about the spelling just seems childish at this point. We live in a world full of mistakes and problems. Dealing with life despite of them is a fundamental life skill.
- ramraj07 6y ago
  There's definitely a middle ground here. I have been in academic contexts where every sentence has to be absolute perfection before we submit for publication. Thats overkill, for sure, but there is merit to proofreading as a sign of legitimacy of the argument itself. Some dude literally wrote this text and didn't even re-read the post before publishing. Clearly, he's either the greatest expert at technological things that it's second nature to him even more than LANGUAGE, or he just did a shoddy job on both sides. Unless I see the name to be Linus Torvalds or something, I'm just going to assume this post is low quality in general.

avthar 6y ago

This is a good beginners' resource. For those looking for similar Postgres related resources, I've found this handy Postgres Cheat Sheet [1] to be really useful. [1] https://postgrescheatsheet.com https://postgrescheatsheet.com

devwastaken 6y ago

Is there a good way to embed postgres in applications? I've preferred sqlite, but would like to just use postgres everywhere, but requiring users to install it as a service and maintain that globally just makes it unreliable for embedding.

tuatoru 6y ago
No.