Rectangle 27 115

Sign in the user before accessing the database

By default the database in a project in the new Firebase Console is only readable/writeable by authenticated users:

{
  "rules": {
    ".read": "auth != null",
    ".write": "auth != null"
  }
}

Since you're not signing the user in from your code, the database denies you access to the data. To solve that you will either need to allow unauthenticated access to your database, or sign in the user before accessing the database.

The simplest workaround for the moment (until the tutorial gets updated) is to go into the Database panel in the console for you project, select the Rules tab and replace the contents with these rules:

{
  "rules": {
    ".read": true,
    ".write": true
  }
}

This makes your new database readable and writeable by everyone. Be certain to secure your database again before you go into production, otherwise somebody is likely to start abusing it.

For a (slightly) more time-consuming, but more secure, solution, call one of the signIn... methods of Firebase Authentication to ensure the user is signed in before accessing the database. The simplest way to do this is using anonymous authentication:

firebase.auth().signInAnonymously().catch(function(error) {
  // Handle Errors here.
  var errorCode = error.code;
  var errorMessage = error.message;
  // ...
});

And then attach your listeners when the sign-in is detected

firebase.auth().onAuthStateChanged(function(user) {
  if (user) {
    // User is signed in.
    var isAnonymous = user.isAnonymous;
    var uid = user.uid;
    var userRef = app.dataInfo.child(app.users);

    var useridRef = userRef.child(app.userid);

    useridRef.set({
      locations: "",
      theme: "",
      colorScheme: "",
      food: ""
    });

  } else {
    // User is signed out.
    // ...
  }
  // ...
});

Thanks - used the insecure fix and quoted your answer in a reply to a similar question to progress past Firebase permission issues in this Ember tutorial. But where do we add the (secure) anonymous auth code?

javascript - Firebase Permission Denied - Stack Overflow

javascript firebase firebase-database firebase-security
Rectangle 27 36

If the user send key "k1" then I upsert it to the database. Is this co...

The PUT method requests that the enclosed entity be stored under the supplied Request-URI. If the Request-URI refers to an already existing resource, the enclosed entity SHOULD be considered as a modified version of the one residing on the origin server. If the Request-URI does not point to an existing resource, and that URI is capable of being defined as a new resource by the requesting user agent, the origin server can create the resource with that URI.

I therefore think that the use of PUT for an insert or update is perfectly legitimate, provided that in both cases the URI is known in advance. If you're using the key as part of the URI (as k1 in http://www.somewhere.com/resources/k1) this should be the case. To be ideally RESTful, however, a GET to the same URL should also allow you to download the resource.

I don't think this operation could be considered RESTful because it does two things. It seems to be providing a macro to satisfy the needs of a particular client, rather than simple access to data. A standard RESTful design would be

  • Getting a list of keys by sending a GET to the parent URL. In the example above, that would be http://www.somewhere.com/resources;

It's less clear cut, but I think it would also be legitimate to delete all resources by sending a single DELETE request to http://www.somewhere.com/resources.

api - In REST is POST or PUT best suited for upsert operation? - Stack...

api rest post put
Rectangle 27 753

It appears that your package manager failed to create the database named $user for you. The reason that

psql -d template1

works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights assigned to you by the database. Try this at a shell prompt:

createdb

and then see if you can log in again with

This will simply create a database for your login user, which I think is what you are looking for. If createdb fails, then you don't have enough rights to make your own database, and you will have to figure out how to fix the homebrew package.

$ createdb -h localhost
could not connect to database postgres: could not connect to server
psql -h localhost

I still get a password prompt'd and have no idea which one is it, which one is the default postgres app password?

Thanks @Kirk. Do we really need -d template1 in your first command? I've seen "template1" in tutorials all over the Internet but it only serves to confuse me. A more logical approach would be, in my opinion 1) Create the PostgreSQL user e.g. "usera" 2) Create a database with the same name as the user "usera" (I think this is crazy but it seems PostgreSQL requires it) 3) Log into PostgreSQL as the super user "postgres" and assign the privileges of database "usera" to the user "usera" (oh my god, is this really the real life?)

