AD integration on PostgreSQL flexible server is creating a role with no Bypass RLS privileges

Question

I have enabled Entra auth on Postgresql flexible server 16.x .

Added AD group "NonProd_PostgreSQL_Admins" as admin however the role that gets created automatically is missing the Bypass RLS privileges as you can see below. Is this a known issue ? How can it be fixed ?

postgres=> \du

Role name           |                         Attributes                         

------------------------------+------------------------------------------------------------

 NonProd_PostgreSQL_Admins | Create role, Create DB

 azure_pg_admin               | Cannot login

 azuresu                      | Superuser, Create role, Create DB, Replication, Bypass RLS

 admin                      | Create role, Create DB, Bypass RLS

Answer 1

Hi Subbu,

Welcome to Microsoft Q&A forum.

As I understand, you have enabled Entra auth on Postgresql flexible server 16.x .

Added AD group "NonProd_PostgreSQL_Admins" as admin however the role that gets created automatically is missing the Bypass RLS privileges.

PostgreSQL has BYPASSRLS and NOBYPASSRLS permissions, which can be assigned to a role; NOBYPASSRLS is assigned by default. With newly provisioned servers in Azure Database for PostgreSQL - Flexible Server bypassing row level security privilege (BYPASSRLS) is implemented as follows:

• For Postgres 16 and above versioned servers we follow standard PostgreSQL 16 behavior. Nonadministrative users created by azure_pg_admin administrator role allows you to create roles with BYPASSRLS attribute\privilege as necessary.
• For Postgres 15 and below versioned servers. , you can use azure_pg_admin user to do administrative tasks that require BYPASSRLS privilege, but can't create nonadmin users with BypassRLS privilege, since administrator role has no superuser privileges, as common in cloud based PaaS PostgreSQL services.

Let us know if this helps or you have a different ask.

Thanks

Answer 2

Hi Subbu,

Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with [ALTER TABLE ... FORCE ROW LEVEL SECURITY](https://www.postgresql.org/docs/current/sql-altertable.html"ALTER TABLE").

PostgreSQL roles have an attribute that can be set, called BYPASSRLS. Roles with this attribute set will not be bound by any RLS policy on any table.

Let’s make charlie a BYPASSRLS user:

corp=# ALTER ROLE charlie BYPASSRLS;
ALTER ROLE

Using this superpower, he can now update the invoices table even though the RLS policies on the table forbid him from doing so:

corp=> select current_user;
 current_user
--------------
 charlie
(1 row)

corp=> begin;
BEGIN
corp=*> update invoices set finalized=true where id=202347;
UPDATE 1
corp=*> rollback;
ROLLBACK

Typically this attribute should be set only for database administration users, as all queries run by such users totally bypass any and all RLS policies.

Thanks