Database are fundamental concepts for every RDBMS, but they are easy to forget unless you are dedicated DBA, since most of the time you only need this knowledge when you bootstrap a new project. This kind of situation has happened to me countless times, so in this post, I’m solving this problem once for all, and I hope it could be helpful for you as well.
MySQL is relatively simple, it has no
schema, or to be precisely
schema is synonymous to
database is a kind of namespace containing common database objects like tables, indexes, foreign keys etc. A MySQL instance can hold many databases which are isolated to each other.
User in MySQL is called
user account, which consists of 2 parts: user name and host name, because of this we can have finer control over privileges when same user name connect from different hosts, and typically we need to distinguish between localhost, hosts within the same private network, and public Internet addresses.
MySQL has a default administrative user
root, for self-hosted MySQL instance, you need to provide root password during the installation process, either by config file or command line prompt. With
root user, you can create databases and more administrative users, and typically you need to create several normal users with limited privileges for daily use and application use.
As for Saas MySQL instance, generally venders won’t give you the root password, and you need to create databases and normal users through Web UI.
PostgreSQL always have better compliance with SQL standard comparing to MySQL, for instance, it has
schema with a different meaning from
database. A PostgreSQL database cluster contains multiple named
databases, which are also isolated to each other. And a
database contains more or more schemas, which in turn contains tables and other common database objects.
schema of PostgreSQL means to organize database objects into logical groups, make them more manageable, and allows different users connect to the same database without interfering with each other.
You can CRUD and use schemas like databases or tables:
Schemas in PostgreSQL could be invisible if you do not pay enough attention, because PostgreSQL has 2 very important concepts:
public schema and
schema search path. If you create tables without specifying schema names, they go to the schema named
public by default, and every database contains such a schema unless otherwise explicitly dropped. If you omit schema names when you refer a table, PostgreSQL determines which actual table is by following the
schema search path, which is a list of a schema to look at, the first found is returned.
schema search path is
"$user",public, so the schema with the same name as the current user is searched first, then public schema. So if you want to kill
schema in PostgreSQL and let all users connected to the database share everything, don’t create any schemas and you have it by default. If you want to isolate each user from each other, create a schema for each user with the exact user name, and better off dropping the public schema.
Since PostgreSQL 8.1,
group have been replaced by the merged concept
role, the administrative role is
superuser. For a SaaS PostgreSQL instance, superuser is generally not provided just like MySQL, but you can accomplish most of the operation work through Web UI, like create new user, configure whitelist for remote connection, With these two you should be able to obtain a database connection, then you can carry on creating databases and manipulating schemas via the command line PostgreSQL client
Things get a little more complicated for self-hosted PostgreSQL instances, and you get the chance to touch the details of Postgres’ authenticate protocols. After PostgreSQL has been installed on a Linux machine, you need to run
# postgresql-setup initdb to initiate Postgres, this includes a lot of stuff which all mean to make Postgres ready to use.
PostgreSQL supports a variety of authentication methods and options, and the configuration file for that is
pg_hba.conf, for CentOS it resides in
/var/lib/pgsql/data. You can configure authentication method for incoming connection attempts either from local UNIX sockets or remote SSL/non-SSL TCP sockets, and also for different databases and different roles. The authenticate method could be
trust - absolute trust, no auth needed,
password - client send password across the connection either MD5-hashed or in plain text, and
ident which grab the underlying OS user name for authentication. Kerberos, LDAP, RADIUS and many other industry-level authentication solutions can also be integrated, you can read the official documentation here.
Freshly installed and configured PostgreSQL adds a user named
postgres to the underlying OS, and this corresponds to the very first role in Postgres, which is also a
superuser. From here you create more roles and assign privileges to them, and configure appropriate authentication methods in
Many client apps including
psql implicitly take current OS user name as the
role for establishing Postgres connections, so
roles could be invisible just like
schemas in Postgres, be careful of that.
Oracle also has
databases as logical containers for database objects, but they are strictly bond to
users, which means every time you create a user for Oracle, a
schema is created automatically. Generally you go to your own schema after a database connection is established to Oracle, but you can alter session to another schema, or select data from other schemas if you have privileges granted.
My experience with Oracle is all with big enterprises, when we need an Oracle instance, we file a request, then DBA department assigns us a TNS connect string with everything behind it ready. Nonetheless I’ve ever tried to install Oracle Express Edition manually on a Linux machine, and it was quite miserable. It would be way much easier now as we have Docker and DSC tools at our hands, and I have an Ansible script here.