@eric : The -d template1 is only there to check that the OP could log in at all. Since it is created at initdb time, it always exists, and was an easy check. From that point forward, your procedure is generally "real life".

@ExiRe You just cured a massive headache I was having with one simple command. Thanks!

postgresql - psql: FATAL: database "" does not exist - Stack Ove...

postgresql psql
Rectangle 27 751

It appears that your package manager failed to create the database named $user for you. The reason that

psql -d template1

works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights assigned to you by the database. Try this at a shell prompt:

createdb

and then see if you can log in again with

This will simply create a database for your login user, which I think is what you are looking for. If createdb fails, then you don't have enough rights to make your own database, and you will have to figure out how to fix the homebrew package.

$ createdb -h localhost
could not connect to database postgres: could not connect to server
psql -h localhost

I still get a password prompt'd and have no idea which one is it, which one is the default postgres app password?

Thanks @Kirk. Do we really need -d template1 in your first command? I've seen "template1" in tutorials all over the Internet but it only serves to confuse me. A more logical approach would be, in my opinion 1) Create the PostgreSQL user e.g. "usera" 2) Create a database with the same name as the user "usera" (I think this is crazy but it seems PostgreSQL requires it) 3) Log into PostgreSQL as the super user "postgres" and assign the privileges of database "usera" to the user "usera" (oh my god, is this really the real life?)

@eric : The -d template1 is only there to check that the OP could log in at all. Since it is created at initdb time, it always exists, and was an easy check. From that point forward, your procedure is generally "real life".

@ExiRe You just cured a massive headache I was having with one simple command. Thanks!

postgresql - psql: FATAL: database "" does not exist - Stack Ove...

postgresql psql
Rectangle 27 348

You should not need to query the database directly for the current App...

That introduces a new dependency of having an extra context for starters, but going forward the user database tables change (3 times in the past 2 years) but the API is consistent. For example the users table is now called AspNetUsers in Identity Framework, and the names of several primary key fields kept changing, so the code in several answers will no longer work as-is.

Another problem is that the underlying OWIN access to the database will use a separate context, so changes from separate SQL access can produce invalid results (e.g. not seeing changes made to the database). Again the solution is to work with the supplied API and not try to work-around it.

The correct way to access the current user object in ASP.Net identity (as at this date) is:

var user = UserManager.FindById(User.Identity.GetUserId());

or, if you have an async action, something like:

var user = await UserManager.FindByIdAsync(User.Identity.GetUserId());

FindById requires you have the following using statement so that the non-async UserManager methods are available (they are extension methods for UserManager, so if you do not include this you will only see FindByIdAsync):

using Microsoft.AspNet.Identity;

If you are not in a controller at all (e.g. you are using IOC injection), then the user id is retrieved in full from:

System.Web.HttpContext.Current.User.Identity.GetUserId();

If you are not in the standard Account controller you will need to add the following (as an example) to your controller:

/// <summary>
    /// Application DB context
    /// </summary>
    protected ApplicationDbContext ApplicationDbContext { get; set; }

    /// <summary>
    /// User manager - attached to application DB context
    /// </summary>
    protected UserManager<ApplicationUser> UserManager { get; set; }
this.ApplicationDbContext = new ApplicationDbContext();
    this.UserManager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(this.ApplicationDbContext));
ApplicationUser user = System.Web.HttpContext.Current.GetOwinContext().GetUserManager<ApplicationUserManager>().FindById(System.Web.HttpContext.Current.User.Identity.GetUserId());

When using EF and Identity Framework with Azure, over a remote database connection (e.g. local host testing to Azure database), you can randomly hit the dreaded error: 19 - Physical connection is not usable. As the cause is buried away inside Identity Framework, where you cannot add retries (or what appears to be a missing .Include(x->someTable)), you need to implement a custom SqlAzureExecutionStrategy in your project.

@TBA - thanks, I realized later it's an extension method. Need to add Microsoft.AspNet.Identity using . thanks again

