I am searching for a solution how to create a postgres role, which can connect to all databases(also those being created after the role).
Thats normally something a superuser can do, but in my case, this user should have read-only access only to specific tables.
Is this possible?
Or do I have to update the role everytime I add a new database? Eventhough the table names ar always the same.
thanks
PUBLIC can connect to PostgreSQL databases by default.
PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages.
If you're using pgAdminIII, and you select a database in the object browser, you'll see this in the SQL pane.
GRANT CONNECT, TEMPORARY ON DATABASE sandbox TO public;
You can change default privileges on some kinds of database objects using alter default privileges. This doesn't affect existing database objects. Grant privileges on existing objects the usual way.
alter default privileges is a "per database" statement; you can't alter default privileges for databases that haven't yet been created.
You can grant privileges for tables that exist. You can alter default privileges for all tables that don't yet exist. But you can't alter default privileges for tables having specific names that don't yet exist.
You might be able to set up suitable privileges and defaults on objects in the template database. Then every database you create will include those objects, privileges, and defaults.