In Postgres and Oracle schemas and users (roles) are different entities.
When you create a user in Oracle it is a schema at the same time. You can create objects in it. When you create a role in Oracle it is just used for privilege management and you can not login into database with a role.
Users (and roles) in PostgreSQL are global objects and are not defined in a database but on the instance level.
Schemas are created by users in a specific database and contain database objects. Users are not schemas. if you want a schema with the same name you have to create it separately. The default schema in PostgreSQL is public. Your default schema can be seen in search_path in postgreSQL.
Public schema and public role
When a new database is created, PostgreSQL by default creates a schema named public and grants access on this schema to a backend role named public. All new users and roles are by default granted this public role, and therefore can create objects in the public schema.
PostgreSQL uses a concept of a search path. The search path is a list of schema names that PostgreSQL checks when you don’t use a qualified name of the database object. For example, when you select from a table named “mytable”, PostgreSQL looks for this table in the schemas listed in the search path. It chooses the first match it finds. By default, the search path contains the following schemas:
The first name “$user” resolves to the name of the currently logged in user. By default, no schema with the same name as the user name exists. So the public schema becomes the default schema whenever an unqualified object name is used. Because of this, when a user tries to create a new table without specifying the schema name, the table gets created in the public schema. By default, all users have access to create objects in the public schema, and therefore the table is created successfully.
This becomes a problem if you are trying to create a read-only user. Even if you restrict all privileges, the permissions inherited via the public role allow the user to create objects in the public schema.
To fix this, you should revoke the default create permission on the public schema from the public role using the following SQL statement:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Make sure that you are the owner of the public schema or are part of a role that allows you to run this SQL statement.
The following statement revokes the public role’s ability to connect to the database:
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;This makes sure that users can’t connect to the database by default unless this permission is explicitly granted.
Revoking permissions from the public role impacts all existing users and roles. Any users and roles that should be able to connect to the database or create objects in the public schema should be granted the permissions explicitly before revoking any permissions from the public role in the production environment.
Users vs Roles
Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.
To create a PostgreSQL user, use the following SQL statement:
CREATE USER myuser WITH PASSWORD 'secret_passwd';
To create a role with the following SQL statement:
CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';
Both of these statements create the exact same user. This new user does not have any permissions other than the default permissions available to the public role. All new users and roles inherit permissions from the public role.
Read-only role
The first step is to create a new role named readonly using the following SQL statement:
CREATE ROLE readonly;This is a base role with no permissions and no password. It cannot be used to log in to the database.
Grant this role permission to connect to your target database named “mydb”
The next step is to grant this role usage access to your schema. Let’s assume the schema is named myschema:
If the requirement is to grant access on all the tables and views in the schema, then you can use the following SQL:
The preceding SQL statement grants SELECT access to the readonly role on all the existing tables and views in the schema myschema. Note that any new tables that get added in the future will not be accessible by the readonly user. To help ensure that new tables and views are also accessible, run the following statement to grant permissions automatically:
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON TABLE mytable1, mytable2 TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;Read/write role
The process of adding a read/write role is very similar to the read-only role process covered previously. The first step is creating a role:
Grant this role permission to connect to your target database:
Grant schema usage privilege:
If you want to allow this role to create new objects like tables in this schema, then use the following SQL instead of the one preceding:
The next step is to grant access to the tables. As mentioned in the previous section, the grant can be on individual tables or all tables in the schema. For individual tables, use the following SQL:
For all the tables and views in the schema, use the following SQL:
To automatically grant permissions on tables and views added in the future:
For read/write roles, there is normally a requirement to use sequences also. You can give selective access as follows:
You can also grant permission to all sequences using the following SQL statement:
To automatically grant permissions to sequences added in the future:
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydb TO readwrite;
GRANT USAGE ON SCHEMA myschema TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;