I used to work with Oracle, where after creation of a new database user you need to grant them a few privileges, ex. to connect(CREATE SESSION) to the database or create a table(CREATE TABLE). Otherwise the user is useless and literally can nothing.
- How does it work in Postgres? Why after CREATE USER statement and assigning SCHEMA to him, I’m able to log-in, create schema/ objects and much more things?
- How to display user privileges? I would like to know how powerfull the user is.
Example:
CREATE USER test_user WITH PASSWORD 'abc';
CREATE SCHEMA test_user ;
ALTER SCHEMA test_user OWNER TO test_user ;
Now I can log-in as test_user and do a lot of things in the database.
2
Answers
PostgreSQL manages these things differently from Oracle.
Oracle has all these annoying1 "system privileges"; PostgreSQL manages this via schema permissions: if a user has the
CREATE
privilege on a schema, she can create arbitrary objects in that schema.There is no way to list all privileges of a role in PostgreSQL. You could try an extension like pg_permissions and its
all_permissions
view.1 to me, at least — why can’t I create a view on my own table by default?
https://www.postgresql.org/docs/current/sql-createuser.html
https://www.postgresql.org/docs/current/sql-grant.html
Section 5.7
https://www.postgresql.org/docs/current/ddl-priv.html
<…>