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
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
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
It would be nice if I would not have to write
ALTER TABLE ... OWNER TO ink;
after each create table command.
Here is Solutions:
We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.
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:
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.
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.
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
PostgreSQL has a different approach on this then Oracle. The schema and owner are 2 different ‘things’.
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