Type or namesapce UserStore could not be found.I added using Microsft.AspNet.Indentity

@Zapnologica: That sounds like a new question (suggest you post it). You can extend the ApplicationUser class (application specific) and the AspNetUsers table in parallel and they will provide any new fields. Again: Do not hit the database directly! :)

@LifeH2O: The ApplicationUser returned by FindById is your class, complete with your extra properties. Please try it.

c# - ASP.NET MVC 5 - Identity. How to get current ApplicationUser - St...

c# asp.net-mvc-5 identity asp.net-identity
Rectangle 27 342

You should not need to query the database directly for the current App...

That introduces a new dependency of having an extra context for starters, but going forward the user database tables change (3 times in the past 2 years) but the API is consistent. For example the users table is now called AspNetUsers in Identity Framework, and the names of several primary key fields kept changing, so the code in several answers will no longer work as-is.

Another problem is that the underlying OWIN access to the database will use a separate context, so changes from separate SQL access can produce invalid results (e.g. not seeing changes made to the database). Again the solution is to work with the supplied API and not try to work-around it.

The correct way to access the current user object in ASP.Net identity (as at this date) is:

var user = UserManager.FindById(User.Identity.GetUserId());

or, if you have an async action, something like:

var user = await UserManager.FindByIdAsync(User.Identity.GetUserId());

FindById requires you have the following using statement so that the non-async UserManager methods are available (they are extension methods for UserManager, so if you do not include this you will only see FindByIdAsync):

using Microsoft.AspNet.Identity;

If you are not in a controller at all (e.g. you are using IOC injection), then the user id is retrieved in full from:

System.Web.HttpContext.Current.User.Identity.GetUserId();

If you are not in the standard Account controller you will need to add the following (as an example) to your controller:

/// <summary>
    /// Application DB context
    /// </summary>
    protected ApplicationDbContext ApplicationDbContext { get; set; }

    /// <summary>
    /// User manager - attached to application DB context
    /// </summary>
    protected UserManager<ApplicationUser> UserManager { get; set; }
this.ApplicationDbContext = new ApplicationDbContext();
    this.UserManager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(this.ApplicationDbContext));
ApplicationUser user = System.Web.HttpContext.Current.GetOwinContext().GetUserManager<ApplicationUserManager>().FindById(System.Web.HttpContext.Current.User.Identity.GetUserId());

When using EF and Identity Framework with Azure, over a remote database connection (e.g. local host testing to Azure database), you can randomly hit the dreaded error: 19 - Physical connection is not usable. As the cause is buried away inside Identity Framework, where you cannot add retries (or what appears to be a missing .Include(x->someTable)), you need to implement a custom SqlAzureExecutionStrategy in your project.

@TBA - thanks, I realized later it's an extension method. Need to add Microsoft.AspNet.Identity using . thanks again

Type or namesapce UserStore could not be found.I added using Microsft.AspNet.Indentity

@Zapnologica: That sounds like a new question (suggest you post it). You can extend the ApplicationUser class (application specific) and the AspNetUsers table in parallel and they will provide any new fields. Again: Do not hit the database directly! :)

@LifeH2O: The ApplicationUser returned by FindById is your class, complete with your extra properties. Please try it.

c# - ASP.NET MVC 5 - Identity. How to get current ApplicationUser - St...

c# asp.net-mvc-5 identity asp.net-identity
Rectangle 27 9

This error could also appear if your Entity Framework database initializers are not compatible with the permissions of the database user that your Azure Mobile Service is using.

For example, when you create database for a Azure Mobile Service, Azure automatically creates a DB user for your service. This user does not have admin permissions - it can generally read and write data to a table. In that case, if you are using DropCreateDatabaseAlways DB initializer your user will not have sufficient permission to actually drop the database and you may see the error that you have mentioned.

There are new initializers that were introduced to work with limited set of permissions:

  • ClearDatabaseSchemaAlways - use instead of DropCreateDatabaseAlways
  • ClearDatabaseSchemaIfModelChanges - use instead of DropCreateDatabaseIfModelChanges

