PostgreSQL - Readonly "SuperUser"

Question

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

Mike Sherrill 'Cat Recall' · Accepted Answer · 2016-01-03 15:26:32Z

3

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.

answered Jan 3, 2016 at 15:26

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

2 Comments

Vamiris Over a year ago

Thank you, I understand now what I wanted. But ok, this way I have to update the user everytime a new DB is created.

Mike Sherrill 'Cat Recall' Over a year ago

Depends on who the user is, and on what you can put in the template database.

Collectives™ on Stack Overflow

PostgreSQL - Readonly "SuperUser"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related