PostgreSQL privilege, setting or feature to limit number of rows a user can select

Question

Using PostgreSQL, is it somehow possible to restrict the SELECT privilege of a certain user so that he can only select a certain limited number of rows from a certain table?

For example, user joe should be allowed to execute

SELECT * FROM my_table WHERE id = 17;

but at the same time should not be allowed to execute (because it matches more than 1 row)

SELECT * FROM my_table;

To clarify, id = 17 is of course just an example here. I'm actually looking for a way to allow joe to execute SELECT ... FROM my_table WHERE ... only if the query returns at most one row. I obviously can't simply use GRANT SELECT ON my_table TO joe to solve this, but is it still somehow possible?

Use functions to access tables and give the user only access to this functions and revoke all direct access to the tables. — Frank Heikens
– Frank Heikens, Commented Dec 30, 2024 at 13:55
I actually want to have count(*) <= 1 as constraint. Please provide enough info, because it is unclear how you would create this constraint. — Luuk
– Luuk, Commented Dec 31, 2024 at 13:24
@Luuk I doubt it's about an actual, literal SQL constraint - OP seems to be looking for a way to constrain/restrict a user, imposing a limit of how many rows they are allowed to get at any time from a given table. Whether that's going to be handled by RLS, the privilege system, a routine, a rule, a view or a configuration setting, is less important, since it's not immediately obvious how to do that with any of these. Except by revoking all access and forcing the user to interface with everything through functions that strap on a limit clause everywhere. — Zegarek
– Zegarek, Commented Dec 31, 2024 at 13:38

Chandrasekar B · Accepted Answer · 2025-01-06 06:17:45Z

0

In PostgreSQL, you can't directly enforce a limit on the number of rows a user can retrieve with a SELECT statement at the database permission level. However, you can achieve the desired behavior by using Row-Level Security (RLS) or by creating a security-definer function that restricts access.

answered Jan 6 at 6:17

Chandrasekar B

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL privilege, setting or feature to limit number of rows a user can select

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related