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


after each create table command.

4 Answers 4


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.


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:


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.

  • It also has the advantage that takes care of updating ownership of other objects besides just tables (e.g. sequences that arise from SERIAL columns).
    – Paulo
    Commented Aug 6, 2018 at 19:01

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/


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'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.