I was having problems running an Azure Mobile Service in which I had a database with two schemas because another Azure Mobile Service was also using the same database (each service automatically gets its own schema). So, using ClearDatabaseSchemaAlways<T> (or ClearDatabaseSchemaIfModelChanges<T>) instead of the usual DropCreateDataBaseAlways<T> is what fixed it for me! Thanks!

c# - Azure Mobile Service login error with database and "master" user ...

c# sql-server azure azure-sql-database azure-mobile-services
Rectangle 27 794

It appears that your package manager failed to create the database named $user for you. The reason that

psql -d template1

works for you is that template1 is a database created by postgres itself, and is present on all installations. You are apparently able to log in to template1, so you must have some rights assigned to you by the database. Try this at a shell prompt:

createdb

and then see if you can log in again with

This will simply create a database for your login user, which I think is what you are looking for. If createdb fails, then you don't have enough rights to make your own database, and you will have to figure out how to fix the homebrew package.

$ createdb -h localhost
could not connect to database postgres: could not connect to server
psql -h localhost

I still get a password prompt'd and have no idea which one is it, which one is the default postgres app password?

Thanks @Kirk. Do we really need -d template1 in your first command? I've seen "template1" in tutorials all over the Internet but it only serves to confuse me. A more logical approach would be, in my opinion 1) Create the PostgreSQL user e.g. "usera" 2) Create a database with the same name as the user "usera" (I think this is crazy but it seems PostgreSQL requires it) 3) Log into PostgreSQL as the super user "postgres" and assign the privileges of database "usera" to the user "usera" (oh my god, is this really the real life?)

@eric : The -d template1 is only there to check that the OP could log in at all. Since it is created at initdb time, it always exists, and was an easy check. From that point forward, your procedure is generally "real life".

@ExiRe You just cured a massive headache I was having with one simple command. Thanks!

postgresql - psql: FATAL: database "" does not exist - Stack Ove...

postgresql psql
Rectangle 27 510

You probably need to use the system user postgres to create your database - as long as you haven't set up (and given the necessary privileges to) a database user named "h9uest" that corresponds to your system user of the same name, so it is enabled by the default ident authentication method.

sudo su - postgres
sudo -u postgres -i

As recommended here and here.

Then try again. Type exit when done with operating as system user postgres.

@majioa: Obviously, the system user as which you are executing this needs the privilege to do so.

Answer of drees below solves the promblem in common.

su: unknown login: postgres

authentication - PostgreSQL error: Fatal: role "username" does not exi...

postgresql authentication
Rectangle 27 489

You probably need to use the system user postgres to create your database - as long as you haven't set up (and given the necessary privileges to) a database user named "h9uest" that corresponds to your system user of the same name, so it is enabled by the default ident authentication method.

sudo su - postgres
sudo -u postgres -i

As recommended here and here.

Then try again. Type exit when done with operating as system user postgres.

@majioa: Obviously, the system user as which you are executing this needs the privilege to do so.

Answer of drees below solves the promblem in common.

@majioa: Looks like an unrelated problem. Start a new question with details if you can't solve it.

su: unknown login: postgres

authentication - PostgreSQL error: Fatal: role "username" does not exi...

postgresql authentication
Rectangle 27 484

You probably need to use the system user postgres to create your database - as long as you haven't set up (and given the necessary privileges to) a database user named "h9uest" that corresponds to your system user of the same name, so it is enabled by the default ident authentication method.

sudo su - postgres
sudo -u postgres -i

As recommended here and here.

Then try again. Type exit when done with operating as system user postgres.

@majioa: Obviously, the system user as which you are executing this needs the privilege to do so.

Answer of drees below solves the promblem in common.

su: unknown login: postgres

authentication - PostgreSQL error: Fatal: role "username" does not exi...

postgresql authentication
Rectangle 27 5

Think about the database

The use of manual references is perfectly fine - recommended even. Only use DbRefs if they offer specific benefits - such as permitting automatic de-referencing (a mongo script or php code can automatically know that foo points at collection x, with id y [in database z]).

