3

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.

3

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.

1

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.

  • 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 Aug 6 '18 at 19:01
0

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, privacy policy and cookie policy

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