Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

Question

We have a program that transfers cobol data (index files) to a database.

Until now we transferred the data to an oracle database.

In the beginning of the program I logged in with user sys and then I altered the current schema to ink. I did this in this way, because ink has no privileges to create a table.

Then I created a table and the table owner was automatically ink, not sys.

We recently have switched databases from Oracle to Postgres.

Here I log in as "postgres", then I switch to the "ink" schema with:

set search_path to 'ink';

Then I create a table and the table owner automatically is postgres, not ink. So this behavior is reverse.

What do I have to do in order to get the same behaviour as with the oracle database:

In my scenario the table owner should automatically be ink, not postgres.

It would be nice if I would not have to write

ALTER TABLE ... OWNER TO ink;

after each create table command.

Answer 1

In Oracle a schema and a user is the same thing. In Postgres it isn't.

So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that.

If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that user all privileges on the tables. Unlike in Oracle you can establish default privileges for objects created in a schema. You don't need to remember to grant them after creating each table.

create schema ink;
grant usage on schema ink to ink;
alter default privileges 
   in schema ink
   grant all privileges on tables to ink;

Every table created after that will automatically have the defined privileges, that is: ink can do anything with them except drop them, because that user is not the owner. That is different to the Oracle solution.

Answer 2

Be warned: I'm relatively new to PostgreSQL and so I'm not sure if this is a good practice or not, but...

One way I can think this can be addressed is by executing a REASSIGN OWNED statement at the end of the process that has created all your objects in the database, like this:

REASSIGN OWNED BY CURRENT_USER TO ink;

This way, even if the tables are momentarily owned by the postgres user, you'll see them owned by ink once the creation process ends.

Answer 3

Similar to Paulo's and the accepted response, but better (in my opinion):

Have a master role to be the owner of the tables. Also set other users to automatically assume that role on login, so the owner is always the same.

create role foo;
create role foo_a;
create role foo_b;
grant foo to foo_a;
grant foo to foo_b;

-- override default role
alter role foo_a set role foo;
alter role foo_b set role foo;

Whenever foo_a and foo_b login, their default role is set to foo. All objects they create will then be owned by foo, so we will not have to go back and modify permissions or change ownership later.

Reference: http://davidhollenberger.com/2017/03/16/postgres-credential-rotation/

Answer 4

Give ink the right to create tables in the database and connect yourself as ink to the database. Then all the tables that you create there are owned by ink.

PostgreSQL has a different approach on this then Oracle. The schema and owner are 2 different 'things'.