Pivileges of a new user in PostgreSQL

Question

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.

1. 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?
2. 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.

Answer 1

PostgreSQL manages these things differently from Oracle.

Oracle has all these annoying¹ "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.

---

¹ to me, at least — why can't I create a view on my own table by default?

Answer 2

https://www.postgresql.org/docs/current/sql-createuser.html

CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

https://www.postgresql.org/docs/current/sql-grant.html

GRANT — define access privileges Specific types of privileges, as defined in Section 5.7.

Section 5.7 https://www.postgresql.org/docs/current/ddl-priv.html

5.7. Privileges

When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other roles to use it, privileges must be granted.

<...>

The available privileges are:

SELECT

A whole list of specific privileges and what they mean for different objects