PostgreSQL – Query Resource Quota for Accounts

Sounds like no fuss to me:

• Create an OS account for each user, preferably with simple ssh key based authentication;

• in pg_hba.conf use peer for local connections;

• Optionally also use ident for host connections from 127.0.0.1 and run identd on the server. Not strictly required.

• Set a password on their role for use with md5 authentication, then use md5 authentication for connections from allowed remote hosts with the hostssl method.

That way they'll be able to connect over SSL directly with a password, and locally (after ssh'ing in) with no password prompt.

What you describe:

My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running.

sounds a bit like remote ident. This exists and works, but it's completely and utterly insecure. It was written for the days when "my personal computer" would elicit gales of laughter and nobody but nobody had root. These days anyone can set up a laptop (or phone) to claim to be any user they want over ident, so it's completely useless except on the local loopback where you control it.

It does sound like Kerberos is closer to what you want, but honestly it's going to be way more messing around than seems worthwhile. I'd just use peer for local connections and md5 for remote ones.

You can "dive" into the system tables and get all the tables that reference a specific table (and all those that reference them, etc). It will be a recursive query, not very difficult to write.

However, for the simple operation you want to to perform, there is a simple solution:
TRUNCATE with the magic option CASCADE:

truncate table a cascade ;

Tested at rextester.com