January | 2017 | pseudo-scripter

PostgreSQL – Create users and assign roles

January 25, 2017 Rodnee Leave a comment

1. Create role.

CREATE ROLE myuser LOGIN PASSWORD 'mypassword';

2. Alter role.

ALTER ROLE myuser CREATEDB CREATEROLE;

3. Create database.

$ createdb mydatabase -U myuser -h 127.0.0.1 -p 5432;

4. Connect as new user.

psql -h 127.0.0.1 -p 5432 -U myuser;

5. Delete role

DROP OWNED BY myuser cascade;
DROP ROLE myuser;

$ sudo su postgres
$ psql postgres
psql (9.4.10)
Type "help" for help.

postgres=# CREATE ROLE myuser LOGIN PASSWORD 'mypassword';
CREATE ROLE
postgres=# \dg
                                List of roles
   Role name    |                   Attributes                   | Member of
----------------+------------------------------------------------+-----------
 myuser         |                                                | {}
 postgres       | Superuser, Create role, Create DB, Replication | {}
 
 vagrant        |                                                | {}

postgres=# ALTER ROLE myuser CREATEDB CREATEROLE;
ALTER ROLE
postgres=# \dg
                                List of roles
   Role name    |                   Attributes                   | Member of
----------------+------------------------------------------------+-----------
 myuser         | Create role, Create DB                         | {}
 postgres       | Superuser, Create role, Create DB, Replication | {}
 
 vagrant        |                                                | {}

postgres=#\q



$ psql -h 127.0.0.1 -p 5432 -U myuser;
Password for user myuser:
psql: FATAL:  database "myuser" does not exist



$ createdb mydatabase -U myuser -h 127.0.0.1 -p 5432; 
Password:



$ psql -h 127.0.0.1 -p 5432 -U myuser -d mydatabase; 
Password for user myuser:
psql (9.4.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=> \dt
No relations found.

mydatabase=> \q

Source:
https://www.postgresql.org/docs/current/static/sql-createrole.html
http://grokbase.com/t/postgresql/pgsql-patches/068m331a82/drop-owned-by-doesnt-work

Categories: notes, postgresql Tags: createdb, createrole

end-to-end-encryption-with-openshift-part-1-two-way-ssl

January 25, 2017 Rodnee Leave a comment

This is the first part of a 2 part article, part 2 (End To End Encryption With OpenShift Part 2: Re-encryption) will be authored by Matyas Danter, Sr Consultant with Red Hat, it will be published soon. This article aims to demonstrate use cases for Openshift routes to achieve end-to-end encryption. This is a desirable and sometimes…

via End To End Encryption With OpenShift Part 1: Two-Way SSL — RHD Blog

Categories: Interesting