Hello,
Section 5.7. on Row Security Policies (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for 9.5 says:
[...]
CREATE POLICY user_policy ON users
USING (user = current_user);
---
I’m trying understand the example as it references both an `accounts` table and a `users` table which isn’t defined. Is this a mishmash of example fragments or should the CREATE POLICY statement reference the `accounts` table instead of `users`? Specifically, what does `user` reference in the statement "CREATE POLICY user_policy ON users USING (user = current_user);”?
Is this a table column in a `users` table the example doesn’t define or does PostgreSQL keep track of what user/role inserted a row and allow policies to use it?
It assumes the user can envision a trivial "users" table having at least a column named "user" that represents the user's name/id and which the names of said users are identical to those assigned to them in the PostgreSQL database and accessible via the "pg_authid" catalog (rolname) and its related views: namely "pg_user" (usename).
So, in effect the following works, and returns a single row.
SELECT *
FROM users
JOIN pg_user ON (user = usename)
WHERE user = current_user;
David J.
