Postgresql – Restrict access to the PostgresSQL/ Post GIS database used through QGIS

This is one of those "It depends" questions.

Performance depends on resources, contention, configuration, and the VM engine

Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll generally perform very well on a properly tuned VM engine. Exact results will depend on the VM system used, on how you provide access to resources, and lots more.

You can get great results on high end VPS plans with guaranteed low contention ratios and good storage.

Contended/under-resourced VM host: If you put it on the same box as three other application servers and a file server, all of which share the same RAID 5 array and are fighting over RAM and CPU, it'll perform terribly.

If you put it on a cheap over-subscribed and overcomitted VPS host somewhere you'll get similarly poor results. If half your RAM is really swap on the host's disk, nothing is going to be fast.

"In the cloud": If you put it on EC2, Azure, or whatever, then performance will depend on the contention ratios for the service, the storage they're using, what other users are doing, how good their QoS is, and lots more.

At least for EC2 the disk subsystem has horrible performance (on standard VMs, at least in 2012) so it only performs OK if you have enough RAM to cache at least your indexes. Amazon has introduced new high I/O instances that might be better but I haven't seen benchmarks yet.

Usually, you'll get something in-between if you choose lightly contended hosting with decent disks, like high quality higher-end virtual private server hosts.

Direct vs VM guarantees

Re specific guarantees about things like file ordering, that depends on your VM setup. Are you using VMs backed by files? By raw block devices? By an iSCSI SAN? It also depends on how your VM engine is configured, and exactly which VM system you're using.

Best case

In the best case - a system with all-paravirt drivers, VT-x, VT-IO, uncontended access to host resources, etc - you'll probably get performance pretty close to the host. If you give the VM direct block devices not host files for storage then you'll get proper file ordering without host-side fragmentation. Exactly how close will depend on your particular hardware, host and guest, configuration, and more; benchmark it with your workload.

fsync() and write durability

One thing to watch out for with VMs is that you must make sure the disk system tells the truth about fsync(). A very easy way to make VMs a lot faster is to ignore fsync() requests. That's fine until the VM host crashes or loses power, at which point your databases are likely to be hopelessly corrupted. The VM host must either honour fsync() requests by respecting the guest OS's disk flush commands, or must offer non-volatile write cache that won't go away if power is lost. Some SANs use SSDs for that, most other systems use battery backed RAID controller cache memory. If your VM can process more than a few hundred transactions per second it's likely to either be ignoring fsync or on write-caching storage, and you should find out which before it eats your data.

But why?

(Updated): As noted by Chris Travers, why should you virtualise DB servers? Why not handle replication, heartbeat and failover at the DB server level, migrate via promotion of replicas, and get the full performance of the bare metal?

I wrote this original response with the mindset that a VM was a given, and the question was how to get the best results. The best virtualisation for a DB server is still, in my mind, no virtualisation. That said, I've only managed fairly small sites.

1. The documentation should cover that pretty well. See in particular pg_hba.conf. It's hard to make "simple" guides on security, because if you simplify in the wrong way you make it wrong.

2. Trivially so long as you have the ability to edit pg_hba.conf and reload the server. Just add an pg_hba.conf entry with trust authentication, reload PostgreSQL (pg_ctl reload, /etc/init.d/postgresql reload, whatever), change the password, and put pg_hba.conf back how it was.

3. Disabling hostbased and password login security in PostgreSQL can be done by setting trust authentication in pg_hba.conf. It's usually much saner to use peer authentication for local connections instead, but this only works on unix systems.

4. Users are just roles with the LOGIN attribute. Roles are global across the server. Most GRANTs of rights to users/roles are specific to particular databases or objects within databases. See the documentation for the GRANT command.

5. "trick?". I think you'll want to post a new, more detailed and specific question on https://dba.stackexchange.com/ with the details, including exact commands run, exact error messages, etc.

I'm kind of surprised by your expectation that dropping then re-creating a user would retain their settings. This would create the opportunity for colossal security holes. The sysadmin joe leaves, you drop their account. New data entry joe starts, you create their account. Oops! The new Joe has all the rights of the completely different old Joe.

If you drop a table then re-create it, does it still have its contents? Why would users/roles be any different?

If you don't want to drop a user account, don't drop the user account. Disable it by REVOKEing CONNECT rights on the database(s), changing their password, etc.