Re: Why psql connection assumes default database name as the username

On Tue, Mar 28, 2017 at 6:00 AM, jesusthefrog <jesusthefrog(at)gmail(dot)com>
wrote:

> This also (intentionally or not) matches Oracle's behavior. That is: by
> default the user connecting is effectively the same as the database you are
> connecting to.
>

Yes, some research on internet indicates that in Oracle the User and the
Schema have the same name, not the same database name though. Also, the
User/Schema name seem to be a database User name not an operating system
username. The sample User/Schema names were 'oe'(order entry), 'hr' (human
resources), etc. Somewhere I also read "User is an account to connect to a
database and a schema is a set of objects (table, view, etc) that belong to
that account".

Can a similar definition be applied in PostgreSQL for User and Database
name mapping, i.e if a database name is same as a user name then the
access privileges for that datbase are guided by the properties defined for
that user in view pg_roles?

Further investigating, here is snippet from PostgreSQL documentation:

"PostgreSQL manages database access permissions using the concept of roles.
Database roles are conceptually completely separate from operating system
users. In practice it might be convenient to maintain a correspondence, but
this is not required.
Every connection to the database server is made using the name of some
particular role, and this role determines the initial access privileges for
commands issued in that connection. The role name to use for a particular
database connection is indicated by the client that is initiating the
connection request in an application-specific fashion. For example, the
psql program uses the -U command line option to indicate the role to
connect as. Many applications assume the name of the current operating
system user by default (including createuser and psql). Therefore it is
often convenient to maintain a naming correspondence between roles and
operating system users."

So the OS username and database name mapping seem to be in place for
convenience as depesz suggested (and probably as an aid to determine the
access privileges for a database by seeing the properties of the role that
has the same name as the database, if present).

Regards,
Neha