How does one connect to a schema within a PDB (other than schema SYS)?

Using Oracle 18c on RedHat Linux 8

Connecting to CDB as ‘SYS’, I cannot figure out how to connect to a schema within the PDB other than as ‘SYS’. Maybe I don’t understand the CDB/PDB architecture well enough.

Here is what I have tried after logging into the Oracle 18c CDB as ‘SYS’ (using sqlplus sys as sysdba):

ALTER SESSION SET CONTAINER= PDB; conn myschema/mypasswd 

and I also tried (right from the CDB)

conn myschema/mypasswd@PDB; 

Both approaches fail to get me connected to the schema myschema in the PDB. That is, I am still ‘SYS’ in the PDB.

Are there any suggestions/explanations on how to resolve this? Do I have to setup some kind of access from the CDB-to-PDB for the schema myschema?

Can’t create schema from `assumed` role with ALL privileges

For the postgresql 10 have this code:

CREATE DATABASE featurestore WITH OWNER editor; CREATE ROLE fstore_migrations WITH NOLOGIN NOCREATEROLE; CREATE ROLE editor WITH LOGIN PASSWORD XXXX NOINHERIT NOCREATEROLE; GRANT fstore_migrations TO editor; GRANT ALL ON DATABASE featurestore TO fstore_migrations; 

AND then log in as editor, and

SET ROLE FEATURESTORE_MIGRATIONS; SELECT current_database()               # returns featurestore SELECT SESSION_USER, CURRENT_USER;      # returns editor, fstore_migrations CREATE SCHEMA IF NOT EXISTS BUILDINGS   # (psycopg2.errors.InsufficientPrivilege) permission denied for database featurestore 