Having what is effectively a foreign key in one collection, and it being a different type from that of the collection it's pointing at is a bad design. Type juggling is something to eliminate from development in general, not introduce.

Code like this ran on the db should work:

form = db.forms.findOne();
user = db.users.findOne({_id: form.user_id});

And it shouldn't matter what type the users collection _id field is. With the schema in the question this code becomes:

form = db.forms.findOne();
user = db.users.findOne({_id: new MongoId(form.user_id)});

In and of itself, that's not a huge difference, but it means you have to think/remember to convert these references and it becomes problematic if/when a collection is created which uses a different type - it introduces inconsistencies.

> school = db.schools.insert({_id: 123, name: "my school"});
> userId = new ObjectId();
> db.users.insert({_id: userId, name: "Me"});
> db.forms.insert({user_id: userId, school_id: 123});

Let's say that the school id is a unique code. if there's no possibility it could change - it's appropriate and a good idea to use as the _id field. Now, the school id and the user id are different types:

> db.forms.findOne();
{
"_id" : ObjectId("508940370392baf87e68e31d"),
"user_id" : ObjectId("5089401c0392baf87e68e31b"),
"school_id" : 123
}

If they are stored as-is, it's still possible to be completely ignorant of these different types in queries:

form = db.forms.findOne();
school = db.schools.findOne({_id: form.school_id});
user = db.users.findOne({_id: form.user_id});

If different types are used that means that it becomes necessary to think "with this collection I need to convert the string to an ObjectId, but with this one I must not".

That's a problem that could have been avoided, but instead - it was introduced.

A very good point, in a very good answer! In this case I will stick with storing the key as a string rather than en ObjectId as I am unsure if indexing the uid field in the forms collection will be as good if the field are of type ObjectId. Any thoughts on that? Consequences for indexing I mean.

There's an existing question which covers that. Object ids are smaller, and therefore faster to compare

Yeah good point about type juggling.Let me see if I can clean up my schema design.Thanks :)

@AD7six Sry for the follow-up. Just to make sure. Based on the examples in my question, you would go for alternative 2, using new Mongoid() when storing the uid, and not as string? Thanks.

Yes, store forms.uid exactly the same as users._id

php - MongoDB, Using Mongodb ObjectID between collections - Stack Over...

php mongodb
Rectangle 27 424

Grant usage/select to a single table

If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.

To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view:

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created.

You should put your edit concerning PG9 at the top of the post.

Nice. One thing I'd add is that you may also need to allow sequences to be read by this user; so: GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO xxx;

Note that to prevent this user from being able to create new tables, I had to REVOKE CREATE ON SCHEMA public FROM PUBLIC;. Without that, the "read only" user couldn't modify existing tables, but could create new tables in the schema and add/remove data from those tables.

@Ajedi32 This should be part of the accepted answer! Thanks

For the newbies like me I think it's worth mentionning that you should start the console using psql mydb otherwise most of these manipulations wouldn't do. It personally took me a considerable amount of time to figure it out by my own. Hope this helps someone.

How do you create a read-only user in PostgreSQL? - Stack Overflow

postgresql
Rectangle 27 76

When you create a new MVC 5 application and choose "Individual User Accounts", a new ASP.NET Identity Provider is included which uses Entity Framework 6 Code-First.

Microsoft has adopted EF-Code-First to make Identity as customizable as possible.

When Identity is accessed for the first time, Entity Framework checks to see if the database exists. Unless configured otherwise, it uses the "DefaultConnection" to find the identity database. If the database does not exist when Identity is called, EF automatically created the database.

`AttachDbFilename=|DataDirectory|\aspnet-WebApplication3-20140417072624.mdf`

If you double click on this .mdf file, the VS2013 Server Explorer will open your DB. If you have already attempted to access any Identity functionality, you will these tables created:

By default, your app is configured to use SQL Server Compact (MDF file) so you don't have to have an actual SQL Server Instance running. All of this is customizable. The name of your MDF file, the schema of Identity Database, the choice of SQL Compact vs an actual SQL Server instance. Change your Connection String, or create a new one and pass this new connection to your context.

