Question

How do I configure a read-only user for PostgreSQL in Power BI to maintain security?

I need to connect Power BI to a PostgreSQL database while ensuring that users have read-only access to maintain security. What is the best way to configure a read-only user in PostgreSQL for Power BI? What permissions and settings should be applied to prevent unauthorized modifications while allowing data access for reporting?

Answer 1

To configure a read-only user for PostgreSQL in Power BI securely:

Create a Read-Only User

CREATE ROLE powerbi_readonly WITH LOGIN PASSWORD 'strongpassword';

Grant Read-Only Access

GRANT CONNECT ON DATABASE your_database TO powerbi_readonly;
GRANT USAGE ON SCHEMA public TO powerbi_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powerbi_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powerbi_readonly;

Revoke Write Permissions

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM powerbi_read

Connect Power BI

• Use Get Data → PostgreSQL Database in Power BI.
• Enter powerbi_readonly credentials.

Enhance Security (Optional)

• Restrict system table access:

REVOKE ALL ON pg_stat_activity FROM powerbi_readonly;

Enable SSL encryption for secure connections.