What did I miss? Is there any way to see permissions on database (rather than specific tables/schemas ?

How to completely restrict a postgres role to a single schema

I’d like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public.

My initial idea was to create a new schema ‘readonly’ and then create views in that schema:

create view readonly.table1 as select * from public.table1 

Then alter the search path for the readonly user to limit it only to the ‘readonly’ schema. However, it looks like I can still view the public schema as that role (though I can’t select anything from any tables in schema public).

Is there a way to remove all visibility into the public schema from a role? Unfortunately, moving everything to a different schema is not possible.

Schema design for store billing with multiple payment providers

What I’m doing

I’m writing a Telegram bot using TelegrafJS + MongoDB, this bot allow the user to subscribe to a private channel using three types of billing:

  • PayPal
  • Stripe
  • Manual (when the user doesn’t have any credit card, I generate manually an access code for the private channel, when the user insert the access code, a subscription is created).

The main problem here is the customer reference, infact when I generate a subscription using PayPal or Stripe the customer_id is different ’cause they are different payment providers, so I have the following situation:

P. Provider | Customer Id | Subscription Id    PayPal        1ac            a    Stripe        2ac            b 

actually a I created the following model for store the customer and the subscriptions:

Customer Model

let CustomerSchema = new Schema({     telegram_id: Number,     email: {         type: String     },     hasTrial: {         type: Boolean,         default: true     },     subscriptions: [SubscriptionSchema],     created_at: {         type: Date,         default: Date.now,         required: true     } }); 

as you can see there is the field telegram_id, this is valorized using the telegram.chat.id of the user, and allow me to manage the user in the private channel (resource that the user pays to access).

There is also the hasTrial field, which allow me to create a manual subscription just one time, this subscription isn’t linked to any payment providers but is stored in the same way of others subscriptions:

Subscription Model

let SubscriptionSchema = new Schema({     _id: String,     period_start: {         type: Date,         required: true     },     period_end: {         type: Date,         required: true     },     status: {         type: String,         required: true     },     plan_id: {         type: String,         required: true     } }); 

the main question is: how can I keep track of subscription a and b which are part of different payment providers (so different customer_id)?

What I thought

I thought to create two additional fields in the Customer Model as:

stripe_id: String paypal_id: String 

but I honestly don’t like this solution. I need to store someway the the customer id of both payment providers ’cause the subscription status is changed via the webhooks, eg:

  1. Stripe send subscription.cancelled hook to b subscription which is linked to 2ac customer
  2. My application set the subscription status as canceled and also kick out from the channel the user which have as telegram_id: 5

with the current database design I can’t know which user the subscription is linked to..

Could you suggest me something?

What is the schema vocabulary for industry dealing in online resume and cover letter builder?

I’d like to know if a company dealing in online resume and cover letter service, and have pages like resume templates etc then which schema type is best suitable for the page.

The following pages are:

  • Resume Templates
  • Resume Samples
  • Cover Letter Examples
  • Online resume builder

I would appreciate if anyone have experience in working with career industry and can answer this with an example of schema code.

Blocking drop table for specific schema using trigger

I’m trying to prevent anyone from dropping tables on a specific schema named “test” unless the user is an rds_superuser but the function I wrote is blockin on all schemas.

CREATE OR REPLACE FUNCTION guard_tables() RETURNS event_trigger LANGUAGE plpgsql AS $  $   BEGIN IF TG_TABLE_SCHEMA = 'test' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN RAISE EXCEPTION 'command % is disabled for this table', tg_tag; END IF; END; $  $  ; 

How do I get this to work?

Schema changes on sharded database

I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5. It is already partitioned. I am working on optimising it here and there, but that is not trivial and only provides small improvements. Table is constanlty growing and we expect our userbase increace significantly so I need a way to scale up.

I want to use multi-tenant sharding approach. X tenants per shard. Shard resolving on app layer. Most of tenants have relateively small datasets, but few are huge and I want to be able to place them to separate shards . To do that I need lookup tables. Cross-shard queries are not concern at all, naturaly we have almost all of our queris per tenant, so all the data for the tenant will sit in same shard.

I will be using logical sharding, 4 phisical shards x32 logical (that is twice more shards than partitions currenly). Each logical shard is separate database. In most tutorials/talks people seem to use schemas instead of databases. Why? Databases are more isolated, and when moving single tenant or virtual shard to other location it does not seem to have any difference. So db looks like a better candidate to me.

Drawbacks look acceptable: update existing code (significantly), app should be shard aware

The question is: How do I handle migrations(schema changes)?

As first step I will have to create 128 databases, ensuring all of them have all tables, indexes, etc. I also want each of dbs have its own sequences to have ids unique accross all shards. Not trivial to me.

But further changes are problem aswell. Do I just iterate all connections and aplly changes? Is there a better (maybe async) way? What do I do if at some point shema in one shard is different from another.

Is this schema between a Desktop App and an API secure?

From my previous question: How secure is this schema between a Desktop App (c++) and an API (php).

To make it short: Client logs in using the Desktop App (and receives a JWT) and every X seconds/minutes the Desktop App sends this JWT to see if it’s still valid. No webpage is involved, everything is done between the Desktop App sending POST requests and the API answering.

I have 2 .php files: check.php (it’s the one that receives the Requests every X seconds/minutes) and login.php (used once to log in everytime the user opens the Desktop App).

Login.php

Receive username, password and a random value. Check if username and password are okay, then generate and store in DB a JWT using the random value and SharedSecret_1. Send the JWT back to the Desktop App so it can check if it’s valid and proceed to let the user use the software.

Check.php

Receive a JWT and a random value. Check if the JWT is the same than the one in the DB then generate and store in DB a new JWT using the random value and SharedSecret_2. Send the JWT back to the Desktop App so it can check if it’s valid.


I had 4 main problems:

  1. How to make sure the Desktop App knows if the JWT it receives is valid and not faked by the user.
  2. How to make sure the API knows if the data sent by the Desktop App is valid and not faked.
  3. The API needs to send a unique JWT everytime because if it’s always “Y” then user would be able to forward the data send by the Desktop App and fake an answer “Y”.
  4. I don’t want multiple users using the same account at the same time. Only 1 connection per account. (Like in an online game were if someone logs in while you are logged in, you get kicked).

From the answer I came with this idea:

  • Desktop App signs data before sending it solving 2nd problem. (Is it secure if the Desktop App sends the data with a JWT using a SharedSecret?)
  • Both Desktop App and API share a Secret Key (would be different in case I can sign data client-side using JWT) that the API will use to generate the JWT and the Desktop App will use to verify this JWT. Solving 1st problem.
  • A random value is sent by the Desktop App (along the JWT) everytime it performs the checks so the API uses it to generate a different JWT.
  • I solve problem 4 Using different SharedSecrets for “login” and “check”. If a user forwards it to login, it will generate a token with SharedSecret_1 and when the Desktop App verifies this token it will use SharedSecret_2 making it invalid.

My questions are:

  1. Is this approach secure?
  2. Can this random value be known by anyone without risking security? (Because the user would still need the shared secret to be able to generate a valid JWT)
  3. Should the random value be sent inside the JWT?
  4. How “random” should this value be? Is it okay if it’s a simple number from 1 to 100000?

When I’m asking about security I mean against piracy, preventing users from accessing my paid app for free. (I know it can’t be 100% secure, but I want it to be as secure as possible). I’m not taking into account what happens if a user reverses my Desktop App because if this happens then the user will simply remove the checks or will know the shared secret.