All this is well and good, but an important question you asked is basically "Where is my context?", and the just as relevant implied questions regarding how you can further customize your DB or alter validation logic.

Microsoft.AspNet.Identity.EntityFramework
IdentityDBContext<TUser>
UserManager

Open your AccountController, and notice the constructor has UserManager object passed which in turn has a new UserStore object passed, which gets passed a ApplicationDbContext.

public AccountController()
        : this(new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext())))

The ApplicationDbContext is defined in your Models Folder. Inside that folder, you will find an IdentityModels.cs file. Open it and you will see

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection")
    {
    }
}

This is where your Identity Context is assigned. you can change the connection name passed to the ApplicationDbContext constructor, or define and use a different context in your account controller.

Another class defined IN IdentityModels.cs file is the ApplicationUser class which inherits from IdentityUser class.

public class ApplicationUser : IdentityUser
{
}

Any properties you add to this class will be persisted in your ASPNetUsers Table. The rest of the schema is defined in IdentityDbContext class. So, while you can add more tables (e.g. Privileges) to your Identity Schema by adding a DBSet to the Context Definition,

public DBSet<Privileges> { get; set; }

Altering other tables (Roles, Claims, etc) is also possible, but far more involved. For example, to customize the Roles table, you would have to implement a NewIdentityRole inheriting from IdentityRole and add it's relationship by overriding the OnModelCreating() method for your Context.

This article on Customizing Roles Tables does a good job of describing the steps involved. Even here, you will find that there is significant trouble invested into simply ADDING new columns. Removing tables or columns from the original schema created in the IdentityDbContext class is probably as much trouble as creating your own implementation of IdentityDbContext class.

Dave. Thanks for your help and advice. I checked and I think some of your explanation no longer applies to the latest templates used with MVC5 and the VS2013 Update 2. This combination uses ASP.NET Identity 2 but I think some of your explanation refers to the older Identity model.

@Melina, please expand on that. I described the environment I'm on which I think has all the latest nuget updates. If any advances have happened I'm not aware of, I'd like to understand more...

I just checked a bit more into what the RC for update 2 offers and I think I will stay away from that for a while. As far as I can see the account controller appears not to be finished. They made a lot of changes on the account controller but it appears to lack a login method and when I start up the same application there's no "login" or "register" links to click. Seems like the RC is a WIP :-( Will accept your answer as it applies to what most people are using. thanks

How does a new ASP.NET MVC 5 application know how to create a database...

asp.net asp.net-mvc entity-framework asp.net-mvc-5 asp.net-identity
Rectangle 27 5

Login roles are common for all databases in a server. You can see them in the bottom of the object browser (left panel). To execute arbitrary SQL query open Query tool (Ctrl-E) from Tools in main menu or click on icon with 'SQL' (previously you have to select a database). To change user password execute SQL:

ALTER ROLE username PASSWORD 'newpassword'
ALTER USER
ALTER ROLE

As my edit was rejected, I will leave this here as a comment. It is required to pass the newpassword within single quotes, e.g., ALTER ROLE username PASSWORD 'newpassword'. This is shown in the linked docs, but is not obvious in the answer itself.

postgresql - View database user and password in PgAdmin - Stack Overfl...

postgresql pgadmin
Rectangle 27 35

Create the user with a password :

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Then grant him rights on a specific database :

Then grant the user rights on a specific database :

grant all privileges on database db_name to someuser;

I have already done this: create user ravi with password 'ravi'; grant all privileges on database nominatim to ravi; But I can not connect with following PHP code: <?php $connString = 'host=localhost port=5432 dbname=nominatim user=ravi password=ravi'; $connHandler = pg_connect($connString); echo 'Connected to '.pg_dbname($connHandler); ?>

@Darji Krunal: what's the PHP error, how does it look like?

php - How to create user for a db in postgresql? - Stack Overflow

php database postgresql user connection
Rectangle 27 7

I would most probably create a table of the following structure:

