I&#39;ve lost count of the amount of times I&#39;ve forgotten the syntax for creating users, databases and permissions within postgresql. After spending so many years working with MySQL it became muscle memory and for some reason I can&#39;t shake those commands. Unlike MySQL, which I used to host myself and understand the internals of security/performance, I&#39;ve only ever used hosted versions of PostgreSQL where all of this is taken care of.
Create a new database and a user to manage it:
<div class="highlight"><pre><code class="language-sql">CREATE DATABASE marcs_stash;
CREATE USER marc WITH PASSWORD &#x27;s3cr3t&#x27;;
GRANT ALL PRIVILEGES ON DATABASE marcs_stash TO marc;
ALTER DATABASE marcs_stash OWNER TO marc;</code></pre></div>I also find myself creating read-only users for contractors, internal teams and third part analytics tools so I&#39;ll share those here as well.
<div class="highlight"><pre><code class="language-sql">CREATE USER spy WITH ENCRYPTED PASSWORD &#x27;s3cr3t&#x27;;
GRANT CONNECT ON DATABASE stuff TO spy;
GRANT USAGE ON SCHEMA public TO spy;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO spy;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO spy;</code></pre></div>I&#39;ll likely be updating this as I go and learn more about PostgreSQL permissions and security. Comments and improvement suggestions are always welcome.

I've lost count of the amount of times I've forgotten the syntax for creating users, databases and permissions within postgresql. After spending so many years working with MySQL it became muscle memory and for some reason I can't shake those commands. Unlike MySQL, which I used to host myself and understand the internals of security/performance, I've only ever used hosted versions of PostgreSQL where all of this is taken care of.

Create a new database and a user to manage it:

``` sql
CREATE DATABASE marcs_stash;
CREATE USER marc WITH PASSWORD 's3cr3t';
GRANT ALL PRIVILEGES ON DATABASE marcs_stash TO marc;
ALTER DATABASE marcs_stash OWNER TO marc;
```

I also find myself creating read-only users for contractors, internal teams and third part analytics tools so I'll share those here as well.

``` sql
CREATE USER spy WITH ENCRYPTED PASSWORD 's3cr3t';
GRANT CONNECT ON DATABASE stuff TO spy;
GRANT USAGE ON SCHEMA public TO spy;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO spy;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO spy;
```

I'll likely be updating this as I go and learn more about PostgreSQL permissions and security. Comments and improvement suggestions are always welcome.

PostgreSQL Create User