How to create a user with readonly privileges for all databases in Postgresql?

security postgresql permissions database-permissions
Alptugay picture Alptugay · Jun 23, 2011 · Viewed 37.5k times · Source

I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:

GRANT select ON DATABASE dbname to user1;

But I got the following error:

ERROR:  invalid privilege type SELECT for database

When I googled people advised to do the grant select operation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Jun 23, 2011

You cannot do this on database level, only on schema level.

Assuming you are only using the public schema in each database, you can do this:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;

Related questions

How can I hash passwords in postgresql?

I need to hash some passwords with salt on postgresql, and I haven't been able to find any relevant documentation on how to get that done. So how can I hash passwords (with some salts) in postgresql?

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
SecurityError: Blocked a frame with origin from accessing a cross-origin frame

I am loading an <iframe> in my HTML page and trying to access the elements within it using Javascript, but when I try to execute my code, I get the following error: SecurityError: Blocked a frame with origin "…

Read More