The exact types of course depend on your needs (and of course on the dbms you are using). You could also use the NumberValue (decimal) field for int's and booleans. You may need other types as well.

You need some link to the Master records which own the value. It's probably easiest and fastest to create a user fields table for each master table and add a simple foreign key. This way you can filter master records by user fields easily and quickly.

You may want to have some kind of meta data information. So you end up with the following:

Whatever you do, I would not change the table structure dynamically. It is a maintenance nightmare. I would also not use XML structures, they are much too slow.

I like your strategy, and maybe opt for it but in 2017, Will you opt for something different? like json

In our project, we implemented our own data structures which serialize to something similar to json. It features a typesave interface to read and write data without casting and with great programming language integration. That's really great. It has the same problem as all of this kind of "documents" in databases. It's hard to query for spcific values and it cannot easily reference data outside of the "document". Depending on the usage, both is not even an issue.

Besides of that, what I proposed in 2011 is IMHO still a valid solution.

sql - How to design a database for User Defined Fields? - Stack Overfl...

sql database design user-defined-fields
Rectangle 27 4

As a rule of thumb, don't save files in the database.

With Web servers, store images and other binary assets as files, with the path name stored in the database rather than the file itself. Most Web servers are better at caching files than database contents, so using files is generally faster. (Although you must handle backups and storage issues yourself in this case.)

Works fine, but take so much time that i expected. Hence, image are 33% bigger size, and totally looks bulgy.

As you discovered, unwanted overhead in encoding/decoing + extra space used up which means extra data transfer back and forth as well.

down vote As @mike-m has mentioned. Base64 encoding is not a compression method. Why use Base64 encoding is also answered by a link that @mike-m posted What is base 64 encoding used for?.

In short there is nothing to gain and much to loose by base64 encoding images before storing them on the file system be it S3 or otherwise.

What about Gzip or other forms of compression without involving base64. Again the answer is that there is nothing to gain and much to lose. For example I just gzipped a 1941980 JPEG image and saved 4000 bytes that's 0.2% saving.

The reason is that images are already in compressed formats. They cannot be compressed any further.

When you store images without compression they can be delivered directly to browsers and other clients and they can be cached. If they are compressed (or base64 encoded) they need to be decompressed by your app.

Modern browsers are able to display base64 images embedded to the HTML but then they cannot be cached and the data is about 30% larger than it needs to be.

User can post there data and image and all are secure.

I presume that you mean a user can download images that belong to him or shared with him. This can be easily achieved by savings the files off the webspace in the file system and saving only the path in the database. Then the file is sent to the client (after doing the required checks) with fpassthru

How they take care about images file. In performance issue, when large user involved, it seams to me, i need 100000 folder for 100000 user and their sub folder. When large amount of user browse same root folder, how file system process each unique folder.

Use a CDN or use a file system that's specially suited for this like BTRFS

Yes Indeed. Use it to the fullest by saving all the information about the file and it's file path in the database. Then save the file itself in the file system. You get best of both worlds.

php - Slowness found when base 64 image select and encode from databas...

php mysql angularjs ionic-framework base64
Rectangle 27 23

The role userAdminAnyDatabase gives the user the ability to create users and assign arbitrary roles to them. Because of this, that user has the power to do anything on the database, because he can give anybody any permission (including himself).

However, the userAdminAnyDatabase role by itself doesn't allow the user to do anything else besides assigning arbitrary rights to arbitrary users. To actually do something on the database, that user needs to have the following additional roles:

readWriteAnyDatabase
dbAdminAnyDatabase
clusterAdmin

A user who has the above three rights and userAdminAnyDatabase is a true super-user and can do anything.

mongodb - Create Superuser in mongo - Stack Overflow

mongodb permissions user database nosql
Rectangle 27 7

Someone could inject SQL to cause an authorization check to return the equivalent of true instead of false to get access to things that should be off-limits.

Or they could inject a join of a catalog table to itself 20 or 30 times to bring database performance to a crawl.

Or they could call a stored procedure that runs as a different database user that does modify data.

If my database user is read only, why do I need to worry about sql inj...

database sql-injection