Re: How to handle application connectivity change when moving from Oracle to Postgres in GCP

Somehow off-topic here, but replying inline anyway.

Regards
Dimitre

Il dom 10 set 2023, 19:44 kunwar singh <krishsingh.111_at_gmail.com> ha scritto:

> Hi Listers,
>
> Say we have this App->DB connectivity Model.
>
> APP1
> Contains synonyms which point to objects under APP1.
>
> APPOWN
> Contains the tables, indexes
>
> And it is a Java application that connects to APP1.
>
> Now when the database is switched from Oracle 19c onprem to CloudSQL
> Postgres say v15.
> Questions that I have:
>
> 1. What needs to be done at the database end in terms of schema setup in
> order to ensure the application is able to connect to the relevant tables
> without any concerns?
>

You could follow the same approach:
create the app1 and appown users, create one or more schemata, owned by the appown user, assign whatever privileges you need to the app1 user and create the synonyms with the app1 user.
After that you can use the app1 user to connect to the database instance.

2. Do I need to create users APP1 and APPOWN in postgres? If so, what
> privileges are required to be assigned to these?
>

See above. The privileges are standard, bear in mind that if you create the users using the GCP tools (gcloud/web interface) they will have some privileges by default, they will be part of the cloudsqlsuperuser role so they will have the createrole, the createdb and the login roles (see https://cloud.google.com/sql/docs/postgres/users#default-users for more info).

3. How to tie them with service accounts?
>

The service accounts implements the communication between the GCP resources, for example you'll need to grant the service accounts used by your compute instances, the virtual machines that hosts your application, the privileges, needed to connect to your could sql instances (see https://cloud.google.com/sql/docs/postgres/connect-instance-compute-engine#configure_a_service_account for more info).

> 4. With which username/service account does the application make
> connection to the database?
>
> I still don't understand user/schema concepts in postgres tbh as I'm
> still a noob :)
>
>

libt.tld_type is 1, lib_t.tld_source_file is '/usr/local/lib/aarch64-linux-gnu/perl/5.30.0/auto/share/dist/Mail-DMARC-opendmarc/effective_tld_names.dat'

--
http://www.freelists.org/webpage/oracle-l