PostgreSQL 11 – Can I turn a schema into the wild west?

I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, including to tables they do not own. The reason I have been asked to make the schemas a free-for-all is my supervisor does not want any time spent administering the database beyond me adding/deleting schemas. I am trying to set this up with a PostgreSQL 11 Amazon RDS.

For the test schema, in an attempt to simplify the SQL, I have created a group role and added the users working on the project (each has inherit permissions enabled) to the group. I am then granting permissions to the group at the database and schema level. My hope was that everyone in the group would be able to do anything the group can do, and do it to table(s) created by any other user in the group.

My initial attempt had limited success. I can add a table and the other users can view the table, append data to the table, edit individual cells, and delete rows. However, they are unable to delete the table I created. In addition, the users can only do that to tables I create. If another user creates a table, no one is able to even view the table, even though I set default privileges on the schema before anyone created any tables. I came across the reason for this while trying to make it work and am hoping there is a way around it.

To open things up as much as I can, I took the following actions:

  • Gave the group role full permission to the database with GRANT ALL ON DATABASE dbname TO grpname;

  • After I created the schema I ran: GRANT ALL ON SCHEMA testschema TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON TABLES TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON SEQUENCES TO grpname;

    ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT EXECUTE ON FUNCTIONS TO grpname;

To confirm the reason I found regarding why ALTER DEFAULT PRIVILEGES only gave those privileges to tables I created, I created a new schema with the group role as the owner and added tables as several users, including myself. Since the group role did not create the tables, its members did, no one could view any table other than their own, let alone modify another user’s table.

A final wrinkle involves where this development database is located. It is being added to a server group that includes our main production database so I cannot make all users a superuser. Not sure that would fix things, but thought I would mention it.

Have I missed/overlooked something that would allow a schema free-for-all?

How many areas is reasonable for areaServed schema?

I’m adding some schema to my site and was wondering about the areaServed property. Is this used for SEO in any way? If it is, is it good or bad to cram as much into it as possible?

i.e. if I simply added "Wolverhampton" (UK) as a city, would that be just the same as adding all the surrounding suburbs too? Would there be some benefit/harm in doing this?

"areaServed": [ "Wolverhampton"]  vs.  "areaServed": [ "Wolverhampton", "Wednesfield", "Bushbury", "Penn", "Bilston", "etc etc etc"] 

Schema markup for product categories and subcategories – how to?

I’ve been wondering about which Schema should I use for an e-commerce site of one of my clients’ website. Can’t show proper code and sensitive information, but I’ll try to explain what I’ve been thinking about.

Right here: https://inchoo.net/online-marketing/schema-markup-for-ecommerce-websites/

The author says that for category pages I should use ItemList Schema

<script type="application/ld+json">  {  "@context":"http://schema.org",  "@type":"ItemList",  "ItemListElement":  [    {   "@type":"ListItem",    "position":0,    "url":"https://www.example.com/ankle-boots-ana.html"   },    {    "@type":"ListItem",    "position":1,    "url":"https://www.example.com/ankle-boots-clara.html"    },    {    "@type":"ListItem",    "position":2,    "url":"https://www.example.com/ankle-boots-maria.html"    }   ]  } </script> 

Does anyone have an example of how it would look? For example, if I have a category with electric guitars with 5 subcategories that contain certain electric guitar models which would be listed in the schema ListItem. Is that the best schema type for this instance?

Second question – when it comes to subcategories, should I use ItemList Schema?

<script type="application/ld+json"> {     "@context": "http://schema.org",     "@type": "ItemList",     "url": "https://www.myexamplestore.com/products/",     "numberOfItems": "2",     "itemListElement": [           {             "@type": "ListItem",             "position": 1,             "item": {               "@type": "Product",                   "image": "https://www.myexamplestore.com/product-one/image.jpg",                   "url": "https://www.myexamplestore.com/products/#productone",                   "name": "Product One",                   "offers": {                       "@type": "Offer",                       "price": "13.00",                       "priceCurrency": "USD",                       "url": "https://www.myexamplestore.com/products/"                   }             }           },           {             "@type": "ListItem",             "position": 2,             "item": {               "@type": "Product",                   "image": "https://www.myexamplestore.com/product-two/image.jpg",                   "url": "https://www.myexamplestore.com/products/#producttwo",                   "name": "Product Two",                   "offers": {                       "@type": "Offer",                       "price": "12.00",                       "priceCurrency": "USD",                       "url": "https://www.myexamplestore.com/products/"                   }               }           }     ] } </script> 

I know that it’s a bit of a gray area for Google when it comes to adding product information on category pages – does that apply also for the subcategory pages?

I would be so thankful for Your help and guidance <3 Cheers

Appropriate database schema for heroes’ skills in a Tower Defense game?

In a Tower Defense game, each hero (or character) has some skills. Each skill has some "chance of appearance" (CoA for short) when attacking enemies and if appeared it has some "hit accuracy" (i.e. hit on enemies) (HA for short) and has effects on the enemies (if hit by the skill) (EoH for short).

Examples:

Skill #1: Frost 1, CoA: 50%, HA: 100%, EoH: (1) slow enemy down for 4s and (2) yield 200 damage points.

Skill #2: Frost 2, CoA: 100%, HA: 50%, EoH: (1) make enemy frozen for 8s.

I first tried to breakdown skills into "atomic" effects and come up with tables as below:

enter image description here

All looks good?

But now we want to update Skill #1 a bit to make it more complicated:

Skill #1: Frost 1, CoA: 50%, HA: 100%, EoH: (1) slow enemy down by 40% for 4s and (2) yield 200 damage points. (Please pay attention to the 40% here!)

Or even coming up with more complicated skills:

Skill #3: Frost 3, CoA: 20%, HA: 100%, EoH: (1) frozen enemy for the first 4s then slow enemy down by 50% for next 4s and (2) yield 200 more damage points.

Skill #4: Frost 4, CoA: 10%, attack enemy 3 times continuously, each time: HA: 50%, EoH: (1) frozen enemy for the first 2s then slow enemy down by 50% for next 2s and (2) yield 400 more damage points.

Each effect now has more parameters to be specified — which makes my current schema not fit anymore. Please help deal with this!

The most generic e-shop DB schema

Let’s say I create a schema for an e-shop what potentially sells everything: laptops, phones, tires, snacks etc. Considering this, I cannot create a new table/entity for each good category the shop sells and describe the good’s properties by new attributes.

So, I went to more generic model to describe each product To me, it seems fine but as soon as I’ve tried to implement this solution, it’s appeared to impossible to execute any query like

select names and prices of all laptops where ssd capacity >= 512 and weight < 2kg and price <= 2000 money

How is possible to change this schema and keep the ability to store information about any product?

Postgres: Efficient schema for querying with one exact string match and two range matches

The table I need to query:

CREATE TABLE regions (     state text NOT NULL,     zip_begin text NOT NULL,  -- 9-digit zip code     zip_end text NOT NULL,     date_begin date NOT NULL,     date_end date,      data ..., ) 

There are ~50 states and between 0-5M rows per state. The zip and date ranges might overlap.

The following will be one of the most common queries in my OLTP application:

SELECT data FROM regions WHERE state = {state}   AND {date} BETWEEN date_begin AND date_end   AND {zip} BETWEEN zip_begin AND zip_end 

This query usually yields one row, but may sometimes yield more.

From the Postgres docs, it sounds like a GiST index might do what I need, but I don’t really understand how those work.