0

If I am creating a new Database on the same hostname/server, Will the roles/users carry over? Or will I have to create them again with new names and passwords?

I see roles/users listed as server objects, rather than database specific.enter image description here

So I'm not sure if they will carry over to a new DB. Both DBs share the same server/host.

Follow up Question: If I already have roles created across the server. And they currently have read_only access on certain schemas in Database A.

If I want to grant them access to create, insert, update etc permissions on schemas in DataBase B. I would just need to say: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema1 in Database B TO role1?

This wouldn't affect their privileges in Database A, correct? Is it considered good practice for role1 to have readonly access in Database A and and create access in Database B?

Improve this question
2
  • When you say "server", do you mean machine/host, or do you mean database server/database instance? If you create a new database "cluster" on the same machine, they do not carry over. If you create a new database on the same cluster, they do carry over.
    – jjanes
    Commented Jul 18, 2022 at 23:54
  • To clarify, When I said host, I meant hostname. I have a host called: db-host-1.abc.io. This also correlates to my RDS instance. Both Database A and B connect to this host.
    – user16573033
    Commented Jul 19, 2022 at 0:22

1 Answer 1

Reset to default
1

Quote from the manual

Database roles are global across a database cluster installation (and not per individual database). To create a role use the CREATE ROLE SQL command:

So, yes. Roles "carry over to a new database".

However, privileges are stored per object (table, view, sequence, database, ...). So the privileges for a table named person in the database one for a user arthur might be completely different than the privileges for that user for a table with the same name in a database named two

Improve this answer
1
  • So If I already have roles created across the server. And they currently have read_only access on certain schemas in Database A. and I want to grant them access to create, insert, update etc permissions on schemas in DataBase B. I would just need to say: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema1 in Database B TO role1? This wouldn't affect their privileges in Database A, correct? Is it considered good practice for role1 to have readonly access in Database A and and create access in Database B?
    – user16573033
    Commented Jul 18, 2022 at 17:43

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.