Total security in a PostgreSQL database

Re:Total security in a PostgreSQL database by yummyfajitas on Sunday February 09, 2014 @02:17AM (#7204728)

Read my next paragraph. A split of public and private data is only marginally more difficult, and is very helpful:

    CREATE TABLE public_user_content (
      body VARCHAR(512) NOT NULL,
      user_id BIGINT REFERENCES users(id)
    )

    CREATE TABLE users (
      id BIGINT PRIMARY KEY,
      username VARCHAR(128) NOT NULL,
      email VARCHAR(256) NOT NULL,
      badly_hashed_password VARCHAR(8),
      social_security_number VARCHAR(9)
    )

    GRANT SELECT ON public_user_content TO public_readers;
    GRANT SELECT ON users (username) TO public_readers;

Then in your view:

    @run_with_role('public_readers')
    def get_public_feed(request):
        ....

SQL injection in `get_public_feed` is no longer part of the attack surface for stealing PII.

[ Parent | on HN ]

Re:Total security in a PostgreSQL database by wglb on Monday February 10, 2014 @09:18AM (#7211161)

This is a good idea.

However, in the context of forms authentication, there comes a delicate moment where the user name and hashed password must be exposed to an unauthenticated user, no?

[ Parent | on HN ]

Re:Total security in a PostgreSQL database by yummyfajitas on Monday February 10, 2014 @02:01PM (#7212926)

Of course. And once one user logs in, if you have SQLi vulnerabilities in the `edit_user_pii` page, the attacker has read/write access to everything. Like I said, it's a way to reduce your attack surface, not a way to completely lock everything down.

[ Parent | on HN ]