Row security policies
You can apply row security policies to a table. These policies determine, on a per-user basis, which rows can be processed. You can create a row security policy for specific commands like SELECT, INSERT, UPDATE, and DELETE, or specify it for ALL commands. This feature is also known as row-level security.
Connect to the database as the postgres user and create a table for examples. You can use psql for this:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresIn the code above, 10.92.6.36 is the server address, 5432 is the port for connections, -U postgres is a user name, and postgres is the database name.
CREATE TABLE users (
user_id serial PRIMARY KEY,
user_name VARCHAR NOT NULL,
pwhash VARCHAR,
real_name VARCHAR NOT NULL
);Insert data to the table:
INSERT INTO users (user_name, pwhash, real_name)
VALUES
('james', 'xxx', 'James Brown'),
('mary', 'xxx', 'Mary Smith'),
('alice', 'xxx', 'Alice Gray');CREATE ROLE james WITH LOGIN PASSWORD 'password1';
GRANT ALL ON users TO james;
CREATE ROLE mary WITH LOGIN PASSWORD 'password2';
GRANT ALL ON users TO mary;Now users james and mary have ALL privileges on the users table.
Enable row security
To enable row security, execute the ALTER TABLE command with the ENABLE ROW LEVEL SECURITY parameter.
ALTER TABLE users ENABLE ROW LEVEL SECURITY;When row security is enabled on a table, access to the table for selecting and modifying rows must be allowed by a row security policy. If no policy is defined for a table, the default deny policy is applied: no rows are visible or can be modified. Operations that are applied to the whole table, for example, TRUNCATE, REFERENCES, are not subject to row security.
Superusers and roles with the BYPASSRLS attribute always bypass the row security system. Table owners are also not affected by row security, but they can enable it for themselves with the ALTER TABLE … FORCE ROW LEVEL SECURITY command.
Reconnect to the database as the user james:
$ psql -h 10.92.6.36 -p 5432 -U james postgresIf you try to select rows from the users table, you will get the following result:
SELECT * FROM users; user_id | user_name | pwhash | real_name ---------+-----------+--------+----------- (0 rows)
The user james cannot access table data because row security is enabled and no row security policy is created.
|
NOTE
Only a table owner can enable and disable row security and add policies to a table.
|
Manage row security policies
Row security policies can be specific to commands, or to roles, or both. A policy can be specified to apply to ALL commands, or to SELECT, INSERT, UPDATE, or DELETE. Multiple roles can be assigned to a current policy. See CREATE POLICY for details.
PostgreSQL also applies standard role membership and inheritance rules to row security policies.
Each policy has a name. Multiple policies can be defined for a single table. Policies are table-specific, each policy for a table must have a unique name. Different tables can have policies with the same name.
Reconnect to the database as the postgres user and define the following row security policy:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresCREATE POLICY policy1 ON users FOR ALL TO PUBLIC USING (user_name=current_user);This policy allows all roles to execute all commands if the particular value from the user_name column of the users table is equal to the current user name.
PUBLIC is used instead of a role name and applies the policy to all roles.
The USING expression is any SQL conditional expression returning a boolean value.
According to this policy, the users james and mary created above can manipulate rows that contain information about each of them.
Reconnect to the database as james and select data:
$ psql -h 10.92.6.36 -p 5432 -U james postgres SELECT * FROM users;The result:
user_id | user_name | pwhash | real_name
---------+-----------+--------+-------------
1 | james | xxx | James Brown
Reconnect to the database as mary and select data:
$ psql -h 10.92.6.36 -p 5432 -U mary postgres SELECT * FROM users;The result:
user_id | user_name | pwhash | real_name
--------+-----------+--------+-----------
2 | mary | xxx | Mary Smith
Reconnect to the database as the postgres user, create a new user alice and add a new policy:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresCREATE ROLE alice WITH LOGIN PASSWORD 'password3';
GRANT ALL ON users TO alice;
CREATE POLICY policy2 ON users TO alice USING (true) WITH CHECK (true);The WITH CHECK expression should return true to allow a user to insert and update rows.
This policy allows alice all operations on all rows from the table.
Reconnect to the database as alice and check the result:
$ psql -h 10.92.6.36 -p 5432 -U alice postgresDELETE FROM users WHERE user_id=1;
SELECT * FROM users;The result:
user_id | user_name | pwhash | real_name
---------+-----------+--------+-------------
2 | mary | xxx | Mary Smith
3 | alice | xxx | Alice Gray
You can also use the ALTER POLICY command to update a policy. The following code renames policy1 to policy_for_users:
ALTER POLICY policy1 ON users RENAME TO policy_for_users;The DROP POLICY command deletes a policy:
DROP POLICY policy_for_users ON users;