_jhqp · HackerTrans

> why the SQL API even allows injection vulnerability

How would one implement this?

"SQL APIs" use prepared statements. Meaning you have a string for SQL and some dynamic variables that inject into that string via $1, $2 etc.

BUT now if developer makes that string dynamic via a variable, then you have SQL injection again.

_jhqp

2 anni fadiscuss

Literally what PostgreSQL does.

E.g. you can do manual selects from internal tables to see the same content as `\dt` command for example.

_jhqp

2 anni fadiscuss

Good point. Connecting to the Cluster is indeed actually a 4th layer.

There you do need user with LOGIN, valid password & SSL.

_jhqp

2 anni fadiscuss

You can help out by posting answer / upvoting good answer.

_jhqp

2 anni fadiscuss

Then I would argue for trying to centrally ensure that all roles have INHERIT in your DBs. :thinking:

Doing ALTER DEFAULT PRIVILEGES could be another future footgun of it's own.

_jhqp

2 anni fadiscuss

What do you mean exactly?

If roles have INHERIT, then doing the following works, no?

* Role A creates table * GRANT A TO B; * ROLE B can read from table just like A can.

Also if Role A creates new table, Role B can read that too no?

_jhqp

2 anni fadiscuss

It's a bit confusing and legacy.

All roles function like you would expect groups to function

A role that is not allowed to login is a `group`.

While the CREATE USER and CREATE GROUP commands still exist, they are simply aliases for CREATE ROLE.

_jhqp

2 anni fadiscuss

Everything in PostgreSQL is a role.

It's just named such that when a ROLE allows `login` it's considered a user

_jhqp

2 anni fadiscuss

Here's your cookbook: https://stackoverflow.com/questions/78401179/how-do-postgres...

_jhqp

2 anni fadiscuss

I agree with the sentiment that IAM in PostgreSQL is complex.

What makes it complex is that there are 3 layers of objects (Database, Schema, Tables) and also implicit grants given to DB object owners

To be able to select from a table you need:

* CONNECT on the Database

* USAGE on the Schema (Given implicitly to schema owner)

* SELECT on the Table (Given implicitly to table owner)

To see these privileges we need to understand acl entries of this format

`grantee=privilege-abbreviation[]/grantor:`

* Use \l+ to see privileges of Database

* Use \dn+ to see privileges of Schemas

* Use \dp+ to see privileges of Tables

Privileges are seen [here](https://www.postgresql.org/docs/current/ddl-priv.html)

e.g. in the following example user has been given all permissions by postgres role

`user=arwdDxt/postgres`

If the “grantee” column is empty for a given object, it means the object has default owner privileges (all privileges) or it can mean privileges to PUBLIC role (every role that exists)

`=r/postgres`

Also it's confusing when Public schema is used. You have CREATE permission on schema so when the tables are created with the same user you select data with and you have owner permissions out of the box.

comments